Incremental recovery of postgres
DBA children's shoes must be very familiar with the concept of incremental recovery, similar to the incremental recovery of mysql, using "T1 moment complete" + "T1 to T2 time wal log", you can restore postgres to T2 time.
Preliminary preparation:
Configure postgres.conf:
Wal_level=archive or hot_standby or higher
Archive_mode = on
Archive_command='DATE=date +% Y% m% dash Dir = "/ paic/pg6666/pg_archlog/$DATE"; (test-d $DIR | | mkdir-p $DIR) & & cp% p $DIR/%f'
Backup script backup.sh:
#! / bin/bash export LANG=en_US.utf8 export PGHOME=/paic/postgres/base/9.4.0 export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH export DATE= `date + "% Y%m%d" `export PATH=$PGHOME/bin:$PATH:. Export PGDATA=/paic/pg6666/dataBASEDIR= "/ paic/postgres/home/postgres/pg_bak" date +% Fmuri% T if [!-d $BASEDIR/$DATE]; then mkdir-p $BASEDIR/$DATE if [$?-eq 0]; then psql-h 127.0.0.1-p 6666-U postgres postgres-c "select pg_start_backup (now ():: text)" if [$?-eq 0] Then cp-r-L $PGDATA $BASEDIR/$DATE else echo-e "select pg_start_backup (now ():: text) error" exit 1 fi psql-h 127.0.0.1-p 6666-U postgres postgres-c "select pg_stop_backup ()" date +% Flam% T echo-e "backup successed" exit 0 else echo-e "mkdir-p $BASEDIR/$DATE error" exit 1 fi else echo-e "$DATE backuped Don't backup repeated "exit 1 fi
Restore script recovery.sh:
#! / bin/bash export LANG=en_US.utf8 export PGHOME=/paic/postgres/base/9.4.0 export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH export PATH=$PGHOME/bin:$PATH:. Export PGDATA=/paic/pg6666/dataexport DATE= `date + "% Y%m%d" `if [- z "$1"]; then echo "1st argument is empty!" else if [- z "$2"]; then echo "2nd argument is empty!" Else if [- f $PGDATA/postmaster.pid]; then echo "shutdown database first!" Else cd $PGDATA rm-rf * cp-r / paic/postgres/home/postgres/pg_bak/$DATE/data/* $PGDATA/ cd $PGDATA/pg_xlog rm-rf * cd $PGDATA cp $PGHOME/share/recovery.conf.sample. / recovery.conf echo restore_command =\'cp / paic/pg6666/pg_archlog/$DATE/%f% p\'> >. / recovery.conf echo recovery_target_time =\'$1 $2\'> >. / recovery.conf pg_ctl start fi fifi
Please modify the directories in backup.sh and recovery.sh.
Simulate failure recovery:
1. Preparation stage
-bash-4.1$ psql
Psql (9.4.0)
Type "help" for help.
Postgres=#\ c mydb alex
You are now connected to database "mydb" as user "alex".
Confirm initial state of the database
Mydb=#\ d
List of relations
Schema | Name | Type | Owner
-+-
Public | test | table | alex
(1 row)
At this point, the mydb database has only test tables.
Create aaa
Mydb=# create table aaa (id int)
CREATE TABLE
Mydb=#\ d
List of relations
Schema | Name | Type | Owner
-+-
Public | aaa | table | alex
Public | test | table | alex
(2 rows)
Mydb=# checkpoint;-make sure the changes are written to the file (optional)
CHECKPOINT
Mydb=# select pg_switch_xlog ();-- make sure changes are written to the archive (optional)
Pg_switch_xlog
0/E6000120
(1 row)
The above operation was completed before 13:52:00 on 2017-12-22.
Then at 13:53:00 on 2017-12-22, create the table bbb
Mydb=# create table bbb (id int)
CREATE TABLE
Mydb=#\ d
List of relations
Schema | Name | Type | Owner
-+-
Public | aaa | table | alex
Public | bbb | table | alex
Public | test | table | alex
(3 rows)
Mydb=# checkpoint
CHECKPOINT
Mydb=# select pg_switch_xlog ()
Pg_switch_xlog
0/E7013FC0
(1 row)
Mydb=#\ Q
The above operation was completed before 13:54:00 on 2017-12-22.
two。 Now try to roll back the database to the specified point in time
-bash-4.1 $pg_ctl stop-m fast
Waiting for server to shut down. Done
Server stopped
-bash-4.1$ cd-- my recovery.sh file is in the home directory, so I need to change the directory
-bash-4.1 $. Recovery.sh 2017-12-22 13:52:00-pass in the time parameter $1RV 2017-12-22, $2RV 13RV 52JUR 00 and execute the script
Pg_ctl: another server might be running; trying to start server anyway
Server starting
-bash-4.1 $2017-12-22 13:54:26 HKT:undefined: [13323]: LOG: redirecting log output to logging collector process
2017-12-22 13:54:26 HKT:undefined: [13323]: HINT: Future log output will appear in directory "/ paic/pg6666/data/pg_log".
-bash-4.1 $
-bash-4.1 $
-bash-4.1$ psql
Psql (9.4.0)
Type "help" for help.
Postgres=#\ c mydb alex
You are now connected to database "mydb" as user "alex".
Mydb=#\ d
List of relations
Schema | Name | Type | Owner
-+-
Public | aaa | table | alex
Public | test | table | alex
(2 rows)
Mydb=#\ Q
You can see that the database has been rolled back to 2017-12-22 13:52:00, and the table aaa status has just been created.
Continue testing
-bash-4.1 $pg_ctl stop
Waiting for server to shut down.... Done
Server stopped
-bash-4.1$ cd
-bash-4.1 $. Recovery.sh 2017-12-22 13:53:10
Pg_ctl: another server might be running; trying to start server anyway
Server starting
-bash-4.1 $2017-12-22 13:56:39 HKT:undefined: [13390]: LOG: redirecting log output to logging collector process
2017-12-22 13:56:39 HKT:undefined: [13390]: HINT: Future log output will appear in directory "/ paic/pg6666/data/pg_log".
-bash-4.1 $
-bash-4.1$ psql
Psql (9.4.0)
Type "help" for help.
Postgres=#\ c mydb alex
You are now connected to database "mydb" as user "alex".
Mydb=#\ d
List of relations
Schema | Name | Type | Owner
-+-
Public | aaa | table | alex
Public | bbb | table | alex
Public | test | table | alex
(3 rows)
You can see that the database has been rolled back to 2017-12-22 13:53:10, and the status of table bbb has just been created.
3. Now simulate the rollback of the erroneous operation of deleting tables
2017-12-22 14:01:00 delete table test
Mydb=# drop table test
DROP TABLE
Mydb=#\ d
List of relations
Schema | Name | Type | Owner
-+-
Public | aaa | table | alex
Public | bbb | table | alex
Mydb=# checkpoint
CHECKPOINT
Mydb=# select pg_switch_xlog ()
Pg_switch_xlog
0/E9005140
(1 row)
Mydb=#\ Q
Perform recovery.sh rollback of the database
-bash-4.1 $pg_ctl stop
Waiting for server to shut down.... Done
Server stopped
-bash-4.1$ cd
-bash-4.1 $. Recovery.sh 2017-12-22 14:00:00
Pg_ctl: another server might be running; trying to start server anyway
Server starting
-bash-4.1 $2017-12-22 14:02:09 HKT:undefined: [13583]: LOG: redirecting log output to logging collector process
2017-12-22 14:02:09 HKT:undefined: [13583]: HINT: Future log output will appear in directory "/ paic/pg6666/data/pg_log".
-bash-4.1 $
-bash-4.1$ psql
Psql (9.4.0)
Type "help" for help.
Postgres=#\ c mydb alex
You are now connected to database "mydb" as user "alex".
Mydb=#\ d
List of relations
Schema | Name | Type | Owner
-+-
Public | aaa | table | alex
Public | bbb | table | alex
Public | test | table | alex
(3 rows)
You can see that the database has been rolled back to the state that table test was not deleted when it was rolled back to 13:14:00 on 2017-12-22.
This lab is limited to simulation in the test environment, which helps to understand the backup and recovery mechanism of postgres and forbids it to be used in production!
