作为一名.neter，mssql似乎是所有.net程序员所掌握的第一个关系型数据库
但是随着.net core(.net5+)的逐渐普及，在linux上部署.net程序已经越来越普遍
而mssql对linux的支持却仍然像一坨翔，mssql on linux，不仅缺少一些功能，而且远不如其他sql方便好用（当然，企业授权用户表示很淦
在linux上广为人知的关系型数据库无非mysql和postgresql（以下简称pgsql）,而pgsql拥有着健全的数据类型和更多高级玩法，无疑，我选择了pgsql
更关键的是其pgsql和mssql语法几乎一致，学习成本压根没有，只需要看一眼即可使用
这里不过多追溯其和mssql相近的操作，例如curd，只突出展示pgsql中常用的功能或新功能（较于mssql）
pgsql连接字符串为：Host={0};Username=postgres;Password={1}，默认端口为5432
一.命令行操作 🔗
切换至postgres账户后，输入psql进入终端
仅在某些时期偶尔使用，需要了再来查，DataGrip或navicat不香吗
| 命令 | 说明 |
|---|---|
\l 或 \list |
列出所有数据库。 |
\c [dbname] |
连接到指定数据库。 |
\conninfo |
显示当前连接的信息。 |
\q |
退出 psql 会话。 |
\d |
显示数据库中所有关系（表、视图、序列、索引等）。 |
\dt |
显示当前数据库中的所有表。 |
\ds |
显示当前数据库中的所有序列。 |
\dv |
显示当前数据库中的所有视图。 |
\di |
显示当前数据库中的所有索引。 |
\dn |
显示所有模式。 |
\dp |
显示所有对象的访问权限。 |
\df |
显示所有函数。 |
\dm |
显示所有物化视图。 |
\dC |
显示类型的所有转换。 |
\dT |
显示所有数据类型。 |
\x |
切换扩展显示模式（用于更好地查看宽表数据）。 |
\timing |
切换执行时间显示，显示每个命令的执行时间。 |
\o [file] |
将查询结果输出到文件中。 |
\s |
显示命令历史记录。 |
\s [file] |
将命令历史保存到指定文件。 |
\dn+ |
显示模式的附加信息。 |
\d+ |
显示表的详细信息，包括存储参数。 |
\db |
显示所有表空间。 |
\du |
显示所有角色（用户）。 |
\du+ |
显示所有角色及其属性和成员关系。 |
\dg |
与 \du 相同，显示角色（用户组）。 |
\i [file] |
执行 SQL 文件中的命令。 |
\! [command] |
执行 shell 命令。 |
\echo [text] |
输出字符串。 |
\set [var] [val] |
设置 psql 变量。 |
二.数据的备份和导出 🔗
在mssql中，得益于SSMS（SQL Server Management Studio）的可视化界面，只需要动一动鼠标即可完成备份，并可通过SQL代理来实现定时备份成bak文件的功能
而在pgsql中，无疑shell更好操作，且可与linux中的sh脚本配合使用，能达到自动备份且上传至阿里云oss等功能
备份分为2种,以下是 pg_basebackup 和 pg_dump 的区别和特点的简介
| 特性 | pg_basebackup |
pg_dump |
|---|---|---|
| 备份类型 | 物理备份 | 逻辑备份 |
| 备份内容 | 整个数据库实例，包括所有数据库、表空间和 WAL 日志 | 单个数据库或特定对象（如表、视图、函数等） |
| 备份输出格式 | 数据文件（目录或 tar 格式） | SQL 脚本或自定义格式 |
| 恢复速度 | 快速，因为直接还原数据文件 | 较慢，需要执行每个 SQL 语句 |
| 适用场景 | 灾难恢复、主从复制初始同步 | 数据库迁移、部分数据恢复、逻辑结构分析 |
| 恢复灵活性 | 恢复整个实例 | 可以选择性恢复特定数据库或对象 |
| 细粒度控制 | 无法控制，备份的是整个实例 | 可以指定单个数据库、表或其他对象进行备份 |
| 增量备份支持 | 不支持，需要结合 WAL 日志手动管理 | 不支持 |
| 常用命令示例 | pg_basebackup -U postgres -D /root -F tar -X fetch -P |
pg_dump -U postgres -d postgres -f 1.sql |
| 使用的场景 | 主从同步、灾难恢复快速重建 | 数据导出、迁移、数据分析 |
总结 🔗
pg_basebackup主要用于物理备份，适合于整个数据库实例的快速恢复和主从同步设置。pg_dump提供灵活的逻辑备份，适合用于数据库的迁移、部分数据恢复以及备份的精细化控制。
参数 🔗
pg_dump参数如下
| 参数 | 说明 |
|---|---|
-U username |
指定连接数据库的用户名 |
-d database_name |
指定要备份的数据库名称 |
-f filename |
指定输出文件的路径和名称 |
-F format |
指定输出文件的格式，支持 p（纯文本）、c（自定义格式）、d（目录格式） |
-t table_name |
仅备份指定的表 |
-n schema_name |
仅备份指定的模式 |
-s |
仅备份模式（schema），不包括数据 |
-a |
仅备份数据，不包括模式 |
--data-only |
同 -a，仅备份数据 |
--schema-only |
同 -s，仅备份模式 |
--exclude-table |
排除指定的表 |
--exclude-schema |
排除指定的模式 |
-v |
详细模式，显示执行的 SQL 语句 |
--compress=0-9 |
指定压缩级别（0 到 9，适用于自定义和目录格式） |
-h host |
指定数据库服务器的主机 |
-p port |
指定数据库服务器的端口 |
--blobs |
备份大对象（BLOBs） |
--column-inserts |
使用列名的 INSERT 语句 |
--inserts |
使用传统的 INSERT 语句（而不是 COPY） |
--no-owner |
不在转储文件中设置对象的所有权 |
--no-privileges |
不在转储文件中设置权限 |
--no-comments |
不在转储文件中输出对象的注释 |
--clean |
在恢复前清理（删除）目标数据库中的对象 |
--if-exists |
在清理对象时使用 IF EXISTS 选项 |
pg_basebackup参数如下
| 参数 | 说明 |
|---|---|
-D directory |
指定备份的目标目录或文件 |
-F format |
指定输出格式，支持 plain（目录格式）和 tar（tar 格式） |
-X method |
指定 WAL 日志获取方式，支持 fetch 和 stream |
-P |
显示备份进度 |
-v |
详细模式，显示执行过程中的详细信息 |
-z |
压缩输出数据（仅在 tar 格式下有效） |
-Z level |
指定压缩级别（0 到 9，仅在 tar 格式下有效） |
-h host |
指定数据库服务器的主机 |
-p port |
指定数据库服务器的端口 |
-U username |
指定连接数据库的用户名 |
--checkpoint=fast |
执行快速检查点，以减少数据库关闭时间 |
--wal-method=none |
不包括 WAL 日志（不推荐） |
--slot=slot_name |
使用指定的复制槽进行备份 |
--write-recovery-conf |
在备份目录中生成 recovery.conf 文件（用于恢复配置） |
--target-gxid=GID |
将备份作为一个一致性点，该一致性点是由全球事务 ID 指定的 |
--progress |
显示备份进度（与 -P 相同） |
--max-rate=rate |
限制传输速率（以字节/秒为单位），用于限制备份进程对网络和磁盘带宽的使用 |
总结 🔗
pg_dump用于逻辑备份，适合备份单个数据库或特定表，并支持多种输出格式。pg_basebackup用于物理备份，适合完整数据库实例备份，尤其是用于复制和灾难恢复。
根据实际需求选择合适的工具和参数组合，以确保备份过程高效且符合业务需求。
示例 🔗
1pg_basebackup -U postgres -D /root -F tar -X fetch -P
2pg_dump -U postgres -d postgres -f /root/backup/1.sql
三.数据的恢复 🔗
pg_dump方式 🔗
此方法直接采用：psql -U postgres -d postgres -f /etc/postgresql/1.sql 即可完成恢复
错误处理 🔗
- 如果在执行过程中出现错误，psql 会输出错误信息。可以在命令中添加 –set ON_ERROR_STOP=on 参数，使在出错时立即停止执行，以便进行调试和修正
- 如果在恢复过程中有外键约束错误，可以尝试在备份时使用 –disable-triggers 选项，这会在恢复后再重新启用触发器
pg_basebackup方式 🔗
此方法需要关停pgsql服务，然后 tar -xf base.tar -C /path/to/data_directory 重启pgsql服务
备份脚本 🔗
不要使用root账户，请使用postgres账户设置cron来执行此脚本
1#!/bin/bash
2
3# PostgreSQL 连接信息
4USER="postgres"
5DB_NAME="my_database"
6BACKUP_DIR="/path/to/backup_directory"
7BACKUP_FILE="${BACKUP_DIR}/my_database_backup_$(date +\%Y-%m-%d).sql.tar.gz"
8
9# 创建备份目录（如果不存在）
10mkdir -p $BACKUP_DIR
11
12# 执行备份并压缩
13pg_dump -U $USER -d $DB_NAME -F p | gzip > $BACKUP_FILE
14
15# 可选：删除7天前的备份文件
16find $BACKUP_DIR -type f -name "*.sql.tar.gz" -mtime +7 -exec rm {} \;
17
18echo "Backup and compression completed at $(date)" >> /path/to/backup_log.txt