今天需要对生产环境的postgres进行备份,使用pg_dump命令:
pg_dump -h 10.0.0.171 -p 5432 -U pguser -d alchemy_lab -f ./backup.sql
提示需要输入密码,也可以利用环境变量预置密码:
export PGPASSWORD=pgpasswd
另外老版本的pg_dump命令没有-d选项,选择的数据库都写到最后,而且-W仅仅是需要密码的意思,并不能通过-W passwd来指定密码:
pg_dump dumps a database as a text file or to other formats.
Usage:
pg_dump [OPTION]... [DBNAME]
General options:
-f, --file=FILENAME output file or directory name
-F, --format=c|d|t|p output file format (custom, directory, tar,
plain text (default))
-v, --verbose verbose mode
-V, --version output version information, then exit
-Z, --compress=0-9 compression level for compressed formats
--lock-wait-timeout=TIMEOUT fail after waiting TIMEOUT for a table lock
-?, --help show this help, then exit
Connection options:
-h, --host=HOSTNAME database server host or socket directory
-p, --port=PORT database server port number
-U, --username=NAME connect as specified database user
-w, --no-password never prompt for password
-W, --password force password prompt (should happen automatically)
--role=ROLENAME do SET ROLE before dump
If no database name is supplied, then the PGDATABASE environment
variable value is used.
但是报错SCRAM authentication requires libpq version 10 or above:
|># pg_dump -h 10.0.0.171 -p 5432 -U pguser alchemy_lab -f ./backup.sql
pg_dump: [archiver (db)] connection to database "alchemy_lab" failed:
SCRAM authentication requires libpq version 10 or above
应该是本地pg客户端版本太低,而centos通过 ```yum update psql``` 命令也无法提升版本。尝试了网上几个办法都没有成功,于是脾气暴躁的我变得毫无耐心,于是决定用docker来解决。
现在本地准备好脚本 dump.sh, 将其放在/data1/dump/目录下,对应下面的本地文件挂载目录:
#!/bin/bash
docker run --name pd -e POSTGRES_PASSWORD=pgpasswd -e PGPASSWORD=pgpasswd \
-v /data1/dump:/var/lib/postgresql/data postgres:14 pg_dump -h 10.0.0.171 \
-p 5432 -U pguser -f /var/lib/postgresql/data/pd.sql -d alchemy_lab
docker rm pd
date=$(date +%Y%m%d)
mv /data1/dump/pd.sql /data1/dump/pd.sql.$date
- POSTGRES_PASSWORD是pg容器初始化启动时需要的密码;
- PGPASSWORD是pg_dump执行时需要的远程pg数据库的密码;
- -f 指定将备份文件存在/var/lib/postgresql/data/pd.sql, 其实就是本地文件/data1/dump/pd.sql;
- 将文件名加入日期,接下来可以将文件传到oss