Shulou(Shulou.com)06/01 Report--

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!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.