PostgreSQL 11（七）导入导出与备份还原

参考： PostgreSQL 11 Backup and Restore

pg_dump、pg_dumpall、pg_dumpall 逻辑备份还原

---------------------------------------------------------
pg_dump 逻辑导出、导入 （https://www.yiibai.com/manual/postgresql/app-pgdump.html）
---------------------------------------------------------
# '-C' 包含 'create database' 命令：
pg_dump -p 5433 -C -d test > /opt/postgre_data/backup/dump.sql

# '-c' 包含 'drop database' 命令：
pg_dump -p 5433 -C -c -d test > /opt/postgre_data/backup/dump.sql

# '-c --if-exists' 包含 'drop table if exist' 命令：
pg_dump -p 5433 -c --if-exists -d test > /opt/postgre_data/backup/dump.sql


#导出时不需要包含创建数据库的命令，导入时可以指定到任意数据库
pg_dump -p 5433 --inserts --column-inserts --no-comments --quote-all-identifiers -d test > /opt/postgre_data/backup/dump.sql

# 压缩导出
pg_dump -p 5433 --inserts --column-inserts -d test | gzip > /opt/postgre_data/backup/dump.sql.gz


# 【导入】
psql -p 5432 demo < /opt/postgre_data/backup/dump.sql

---------------------------------------------------------
pg_dumpall 逻辑导出、导入
---------------------------------------------------------

# 【导出】所有数据库及其他信息，包括角色、表空间等 
pg_dumpall -p 5433 --inserts --column-inserts --no-comments --quote-all-identifiers > /opt/postgre_data/backup/dumpall.sql

# 【导入】需要超级用户权限
psql -p 5433 -f /opt/postgre_data/backup/dumpall.sql postgres


---------------------------------------------------------
pg_restore  导入
---------------------------------------------------------
# 其他格式(Fc/Ft)导出，可用 pg_restore 还原
pg_dump -p 5433 --no-comments -Ft -d test > /opt/postgre_data/backup/test.sql


# 如果数据库不存在,加上选项'-C'来创建，数据库名称要与备份的一致
pg_restore -p 5433 -d test /opt/postgre_data/backup/test.sql
pg_restore -p 5433 -C -d test /opt/postgre_data/backup/test.sql

 

物理备份还原

预写日志(WAL)存放目录为 pg_wal，日志记录数据库中的所有修改信息，可用于系统崩溃恢复，日常数据恢复等。WAL 将日志分隔成多段，每段16M。WAL 不进行归档的时候，系统会创建几段文件循环重复覆盖写入。如启用归档，需设置配置参数 wal_level  为 replica 或更高，同时设置 archive_mode 为 on 状态。归档成功后，将被删除或者被新的数据覆盖。当归档命令(archive_command)成功时，它才返回零。archive_command 在配置文件reload后可生效。归档可恢复数据，但不会对配置文件(postgresql.conf, pg_hba.conf, pg_ident.conf)恢复，需单独备份配置文件档命令仅在已经完成的 WAL 段上调用，如果日志少致使长时间才归档，可以设置 archive_timeout 指定时间强制切换WAL 段，也可手动执行命令 pg_switch_wal 切换。
 

启用归档设置（须重启服务）

# vim /var/lib/pgsql/11/data/postgresql.conf
wal_level = replica			# minimal / replica(9.6 以前为 archive,hot_standby)
archive_mode = on
archive_command = 'test ! -f /opt/postgre_data/archivedir/%f && cp %p /opt/postgre_data/archivedir/%f' 

【备份方法一】

# 【备份方法一】data01 为一个新的空目录（或未创建的目录）
su - postgres bash -c 'pg_basebackup -Ft -P -z -v -Z 5 -c fast -X stream  -D /opt/postgre_data/backup/data01'

【备份方法二】

# 【备份方法二】 
# pg_start_backup(label text [, fast boolean [, exclusive boolean ]])
# pg_stop_backup(exclusive boolean [, wait_for_archive boolean ])

su - postgres 
psql -c "select pg_start_backup('hot_backup', false, false);"
tar -jcvf /opt/postgre_data/backup/data.tar.bz2 /var/lib/pgsql/11/data
psql -c "select pg_stop_backup(false, true);"

