PostgreSQL DBA (28)-Backup&Recovery#1 (basic operation)
PostgreSQL Backup and Recovery operation is relatively simple, through a few simple commands and configuration to achieve backup and recovery.
The following is a simple example to illustrate the basic operations of PG backup and recovery.
Scene
1. Perform a backup
two。 Create a datasheet and perform an insert
3. Delete data
4. Use backup to restore to the state before the data was deleted
Parameter configuration
Modify the configuration file postgresql.conf
Archive_mode = on archive_command ='/ home/xdb/archive.sh'wal_level = replicamax_wal_size = 4GBmin_wal_size = 1024MB
The archive.sh script is as follows (the script is referenced from the Dego blog)
[xdb@localhost ~] $cat archive.sh #! / bin/bashexport LANG=en_US.utf8export PGHOME=/appdb/xdb/pg11.2export DATE= `date + "% Y%m%d" `export PATH=$PGHOME/bin:$PATH:.BASEDIR= "/ data/archivelog" if [!-d $BASEDIR/$DATE]; then mkdir-p $BASEDIR/$DATE if [!-d $BASEDIR/$DATE]; thenecho "error mkdir-p $BASEDIR/$DATE!" exit 1 fificp $1$ BASEDIR/$DATE/$2if [$?-eq 0] Then exit 0else echo-e "cp $1$ BASEDIR/$DATE/$2 error!" Exit 1fiecho-e "backup failed!" exit 1
The script copies WAL log to the $BASEDIR/$DATE directory
Modify log output at the same time
Log_destination = 'csvlog'logging_collector = onlog_directory =' pg_log'log_filename = 'postgresql-%Y-%m-%d.log'
Verify the configuration
Start the database
[xdb@localhost testdb] $pg_ctl startwaiting for server to start....2019-03-11 14 listening on IPv4 address 21V 08.591 CST [21847] LOG: listening on IPv4 address "0.0.0.0", port 54322019-03-11 14V 21V 08.591 CST [21847] LOG: listening on IPv6 address ":" Port 54322019-03-11 14 LOG 21V 08.609 CST [21847] LOG: listening on Unix socket "/ tmp/.s.PGSQL.5432" 2019-03-11 1414 LOG 21V 08.635 CST [21847] LOG: redirecting log output to logging collector process2019-03-11 1414 LOG 21V 08.635 CST [21847] HINT: Future log output will appear in directory "pg_log". Doneserver started [xdb@localhost testdb] $psql-d testdbpsql Type "help" for help.testdb=# show wal_level; wal_level-replica (1 row) testdb=# show archive_command; archive_command-/ home/xdb/archive.sh% p% f (1 row)
Switch Lo
Directory information before switching
[xdb@localhost] $ll $PGDATA/pg_waltotal 49152 Murray RWMI. 1 xdb xdb 16777216 Mar 11 14:21 0000000100000000000D Murray. 1 xdb xdb 16777216 Mar 11 14:20 00000001000000000EMurrwi. 1 xdb xdb 16777216 Mar 11 14:20 0000000100000000000Fdrwx Mar. 2 xdb xdb 6 Mar 11 14:21 archive_ status [XDB @ localhost ~] $ll $PGDATA/pg_wal/archive_status total 0 [xdb@localhost ~] $ll / data/archivelog/20190311/total 0
Perform handover
Testdb=# select pg_switch_wal (); pg_switch_wal-0/D0000E8 (1 row) testdb=# select pg_switch_wal (); pg_switch_wal-0/E000120 (1 row)
Directory information after switching
[xdb@localhost] $ll $PGDATA/pg_waltotal 49152 Murray RWMI. 1 xdb xdb 16777216 Mar 11 14:26 00000001000000000EMurrwi. 1 xdb xdb 16777216 Mar 11 14:26 00000001000000000F Murray. 1 xdb xdb 16777216 Mar 11 14:26 0000000100000000000010drwxmuri. 2 xdb xdb 43 Mar 11 14:26 archive_ status [XDB @ localhost ~] $ll $PGDATA/pg_wal/archive_status total 0Murrwmuri. 1 xdb xdb 0 Mar 11 14:26 00000001000000000000000E.done [xdb@localhost] $[xdb@localhost] $ll / data/archivelog/20190311/total 32768 Murray RWMI. 1 xdb xdb 16777216 Mar 11 14:26 0000000100000000000D Murray. 1 xdb xdb 16777216 Mar 11 14:26 00000001000000000000000E
Perform a backup
It can be realized by using the pg_basebackup command provided by PG.
[xdb@localhost] $pg_basebackup-D / data/backup/20190311-1 /-l 20190311-1-vpg_basebackup: initiating basebackup, waiting for checkpoint to completepg_basebackup: checkpoint completedpg_basebackup: write-ahead log start point: 0Greater 10000028 on timeline 1pg_basebackup: starting background WAL receiverpg_basebackup: created temporary replication slot "pg_basebackup_21910" pg_basebackup: write-ahead log end point: 0/10000130pg_basebackup: waiting for background process to finish streaming. Pg_basebackup: basebackup completed [xdb@localhost ~] $
-D specifies the directory where the backup is stored
-l specify LABEL
-v display diagnostic information
After execution, backup information can be obtained by viewing the / data/backup/20190311-1/backup_label file
[xdb@localhost ~] $cat / data/backup/20190311-1/backup_labelSTART WAL LOCATION: 0CSTLABEL 10000028 (file 0000000100000000000010) CHECKPOINT LOCATION: 0/10000060BACKUP METHOD: streamedBACKUP FROM: masterSTART TIME: 2019-03-11 14:31:42 CSTLABEL: 20190311-1START TIMELINE: 1
Simulated PITR
Insert data
Testdb=# create table tbl (id int); CREATE TABLEtestdb=# insert into tbl select generate_series (1m 1000000); INSERT 0 1000000testdb=# create table tbl2 (id int); CREATE TABLEtestdb=# insert into tbl2 select generate_series (1m 1000000); INSERT 0 1000000testdb=#
View archive log
[xdb@localhost] $ll $PGDATA/pg_waltotal 163848 Murray RWMI. 1 xdb xdb 16777216 Mar 11 14:31 00000001000000000F Murray. 1 xdb xdb 16777216 Mar 11 14:31 0000000100000000000010Murrw. 1 xdb xdb 325 Mar 11 14:31 0000000100000000000010.000028.backupMurray. 1 xdb xdb 16777216 Mar 11 14:34 0000000100000000000011Murrw. 1 xdb xdb 16777216 Mar 11 14:34 0000000100000000000012Murrw. 1 xdb xdb 16777216 Mar 11 14:34 0000000100000000000013Murrw. 1 xdb xdb 16777216 Mar 11 14:34 0000000100000000000014Murrw. 1 xdb xdb 16777216 Mar 11 14:34 0000000100000000000015Murrw. 1 xdb xdb 16777216 Mar 11 14:34 0000000100000000000016Murrw. 1 xdb xdb 16777216 Mar 11 14:34 0000000100000000000017Murrw. 1 xdb xdb 16777216 Mar 11 14:35 0000000100000000000018drwx Mar. 2 xdb xdb 4096 Mar 11 14:34 archive_ status [XDB @ localhost ~] $ll $PGDATA/pg_wal/archive_status total 0Murrwmuri. 1 xdb xdb 0 Mar 11 14:31 0000000100000000000F.Murray RW. 1 xdb xdb 0 Mar 11 14:31 0000000100000000000010.000028.backup.Mr. Murray. 1 xdb xdb 0 Mar 11 14:31 0000000100000000000010.copyright RWMI. 1 xdb xdb 0 Mar 11 14:34 0000000100000000000011.copyright RWMI. 1 xdb xdb 0 Mar 11 14:34 0000000100000000000012.copyright RWMI. 1 xdb xdb 0 Mar 11 14:34 000000010000000000013. 1 xdb xdb 0 Mar 11 14:34 0000000100000000000014. 1 xdb xdb 0 Mar 11 14:34 000000010000000000015. 1 xdb xdb 0 Mar 11 14:34 0000000100000000000016.copyright RWMI. 1 xdb xdb 0 Mar 11 14:34 000000010000000000000017.done [xdb@localhost] $[xdb@localhost] $ll / data/archivelog/20190311/total 180228 Murray RWMI. 1 xdb xdb 16777216 Mar 11 14:26 0000000100000000000D Murray. 1 xdb xdb 16777216 Mar 11 14:26 00000001000000000EMurrwi. 1 xdb xdb 16777216 Mar 11 14:31 00000001000000000F Murray. 1 xdb xdb 16777216 Mar 11 14:31 0000000100000000000010Murrw. 1 xdb xdb 325 Mar 11 14:31 0000000100000000000010.000028.backupMurray. 1 xdb xdb 16777216 Mar 11 14:34 0000000100000000000011Murrw. 1 xdb xdb 16777216 Mar 11 14:34 0000000100000000000012Murrw. 1 xdb xdb 16777216 Mar 11 14:34 0000000100000000000013Murrw. 1 xdb xdb 16777216 Mar 11 14:34 0000000100000000000014Murrw. 1 xdb xdb 16777216 Mar 11 14:34 0000000100000000000015Murrw. 1 xdb xdb 16777216 Mar 11 14:34 0000000100000000000016Murrw. 1 xdb xdb 16777216 Mar 11 14:34 0000000100000000000017 [xdb@localhost ~] $
Record the current time
Testdb=# select now (); now-- 2019-03-11 14 row 39 row 37.403147 0814)
Delete data
Testdb=# select now (); now-- 2019-03-11 14 row 40 testdb=# truncate table tbl;TRUNCATE TABLEtestdb=# truncate table tbl2;TRUNCATE TABLEtestdb=# 07.353201508 (1) testdb=# truncate table tbl;TRUNCATE TABLEtestdb=# truncate table tbl2;TRUNCATE TABLEtestdb=#
Restore
Shut down the database and recover data from base backup
[xdb@localhost testdb] $pg_ctl stopwaiting for server to shut down.... Doneserver stopped [xdb@localhost testdb] $lsbackup_label.old log pg_ident.conf pg_notify pg_stat pg_twophase postgresql.auto.confbase pg_commit_ts pg_log pg_replslot pg_stat_tmp PG_VERSION postgresql.confcurrent_logfiles pg_dynshmem pg_logical pg_serial pg_subtrans pg_wal postmaster.optsglobal pg_hba.conf pg_multixact Pg_snapshots pg_tblspc pg_ XTA [XDB @ localhost testdb] $[xdb@localhost testdb] $rm-rf * [xdb@localhost testdb] $cp-R / data/backup/20190311-1Compact *. / [xdb@localhost testdb] $
Create a recovery.conf file and specify the recovery time point
[xdb@localhost testdb] $vim recovery.conf [xdb@localhost testdb] $cat recovery.conf restore_command ='cp / data/archivelog/20190311/%f "% p" 'recovery_target_time='03-11-2019 14V 40V 00'
Perform recovery and verify
[xdb@localhost testdb] $pg_ctl startwaiting for server to start....2019-03-11 14 CST 35.034 CST [21986] LOG: listening on IPv4 address "0.0.0.0", port 54322019-03-11 14 CST 43V 35.034 CST [21986] LOG: listening on IPv6 address ":" Port 54322019-03-11 14 LOG 43 port 35.037 CST [21986] LOG: listening on Unix socket "/ tmp/.s.PGSQL.5432" 2019-03-11 14 LOG 43 pg_log 35.116 CST [21986] LOG: redirecting log output to logging collector process2019-03-11 14 LOG 43 pg_log CST [21986] HINT: Future log output will appear in directory "pg_log". Doneserver started
View log output
2019-03-11 14 ending log output to stderr 43 CST,21986,5c860397.55e2,1,2019 35.116 CST,21986,5c860397.55e2,1,2019-03-11 14:43:35 CST,0,LOG,00000, "ending log output to stderr", "Future log output will go to log destination"csvlog". "."2019-03-11 14 ending log output to stderr-11 14 ending log output to stderr 43 ending log output to stderr-03-11 14:43:35 CST,0,LOG,00000," database system was interrupted. Last known up at 2019-03-11 14:31:42 CST "," 2019-03-11 1414 CST,21988,5c860397.55e4,2,2019 4335.130 CST,21988,5c860397.55e4,2,2019-03-11 14:43:35 CST,0,LOG,00000, "starting point-in-time recovery to 2019-03-11 1440 CST,21988,5c860397.55e4,2,2019 0008", "2019-03-11 1414 CST,21988,5c860397.55e4,2,2019 4335.225 CST,21988,5c860397.55e4,3" 2019-03-11 14:43:35 CST,0,LOG,00000, "restored log file"0000000100000000000010"from archive", "2019-03-11 14purl 43purl 35.305 CST,21988,5c860397.55e4,4,2019-03-11 14:43:35 CST,1/0,0,LOG,00000," redo starts at 0plash 10000028 "," 2019-03-11 14443 CST,1/0,0,LOG,00000 35.306 CST,21988,5c860397.55e4,5 2019-03-11 14:43:35 CST,1/0,0,LOG,00000, "consistent recovery state reached at 0ram 10000130", "2019-03-11 14 CST,21986,5c860397.55e2,2,2019 4335.307 CST,21986,5c860397.55e2,2,2019-03-11 14:43:35 CST,0,LOG,00000," database system is ready to accept read only connections "," 2019-03-11 14 CST,21986,5c860397.55e2,2,2019 4333 CST,21988,5c860397.55e4,6 2019-03-11 14:43:35 CST,1/0,0,LOG,00000, "restored log file"0000000100000000000011"from archive", "2019-03-11 14purl 4343 CST,1/0,0,LOG,00000 35.972 CST,21988,5c860397.55e4,7,2019-03-11 14:43:35 CST,1/0,0,LOG,00000," restored log file "000000010000000000000012" from archive "2019-03-11 1443 purge 36.566 CST,21988 5c860397.55e4Power8 CST,1/0,0,LOG,00000 2019-03-11 14:43:35 CST,1/0,0,LOG,00000, "restored log file"0000000100000000000013"from archive", "2019-03-11 1414 CST,21988,5c860397.55e4,9,2019-03-11 14:43:35 CST,1/0,0,LOG,00000," restored log file "0000000100000000000014"from archive", "2019-03-11 1414 from archive 4343 CST,1/0,0,LOG,00000" 21988from archive from archive 5c860397.55e4 CST,1/0,0,LOG,00000, "restored log file"0000000100000000000015", "from archive", "2019-03-11 1414 from archive" 4338.432 CST,21988,5c860397.55e4,11,2019-03-11 14:43:35 CST,1/0,0,LOG,00000, "restored log file"00000001000000000016"from archive", "2019-03-11 1414 CST,1/0,0,LOG,00000 4343 CST" , 21988Jingjingjia 5c860397.55e4 CST,1/0,0,LOG,00000, "restored log file"0000000100000000000017"from archive", "2019-03-11 14141414 CST,1/0,0,LOG,00000 39.942 CST,21988,5c860397.55e4,13,2019-03-11 14:43:35 CST,1/0,0,LOG,00000," restored log file "00000001000000000018"from archive" "2019-03-11 14 recovery stopping before commit of transaction 40.315 CST,21988,5c860397.55e4,14,2019-03-11 14:43:35 CST,1/0,0,LOG,00000," recovery stopping before commit of transaction 577, time 2019-03-11 14 recovery stopping before commit of transaction 40 13. 662008 March 08 "," 2019-03-11 14 14 recovery stopping before commit of transaction 43 CST,21988,5c860397.55e4,15,2019-03-11 14:43:35 CST,1/0,0,LOG,00000, "recovery has paused" "Execute pg_wal_replay_resume () to continue."
Log prompt recovery has paused, connect to the database, execute pg_wal_replay_resume ()
Testdb=# select pg_wal_replay_resume (); pg_wal_replay_resume-(1 row)
The log output is as follows
2019-03-11 14 redo done at 0/18A8D8A0 47 CST,21988,5c860397.55e4,16,2019 44.741 CST,21988,5c860397.55e4,16,2019-03-11 14:43:35 CST,1/0,0,LOG,00000, "redo done at 0/18A8D8A0", "2019-03-11 14 14 redo done at 0/18A8D8A0 44.741 CST,21988,5c860397.55e4,17,2019-03-11 14:43:35 CST,1/0,0,LOG,00000," last completed transaction was at log time 2019-03-11 1435 Switzerland 10.895964 CST,1/0,0,LOG,00000 " , "2019-03-11 14 CST,21988,5c860397.55e4,19,2019 47 CST,1/0,0,LOG,00000 44.744 CST,21988,5c860397.55e4,18,2019-03-11 14:43:35 CST,1/0,0,LOG,00000," selected new timeline ID: 2 "," 2019-03-11 14 14 charge 47 CST,21988,5c860397.55e4,19,2019-03-11 14:43:35 CST,1/0,0,LOG,00000, "archive recovery complete" "2019-03-11 14 database system is ready to accept connections 47 CST,21986,5c860397.55e2,3,2019-03-11 14:43:35 CST,0,LOG,00000,"
Rename the recovery.conf file to recovery.done
Rename the backup_label file to backup_label.old
[xdb@localhost ~] $ls $PGDATA/recovery*/data/pgsql/testdb/recovery.done [xdb@localhost ~] $ls $PGDATA/backup_label*/data/pgsql/testdb/backup_label.old
Validate data
Testdb=# select count (*) from tbl; count-1000000 (1 row) testdb=# select count (*) from tbl2; count-1000000 (1 row)
references
Base Backup & Point-in-Time Recovery
