PostgreSQL備份工具-pg_backrest(轉)

標籤：oba   host   postfix   ring   move   list   .sh   shm   gpo   

轉自：http://blog.chinaunix.net/uid-7270462-id-5777877.html

 

一、配置集中備份伺服器1.1 備機安裝備份伺服器軟體依賴包

 yum -y install perl-DBD-Pg perl-JSON perl-Thread-Queue  perl-JSON-PP.noarch perl-Digest-SHA.x86_64 

1.2 備機建立一個備份使用者

 useradd backrest  passwd backrest 

1.3 備機安裝備份軟體

sudo wget -q -O -  https://github.com/pgbackrest/pgbackrest/archive/release/1.25.tar.gz   sudo tar zx -C /rootcp -r /root/pgbackrest-release-1.25/lib/pgBackRest        /usr/share/perl5sudo find /usr/share/perl5/pgBackRest -type f -exec chmod 644 {} +sudo find /usr/share/perl5/pgBackRest -type d -exec chmod 755 {} +sudo cp /root/pgbackrest-release-1.25/bin/pgbackrest /usr/bin/pgbackrestsudo chmod 755 /usr/bin/pgbackrestsudo mkdir -m 770 /var/log/pgbackrestsudo chown backrest:backrest /var/log/pgbackrestsudo touch /etc/pgbackrest.confsudo chmod 640 /etc/pgbackrest.confsudo chown backrest:backrest /etc/pgbackrest.confsudo mkdir /var/lib/pgbackrestsudo chmod 750 /var/lib/pgbackrestsudo chown backrest:backrest /var/lib/pgbackrest 

1.4 備機配置SSH產生密鑰

sudo -u backrest mkdir -m 750 /home/backrest/.sshsudo -u backrest ssh-keygen -f /home/backrest/.ssh/id_rsa -t rsa -b 4096 -N "" 

1.5 備機 添加主節點hostsname

 vi /etc/hosts  ping db-primary  sudo ssh [email protected] cat /home/postgres/.ssh/id_rsa.pub |   sudo -u backrest tee -a /home/backrest/.ssh/authorized_keys  // 將主節點公開金鑰複製到備節點  sudo -u backrest ssh [email protected] //測試連接主節點 

1.6 主節點複製備節點公開金鑰到主節點

 sudo ssh [email protected] cat /home/backrest/.ssh/id_rsa.pub | sudo -u postgres tee -a /home/postgres/.ssh/authorized_keys // 將備節點公開金鑰複製到主節點    sudo -u postgres ssh [email protected] //測試連接備節點 

1.7 配置備節點pgbackrest設定檔

[[email protected] ~]#  more /etc/pgbackrest.conf [demo]db1-host=db-primarydb1-path=/usr/local/pg10/datadb1-user=postgres[global]repo-path=/var/lib/pgbackrestretention-full=2start-fast=y[[email protected] ~]# 

1.8 配置主節點pgbackrest設定檔

[[email protected] ~]# more /etc/pgbackrest.conf [demo]db-path=/usr/local/pg10/data[global]backup-host=backupbackup-user=backrestlog-level-file=detail[[email protected] ~]# 

1.9 備份節點產生stanza

[[email protected] ~]#    sudo -u backrest pgbackrest --stanza=demo --db-socket-path=/tmp   --log-level-console=info  stanza-create2017-11-10 03:30:01.791 P00   INFO: stanza-create command begin 1.25: --db1-host=db-primary --db1-path=/usr/local/pg10/data --db1-socket-path=/tmp --db1-user=postgres --log-level-console=info --repo-path=/var/lib/pgbackrest --stanza=demo2017-11-10 03:30:04.275 P00   INFO: stanza-create command end: completed successfully[[email protected] ~]# 

1.10 備份節點檢查stanza狀態

[[email protected] ~]#  sudo -u backrest pgbackrest --stanza=demo --db-socket-path=/tmp --log-level-console=info  check2017-11-10 03:31:41.410 P00   INFO: check command begin 1.25: --db1-host=db-primary --db1-path=/usr/local/pg10/data --db1-socket-path=/tmp --db1-user=postgres --log-level-console=info --repo-path=/var/lib/pgbackrest --stanza=demo2017-11-10 03:31:45.980 P00   INFO: WAL segment 000000060000000000000027 successfully stored in the archive at ‘/var/lib/pgbackrest/archive/demo/10-1/0000000600000000/000000060000000000000027-ec11aa4b133b216e1e4d080207ff6b8c633c58eb.gz‘2017-11-10 03:31:46.014 P00   INFO: check command end: completed successfully[[email protected] ~]# 

