PostgreSql 数据迁移

物理冷备迁移

--关闭数据库
pg_ctl stop -m fast

--备份
tar -jcv -f /back/dbbak0228.tar.bz2 $PGDATA

--恢复
tar -jxv -f /back/dbbak0228.tar.bz2 -C /newdata

pg_basebackup 迁移

源端数据库 pg_hba.conf 文件中需增加 replication 配置。

--备份
pg_basebackup -h 192.168.100.5  -p 5866 -U sysdba -D /backup -Fp -P -Xs -R -v

--恢复
修改postgresql.conf
restore_command = 'cp /备份归档路径/%f %p'

--指定时间恢复
recovery_target_time = '2022-01-18 15:30:12.426998+08'

--执行恢复到最近时间
recovery_target_timeline = 'latest'

--更改数据库为读写状态
pg_ctl promote -D /backup/

pg_dump 迁移

--备份数据库用户角色表空间
pg_dumpall -f backup.sql --globals-only

--备份数据库结构
pg_dump -d cdrapp -C -s > cdrapp.sql
tar -czvf comm.tar.gz comm.sql
tar -xzvf comm.tar.gz

--备份 syd 数据库
pg_dump syd > syd.sql
pg_dump -Fc syd > syd.dump

--恢复 syd 数据库
\i syd.sql
psql -Upostgres -d test -f syd.sql
pg_restore -d syd syd.dump

--备份schema
pg_dump -d chis -n icuis > chis_icuis.sql

--恢复schema
\i chis_icuis.sql

--备份表
pg_dump -d syd -t syd.test > test.sql
copy syd.test to '/home/postgres/test.csv' with csv;

--还原表
\i test.sql
copy syd.test from '/home/postgres/test.csv' with csv;

--导出表定义
pg_dump -d chis -O -s -t "finance.out_trade_order" > finance.out_trade_order.sql

--断开数据库所有连接
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE datname='syd' AND pid<>pg_backend_pid();

copy 单表迁移

--导出表（不带过滤条件）
copy pacs.report_doc_template TO '/tmp/report_doc_template.csv' with csv header;

--导出表（带过滤条件）
COPY (select * from patient where pat_id < 1000000) TO '/tmp/comm_result.csv' WITH CSV HEADER;
				
--将表 test_tab 拷贝至客户端，并且使用'|'作为分隔符
COPY test_tab TO STDOUT (DELIMITER '|');

--将表数据拷贝至压缩文件中
COPY test_tab TO PROGRAM 'gzip > /backup/test_tab_data.gz'; 
COPY emp TO PROGRAM 'zip > /backup/test_tab_data.gz';

--从文件'/backup/test_tab_data'中把数据拷贝至表 test_tab
COPY test_tab FROM '/backup/test_tab_data'; 
COPY test_tab(name) FROM '/backup/test_tab_data';

--将文件中的数据插入表中
\copy sometable from somefile.txt DELIMITER '|';