postgresql 10数据库备份 pg_probackup 简明教程

概述测试环境说明 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 postgre

测试环境说明

OS：CentOS linux release 7.3.1611 (Core) X64

PG：pg10 beta3 source install

pg_probackup: 2.0.3source 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 ~]$

总结

以上是内存溢出为你收集整理的postgresql 10数据库备份 pg_probackup 简明教程全部内容，希望文章能够帮你解决postgresql 10数据库备份 pg_probackup 简明教程所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。