前言
PG的备份大致可以分为两类,逻辑备份和物理备份,各有优略,本文做了详细的说明
1.备份恢复概述
备份大致可以分为两类:逻辑备份和物理备份。 虽然两者都有优点和缺点,但逻辑备份的一个缺点是它们可能非常耗时。 特别是,备份大型数据库可能需要很长时间,从备份数据恢复数据库可能需要更长的时间。 另一方面,可以更快地进行和恢复物理备份,使其成为实际系统中非常重要和有用的功能。
2.逻辑方式
2.1 pg_dump
pg_dump 将表结构及数据以SQL语句的形式导出到文件中, 恢复数据时,将导出的文件作为输入, 执行其中的SQL语句,即可恢复数据。 pg_dump 能够对正在使用的PostgreSQL数据库进行备份, 并且不影响正常业务的读写。
pg_dump备份示例: 1 导出单表数据 pg_dump -h 127.0.0.1 -U admin -p 5432 -W db -t t1 –inserts > bak.sql 2 导出多个表数据 pg_dump -h 127.0.0.1 -U admin -p 5432 -W db -t t1 -t t2 –inserts > bak.sql 3 导出整个数据库 pg_dump -h 127.0.0.1 -U admin -p 5432 -W db –inserts > bak.sql 4 只导出表结构,不导出数据 pg_dump -h 127.0.0.1 -U admin -p 5432 -W db -s > bak.sql 5 只导出数据,不导出表结构 pg_dump -h 127.0.0.1 -U admin -p 5432 -W db –inserts -a > bak.sql 6.压缩导出 pg_dump --dbname=jmedb --format=custom --file=/pgbak/jmedb.dmp
pg_dump后如何恢复? 1.文本文件 drop database jmedb; create database jmedb; psql --file=jmedb.sql psql --dbname=db2 --file=jmedb.sql 2.二进制文件 pg_restore --username=postgres --host=192.168.1.54 --port=35432 --dbname=jmedb /pgbak/jmedb.dmp
2.2 pg_dumpall
相对于pg_dump只能备份单个库, pg_dumpall可以备份整个postgresql实例中所有的数据, 包括角色和表空间定义。 示例如下: pg_dumpall -h 127.0.0.1 -U admin -p 5432 -W –inserts > bak.sql
1.pg_dump支持指定所要备份的对象: 可以单独备份表、schema或者database; 2.pg_dumpall仅支持导出全库数据。 3.pg_dump可以将数据备份为SQL文本文件格式, 也支持备份为用户自定义的压缩格式或者TAR包格式。
2.3 COPY 与 \copy
copy:适合单表或带条件sql结果导出 (可导出为csv或txt格式)
COPY 与 \copy 1.导出 COPY { table_name [ ( column_name [, ...] ) ] | ( query ) } TO { 'filename' | PROGRAM 'command' | STDOUT } [ [ WITH ] ( option [, ...] ) ] 2.导入 COPY table_name [ ( column_name [, ...] ) ] FROM { 'filename' | PROGRAM 'command' | STDIN } [ [ WITH ] ( option [, ...] ) ] 说明: WITH csv:导入csv格式数据 HEADER:不导入第一行 encoding:指定编码
txt格式导出: psql -ddb_name -Uuser_name -W 执行 COPY 表名 copy TO '/home/postgres/test.txt' 或者 \copy test_copy to '/home/postgres/test.txt' 导入txt文件 psql -ddb_name -Uuser_name -W COPY 表名 copy FROM '/home/postgres/test.txt' #或 \copy test_copy from '/home/postgres/test.txt' 备注:COPY是sql命令, 需要superuser权限,性能更好; \copy是元命令,不需要superuser权限
导出csv文件 psql -ddb_name -Uuser_name -W COPY products TO '/path/to/output.csv' WITH csv; 导入csv文件 psql -ddb_name -Uuser_name -W COPY products FROM '/path/to/input.csv' WITH csv; 不导入第一行,指定编码为UTF-8 \copy tmp_3 FROM '/data/tmp_3.csv' WITH csv HEADER encoding 'UTF-8';
可以导出指定的列 psql -ddb_name -Uuser_name -W COPY products (name, price) TO '/path/to/output.csv' WITH csv; 也可以配合查询语句 psql -dplatform -Ubom_rw -W COPY (select * from temp_0524 where id='xxx') TO 'home/postgres/temp_0524.csv' with csv header
3.物理方式
3.1 冷备份
• 直接拷贝PostgreSQL 中用来存储数据的文件。 • 你可以用任何方式来进行通常的系统文件备份,比如: – tar -cf backup.tar /usr/local/pgsql/data • 为了得到一个可用的备份,数据库服务器必须关闭。 • 文件系统级别的备份只为完全备份,并且恢复整个数据集合实例。
3.2 pg_basebackup
pg_basebackup 则是一个用于制作数据库集群物理备份的工具, 它会生成一个完整的数据库集群副本, 包括所有数据文件、WAL 文件和其他必要文件。 这种备份方式更利于快速恢复,并支持流复制初始化从库。
基于事务的恢复案例: 插入3000条记录,但是,只找回其中的2000条 --全备任务 pg_basebackup -Fp -P -v -D pgdata_bak --发起插入的数据 postgres=$ create table test(id int,info text); postgres=$ begin; postgres=$ select txid_current(); txid_current -------------- 839 postgres=$ insert into test select n,'test' from generate_series(1,2000) as n; postgres=$ commit; --再次插入数据 postgres=$ begin; postgres=$ select txid_current(); txid_current -------------- 840 postgres=$ insert into test select n,'test' from generate_series(1,1000) as n; postgres=# commit; --删除数据切日志 postgres=# delete from test; postgres=# select pg_switch_wal(); pg_switch_wal --------------- 0/1007E250 --编辑恢复的文件 [postgres@centos79 ~]$ vi pgdata_bak/postgresql.conf restore_command = 'cp /home/postgres/archive_dir/%f %p' recovery_target_xid = '839' port=5558 --生成文件 touch pgdata_bak/recovery.signal --启动备份实例 [postgres@centos79 ~]$ pg_ctl -D pgdata_bak start
基于时间点的恢复案例 # Place archive logs under /mnt/server/archivedir directory. restore_command = 'cp /mnt/server/archivedir/%f %p' recovery_target_time = "2024-1-1 12:05 GMT" $ touch /usr/local/pgsql/data/recovery.signal
全备脚本:pg_basebackup.sh #!/bin/bash #auth:cuckoo DATE=$(date '+%Y%m%d') sevendays_time=$(date -d -7days '+%Y%m%d') pgpath=/opt/pgsql/bin/ port=15432 pguser=postgres bkpath=/u01/pg_backup/basebackup bktmp=$bkpath/backups-tmp #START BACKUP echo "START BACKUP..............." rm -rf $bkpath/base_$sevendays_time.tar.gz $pgpath/pg_basebackup -Ft -Pv -Xf -z -Z5 -p $port -U $pguser -D $bktmp mv $bktmp/base.tar.gz $bkpath/base_$DATE.tar.gz $pgpath/psql -p $port -U $pguser -c "select pg_switch_wal()" echo "BACKUP END"
3.3 pg_rman
pg_rman是一个开源的PG备份软件,第三方, 本质是一个文件的拷贝,必须和Server安装在一起 支持基于时间的恢复,支持在线的全备和增备 同时还可以支持WAL的备份过期删除
如何备份? --初始化,创建一个目录,用于存放备份 pg_rman init -B /rmanbk 全备脚本 pg_rman backup --backup-mode=full -B /rmanbk pg_rman validate -B /rmanbk 增备脚本 jem_db=# select * from test1; pg_rman backup --backup-mode=incremental -B /rmanbk
--如何恢复呢? pg_ctl stop pg_rman restore -B /rmanbk pg_ctl start
4.总结
通过备份和恢复来保护数据,避免数据丢失,在发生灾难或人为误操作的情况下,能够进行恢复是DBA的日常最重要的工作。不仅要保证能够成功备份,还要保证备份数据能够恢复