【PostgreSQL】psql、pg_dump、pg_restore数据的导出与导入

pg_dump导出数据

样例：以SQL文件和压缩文件导出

以SQL的形式导出。

# 配置初始化的数据库IP地址
DB_IP=
# 配置初始化的数据库的端口号
DB_PORT=5432
# 配置初始化的数据库的超级用户名
DB_SUPERUSER=
# 配置初始化的数据库的超级用户密码
DB_SUPERUSER_PWD=
# 初始化数据库名称等
DB_NAME=
# 先设置好连接数据库的密码
export PGPASSWORD=${DB_SUPERUSER_PWD}
# 导出 参数
# -h 数据库IP -p 数据库端口 -E 导出时的编码
# -U 连接数据库的用户名
# -t 单表导出，可以添加多条-t命令   -n 导出整个schema 不加这两个参数是备份整个数据库
# -v 显示执行过程的信息
# --no-tablespaces 不带表结构
# -f 导出的文件名
# ${DB_NAME}就是要导出哪个数据库，dump.log是执行过程中的信息
nohup /usr/pgsql-11/bin/pg_dump -h ${DB_IP} -p ${DB_PORT} -U ${DB_SUPERUSER} -E UTF8 -t schema.table_name1 -t schema.table_name2 -v --no-tablespaces -f dump.backup  ${DB_NAME} > dump.log 2>&1

以压缩文件形式导出

# -Fc -Z 9 代表以压缩级别9进行压缩
pg_dump -h 127.0.0.1 -p 5432 -U ${DB_SUPERUSER} -n schema -Fc -Z 9 ${DB_NAME} > dump.backup

• 以压缩文件导出后，需要用pg_restore恢复。若导出和恢复的工具版本不同，则会恢复失败。
• 以SQL文件导出后，需要用psql导入数据库。只需要psql和被导入的数据库版本一致即可。但导出文件比较大

常用参数

• -a 只输出数据，不输出结构（表定义）
• -c 创建前删除表定义。
• -i 忽略在 pg_dump 和数据库服务器之间的版本差别．因为 pg_dump知道许多关于系统表的信息，任何给定版本的 pg_dump只能和对应的数据库服务器版本一并使用．如果你需要跨越版本检查时才使用这个选项
• -s 只输出表结构（定义），不输出数据．
• -t 只输出表 table 的数据
• -v 声明冗余模式
• –no-tablespaces 不转储表空间分配

pg_restore恢复数据

export PGPASSWORD=$DB_SUPERUSER_PASSWD
pg_restore -h $DB_IP  -U $DB_SUPERUSER  -p $DB_PORT -d $DB_NAME -F c -e -c ./$RESTORE_FILE_NAME > ./dump.log 2>&1 

psql导入数据

样例

IP=
export PGPASSWORD=
# -c 可以直接执行命令
psql -h $IP -p 5432 -U $DB_SUPERUSER  -d $DB_NAME -c "select version()";
# 导入dump文件
psql -h $IP -p 5432 -U $DB_SUPERUSER -d  $DB_NAME < dump.backup > dump.log 2>&1 

常用参数

• -c, --command=COMMAND：执行一条命令（或内置SQL）然后退出
• -d, --dbname=DBNAME：默认连接的数据库
• -f, --file=FILENAME：执行指定文件中的命令，然后退出
• -a, --echo-all：在读取行时向标准输出打印所有内容
• -h, --host=HOSTNAM：连接数据库服务器的主机IP或套接字目录（默认为“local socket”）
• -p, --port=PORT：连接数据库服务器的端口号（默认为“5432”）
• -U, --username=USERNAME：要连接数据库服务器的用户名

参数相关

• psql参数
• pg_dump参数
• pg_restore参数