psql -h 192.168.1.201 -p $PGPORT -t -A -n -c"SELECT pg_catalog.pg_tablespace_location(oid) AS "Location" FROM pg_catalog.pg_tablespace where spcname not in ('pg_default','pg_global')" -o tablespace_location.txtawk '{print "rm -rf " $1}' tablespace_location.txt > cleandir.shecho "rm -rf $PGDATA" >> cleandir.sh 

cleandir.shpg_basebackup -F p --progress -D $PGDATA -h 192.168.1.201 -p $PGPORT -U replica 

5 0 * * * pg_backup.sh 

restore_command = 'scp postgres@192.168.1.201:**/%f %p'recovery_target_time = '2018-11-10 10:33:12' 

pg_flashback.sh 1 

pg_flashback.sh 2 

##===========================================================  ##   pg_flashback.sh                ##   created by lottu           ##   2018/11/07    ##   usage: pg_flashback.sh 1                          ##============================================================  #!/bin/bashPGHOME=/opt/pgsql96PGDATA=/data/postgres/data              LANG=en_US.utf8PGPORT=5432PGUSER=postgresPGDATABASE=postgresPG_BACK_HOST_IP=192.168.1.202E_BADARGS=65E_FAIL=66if [ $# -ne 1 ];then echo "Usage: `basename $0` Please invoke this script with one command-line arguments" exit $E_BADARGSfiflashback_path=/data/flash_backif [ $1 -eq 1 ];then echo "Info: backup backupset" backup.shelse rebackup.shfi echo "Info: edit recovery.conf"#scp recovery.conf postgres@192.168.1.202:$PGDATAcp /home/postgres/recovery.conf $PGDATAecho "Info: begin start database"pg_ctl start -l /home/postgres/log/pg_server.logif [ $? -eq 0 ];then echo "Info: start database succes" sleep 10 psql -h $PG_BACK_HOST_IP -p 5432 postgres postgres -c "select pg_xlog_replay_resume()"else echo "[Error]: start database fail" exit $E_FAILfi 

[postgres@Postgres202 ~]$ pg_backup.sh waiting for server to shut down......... doneserver stopped 322503/322503 kB (100%), 4/4 tablespaces 

lottu=# create table lottu as select * from lottu01 ; SELECT 4 lottu=# select * from lottu; id | text ------+------- 1001 | lottu 1004 | rax 1002 | world 1003 | world (4 rows) -- 获取时间点；这个时间点“2018-11-10 14:56:56”是我目标恢复时间点。表lottu有4条记录。lottu=# select now(); now ------------------------------ 2018-11-10 14:56:56.30188+08 (1 row) -- 在时间点“2018-11-10 14:57:51”；误操作1 清理表lottu的记录lottu=# delete from lottu; DELETE 4 lottu=# select now(); now ------------------------------- 2018-11-10 14:57:51.891931+08 (1 row) -- 误操作2 删除表lottu。lottu=# drop table lottu; DROP TABLE 

[postgres@Postgres202 ~]$ pg_flashback.sh 1 Info: backup backupset Info: edit recovery.conf Info: begin start databaseserver starting Info: start database succespsql: FATAL: the database system is starting up 

[postgres@Postgres202 ~]$ psql lottu lottupsql (9.6.0) Type "help" for help. lottu=# \d lottu Table "lottu.lottu" Column | Type | Modifiers --------+---------+----------- id | integer | name | text | lottu=# select * from lottu; id | name ----+------ (0 rows) 

[postgres@Postgres202 ~]$ pg_flashback.sh 2 Info: edit recovery.conf Info: begin start databaseserver starting Info: start database successpsql: FATAL: the database system is starting up 

[postgres@Postgres202 ~]$ psql lottu lottupsql (9.6.0) Type "help" for help. lottu=# \d lottu Table "lottu.lottu" Column | Type | Modifiers --------+---------+----------- id | integer | name | text | lottu=# select * from lottu; id | name ------+------- 1001 | lottu 1004 | rax 1002 | world 1003 | world (4 rows) 

[postgres@Postgres202 ~]$ pg_dump -d lottu -t lottu.lottu -f lottu.sql [postgres@Postgres202 ~]$ scp lottu.sql postgres@192.168.1.201:/home/postgres 

[postgres@Postgres201 ~]$ psql lottu lottu psql (9.6.0) Type "help" for help. lottu=# \i lottu.sql SET SET SET SET SET SET SET SET SET SET SET CREATE TABLE ALTER TABLE COPY 4 lottu=# \d lottu Table "lottu.lottu" Column | Type | Modifiers --------+---------+----------- id | integer | name | text | lottu=# select * from lottu; id | name ------+------- 1001 | lottu 1004 | rax 1002 | world 1003 | world (4 rows)