PostgreSQL Backup tool-pg_backrest (GO)

Transferred from: http://blog.chinaunix.net/uid-7270462-id-5777877.html

One, configuration centralized backup server 1.1 standby installation Backup Server Software dependency package

Yum-y Install PERL-DBD-PG Perl-json perl-thread-queue  

1.2 Standby Create a new backup user

Useradd Backrest  

1.3 Standby Installation backup software

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        

1.4 Standby configuration SSH generate key

1.5 Standby Add master node 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  //Copy the primary node public key to the standby node  

1.6 Primary node replication node public key to master node

sudo ssh [email protected] cat/home/backrest/.ssh/id_rsa.pub | Sudo-u postgres tee-a/home/postgres/.ssh/authorized_keys//Copy the spare node public key to the master node    

1.7 Configuring the Standby node Pgbackrest configuration file

[Email protected] ~]#  

1.8 Configuring the Master node pgbackrest configuration file

1.9 Backup Node Generation 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   

1.10 Backup node Check stanza status

[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   

Second, backup node remote backup restore Test 2.1 backup node perform full standby

[[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 check Point completes2017-11-10 03:33:41.457 P00 info:backup Start archive = 000000060000000000000029, LSN = 0/29000028.....2 017-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_s Top_backup () and wait for all WAL segments to archive2017-11-10 03:38:01.745 P00 info:backup Stop archive = 00000006000 0000000000029, 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-co Nsole=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.8 P00 Info:remove expired backup 20171106-081828f_20171106-172152i2017-11-10 03:38:19.838 P00 info:remove expired B Ackup 20171106-081828f2017-11-10 03:38:20.475 P00 info:expire command end:completed successfully[[email protecte  D] ~]#

2.3 Backing up the result set on the standby node check

[Email protected] ~]# Sudo-u backrest pgbackrest Info

Stanza:demo Status:ok db (current) Wal archive Min/max (10-1): 000000010000000000000007/00000006000000000            0000029 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-00 4318I timestamp start/stop:2017-11-10 00:43:18/2017-11-10 00:43:31 wal start/stop:00000006000000 0000000025/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:00000006000000000000 0029/000000060000000000000029 database SIZE:32.3MB, backup SIZE:32.3MB repository size:3.7mb, repository backup Size:3  .7mb[[email protected] ~]#

2.4 Simulate master node failure cleanup Data database directory.

[[email protected] ~]$/usr/local/pg10/bin/psql psql (10.0) Type ' help ' for help.postgres=# \c ghanyou is now connect  Ed to database "Ghan" as the 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 V 01:17 basedrwx------2 postgres postgres 4096 Nov 04:30 globaldrwx------2 postgres postgres 6 Nov 01:17 P G_COMMIT_TSDRWX------2 postgres postgres 6 Nov 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 10 04:3 0 pg_logicaldrwx------4 postgres postgres 01:17 pg_multixactdrwx------2 postgres postgres 10 04:3 0 pg_notifydrwx------2 postgres postgres 6 Nov 01:17 pg_replslotdrwx------2 postgres postgres 6 Nov 01:17 pg_serialdrwx------2 postgres postgres 6 Nov 01:17 pg_snapshotsdrwx--- ---2 postgres postgres 6 Nov 04:30 pg_statdrwx------2 postgres postgres------04:31 pg_stat_tmpdrwx 2 postgres postgres 01:19 pg_subtransdrwx------2 postgres postgres 6 Nov 01:17 pg_tblspcdrwx------2 Postgres postgres 6 Nov 01:17 pg_twophase-rwx------1 postgres postgres 3 Nov 5 07:23 pg_versiondrwx------3 p Ostgres postgres 4096 Nov 04:30 pg_waldrwx------2 postgres postgres------1 01:17 pg_xact-rwx P  Ostgres 5 07:23 postgresql.auto.conf-rwx------1 postgres postgres 22794 Nov 5 22:34 postgresql.conf-rw------- 1 postgres postgres postgres 04:30 postmaster.opts-rw-------1 postgres 04:30  -rw-rw-1 postgres postgres 01:17 recovery.done[[email protected] data]$ rm-rf *[[email protected] data]$ Lltotal 0[[email protected] data]$  

2.5 Performing a Restore command on the master node

[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 C Annot be started) 2017-11-10 04:34:41.180 P00   

2.6 Start the master node PostgreSQL for archive log restore

[[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 0        0: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-efpost Gres 14851 14805 0 04:36 pts/0 00:00:00 grep--color=auto ^post[[email protected] ~]$ etc archive log application finished, database normal Open Data recovery normal [[EMA  Il protected] ~]$/usr/local/pg10/bin/psql psql (10.0) Type ' help ' for help.postgres=# \c Ghanyou is now connected to  Database "Ghan" as User "Postgres". ghan=# Select COUNT (*) from T; Count-------58000 (1 row) ghan=#

PostgreSQL Backup tool-pg_backrest (GO)