二、備份節點遠程備份還原測試2.1 備份節點執行全備

[[email protected] ~]# sudo -u backrest pgbackrest --stanza=demo --db-socket-path=/tmp --log-level-console=info   --type=full backup2017-11-10 03:33:38.586 P00   INFO: backup command begin 1.25: --db1-host=db-primary --db1-path=/usr/local/pg10/data --db1-socket-path=/tmp --db1-user=postgres --log-level-console=info --repo-path=/var/lib/pgbackrest --retention-full=2 --stanza=demo --start-fast --type=full2017-11-10 03:33:40.839 P00   INFO: execute non-exclusive pg_start_backup() with label "pgBackRest backup started at 2017-11-10 03:33:38": backup begins after the requested immediate checkpoint completes2017-11-10 03:33:41.457 P00   INFO: backup start archive = 000000060000000000000029, lsn = 0/29000028.....2017-11-10 03:38:01.028 P00   INFO: full backup size = 32.3MB2017-11-10 03:38:01.028 P00   INFO: execute non-exclusive pg_stop_backup() and wait for all WAL segments to archive2017-11-10 03:38:01.745 P00   INFO: backup stop archive = 000000060000000000000029, lsn = 0/290001302017-11-10 03:38:10.992 P00   INFO: new backup label = 20171110-033338F2017-11-10 03:38:16.270 P00   INFO: backup command end: completed successfully2017-11-10 03:38:16.271 P00   INFO: expire command begin 1.25: --db1-host=db-primary --log-level-console=info --repo-path=/var/lib/pgbackrest --retention-archive=2 --retention-full=2 --stanza=demo2017-11-10 03:38:16.287 P00   INFO: expire full backup set: 20171106-081828F, 20171106-081828F_20171106-172152I2017-11-10 03:38:19.815 P00   INFO: remove expired backup 20171106-081828F_20171106-172152I2017-11-10 03:38:19.838 P00   INFO: remove expired backup 20171106-081828F2017-11-10 03:38:20.475 P00   INFO: expire command end: completed successfully[[email protected] ~]# 

2.3 在備節點檢查上備份結果集

[[email protected] ~]# sudo -u backrest pgbackrest info

stanza: demo    status: ok    db (current)        wal archive min/max (10-1): 000000010000000000000007 / 000000060000000000000029        full backup: 20171109-210931F            timestamp start/stop: 2017-11-09 21:09:31 / 2017-11-09 21:14:00            wal start/stop: 000000050000000000000014 / 000000050000000000000016            database size: 32.3MB, backup size: 32.3MB            repository size: 3.7MB, repository backup size: 3.7MB        incr backup: 20171109-210931F_20171110-004318I            timestamp start/stop: 2017-11-10 00:43:18 / 2017-11-10 00:43:31            wal start/stop: 000000060000000000000025 / 000000060000000000000025            database size: 32.3MB, backup size: 24.2KB            repository size: 3.7MB, repository backup size: 566B            backup reference list: 20171109-210931F        full backup: 20171110-033338F            timestamp start/stop: 2017-11-10 03:33:38 / 2017-11-10 03:38:05            wal start/stop: 000000060000000000000029 / 000000060000000000000029            database size: 32.3MB, backup size: 32.3MB            repository size: 3.7MB, repository backup size: 3.7MB[[email protected] ~]# 

2.4類比主節點故障清理data資料庫目錄。

