Postgresql 备份与恢复

📣 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.文本文件
1.删除数据库 drop database jmedb;
2.创建数据库 create database jmedb;
3.恢复数据  psql --file=jmedb.sql
 指定数据库名称进行恢复  psql --dbname=db2 --file=jmedb.sql     （如果只是恢复数据库  3  4 那个都行 看是否需要指定数据库名称）

2.二进制文件
psql -h 127.0.0.1 -U postgres -p 5432 -f /var/lib/postgresql/data/postgres_123_57_66_185-2024_05_24_15_31_26-dump.sql  

✨ 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"

 

如何备份？
–初始化，创建一个目录，用于存放备份
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