pg基于时间点的恢复（PITR）

[postgres9.6@db ~]$ psql postgres postgres9.6
Null display is "NULL".
Pager is always used.
Timing is on.
psql (9.6.1)
Type "help" for help.

postgres9.6@[local]:5432 postgres# show wal_level;
 wal_level 
-----------
 replica
(1 row)

Time: 0.270 ms
postgres9.6@[local]:5432 postgres# show archive_mode;
 archive_mode 
--------------
 on
(1 row)

Time: 0.214 ms
postgres9.6@[local]:5432 postgres# show archive_command ;
                                            archive_command                                             
--------------------------------------------------------------------------------------------------------
 DATE=`date +%Y%m%d`;DIR="/home/postgres9.6/arch/$DATE";(test -d $DIR || mkdir -p $DIR)&& cp %p $DIR/%f
(1 row)


Time: 0.188 ms


postgres9.6@[local]:5432 postgres# select version();
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 9.6.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-4), 64-bit
(1 row)


Time: 2.709 ms


postgres9.6@[local]:5432 postgres# \c test test
You are now connected to database "test" as user "test".
test@[local]:5432 test# \conninfo
You are connected to database "test" as user "test" via socket in "/tmp" at port "5432".
test@[local]:5432 test# select pg_start_backup('test');                 ********************普通用户无权限执行
ERROR:  permission denied for function pg_start_backup
Time: 0.285 ms


postgres9.6@[local]:5432 test# \conninfo
You are connected to database "test" as user "postgres9.6" via socket in "/tmp" at port "5432".
postgres9.6@[local]:5432 test# create table test(id integer); 
CREATE TABLE
Time: 1.472 ms
postgres9.6@[local]:5432 test#  insert into test values(100);     
INSERT 0 1
Time: 2.224 ms
postgres9.6@[local]:5432 test# select * from test;
 id  
-----
 100
(1 row)


Time: 0.301 ms


postgres9.6@[local]:5432 test#  select pg_start_backup('gao');     
 pg_start_backup 
-----------------
 0/48000028
(1 row)


Time: 2176.125 ms


postgres9.6@[local]:5432 test# \! tar -cvf ./base.tar ./data
...
./data/base/13269/826_vm
./data/base/13269/1247_vm
./data/pg_subtrans/
./data/pg_subtrans/0000
./data/PG_VERSION
./data/backup_label
./data/pg_snapshots/
./data/pg_multixact/
./data/pg_multixact/members/
./data/pg_multixact/members/0000
./data/pg_multixact/offsets/
./data/pg_multixact/offsets/0000
./data/pg_ident.conf
./data/pg_serial/
./data/pg_clog/
./data/pg_clog/0000
./data/pg_notify/
./data/pg_notify/0000
postgres9.6@[local]:5432 test# 


postgres9.6@[local]:5432 test# select pg_stop_backup();     
NOTICE:  pg_stop_backup complete, all required WAL segments have been archived
 pg_stop_backup 
----------------
 0/48000130
(1 row)


Time: 1086.819 ms


postgres9.6@[local]:5432 test# select current_time;
       timetz       
--------------------
 17:29:44.224941+08
(1 row)


Time: 0.922 ms


postgres9.6@[local]:5432 test# insert into test values(200); 
INSERT 0 1
Time: 2.765 ms
postgres9.6@[local]:5432 test#  insert into test values(300);     
INSERT 0 1
Time: 1.772 ms
postgres9.6@[local]:5432 test# select current_time;
       timetz       
--------------------
 17:30:11.306922+08
(1 row)


Time: 0.265 ms


postgres9.6@[local]:5432 test# select pg_switch_xlog(); 
 pg_switch_xlog 
----------------
 0/49000210
(1 row)


[postgres9.6@db ~]$ pg_stop
waiting for server to shut down.... done
server stopped
[postgres9.6@db ~]$ 
[postgres9.6@db ~]$ 
[postgres9.6@db ~]$ ls
arch  base.tar  data  log
[postgres9.6@db ~]$ mv data data.bak


[postgres9.6@db ~]$  tar -xvf base.tar ./data
...
./data/PG_VERSION
./data/backup_label
./data/pg_snapshots/
./data/pg_multixact/
./data/pg_multixact/members/
./data/pg_multixact/members/0000
./data/pg_multixact/offsets/
./data/pg_multixact/offsets/0000
./data/pg_ident.conf
./data/pg_serial/
./data/pg_clog/
./data/pg_clog/0000
./data/pg_notify/
./data/pg_notify/0000
[postgres9.6@db ~]$ 




[postgres9.6@db ~]$ rm -rf ./data/pg_xlog  
[postgres9.6@db ~]$ cp -r ./data.bak/pg_xlog/ ./data  
[postgres9.6@db ~]$  cd ./data/pg_xlog  
[postgres9.6@db pg_xlog]$ ls
000000010000000000000048                  000000010000000000000049  00000001000000000000004B  00000001000000000000004D
000000010000000000000048.00000028.backup  00000001000000000000004A  00000001000000000000004C  archive_status




[postgres9.6@db pg_xlog]$ pwd
/home/postgres9.6/data/pg_xlog
[postgres9.6@db pg_xlog]$ ls
000000010000000000000048                  000000010000000000000049  00000001000000000000004B  00000001000000000000004D
000000010000000000000048.00000028.backup  00000001000000000000004A  00000001000000000000004C  archive_status
[postgres9.6@db pg_xlog]$ cd archive_status/
[postgres9.6@db archive_status]$ ls
000000010000000000000048.00000028.backup.done  000000010000000000000048.done  000000010000000000000049.done  00000001000000000000004A.done
[postgres9.6@db archive_status]$ rm -rf *


[postgres9.6@db data]$ pwd
/home/postgres9.6/data
[postgres9.6@db data]$ cat recovery.conf 
restore_command = 'cp /home/postgres9.6/arch/%f %p'                    
recovery_target_time = '2017-02-08 17:30:00+08'


[postgres9.6@db data]$ pg_ctl start
server starting
[postgres9.6@db data]$ LOG:  database system was shut down at 2017-02-08 17:36:34 CST
LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started


[postgres9.6@db data]$ psql test postgres9.6
Null display is "NULL".
Pager is always used.
Timing is on.
psql (9.6.1)
Type "help" for help.


postgres9.6@[local]:5432 test#  select * from test;
 id  
-----
 100
(1 row)


Time: 1.345 ms