# 结果显示
NOTICE:  pg_stop_backup complete, all required WAL segments have been archived
                               pg_stop_backup                                
NOTICE:  pg_stop_backup complete, all required WAL segments have been archived
                               pg_stop_backup                                
-----------------------------------------------------------------------------
 (0/3A000370,"START WAL LOCATION: 0/39000028 (file 000000010000000000000039)+
 CHECKPOINT LOCATION: 0/39000060                                            +
 BACKUP METHOD: streamed                                                    +
 BACKUP FROM: master                                                        +
 START TIME: 2019-03-20 10:17:18 CST                                        +
 LABEL: hot_backup                                                          +
 START TIMELINE: 1                                                          +
 ","18141 /var/lib/pgsql/11/data/ts_user01")                                +

 

【时间点还原】

当前服务器已存在数据库实例（端口为6401），现在需要查看 实例6401某个时间点的数据，此处在当前服务器新增一个实例来回复数据查看。

======================================================================================
备份
======================================================================================
#数据库完整目录备份
su postgres
pg_basebackup -p 6401 -Ft -P -z -v -Z 5 -c fast -X stream -D /data/pgbackup/pg6401_20191031

#新增数据
psql -p 6401
CREATE TABLE tab( id SERIAL PRIMARY KEY, inserttime TIMESTAMPTZ NOT NULL DEFAULT now());

INSERT INTO tab VALUES(DEFAULT,DEFAULT);SELECT pg_sleep(5);
INSERT INTO tab VALUES(DEFAULT,DEFAULT);SELECT pg_sleep(5);
INSERT INTO tab VALUES(DEFAULT,DEFAULT);SELECT pg_sleep(5);
INSERT INTO tab VALUES(DEFAULT,DEFAULT);SELECT pg_sleep(5);
INSERT INTO tab VALUES(DEFAULT,DEFAULT);SELECT pg_sleep(5);
INSERT INTO tab VALUES(DEFAULT,DEFAULT);SELECT pg_sleep(5);
DELETE FROM tab WHERE id = 2;
INSERT INTO tab VALUES(DEFAULT,DEFAULT);SELECT pg_sleep(5);
INSERT INTO tab VALUES(DEFAULT,DEFAULT);SELECT pg_sleep(5);
INSERT INTO tab VALUES(DEFAULT,DEFAULT);
SELECT * FROM tab;

#先归档日志(WAL文件没写完不会自动归档)
SELECT pg_switch_wal();


======================================================================================
时间点还原
======================================================================================
#配置新实例(用来还原)
su postgres
mkdir -p /data/pg6410
mkdir -p /data/pgbackup/6410/archive

#解压到数据目录
tar -xzvf /data/pgbackup/pg6401_20191031/base.tar.gz -C /data/pg6410

#此测试因在同一台服务器，所以这里换个实例端口
sed -i 's/6401/6410/g' /data/pg6410/postgresql.conf

#配置还原的时间点(此处归档目录从/data/pgbackup/6401读取，注意时区！)
cat << EOF > /data/pg6410/recovery.conf
#recovery_target_time = '2019-10-31 05:24:18+08'
recovery_target_time = '2019-10-31 05:24:18'
restore_command = 'cp /data/pgbackup/6401/archive/%f %p'
EOF


#启动服务！
chmod 0700 /data/pg6410
pg_ctl -D /data/pg6410 -l /data/pg6410/logfile.log start

#此时可以访问数据库查看表数据了！

------------------------------------------------------
#如果还原时间预计不对，还可继续还原(从新更改时间)
cat << EOF > /data/pg6410/recovery.conf
recovery_target_time = '2019-10-31 05:24:30'
restore_command = 'cp /data/pgbackup/6401/archive/%f %p'
EOF

#重启服务
pg_ctl -D /data/pg6410 -l /data/pg6410/logfile.log restart
------------------------------------------------------

#最后执行！恢复完成(recovery.conf文件名字将变成recovery.done)
SELECT pg_wal_replay_resume();