逻辑备份

pw_dump

• pw_dump为磐维数据库逻辑备份的工具，并且支持在备库上执行，用法与主库相同
• 为了保证数据的一致性，pw_dump会对需要转储的表设置共享锁。如果无法在指定时间内锁定某个表（–lock-wait-timeout），会转储失败
• pw_dump支持导出完整一致的数据，导出的数据为启动pw_dump时刻的数据
• 导出格式
  

1. 创建测试数据

创建用户、数据库

postgres=# create user u_test with password 'Abcd1234';
CREATE ROLE
postgres=# grant all privileges to u_test;
ALTER ROLE
postgres=# create database testdb1  owner u_test;
CREATE DATABASE

创建测试表，插入100条数据

testdb1=> create schema test;
CREATE SCHEMA

testdb1=> alter database testdb1  set search_path to test,public;
ALTER DATABASE

[omm@test1 ~]$ gsql -h192.168.1.15 -p17700 -dtestdb1 -Uu_test -WAbcd1234 -r
gsql ((PanWeiDB 2.0.0 (Build0)) compiled at 2024-01-05 17:14:30 commit 9fbca90 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

testdb1=> show search_path;
 search_path  
--------------
 test, public
(1 row)

testdb1=> create table test(id serial,insert_time timestamp) ;
NOTICE:  CREATE TABLE will create implicit sequence "test_id_seq" for serial column "test.id"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "test_id_key" for table "test"
CREATE TABLE

testdb1=> insert into test(id,insert_time) select i,now() from generate_series(1,100) as i;
INSERT 0 100
testdb1=> select count(1) from test;
 count 
-------
   100
(1 row)

2.数据库全量备份

导出testdb1的全量信息，导出格式为目录格式

pw_dump -Uu_test -WAbcd1234 -f backup_testdb1 -p17700 testdb1 -Fd

执行结果如下

[omm@test1 ~]$ pw_dump -Uu_test -WAbcd1234 -f backup_testdb1 -p17700 testdb1 -Fd
pw_dump[port='17700'][testdb1][2024-05-08 15:37:03]: The total objects number is 891.
pw_dump[port='17700'][testdb1][2024-05-08 15:37:03]: [100.00%] 891 objects have been dumped.
pw_dump[port='17700'][testdb1][2024-05-08 15:37:03]: object test start dumping by pid 1253188
pw_dump[port='17700'][testdb1][2024-05-08 15:37:03]: object test finish dumping by pid 1253188
pw_dump[port='17700'][testdb1][2024-05-08 15:37:03]: dump database testdb1 successfully
pw_dump[port='17700'][testdb1][2024-05-08 15:37:03]: total time: 2781  ms

[omm@test1 ~]$ ls -lrt backup_testdb1
total 8
-rw------- 1 omm dbgrp 2531 May  8 15:37 toc.dat
-rw------- 1 omm dbgrp  260 May  8 15:37 6197.dat.gz

3.全库恢复测试

归档格式的备份只能使用pw_restore恢复，恢复示例

创建空库testdb2

postgres=# create database testdb2  owner u_test;
CREATE DATABASE

执行恢复语句，将testdb1的数据恢复到testdb2库

pw_restore backup_testdb1 -Uu_test -WAbcd1234 -p17700 -dtestdb2

[omm@test1 ~]$ pw_restore backup_testdb1 -Uu_test -WAbcd1234 -p17700 -dtestdb2 
start restore operation ...
table test complete data imported !
Finish reading 9 SQL statements!
end restore operation ...
restore operation successful
total time: 73  ms

testdb2=# select count(1) from test.test;
 count 
-------
   100
(1 row)

4. schema级备份

备份输出格式为tar

pw_dump -Uu_test -WAbcd1234 -f backup_testdb1_test.tar -p17700 testdb1 -Ft -ntest

[omm@test1 ~]$ pw_dump -Uu_test -WAbcd1234 -f backup_testdb1_test.tar -p17700 testdb1 -Ft -ntest
pw_dump[port='17700'][testdb1][2024-05-08 17:06:04]: The total objects number is 882.
pw_dump[port='17700'][testdb1][2024-05-08 17:06:04]: [100.00%] 882 objects have been dumped.
pw_dump[port='17700'][testdb1][2024-05-08 17:06:04]: object test start dumping by pid 1301847
pw_dump[port='17700'][testdb1][2024-05-08 17:06:04]: object test finish dumping by pid 1301847
pw_dump[port='17700'][testdb1][2024-05-08 17:06:04]: dump database testdb1 successfully
pw_dump[port='17700'][testdb1][2024-05-08 17:06:04]: total time: 1799  ms

5. schema级恢复测试

创建空库testdb3

testdb2=# create database testdb3 owner u_test;
CREATE DATABASE

pw_restore backup_testdb1_test.tar -Uu_test -WAbcd1234 -p17700 -dtestdb3
执行结果

[omm@test1 ~]$ pw_restore backup_testdb1_test.tar -Uu_test -WAbcd1234 -p17700 -dtestdb3
start restore operation ...
table test complete data imported !
Finish reading 5 SQL statements!
end restore operation ...
restore operation successful
total time: 59  ms

testdb2=# \c testdb3
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "testdb3" as user "omm".
testdb3=# select count(1) from test.test;
 count 
-------
   100
(1 row)

6. 表级备份

备份表test.test，输出custom格式dmp文件

pw_dump -Uu_test -WAbcd1234 -f backup_testdb1_test.dmp -p17700 testdb1 -Fc -ttest.test

执行结果

[omm@test1 ~]$  pw_dump -Uu_test -WAbcd1234 -f backup_testdb1_test.dmp -p17700 testdb1 -Fc -ttest.test
pw_dump[port='17700'][testdb1][2024-05-08 17:37:31]: The total objects number is 879.
pw_dump[port='17700'][testdb1][2024-05-08 17:37:31]: [100.00%] 879 objects have been dumped.
pw_dump[port='17700'][testdb1][2024-05-08 17:37:31]: object test start dumping by pid 1319683
pw_dump[port='17700'][testdb1][2024-05-08 17:37:31]: object test finish dumping by pid 1319683
pw_dump[port='17700'][testdb1][2024-05-08 17:37:31]: dump database testdb1 successfully
pw_dump[port='17700'][testdb1][2024-05-08 17:37:31]: total time: 1217  ms

备份表test.test，限定id<=10

pw_dump -Uu_test -WAbcd1234 -f backup_testdb1_test2.dmp -p17700 testdb1 -Fc -ttest.test --table-condition=“test;where id<=10”

执行结果

[omm@test1 ~]$ pw_dump -Uu_test -WAbcd1234 -f backup_testdb1_test2.dmp -p17700 testdb1 -Fc -ttest.test --table-condition="test;where id<=10"
pw_dump[port='17700'][testdb1][2024-05-08 17:38:06]: The total objects number is 879.
pw_dump[port='17700'][testdb1][2024-05-08 17:38:06]: [100.00%] 879 objects have been dumped.
pw_dump[port='17700'][testdb1][2024-05-08 17:38:06]: object test start dumping by pid 1320038
pw_dump[port='17700'][testdb1][2024-05-08 17:38:06]: object test finish dumping by pid 1320038
pw_dump[port='17700'][testdb1][2024-05-08 17:38:06]: dump database testdb1 successfully
pw_dump[port='17700'][testdb1][2024-05-08 17:38:06]: total time: 1254  ms

备份表test.test，限定id>10，并且只导出表数据

pw_dump -Uu_test -WAbcd1234 -f backup_testdb1_test3.dmp -p17700 testdb1 -Fc -ttest.test --table-condition=“test;where id>10” --data-only

 [omm@test1 ~]$ pw_dump -Uu_test -WAbcd1234 -f backup_testdb1_test3.dmp -p17700 testdb1 -Fc -ttest.test --table-condition="test;where id>10" --data-only
pw_dump[port='17700'][testdb1][2024-05-08 17:45:35]: object test start dumping by pid 1324316
pw_dump[port='17700'][testdb1][2024-05-08 17:45:35]: object test finish dumping by pid 1324316
pw_dump[port='17700'][testdb1][2024-05-08 17:45:35]: dump database testdb1 successfully
pw_dump[port='17700'][testdb1][2024-05-08 17:45:35]: total time: 1182  ms

7. 表级恢复测试

sql文件格式的备份文件需要用gsql进行恢复
删除testdb3中的test表

testdb3=# drop table test.test;
DROP TABLE

pw_restore backup_testdb1_test.dmp -Uu_test -WAbcd1234 -p17700 -dtestdb3

[omm@test1 ~]$ pw_restore backup_testdb1_test.dmp -Uu_test -WAbcd1234 -p17700 -dtestdb3
start restore operation ...
table test complete data imported !
Finish reading 4 SQL statements!
end restore operation ...
restore operation successful
total time: 34  ms

testdb3=# select count(1) from test.test;
 count 
-------
   100
(1 row)

删除testdb3中的test表

testdb3=# drop table test.test;
DROP TABLE

导入ID<=10的数据，使用-c参数，重建表

pw_restore backup_testdb1_test2.dmp -Uu_test -WAbcd1234 -p17700 -dtestdb3 -c

[omm@test1 ~]$ pw_restore backup_testdb1_test2.dmp -Uu_test -WAbcd1234 -p17700 -dtestdb3 -c
start restore operation ...
table test complete data imported !
Finish reading 4 SQL statements!
end restore operation ...
restore operation successful
total time: 31  ms

testdb3=# select count(1) from test.test;
 count 
-------
    10
(1 row)

导入ID>10的数据,追加导入

pw_restore backup_testdb1_test3.dmp -Uu_test -WAbcd1234 -p17700 -dtestdb3

[omm@test1 ~]$ pw_restore backup_testdb1_test3.dmp -Uu_test -WAbcd1234 -p17700 -dtestdb3 
start restore operation ...
table test complete data imported !
Finish reading 3 SQL statements!
end restore operation ...
restore operation successful
total time: 18  ms

testdb3=# select count(1) from test.test;
 count 
-------
   100
(1 row)

pw_dumpall

pw_dumpall可导出所有数据库相关信息的工具，包括postgres、自定义数据库的数据以及所有数据库的公共对象，只能导出纯文本格式，需使用gsql恢复

导出所有库数据

pw_dumpall -f backupall.sql -p17700

执行结果

[omm@test1 ~]$ pw_dumpall -f backupall.sql -p17700
pw_dump[port='17700'][dbname='human_tpcds'][2024-05-08 17:53:33]: The total objects number is 889.
pw_dump[port='17700'][dbname='human_tpcds'][2024-05-08 17:53:33]: [100.00%] 889 objects have been dumped.
pw_dump[port='17700'][dbname='human_tpcds'][2024-05-08 17:53:33]: dump database dbname='human_tpcds' successfully
pw_dump[port='17700'][dbname='human_tpcds'][2024-05-08 17:53:33]: total time: 1536  ms
pw_dump[port='17700'][dbname='panweidb'][2024-05-08 17:53:35]: The total objects number is 883.
pw_dump[port='17700'][dbname='panweidb'][2024-05-08 17:53:35]: [100.00%] 883 objects have been dumped.
pw_dump[port='17700'][dbname='panweidb'][2024-05-08 17:53:35]: dump database dbname='panweidb' successfully
pw_dump[port='17700'][dbname='panweidb'][2024-05-08 17:53:35]: total time: 1476  ms
pw_dump[port='17700'][dbname='postgres'][2024-05-08 17:53:36]: The total objects number is 888.
pw_dump[port='17700'][dbname='postgres'][2024-05-08 17:53:36]: [100.00%] 888 objects have been dumped.
pw_dump[port='17700'][dbname='postgres'][2024-05-08 17:53:36]: dump database dbname='postgres' successfully
pw_dump[port='17700'][dbname='postgres'][2024-05-08 17:53:36]: total time: 1530  ms
pw_dump[port='17700'][dbname='test'][2024-05-08 17:53:38]: The total objects number is 885.
pw_dump[port='17700'][dbname='test'][2024-05-08 17:53:38]: [100.00%] 885 objects have been dumped.
pw_dump[port='17700'][dbname='test'][2024-05-08 17:53:38]: dump database dbname='test' successfully
pw_dump[port='17700'][dbname='test'][2024-05-08 17:53:38]: total time: 1454  ms
pw_dump[port='17700'][dbname='testdb'][2024-05-08 17:53:39]: The total objects number is 897.
pw_dump[port='17700'][dbname='testdb'][2024-05-08 17:53:40]: [100.00%] 897 objects have been dumped.
pw_dump[port='17700'][dbname='testdb'][2024-05-08 17:53:40]: dump database dbname='testdb' successfully
pw_dump[port='17700'][dbname='testdb'][2024-05-08 17:53:40]: total time: 1584  ms
pw_dump[port='17700'][dbname='testdb1'][2024-05-08 17:53:41]: The total objects number is 891.
pw_dump[port='17700'][dbname='testdb1'][2024-05-08 17:53:41]: [100.00%] 891 objects have been dumped.
pw_dump[port='17700'][dbname='testdb1'][2024-05-08 17:53:41]: dump database dbname='testdb1' successfully
pw_dump[port='17700'][dbname='testdb1'][2024-05-08 17:53:41]: total time: 1324  ms
pw_dump[port='17700'][dbname='testdb2'][2024-05-08 17:53:42]: The total objects number is 891.
pw_dump[port='17700'][dbname='testdb2'][2024-05-08 17:53:42]: [100.00%] 891 objects have been dumped.
pw_dump[port='17700'][dbname='testdb2'][2024-05-08 17:53:42]: dump database dbname='testdb2' successfully
pw_dump[port='17700'][dbname='testdb2'][2024-05-08 17:53:42]: total time: 1544  ms
pw_dump[port='17700'][dbname='testdb3'][2024-05-08 17:53:44]: The total objects number is 891.
pw_dump[port='17700'][dbname='testdb3'][2024-05-08 17:53:44]: [100.00%] 891 objects have been dumped.
pw_dump[port='17700'][dbname='testdb3'][2024-05-08 17:53:44]: dump database dbname='testdb3' successfully
pw_dump[port='17700'][dbname='testdb3'][2024-05-08 17:53:44]: total time: 1592  ms
pw_dumpall[port='17700'][2024-05-08 17:53:44]: dumpall operation successful
pw_dumpall[port='17700'][2024-05-08 17:53:44]: total time: 12144  ms