一、Copy
COPY在 PostgreSQL表和标准文件系统文件之间 移动数据。COPY TO把一个表的内容复制 到一个文件，而COPY FROM 则从一个文件复制数据到一个表（把数据追加到表中原有数据）。COPY TO也能复制一个 SELECT查询的结果。 支持text、 csv（逗号分隔值）或者binary。 默认是text。
（一）语法：
COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
TO { 'filename' | PROGRAM 'command' | STDOUT }
[ [ WITH ] ( option [, ...] ) ]
COPY table_name [ ( column_name [, ...] ) ]
FROM { 'filename' | PROGRAM 'command' | STDIN }
[ [ WITH ] ( option [, ...] ) ]
（二）常用参数
table_name，column_name，query，filename
（三）示例：
导出表
COPY user TO '/tmp/data/test.csv' WITH csv;
导出字段
COPY user(name,password) TO '/tmp/data/test.csv' WITH csv;
COPY (select name,age from user) TO '/tmp/data/test.csv' WITH csv header;
导入表
COPY user from '/tmp/data/test.csv' ;
（四）注意事项：
COPY TO只能被用于纯粹的表，不能用于视图。 不过你可以写COPY (SELECT * FROM viewname) TO ... 拷贝一个视图的当前内容。
COPY FROM可以被用于纯粹的表和具有 INSTEAD OF INSERT触发器的视图。 同时调用目标表上的任何触发器 和检查约束。
COPY命令的用户必须是 PostgreSQL用户（运行服务器的用户 ID）可访问的并且是可读或者可写的。 只允许数据库超级用户COPY一个文件或者命令， 因为它允许读取或者写入服务器有特权访问的任何文件。
COPY默认利用tab作为列的界限，空格作为字符
二、Pg_dump
pg_dupg_dump — 把PostgreSQL数据库抽取为一个脚本文件或其他归档文件
（一）语法
Pg_dump [connection-option...] [option...] [dbname]
样例：pg_dump dbname > outfile
psql dbname < infile
pg_restore -d newdb db.dump
（二）常用参数：
-a --data-only
-b --blobs
-B --no-blobs
-c --clean
d directory
-j njobs --jobs=njobs
-n schema --schema=schema
-t table --table=table
参数命令中大小写会造成语义相反。
（三）示例：
要把一个数据库mydb转储到一个 SQL 脚本文件：
$ pg_dump mydb > db.sql
要用 5 个并行的工作者任务转储一个数据库到一个目录格式的归档：
$ pg_dump -Fd mydb -j 5 -f dumpdir
要把一个归档文件重新载入到一个（新创建的）名为newdb的数据库：
$ pg_restore -d newdb db.dump
要转储detroit模式中名称以emp开始的所有表，排除名为employee_log的表：
$ pg_dump -t 'detroit.emp*' -T detroit.employee_log mydb > db.sql
（四）注意事项：
为maintenance_work_mem和max_wal_size设置适当的（即比正常值大的）值。
如果使用 WAL 归档或流复制，在转储时考虑禁用它们。在载入转储之前，可通过将archive_mode设置为off、将wal_level设置为minimal以及将max_wal_senders设置为零（在录入dump前）来实现禁用。 之后，将它们设回正确的值并执行一次新的基础备份。
如果在数据库服务器上有多个 CPU 可用，可以考虑使用pg_restore的--jobs选项。这允许并行数据载入和索引创建。
恢复后执行统计信息收集
ANALYZE [ VERBOSE ] [ table_name [ ( column_name [, ...] ) ] ]描述
ANALYZE收集一个数据库中的表的内容的统计信息，并且将结果存储在pg_statistic系统目录中。
三、PG_DUMPALL
pg_dumpall备份一个给定集簇中的每一个数据库，并且也保留了集簇范围的数据，如角色和表空间定义。
（一）语法
pg_dumpall > outfile
转储的结果可以使用psql恢复：
psql -f infile postgres
（二）注意事项
PG_DUMPALL的过程中，每个数据库自身是一致的，但是不同数据库的快照并不同步。
建议在每个数据库上运行ANALYZE，这样优化器就可以得到有用的统计信息。你也可以运行vacuumdb -a -z来分析所有数据库。
四、PITR
在任何时间，PostgreSQL在数据集簇目录的pg_wal/子目录下都保持有一个预写式日志（WAL）。这个日志存在的目的是为了保证崩溃后的安全：如果系统崩溃，可以“重放”从最后一次检查点以来的日志项来恢复数据库的一致性。我们可以把一个文件系统级别的备份和WAL文件的备份结合起来。当需要恢复时，我们先恢复文件系统备份，然后从备份的WAL文件中重放来把系统带到一个当前状态。
（一）修改配置文件 postgresql.conf
archive_mode = on
archive_command = 'cp -i %p /home/sure/mywork/archive/%f'
wal_level = replica
重启数据库
./pg_ctl -l logfile start
（二）对数据库进行物理备份
select pg_start_backup('stm');
打包数据库
tar -cvzf data.tar data
结束备份
select pg_stop_backup();
这时会再备份出的data下产生一个backup_label的文件，记录了可以查看内容有checkpoint时间,基础备份的开始和结束时间，以及标签名称等
这里也可以用pg_basebackup工具备份
pg_basebackup -F t -R -D /home/postgres/bak
（三）更新数据库
copy weather from '/home/postgres/test.txt';
切换归档产生新归档文件
PostgreSQL手动切换WAL日志的命令：
在PG10之前：
highgo=# select pg_switch_xlog(); pg_switch_xlog
在PG10之后：
highgo=# select pg_switch_wal(); pg_switch_wal
（四）模拟数据库毁坏并恢复
模拟毁坏 cp -r data data1
解压备份：$ tar xvf data.tar
清理pg_wal rm -rf ./*
修改 postgresql.conf 将之前的配置去掉
配置recovery.conf
restore_command = 'cp /home/postgres/archive/%f %p'
archive_cleanup_command='pg_archivecleanup /home/sure/mywork/archivedir %r'
recovery_target_time='2018-07-21 14:35:12'
重启数据库
Pg_ctl start
结束后,recovery.conf会改名变成recovery.done。
验证数据
Select * from weather;
五、总结：
COPY适合单表或者一个查询的结果，小范围的移动
PG_DUMP适用于数据实时性要求低，单个数据库的备份恢复
PG_DUMPALL适用于数据实时性要求低，整个数据库集簇的备份与恢复
PITR 适用于数据量较大，实时性较高的备份和恢复
网友评论