[[email protected] ~]$ /usr/local/pg10/bin/psql psql (10.0)Type "help" for help.postgres=# \c ghanYou are now connected to database "ghan" as user "postgres".ghan=# select count(*) from t;  count ------- 58000(1 row)ghan=# ghan=# \q[[email protected] ~]$ cd /usr/local/pg10/data[[email protected] data]$ lltotal 68-rw------- 1 postgres postgres   231 Nov  9 21:13 backup_label.olddrwx------ 6 postgres postgres    54 Nov 10 01:17 basedrwx------ 2 postgres postgres  4096 Nov 10 04:30 globaldrwx------ 2 postgres postgres     6 Nov 10 01:17 pg_commit_tsdrwx------ 2 postgres postgres     6 Nov 10 01:17 pg_dynshmem-rwx------ 1 postgres postgres  4649 Nov  5 22:22 pg_hba.conf-rwx------ 1 postgres postgres  1636 Nov  5 07:23 pg_ident.confdrwx------ 4 postgres postgres    68 Nov 10 04:30 pg_logicaldrwx------ 4 postgres postgres    36 Nov 10 01:17 pg_multixactdrwx------ 2 postgres postgres    18 Nov 10 04:30 pg_notifydrwx------ 2 postgres postgres     6 Nov 10 01:17 pg_replslotdrwx------ 2 postgres postgres     6 Nov 10 01:17 pg_serialdrwx------ 2 postgres postgres     6 Nov 10 01:17 pg_snapshotsdrwx------ 2 postgres postgres     6 Nov 10 04:30 pg_statdrwx------ 2 postgres postgres    84 Nov 10 04:31 pg_stat_tmpdrwx------ 2 postgres postgres    18 Nov 10 01:19 pg_subtransdrwx------ 2 postgres postgres     6 Nov 10 01:17 pg_tblspcdrwx------ 2 postgres postgres     6 Nov 10 01:17 pg_twophase-rwx------ 1 postgres postgres     3 Nov  5 07:23 PG_VERSIONdrwx------ 3 postgres postgres  4096 Nov 10 04:30 pg_waldrwx------ 2 postgres postgres    18 Nov 10 01:17 pg_xact-rwx------ 1 postgres postgres    88 Nov  5 07:23 postgresql.auto.conf-rwx------ 1 postgres postgres 22794 Nov  5 22:34 postgresql.conf-rw------- 1 postgres postgres    57 Nov 10 04:30 postmaster.opts-rw------- 1 postgres postgres    79 Nov 10 04:30 postmaster.pid-rw-rw-rw- 1 postgres postgres    99 Nov 10 01:17 recovery.done[[email protected] data]$ rm -rf *[[email protected] data]$ lltotal 0[[email protected] data]$ 

2.5 在主節點執行還原命令

 [[email protected] data]# sudo -u postgres pgbackrest --stanza=demo --delta --log-level-console=info   restore2017-11-10 04:34:39.380 P01   INFO: restore file /usr/local/pg10/data/base/1/13064 (0B, 100%)2017-11-10 04:34:39.400 P01   INFO: restore file /usr/local/pg10/data/base/1/13059 (0B, 100%)2017-11-10 04:34:39.425 P01   INFO: restore file /usr/local/pg10/data/base/1/13054 (0B, 100%)2017-11-10 04:34:39.493 P01   INFO: restore file /usr/local/pg10/data/base/1/13049 (0B, 100%)2017-11-10 04:34:39.501 P00   INFO: write /usr/local/pg10/data/recovery.conf2017-11-10 04:34:40.137 P00   INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)2017-11-10 04:34:41.180 P00   INFO: restore command end: completed successfully 

2.6 啟動主節點Postgresql 進行歸檔日誌還原

[[email protected] ~]$ ./startpg10.sh waiting for server to start........ doneserver started[[email protected] ~]$ ps -ef |grep ^postpostfix   1698  1554  0 Nov04 ?        00:00:01 qmgr -l -t unix -upostfix  13544  1554  0 03:10 ?        00:00:00 pickup -l -t unix -upostgres 14805 14804  0 04:35 pts/0    00:00:00 -bashpostgres 14833     1  0 04:36 pts/0    00:00:00 /usr/local/pg10/bin/postgres -D /usr/local/pg10/datapostgres 14834 14833  0 04:36 ?        00:00:00 postgres: startup process   recovering 00000006000000000000002Apostgres 14839 14833  0 04:36 ?        00:00:00 postgres: checkpointer process   postgres 14840 14833  0 04:36 ?        00:00:00 postgres: writer process   postgres 14842 14833  0 04:36 ?        00:00:00 postgres: stats collector process   postgres 14848 14834 39 04:36 ?        00:00:00 /usr/bin/perl /usr/bin/pgbackrest --log-level-console=info --stanza=demo archive-get 00000007.history pg_wal/RECOVERYHISTORYpostgres 14849 14848  4 04:36 ?        00:00:00 ssh -o LogLevel=error -o Compression=no -o PasswordAuthentication=no [email protected] /usr/bin/pgbackrest --buffer-size=4194304 --command=archive-get --compress-level=6 --compress-level-network=3 --db1-path=/usr/local/pg10/data --protocol-timeout=1830 --stanza=demo --type=backup remotepostgres 14850 14805  0 04:36 pts/0    00:00:00 ps -efpostgres 14851 14805  0 04:36 pts/0    00:00:00 grep --color=auto ^post[[email protected] ~]$ 等歸檔日誌應用完，資料庫正常開啟資料恢複正常[[email protected] ~]$ /usr/local/pg10/bin/psql psql (10.0)Type "help" for help.postgres=# \c ghanYou are now connected to database "ghan" as user "postgres".ghan=# select count(*) from t;  count ------- 58000(1 row)ghan=#

 

PostgreSQL備份工具-pg_backrest(轉)