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

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化的资料的朋友，可以戳这里获取

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！

【导出】所有数据库及其他信息，包括角色、表空间等

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

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

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上软件测试知识点，真正体系化！

由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新

需要这份系统化的资料的朋友，可以戳这里获取

涵盖了95%以上软件测试知识点，真正体系化！**

由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新

需要这份系统化的资料的朋友，可以戳这里获取