postgresql 10数据库备份 pg_probackup 简明教程 原创 pgmia2017-09-01 12:58:10博主文章分类：postgreSQL 文章标签sqlpostgre文章分类数据库阅读数1.5万 测试环境说明 OS：CentOS Linux release 7.3.1611 (Core) X64 PG：pg10 beta3 source install pg_probackup: 2.0.3 source install 建立归档备份目录 [postgres@masterdb ~]# mkdir /backupdir [postgres@masterdb ~]# chown postgres.postgres -R /backupdir 数据库参数配置 ### - Archiving - ### archive_mode = on archive_command = 'pg_probackup archive-push -B /backupdir --instance pg10 --wal-file-path %p --wal-file-name %f' ### default ### max_wal_senders = 10 hot_standby = on full_page_writes = on 初始化备份目录 [postgres@masterdb ~]# su - postgres [postgres@masterdb ~]$ pg_probackup init -B /backupdir INFO: Backup catalog '/backupdir' successfully inited 添加备份实例目录 [postgres@masterdb ~]$ pg_probackup add-instance -B /backupdir -D /pgdata10/ --instance pg10 INFO: Instance 'pg10' successfully inited 显示备份实例配置 [postgres@masterdb ~]$ pg_probackup show-config -B /backupdir --instance pg10 #Backup instance info PGDATA = /pgdata10/ system-identifier = 6460633300993501667 #Connection parameters: PGDATABASE = postgres #Replica parameters: #Logging parameters: #Retention parameters: #Compression parameters: compress-algorithm = none compress-level = 6 [postgres@masterdb ~]$ pg_probackup backup -B /backupdir -b full --instance pg10 INFO: wait for pg_stop_backup() INFO: Backup OVKYC7 completed [postgres@masterdb ~]$ 增量备份 [postgres@masterdb ~]$ pg_probackup backup -B /backupdir -b PAGE --instance pg10 INFO: wait for LSN 0/4000028 in archived WAL segment /backupdir/wal/pg10/000000010000000000000004 INFO: wait for pg_stop_backup() INFO: Backup OVKYME completed [postgres@masterdb ~]$ 显示备份 [postgres@masterdb ~]$ pg_probackup show -B /backupdir BACKUP INSTANCE 'pg10' =============================================================================================================================== Instance ID Recovery time Mode WAL Current/Parent TLI Time Data Start LSN Stop LSN Status =============================================================================================================================== pg10 OVKYME 2017-09-01 10:51:56 PAGE ARCHIVE 1 / 0 8s 4407kB 0/4000028 0/50000F0 OK pg10 OVKYC7 2017-09-01 10:45:57 FULL ARCHIVE 1 / 0 17s 31MB 0/2000060 0/2000198 OK [postgres@masterdb ~]$ 验证备份 [postgres@masterdb ~]$ pg_probackup validate -B /backupdir --instance pg10 INFO: Validate backups of the instance 'pg10' INFO: Validate backup OVKYME INFO: backup validation completed successfully INFO: Validate backup OVKYC7 INFO: backup validation completed successfully INFO: All backups are valid [postgres@masterdb ~]$ 恢复备份 [postgres@masterdb pgdata10]$ pg_probackup restore -B /backupdir --instance pg10 -D /pgdata10/ INFO: backup validation completed successfully 配置保存期限 [postgres@masterdb ~]$ pg_probackup set-config -B /backupdir --instance pg10 --retention-redundancy 2 --retention-window 7 [postgres@masterdb ~]$ pg_probackup show-config -B /backupdir --instance pg10 #Backup instance info PGDATA = /pgdata10/ system-identifier = 6460633300993501667 #Connection parameters: PGDATABASE = postgres #Replica parameters: #Logging parameters: #Retention parameters: retention-redundancy = 2 retention-window = 7 #Compression parameters: compress-algorithm = none compress-level = 6 [postgres@masterdb ~]$ 并行备份 full [postgres@masterdb ~]$ pg_probackup backup -B /backupdir --instance pg10 -b FULL -j 4 INFO: wait for pg_stop_backup() INFO: Backup OVL360 completed [postgres@masterdb ~]$ incremental [postgres@masterdb ~]$ pg_probackup backup -B /backupdir --instance pg10 -b page -j 6 INFO: wait for LSN 0/B000028 in archived WAL segment /backupdir/wal/pg10/00000002000000000000000B INFO: wait for pg_stop_backup() INFO: Backup OVL3BK completed [postgres@masterdb ~]$ [postgres@masterdb ~]$ [postgres@masterdb ~]$ pg_probackup show -B /backupdir --instance pg10 =============================================================================================================================== Instance ID Recovery time Mode WAL Current/Parent TLI Time Data Start LSN Stop LSN Status =============================================================================================================================== pg10 OVL3BK 2017-09-01 12:33:24 PAGE ARCHIVE 2 / 1 6s 4417kB 0/B000028 0/C0000F0 OK pg10 OVL360 2017-09-01 12:30:11 FULL ARCHIVE 2 / 1 13s 31MB 0/9000060 0/9000198 OK pg10 OVKYME 2017-09-01 10:51:56 PAGE ARCHIVE 1 / 0 8s 4407kB 0/4000028 0/50000F0 OK pg10 OVKYC7 2017-09-01 10:45:57 FULL ARCHIVE 1 / 0 17s 31MB 0/2000060 0/2000198 OK [postgres@masterdb ~]$ restore [postgres@masterdb ~]$ pg_probackup restore -B /backupdir --instance pg10 -D /pgdata10/ -j 6 INFO: backup validation completed successfully INFO: Restore of backup OVL3BK completed. [postgres@masterdb ~]$ pg_probackup show -B /backupdir --instance pg10 -D /pgdata10 =============================================================================================================================== Instance ID Recovery time Mode WAL Current/Parent TLI Time Data Start LSN Stop LSN Status =============================================================================================================================== pg10 OVL3BK 2017-09-01 12:33:24 PAGE ARCHIVE 2 / 1 6s 4417kB 0/B000028 0/C0000F0 OK pg10 OVL360 2017-09-01 12:30:11 FULL ARCHIVE 2 / 1 13s 31MB 0/9000060 0/9000198 OK pg10 OVKYME 2017-09-01 10:51:56 PAGE ARCHIVE 1 / 0 8s 4407kB 0/4000028 0/50000F0 OK pg10 OVKYC7 2017-09-01 10:45:57 FULL ARCHIVE 1 / 0 17s 31MB 0/2000060 0/2000198 OK [postgres@masterdb ~]$