Postgresql备份与恢复

备份

逻辑备份

pg_dump -F c -f dumpfile pgbench -v #备份指定库
pg_dumpall -g #备份所有库
pg_dumpall --schema-only > myscriptdump.sql
pg_dumpall --roles-only > myroles.sql
pg_dumpall --tablespaces-only > mytablespaces.sql
pg_dumpall --globals-only > myglobals.sql

物理备份

pg_basebackup -D /var/lib/postgresql/backup/$(date "+%Y%m%d%H%M") -c fast -P -R
barman

恢复

逻辑恢复

pg_restore --schema-only -v dumpfile 2>/dev/null | head | grep Started
psql -f myglobals.sql
pg_restore -C -d postgres -j 4 dumpfile
psql -f myscriptdump.sql
pg_restore -d database_name backup_file.sql #恢复指定表

物理恢复

systemctl stop postgresql
rm -rf /var/lib/postgresql/15/main/
cp -a /var/lib/postgresql/backup/202212281409/ /var/lib/postgresql/15/main/
rm /var/lib/postgresql/15/main/standby.signal
rm /var/lib/postgresql/15/main/postgresql.auto.conf
systemctl start postgresql

工具推荐

1. PG Back Web

• https://github.com/eduardolat/pgbackweb
• 通过其直观的网页界面和自动化功能，可以大大简化你的备份任务。

2. pg_rman

• https://github.com/ossc-db/pg_rman/
• 实现了全量、增量、归档的层级的备份，可以很灵活的管理PostgreSQL数据库的备份。

案例

二进制备份指定表

#pg_dump版本要和postgresql server一致
#单表
pg_dump -F c -t p -f postgres_p.dmp -C -E UTF8 -h 192.168.21.195 -p 5432 -U postgres postgres

#多表
pg_dump -F c -t 'data.t2*' -f db2-t2.dmp -C -E UTF8 -U postgres db2

#单库
pg_dump -F c  -f db2.dmp -C -E UTF8 -U postgres db2

#恢复
pg_restore -h 192.168.21.195 -p 5432 -U postgres -d postgres -v postgres_p.dmp