PostgreSQL物理备份恢复之 pg_rman的用法说明

简介

类似oracle 的 rman, 用于 postgres的备份与恢复

下载

https://github.com/ossc-db/pg_rman/tree/V1.3.9

安装

tar zxvf pg_rman*.tar.gz
chown postgres.postgres -R pg_rman*
su - postgres
cd xxx/pg_rman*
make && make install 

使用

开启归档

[postgres@node_206 /postgresql/pgsql/archive_log]$tail -3 /postgresql/pgsql/data/postgresql.conf 
#for pg_Rman
archive_mode = on # enables archiving; off, on, or always
archive_command = 'test ! -f /postgresql/pgsql/archive_log/%f && cp %p /postgresql/pgsql/archive_log/%f'

重启 PG

pg_ctl restart -m fast

初始化

pg_rman init -B /postgresql/pgsql/pg_rman_backups

全量备份

pg_rman backup --backup-mode=full -B /postgresql/pgsql/pg_rman_backups

实例

[postgres@node_206 /postgresql/pgsql]$pg_rman init -B /postgresql/pgsql/pg_rman_backups/
INFO: ARCLOG_PATH is set to '/postgresql/pgsql/archive_log'
INFO: SRVLOG_PATH is set to '/postgresql/pgsql/pg_log'
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$vim ~/.bash_profile 
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$source !$
source ~/.bash_profile
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$psql
psql (12.3)
Type "help" for help.
postgres=# \l
                 List of databases
  Name  | Owner  | Encoding |  Collate  |  Ctype  |  Access privileges  
-----------+----------+----------+-------------+-------------+-----------------------
 postgres | postgres | UTF8   | en_US.UTF-8 | en_US.UTF-8 | 
 repmgr  | repmgr  | UTF8   | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8   | en_US.UTF-8 | en_US.UTF-8 | =c/postgres     +
      |     |     |       |       | postgres=CTc/postgres
 template1 | postgres | UTF8   | en_US.UTF-8 | en_US.UTF-8 | =c/postgres     +
      |     |     |       |       | postgres=CTc/postgres
 test   | postgres | UTF8   | en_US.UTF-8 | en_US.UTF-8 | 
(5 rows)
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# select * from test;
 id |     crt_time     
----+----------------------------
 1 | 2020-11-17 23:23:31.407616
 2 | 2020-11-17 23:23:31.407728
 3 | 2020-11-17 23:23:31.407731
 4 | 2020-11-17 23:23:31.407732
 5 | 2020-11-17 23:23:31.407732
 6 | 2020-11-17 23:23:31.407733
 7 | 2020-11-17 23:23:31.407733
 8 | 2020-11-17 23:23:31.407734
 9 | 2020-11-17 23:23:31.407734
 10 | 2020-11-17 23:23:31.407735
(10 rows)
test=# insert into test (id) select n from generate_series(11,20) n;
INSERT 0 10
test=# \q
[postgres@node_206 /postgresql/pgsql]$pg_rman backup --backup-mode=full -B /postgresql/pgsql/pg_rman_backups
INFO: copying database files
INFO: copying archived WAL files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.
[postgres@node_206 /postgresql/pgsql]$pg_rman validate -B /postgresql/pgsql/pg_rman_backups
INFO: validate: "2020-11-17 23:30:25" backup and archive log files by CRC
INFO: backup "2020-11-17 23:30:25" is valid
[postgres@node_206 /postgresql/pgsql]$pg_rman show 
ERROR: required parameter not specified: BACKUP_PATH (-B, --backup-path)
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$pg_rman show -B /postgresql/pgsql/pg_rman_backups/
=====================================================================
 StartTime      EndTime       Mode  Size  TLI Status 
=====================================================================
2020-11-17 23:30:25 2020-11-17 23:30:27 FULL  64MB   6 OK
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$psql
psql (12.3)
Type "help" for help.
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# insert into test (id) select n from generate_series(21,30) n;
INSERT 0 10
test=# \q
[postgres@node_206 /postgresql/pgsql]$pg_rman backup --backup-mode=incremental -B /postgresql/pgsql/pg_rman_backups
INFO: copying database files
INFO: copying archived WAL files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.
[postgres@node_206 /postgresql/pgsql]$pg_rman validate -B /postgresql/pgsql/pg_rman_backups
INFO: validate: "2020-11-17 23:31:08" backup and archive log files by CRC
INFO: backup "2020-11-17 23:31:08" is valid
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$pg_rman show -B /postgresql/pgsql/pg_rman_backups/
=====================================================================
 StartTime      EndTime       Mode  Size  TLI Status 
=====================================================================
2020-11-17 23:31:08 2020-11-17 23:31:10 INCR  33MB   6 OK
2020-11-17 23:30:25 2020-11-17 23:30:27 FULL  64MB   6 OK
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$psql
psql (12.3)
Type "help" for help.
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# drop table test;
DROP TABLE
test=# \q
[postgres@node_206 /postgresql/pgsql]$pg_ctl stop -m fast
waiting for server to shut down.... done
server stopped
[postgres@node_206 /postgresql/pgsql]$pg_ctl start
waiting for server to start....2020-11-17 10:32:09.637 EST [58824] LOG: 00000: starting PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2020-11-17 10:32:09.637 EST [58824] LOCATION: PostmasterMain, postmaster.c:998
2020-11-17 10:32:09.637 EST [58824] LOG: 00000: listening on IPv4 address "0.0.0.0", port 5432
2020-11-17 10:32:09.637 EST [58824] LOCATION: StreamServerPort, pqcomm.c:593
2020-11-17 10:32:09.637 EST [58824] LOG: 00000: listening on IPv6 address "::", port 5432
2020-11-17 10:32:09.637 EST [58824] LOCATION: StreamServerPort, pqcomm.c:593
2020-11-17 10:32:09.643 EST [58824] LOG: 00000: listening on Unix socket "/tmp/.s.PGSQL.5432"
2020-11-17 10:32:09.643 EST [58824] LOCATION: StreamServerPort, pqcomm.c:587
2020-11-17 10:32:09.688 EST [58824] LOG: 00000: redirecting log output to logging collector process
2020-11-17 10:32:09.688 EST [58824] HINT: Future log output will appear in directory "/postgresql/pgsql/pg_log".
2020-11-17 10:32:09.688 EST [58824] LOCATION: SysLogger_Start, syslogger.c:675
 done
server started
[postgres@node_206 /postgresql/pgsql]$psql -d test
psql (12.3)
Type "help" for help.
test=# insert into test (id ) select n from generate_series(21,30) n;
ERROR: relation "test" does not exist
LINE 1: insert into test (id ) select n from generate_series(21,30) ...
          ^
test=# insert into test (id ) select n from generate_series(21,30) n;
ERROR: relation "test" does not exist
LINE 1: insert into test (id ) select n from generate_series(21,30) ...
          ^
test=# \d test
Did not find any relation named "test".
test=# \q
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$repmgr cluster show
 ID | Name   | Role  | Status    | Upstream  | Location | Priority | Timeline | Connection string                     
----+----------+---------+---------------+------------+----------+----------+----------+-------------------------------------------------------------
 1 | node_206 | primary | * running   |      | default | 100   | 6    | host=node_206 user=repmgr dbname=repmgr connect_timeout=2
 2 | node_205 | standby | ? unreachable | ? node_206 | default | 100   |     | host=node_205 user=repmgr dbname=repmgr connect_timeout=2 
WARNING: following issues were detected
 - unable to connect to node "node_205" (ID: 2)
 - node "node_205" (ID: 2) is registered as an active standby but is unreachable
HINT: execute with --verbose option to see connection error messages
[postgres@node_206 /postgresql/pgsql]$pg_rman show -B /postgresql/pgsql/pg_rman_backups/
=====================================================================
 StartTime      EndTime       Mode  Size  TLI Status 
=====================================================================
2020-11-17 23:31:08 2020-11-17 23:31:10 INCR  33MB   6 OK
2020-11-17 http://www.devze.com23:30:25 2020-11-17 23:30:27 FULL  64MB   6 OK
[postgres@node_206 /postgresql/pgsql]$pg_rman restore -B /postgresql/pgsql/pg_rman_backups/ --restore-target-time='2020-11-17 23:30:25'
pg_rman: unrecognized option '--restore-target-time=2020-11-17 23:30:25'
ERROR: option is not specified
HINT: Try "pg_rman --help" for more information.
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$pg_rman restore -B /postgresql/pgsql/pg_rman_backups/ --recovery-target-time='2020-11-17 23:30:25'
ERROR: PostgreSQL server is running
HINT: Please stop PostgreSQL server before executing restore.
[postgres@node_206 /postgresql/pgsql]$pg_ctl stop -m fast
waiting for server to shut down.... done
server stopped
[postgres@node_206 /postgresql/pgsql]$pg_rman restore -B /postgresql/pgsql/pg_rman_backups/ --recovery-target-time='2020-11-17 23:30:25'
ERROR: cannot do restore
DETAIL: There is no valid full backup which can be used for given recovery condition.
[postgres@node_206 /postgresql/pgsql]$pg_rman validate -B /postgresql/pgsql/pg_rman_backups
[postgres@node_206 /postgresql/pgsql]$pg_rman restore -B /postgresql/pgsql/pg_rman_backups/ --recovery-target-time='2020-11-17 23:30:25'
ERROR: cannot do restore
DETAIL: There is no valid full backup which can be used for given recovery condition.
[postgres@node_206 /postgresql/pgsql]$pg_ctl start
waiting for server to start....2020-11-17 10:34:02.558 EST [58862] LOG: 00000: starting PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2020-11-17 10:34:02.558 EST [58862] LOCATION: PostmasterMain, postmaster.c:998
2020-11-17 10:34:02.559 EST [58862] LOG: 00000: listening on IPv4 address "0.0.0.0", port 5432
2020-11-17 10:34:02.559 EST [58862] LOCATION: StreamServerPort, pqcomm.c:593
2020-11-17 10:34:02.559 EST [58862] LOG: 00000: listening on IPv6 address "::", port 5432
2020-11-17 10:34:02.559 EST [58862] LOCATION: StreamServerPort, pqcomm.c:593
2020-11-17 10:34:02.565 EST [58862] LOG: 00000: listening on Unix socket "/tmp/.s.PGSQL.5432"
2020-11-17 10:34:02.565 EST [58862] LOCATION: StreamServerPort, pqcomm.c:587
2020-11-17 10:34:02.608 EST [58862] LOG: 00000: redirecting log output to logging collector process
2020-11-17 10:34:02.608 EST [58862] HINT: Future log output will appear in directory "/postgresql/pgsql/pg_log".
2020-11-17 10:34:02.608 EST [58862] LOCATION: SysLogger_Start, syslogger.c:675
 done
server started
[postgres@node_206 /postgresql/pgsql]$pg_rman show -B /postgresql/pgsql/pg_rman_backups/
=====================================================================
 StartTime      EndTime       Mode  Size  TLI Status 
=====================================================================
2020-11-17 23:31:08 2020-11-17 23:31:10 INCR  33MB   6 OK
2020-11-17 23:30:25 2020-11-17 23:30:27 FULL  64MB   6 OK
[postgres@node_206 /postgresql/pgsql]$pg_rman validate -B /postgresql/pgsql/pg_rman_backups
[postgres@node_206 /postgresql/pgsql]$pg_ctl stop
waiting for server to shut down.... done
server stopped
[postgres@node_206 /postgresql/pgsql]$pg_rman restore -B /postgresql/pgsql/pg_rman_backups/ --recovery-target-time='2020-11-17 23:30:25'
ERROR: cannot do restore
DETAIL: There is no valid full backup which can be used for given recovery condition.
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$pg_ctl start
waiting for server to start....2020-11-17 10:34:22.842 EST [58881] LOG: 00000: starting PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2020-11-17 10:34:22.842 EST [58881] LOCATION: PostmasterMain, postmaster.c:998
2020-11-17 10:34:22.842 EST [58881] LOG: 00000: listening on IPv4 address "0.0.0.0", port 5432
2020-11-17 10:34:22.842 EST [58881] LOCATION: StreamServerPort, pqcomm.c:593
2020-11-17 10:34:22.842 EST [58881] LOG: 00000: listening on IPv6 address "::", port 5432
2020-11-17 10:34:22.842 EST [58881] LOCATION: StreamServerPort, pqcomm.c:593
2020-11-17 10:34:22.846 EST [58881] LOG: 00000: listening on Unix socket "/tmp/.s.PGSQL.5432"
2020-11-17 10:34:22.846 EST [58881] LOCATION: StreamServerPort, pqcomm.c:587
2020-11-17 10:34:22.888 EST [58881] LOG: 00000: redirecting log output to logging collector process
2020-11-17 10:34:22.888 EST [58881] HINT: Future log output will appear in directory "/postgresql/pgsql/pg_log".
2020-11-17 10:34:22.888 EST [58881] LOCATION: SysLogger_Start, syslogger.c:675
 done
server started
[postgres@node_206 /postgresql/pgsql]$pg_rman backup --backup-mode=full -B /postgresql/pgsql/pg_rman_backups
INFO: copying database files
INFO: copying archived WAL files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.
[postgres@node_206 /postgresql/pgsql]$pg_rman validate -B /postgresql/pgsql/pg_rman_backups
INFO: validate: "2020-11-17 23:34:26" backup and archive log files by CRC
INFO: backup "2020-11-17 23:34:26" is valid
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$pg_rman show -B /postgresql/pgsql/pg_rman_backups/
=====================================================================
 StartTime      EndTime       Mode  Size  TLI Status 
=====================================================================
2020-11-17 23:34:26 2020-11-17 23:34:28 FULL  114MB   6 OK
2020-11-17 23:31:08 2020-11-17 23:31:10 INCR  33MB   6 OK
2020-11-17 23:30:25 2020-11-17 23:30:27 FULL  64MB   6 OK
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$psql 
psql (12.3)
Type "help" for help.
postgres=# \l
                 List of databases
  Name  | Owner  | Encoding |  Collate  |  Ctype  |  Access privileges  
-----------+----------+----------+-------------+-------------+-----------------------
 postgres | postgres | UTF8   | en_US.UTF-8 | en_US.UTF-8 | 
 repmgr  | repmgr  | UTF8   | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8   | en_US.UTF-8 | en_US.UTF-8 | =c/postgres     +
      |     |     |       |       | postgres=CTc/postgres
 template1 | postgres | UTF8   | en_US.UTF-8 | en_US.UTF-8 | =c/postgres     +
      |     |     |       |       | postgres=CTc/postgres
 test   | postgres | UTF8   | en_US.UTF-8 | en_US.UTF-8 | 
(5 rows)
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# \dt
Did not find any relations.
test=# create table test(id int); 
CREATE TABLE
test=# drop table test(id int);
ERROR: syntax error at or near "("
LINE 1: drop table test(id int);
            ^
test=# 
test=# \q
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$vim $PGDATA/postgresql.conf
postgres=# \l
                 List of databases
  Name  | Owner  | Encoding |  Collate  |  Ctype  |  Access privileges  
-----------+----------+----------+-------------+-------------+-----------------------
 postgres | postgres | UTF8   | en_US.UTF-8 | en_US.UTF-8 | 
 repmgr  | repmgr  | UTF8   | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8   | en_US.UTF-8 | en_US.UTF-8 | =c/postgres     +
      |     |     |       |       | postgres=CTc/postgres
 template1 | postgres | UTF8   | en_US.UTF-8 | en_US.UTF-8 | =c/postgres     +
      |     |     |       |       | postgres=CTc/postgres
 test   | postgres | UTF8   | en_US.UTF-8 | en_US.UTF-8 | 
(5 rows)
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# \dt
Did not find any relations.
test=# create table test(id int); 
CREATE TABLE
test=# drop table test(id int);
ERROR: syntax error at or near "("
LINE 1: drop table test(id int);
            ^
test=# 
test=# \q
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$vim $PGDATA/postgresql.conf
[postgres@node_206 /postgresql/pgsql]$vim $PGDATA/postgresql.conf
                 List of databases
  Name  | Owner  | Encoding |  Collate  |  Ctype  |  Access privileges  
-----------+----------+----------+-------------+-------------+-----------------------
 postgres | postgres | UTF8   | en_US.UTF-8 | en_US.UTF-8 | 
 repmgr  | repmgr  | UTF8   | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8   | en_US.UTF-8 | en_US.UTF-8 | =c/postgres     +
      |     |     |       |       | postgres=CTc/postgres
 template1 | postgres | UTF8   | en_US.UTF-8 | en_US.UTF-8 | =c/postgres     +
      |     |     |       |       | postgres=CTc/postgres
 test   | postgres | UTF8   | en_US.UTF-8 | en_US.UTF-8 | 
(5 rows)
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# \dt
Did not find any relations.
test=# create table test(id int); 
CREATE TABLE
test=# drop table test(id int);
ERROR: syntax error at or near "("
LINE 1: drop table test(id int);
            ^
test=# 
test=# \q
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$vim $PGDATA/postgresql.conf
[postgres@node_206 /postgresql/pgsql]$vim $PGDATA/postgresql.conf
[postgres@node_206 /postgresql/pgsql]$pg_ctl restart -m fast
waiting for server to shut down.... done
server stopped
waiting for server to start....2020-11-17 10:36:41.191 EST [58933] LOG: 00000: starting PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2020-11-17 10:36:41.191 EST [58933] LOCATION: PostmasterMain, postmaster.c:998
2020-11-17 10:36:41.192 EST [58933] LOG: 00000: listening on IPv4 address "0.0.0.0", port 5432
2020-11-17 10:36:41.192 EST [58933] LOCATION: StreamServerPort, pqcomm.c:593
2020-11-17 10:36:41.192 EST [58933] LOG: 00000: listening on IPv6 address "::", port 5432
2020-11-17 10:36:41.192 EST [58933] LOCATION: StreamServerPort, pqcomm.c:593
2020-11-17 10:36:41.197 EST [58933] LOG: 00000: listening on Unix socket "/tmp/.s.PGSQL.5432"
2020-11-17 10:36:41.197 EST [58933] LOCATION: StreamServerPort, pqcomm.c:587
2020-11-17 10:36:41.229 EST [58933] LOG: 00000: redirecting log output to logging collector process
2020-11-17 10:36:41.229 EST [58933] HINT: Future log output will appear in directory "/postgresql/pgsql/pg_log".
2020-11-17 10:36:41.229 EST [58933] LOCATION: SysLogger_Start, syslogger.c:675
 done
server started
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$ll
total 28
drwxr-xr-x. 2 postgres postgres 4096 Nov 17 23:36 archive_log
drwxrwxr-x. 2 postgres postgres  6 Nov 17 15:51 backups
drwxr-xr-x. 2 postgres postgres 4096 Nov 17 16:43 bin
drwx------. 19 postgres postgres 4096 Nov 17 23:36 data
drwxr-xr-x. 6 postgres postgres 4096 Nov 15 16:14 include
drwxr-xr-x. 4 postgres postgres 4096 Nov 17 06:37 lib
drwxr-xr-x. 2 postgres postgres 4096 Nov 17 23:36 pg_log
drwxrwxr-x. 5 postgres postgres 104 Nov 17 23:30 pg_rman_backups
drwxr-xr-x. 8 postgres postgres 4096 Nov 15 16:14 share
[postgres@node_206 /postgresql/pgsql]$rm -rf archive_log/*
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$rm -rf pg_rman_backups/*
[postgres@node_206 /postgresql/pgsql]$ll
total 24
drwxr-xr-x. 2 postgres postgres  6 Nov 17 23:36 archive_log
drwxrwxr-x. 2 postgres postgres  6 Nov 17 15:51 backups
drwxr-xr-x. 2 postgres postgres 4096 Nov 17 16:43 bin
drwx------. 19 postgres postgres 4096 Nov 17 23:36 data
drwxr-xr-x. 6 postgres postgres 4096 Nov 15 16:14 include
drwxr-xr-x. 4 postgres postgres 4096 Nov 17 06:37 lib
drwxr-xr-x. 2 postgres postgres 4096 Nov 17 23:36 pg_log
drwxrwxr-x. 2 postgres postgres  6 Nov 17 23:36 pg_rman_backups
drwxr-xr-x. 8 postgres postgres 4096 Nov 15 16:14 share
[postgres@node_206 /postgresql/pgsql]$pwd
/postgresql/pgsql
[postgres@node_206 /postgresql/pgsql]$ls
archive_log backups bin data include lib pg_log pg_rman_backups share
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$ll
total 24
drwxr-xr-x. 2 postgres postgres  6 Nov 17 23:36 archive_log
drwxrwxr-x. 2 postgres postgres  6 Nov 17 15:51 backups
drwxr-xr-x. 2 postgres postgres 4096 Nov 17 16:43 bin
drwx------. 19 postgres postgres 4096 Nov 17 23:36 data
drwxr-xr-x. 6 postgres postgres 4096 Nov 15 16:14 include
drwxr-xr-x. 4 postgres postgres 4096 Nov 17 06:37 lib
drwxr-xr-x. 2 postgres postgres 4096 Nov 17 23:36 pg_log
drwxrwxr-x. 2 postgres postgres  6 Nov 17 23:36 pg_rman_backups
drwxr-xr-x. 8 postgres postgres 4096 Nov 15 16:14 share
[postgres@node_206 /postgresql/pgsql]$pwd
/postgresql/pgsql
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$vim $PGDATA/postgresql.conf
total 24
drwxr-xr-x. 2 postgres postgres  6 Nov 17 23:36 archive_log
drwxrwxr-x. 2 postgres postgres  6 Nov 17 15:51 backups
drwxr-xr-x. 2 postgres postgres 4096 Nov 17 16:43 bin
drwx------. 19 postgres postgres 4096 Nov 17 23:36 data
drwxr-xr-x. 6 postgres postgres 4096 Nov 15 16:14 include
drwxr-xr-x. 4 postgres postgres 4096 Nov 17 06:37 lib
drwxr-xr-x. 2 postgres postgres 4096 Nov 17 23:36 pg_log
drwxrwxr-x. 2 postgres postgres  6 Nov 17 23:36 pg_rman_backups
drwxr-xr-x. 8 postgres postgres 4096 Nov 15 16:14 share
[postgres@node_206 /postgresql/pgsql]$pwd
/postgresql/pgsql
[postgres@node_206 /postgresql/pgsql]$ls
archive_log backups bin data include lib pg_log pg_rman_backups share
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$ll
total 24
drwxr-xr-x. 2 postgres postgres  6 Nov 17 23:36 archive_log
drwxrwxr-x. 2 postgres postgres  6 Nov 17 15:51 backups
drwxr-xr-x. 2 postgres postgres 4096 Nov 17 16:43 bin
drwx------. 19 postgres postgres 4096 Nov 17 23:36 data
drwxr-xr-x. 6 postgres postgres 4096 Nov 15 16:14 include
drwxr-xr-x. 4 postgres postgres 4096 Nov 17 06:37 lib
drwxr-xr-x. 2 postgres postgres 4096 Nov 17 23:36 pg_log
drwxrwxr-x. 2 postgres postgres  6 Nov 17 23:36 pg_rman_backups
drwxr-xr-x. 8 postgres postgres 4096 Nov 15 16:14 share
[postgres@node_206 /postgresql/pgsql]$pwd
/postgresql/pgsql
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$vim $PGDATA/postgresql.conf
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$tail -3  $PGDATA/postgresql.conf
#for pg_Rman
archive_mode = on # enables archiving; off, on, or always
archive_command = 'test ! -f /postgresql/pgsql/archive_log/%f && cp %p /postgresql/pgsql/archive_log/%f'
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$pg_ctl restart -m fast
waiting for server to shut down.... done
server stopped
waiting for server to start....2020-11-17 10:45:13.636 EST [59035] LOG: 00000: starting PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2020-11-17 10:45:13.636 EST [59035] LOCATION: PostmasterMain, postmaster.c:998
2020-11-17 10:45:13.638 EST [59035] LOG: 00000: listening on IPv4 address "0.0.0.0", port 5432
2020-11-17 10:45:13.638 EST [59035] LOCATION: StreamServerPort, pqcomm.c:593
2020-11-17 10:45:13.638 EST [59035] LOG: 00000: listening on IPv6 address "::", port 5432
2020-11-17 10:45:13.638 EST [59035] LOCATION: StreamServerPort, pqcomm.c:593
2020-11-17 10:45:13.644 EST [59035] LOG: 00000: listening on Unix socket "/tmp/.s.PGSQL.5432"
2020-11-17 10:45:13.644 EST [59035] LOCATION: StreamServerPort, pqcomm.c:587
2020-11-17 10:45:13.696 EST [59035] LOG: 00000: redirecting log output to logging collector process
2020-11-17 10:45:13.696 EST [59035] HINT: Future log output will appear in directory "/postgresql/pgsql/pg_log".
2020-11-17 10:45:13.696 EST [59035] LOCATION: SysLogger_Start, syslogger.c:675
 done
server started
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$ll
total 24
drwxr-xr-x. 2 postgres postgres  6 Nov 17 23:36 archive_log
drwxrwxr-x. 2 postgres postgres  6 Nov 17 15:51 backups
drwxr-xr-x. 2 postgres postgres 4096 Nov 17 16:43 bin
drwx------. 19 postgres postgres 4096 Nov 17 23:45 data
drwxr-xr-x. 6 postgres postgres 4096 Nov 15 16:14 include
drwxr-xr-x. 4 postgres postgres 4096 Nov 17 06:37 lib
drwxr-xr-x. 2 postgres postgres 4096 Nov 17 23:45 pg_log
drwxrwxr-x. 2 postgres postgres  6 Nov 17 23:36 pg_rman_backups
drwxr-xr-x. 8 postgres postgres 4096 Nov 15 16:14 share
[postgres@node_206 /postgresql/pgsql]$pg_rman init -B /postgresql/pgsql/pg_rman_backups/
INFO: ARCLOG_PATH is set to '/postgresql/pgsql/archive_log'
INFO: SRVLOG_PATH is set to '/postgresql/pgsql/pg_log'
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$psql 
psql (12.3)
Type "help" for help.
postgres=# \l
                 List of databases
  Name  | Owner  | Encoding |  Collate  |  Ctype  |  Access privileges  
-----------+----------+----------+-------------+-------------+-----------------------
 postgres | postgres | UTF8   | en_US.UTF-8 | en_US.UTF-8 | 
 repmgr  | repmgr  | UTF8   | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8   | en_US.UTF-8 | en_US.UTF-8 | =c/postgres     +
      |     |     |       |       | postgres=CTc/postgres
 template1 | postgres | UTF8   | en_US.UTF-8 | en_US.UTF-8 | =c/postgres     +
      |     |     |       |       | postgres=CTc/postgres
 test   | postgres | UTF8   | en_US.UTF-8 | en_US.UTF-8 | 
(5 rows)
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# \dt
    List of relations
 Schema | Name | Type | Owner  
--------+------+-------+----------
 public | test | table | postgres
(1 row)
test=# drop table test
test-# ;
DROP TABLE
test=# create table test(id int, crt_time timestamp);
CREATE TABLE
test=# insert into test(id) select n from generate_series(1,100) n;
INSERT 0 100
test=# \q
[postgres@node_206 /postgresql/pgsql]$pg_rman backup --backup-mode=full -B /postgresql/pgsql/pg_rman_backups
INFO: copying database files
INFO: copying archived WAL files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.
[postgres@node_206 /postgresql/pgsql]$pg_rman validate -B /postgresql/pgsql/pg_rman_backups
INFO: validate: "2020-11-17 23:46:22" backup and archive log files by CRC
INFO: backup "2020-11-17 23:46:22" is valid
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$psql 
psql (12.3)
Type "help" for help.
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# select * from test;
 id | crt_time 
-----+----------
  1 | 
  2 | 
  3 | 
  4 | 
  5 | 
  6 | 
  7 | 
  8 | 
  9 | 
 10 | 
 11 | 
 12 | 
 13 | 
 14 | 
 15 | 
 16 | 
 17 | 
 18 | 
 19 | 
 20 | 
 21 | 
 22 | 
 23 | 
 24 | 
 25 | 
 26 | 
 27 | 
 28 | 
 29 | 
 30 | 
 31 | 
 32 | 
 33 | 
 34 | 
 35 | 
 36 | 
 37 | 
 38 | 
 39 | 
 40 | 
 41 | 
 42 | 
 43 | 
 44 | 
 45 | 
 46 | 
 47 | 
 48 | 
 49 | 
 50 | 
 51 | 
 52 | 
 53 | 
 54 | 
 55 | 
 56 | 
 57 | 
 58 | 
 59 | 
 60 | 
 61 | 
 62 | 
 63 | 
 64 | 
 65 | 
 66 | 
 67 | 
 68 | 
 69 | 
 70 | 
 71 | 
 72 | 
 73 | 
 74 | 
 75 | 
 76 | 
 77 | 
 78 | 
 79 | 
 80 | 
 81 | 
 82 | 
 83 | 
 84 | 
 85 | 
 86 | 
 87 | 
 88 | 
 89 | 
 90 | 
 91 | 
 92 | 
 93 | 
 94 | 
 95 | 
 96 | 
 97 | 
 98 | 
 99 | 
 100 | 
(100 rows)
test=# 
test=# 
test=# 
test=# 
test=# 
test=# 
test=# 
test=# 
test=# 
test=# insert into test (id) select n from generate_series(101,110) n;
INSERT 0 10
test=# \q
[postgres@node_206 /postgresql/pgsql]$pg_rman backup --backup-mode=incremental -B /postgresql/pgsql/pg_rman_backups
INFO: copying database files
INFO: copying archived WAL files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.
[postgres@node_206 /postgresql/pgsql]$pg_rman validate -B /postgresql/pgsql/pg_rman_backups
INFO: validate: "2020-11-17 23:47:09" backup and archive log files by CRC
INFO: backup "2020-11-17 23:47:09" is valid
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$pg_rman show -B /postgresql/pgsql/pg_rman_backups/
=====================================================================
 StartTime      EndTime       Mode  Size  TLI Status 
=====================================================================
2020-11-17 23:47:09 2020-11-17 23:47:12 INCR  33MB   6 OK
2020-11-17 23:46:22 2020-11-17 23:46:24 FULL  64MB   6 OK
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$psql -d test
psql (12.3)
Type "help" for help.
test=# create table test2( like test );
CREATE TABLE
test=# insert into test2(id) select n from generate_serires(1,100) n;
ERROR: function generate_serires(integer, integer) does not exist
LINE 1: insert into test2(id) select n from generate_serires(1,100) ...
                      ^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
test=# 
test=# insert into test2(id) select n from generate_series(1,100) n;
INSERT 0 100
test=# \q
[postgres@node_206 /postgresql/pgsql]$pg_rman backup --backup-mode=full -B /postgresql/pgsql/pg_rman_backups
INFO: copying database files
INFO: copying archived WAL files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.
[postgres@node_206 /postgresql/pgsql]$pg_rman validate -B /postgresql/pgsql/pg_rman_backups
INFO: validate: "2020-11-17 23:49:32" backup and archive log files by CRC
INFO: backup "2020-11-17 23:49:32" is valid
[postgres@node_206 /postgresql/pgsql]$psql -d test
psql (12.3)
Type "help" for help.
test=# insert into test(id) select n from generate_series(101,100) n;
INSERT 0 0
test=# \q
[postgres@node_206 /postgresql/pgsql]$pg_rman backup --backup-mode=incremental -B /postgresql/pgsql/pg_rman_backups
INFO: copying database files
INFO: copying archived WAL files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.
[postgres@node_206 /postgresql/pgsql]$pg_rman validate -B /postgresql/pgsql/pg_rman_backups
INFO: validate: "2020-11-17 23:49:59" backup and archive log files by CRC
INFO: backup "2020-11-17 23:49:59" is valid
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$pg_rman show -B /postgresql/pgsql/pg_rman_backups/
=====================================================================
 StartTime      EndTime       Mode  Size  TLI Status 
=====================================================================
2020-11-17 23:49:59 2020-11-17 23:50:01 INCR  33MB   6 OK
2020-11-17 23:49:32 2020-11-17 23:49:35 FULL  64MB   6 OK
2020-11-17 23:47:09 2020-11-17 23:47:12 INCR  33MB   6 OK
2020-11-17 23:46:22 2020-11-17 23:46:24 FULL  64MB   6 OK
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$pg_rman delete 2020-11-17 23:47:09 -B /postgresql/pgsql/pg_rman_backups/
WARNING: cannot delete backup with start time "2020-11-17 23:47:09"
DETAIL: This is the incremental backup necessary for successful recovery.
WARNING: cannot delete backup with start time "2020-11-17 23:46:22"
DETAIL: This is the latest full backup necessary for successful recovery.
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$pg_rman show -B /postgresql/pgsql/pg_rman_backups/
=====================================================================
 StartTime      EndTime       Mode  Size  TLI Status 
=====================================================================
2020-11-17 23:49:59 2020-11-17 23:50:01 INCR  33MB   6 OK
2020-11-17 23:49:32 2020-11-17 23:49:35 FULL  64MB   6 OK
2020-11-17 23:47:09 2020-11-17 23:47:12 INCR  33MB   6 OK
2020-11-17 23:46:22 2020-11-17 23:46:24 FULL  64MB   6 OK
[postgres@node_206 /postgresql/pgsql]$pg_rman delete -f 2020-11-17 23:47:09 -B /postgresql/pgsql/pg_rman_backups/
WARNING: using force option will make some of the remaining backups unusable
DETAIL: Any remaining incremental backups that are older than the oldest available full backup cannot be restored.
INFO: delete the backup with start time: "2020-11-17 23:47:09"
INFO: delete the backup with start time: "2020-11-17 23:46:22"
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$ll pg_rman_backups/
total 8
drwx------. 6 postgres postgres 62 Nov 17 23:49 20201117
drwx------. 4 postgres postgres 34 Nov 17 23:45 backup
-rw-rw-r--. 1 postgres postgres 84 Nov 17 23:45 pg_rman.ini
-rw-rw-r--. 1 postgres postgres 40 Nov 17 23:45 system_identifier
drwx------. 2 postgres postgres 6 Nov 17 23:45 timeline_history
[postgres@node_206 /postgresql/pgsql]$ll pg_rman_backups/20201117/
total 0
drwx------. 2 postgres postgres 89 Nov 17 23:50 234622
drwx------. 2 postgres postgres 89 Nov 17 23:50 234709
drwx------. 5 postgres postgres 133 Nov 17 23:49 234932
drwx------. 5 postgres postgres 133 Nov 17 23:50 234959
[postgres@node_206 /postgresql/pgsql]$ll pg_rman_backups/20201117/234622/
total 112
-rw-rw-r--. 1 postgres postgres  433 Nov 17 23:50 backup.ini
-rw-rw-r--. 1 postgres postgres  226 Nov 17 23:46 file_arclog.txt
-rw-rw-r--. 1 postgres postgres 99521 Nov 17 23:46 file_database.txt
-rwx------. 1 postgres postgres  764 Nov 17 23:46 mkdirs.sh
[postgres@node_206 /postgresql/pgsql]$ll pg_rman_backups/20201117/2347
ls: cannot access pg_rman_backups/20201117/2347: No such file or directory
[postgres@node_206 /postgresql/pgsql]$ll pg_rman_backups/20201117/234709/
total 124
-rw-rw-r--. 1 postgres postgres  438 Nov 17 23:50 backup.ini
-rw-rw-r--. 1 postgres postgres  468 Nov 17 23:47 file_arclog.txt
-rw-rw-r--. 1 postgres postgres 113305 Nov 17 23:47 file_database.txt
-rwx------. 1 postgres postgres  764 Nov 17 23:47 mkdirs.sh
[postgres@node_206 /postgresql/pgsql]$ll pg_rman_backups/20201117/234932/
total 116
drwx------. 2 postgres postgres  118 Nov 17 23:49 arclog
-rw-rw-r--. 1 postgres postgres  428 Nov 17 23:49 backup.ini
drwx------. 19 postgres postgres 4096 Nov 17 23:49 database
-rw-rw-r--. 1 postgres postgres  708 Nov 17 23:49 file_arclog.txt
-rw-rw-r--. 1 postgres postgres 99583 Nov 17 23:49 file_database.txt
-rwx------. 1 postgres postgres  764 Nov 17 23:49 mkdirs.sh
drwx------. 2 postgres postgres   6 Nov 17 23:49 srvlog
[postgres@node_206 /postgresql/pgsql]$pg_rman purge
ERROR: required parameter not specified: BACKUP_PATH (-B, --backup-path)
[postgres@node_206 /postgresql/pgsql]$pg_rman purge -B /postgresql/pgsql/pg_rman_backups/
INFO: DELETED backup "2020-11-17 23:47:09" is purged
INFO: DELETED backup "2020-11-17 23:46:22" is purged
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$ll pg_rman_backups/20201117/234622/
ls: cannot access pg_rman_backups/20201117/234622/: No such file or directory
[postgres@node_206 /postgresql/pgsql]$ll pg_rman_backups/20201117
total 0
drwx------. 5 postgres postgres 133 Nov 17 23:49 234932
drwx------. 5 postgres postgres 133 Nov 17 23:50 234959
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$pg_rman show -B /postgresql/pgsql/pg_rman_backups/
=====================================================================
 StartTime      EndTime       Mode  Size  TLI Status 
=====================================================================
2020-11-17 23:49:59 2020-11-17 23:50:01 INCR  33MB   6 OK
2020-11-17 23:49:32 2020-11-17 23:49:35 FULL  64MB   6 OK
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$psql 
psql (12.3)
Type "help" for help.
postgres=# drop database test;
DROP DATABASE
postgres=# \q
[postgres@node_206 /postgresql/pgsql]$pg_ctl stop -m fast
waiting for server to shut down.... done
server stopped
[postgres@node_206 /postgresql/pgsql]$pg_rman restore -B /postgresql/pgsql/pg_rman_backups/ --recovery-target-time='2020-11-17 23:49:32'
ERROR: cannot do restore
DETAIL: There is no valid full backup which can be used for given recovery condition.
[postgres@node_206 /postgresql/pgsql]$pg_rman backup --backup-mode=full -B /postgresql/pgsql/pg_rman_backups
ERROR: could not connect to database postgres: could not connect to server: No such file or directory
	Is the server running locally and accepting
	connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$pg_ctl start
waiting for server to start....2020-11-17 10:53:41.927 EST [59212] LOG: 00000: starting PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2020-11-17 10:53:41.927 EST [59212] LOCATION: PostmasterMain, postmaster.c:998
2020-11-17 10:53:41.928 EST [59212] LOG: 00000: listening on IPv4 address "0.0.0.0", port 5432
2020-11-17 10:53:41.928 EST [59212] LOCATION: StreamServerPort, pqcomm.c:593
2020-11-17 10:53:41.928 EST [59212] LOG: 00000: listening on IPv6 address "::", port 5432
2020-11-17 10:53:41.928 EST [59212] LOCATION: StreamServerPort, pqcomm.c:593
2020-11-17 10:53:41.929 EST [59212] LOG: 00000: listening on Unix socket "/tmp/.s.PGSQL.5432"
2020-11-17 10:53:41.929 EST [59212] LOCATION: StreamServerPort, pqcomm.c:587
2020-11-17 10:53:41.977 EST [59212] LOG: 00000: redirecting log output to logging collector process
2020-11-17 10:53:41.977 EST [59212] HINT: Future log output will appear in directory "/postgresql/pgsql/pg_log".
2020-11-17 10:53:41.977 EST [59212] LOCATION: SysLogger_Start, syslogger.c:675
 done
server started
[postgres@node_206 /postgresql/pgsql]$pg_rman backup --backup-mode=full -B /postgresql/pgsql/pg_rman_backups
INFO: copying database files
INFO: copying archived WAL files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$pg_rman validate -B /postgresql/pgsql/pg_rman_backups
INFO: validate: "2020-11-17 23:53:43" backup and archive log files by CRC
INFO: backup "2020-11-17 23:53:43" is valid
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$pg_rman show -B /postgresql/pgsql/pg_rman_backups/
=www.devze.com====================================================================
 StartTime      EndTime       Mode  Size  TLI Status 
=====================================================================
2020-11-17 23:53:43 2020-11-17 23:53:45 FULL  75MB   6 OK
2020-11-17 23:53:39 2020-11-17 23:53:39 FULL   0B   0 ERROR
2020-11-17 23:49:59 2020-11-17 23:50:01 INCR  33MB   6 OK
2020-11-17 23:49:32 2020-11-17 23:49:35 FULL  64MB   6 OK
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$psql -d test
psql: error: could not connect to server: FATAL: database "test" does not exist
[postgres@node_206 /postgresql/pgsql]$psql 
psql (12.3)
Type "help" for help.
postgres=# create database test;
CREATE DATABASE
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# create table test2(id int, crt_time timestamp );
CREATE TABLE
test=# insert into test2(id) select n from generate_series(1,100) n;
INSERT 0 100
test=# 
test=# \q
[postgres@node_206 /postgresql/pgsql]$pg_rman backup --backup-mode=full -B /postgresql/pgsql/pg_rman_backups
INFO: copying database files
INFO: copying archived WAL files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.
[postgres@node_206 /postgresql/pgsql]$pg_rman validate -B /postgresql/pgsql/pg_rman_backups
INFO: validate: "2020-11-17 23:58:53" backup and archive log files by CRC
INFO: backup "2020-11-17 23:58:53" is valid
[postgres@node_206 /postgresql/pgsql]$pg_rman show -B /postgresql/pgsql/pg_rman_backups/
=====================================================================
 StartTime      EndTime       Mode  Size  TLI Status 
=====================================================================
2020-11-17 23:58:53 2020-11-17 23:58:55 FULL  64MB   6 OK
2020-11-17 23:53:43 2020-11-17 23:53:45 FULL  75MB   6 OK
2020-11-17 23:53:39 2020-11-17 23:53:39 FULL   0B   0 ERROR
2020-11-17 23:49:59 2020-11-17 23:50:01 INCR  33MB   6 OK
2020-11-17 23:49:32 2020-11-17 23:49:35 FULL  64MB   6 OK
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$pg_rman delete -f 2020-11-17 23:53:39 -B /postgresql/pgsql/pg_rman_backups/
WARNING: using force option will make some of the remaining backups unusable
DETAIL: Any remaining incremental backups that are older than the oldest available full backup cannot be restored.
INFO: delete the backup with start time: "2020-11-17 23:53:39"
INFO: delete the backup with start time: "2020-11-17 23:49:59"
INFO: delete the backup with start time: "2020-11-17 23:49:32"
[postgres@node_206 /postgresql/pgsql]$pg_rman purge -B /postgresql/pgsql/pg_rman_backups/
INFO: DELETED backup "2020-11-17 23:53:39" is purged
INFO: DELETED backup "2020-11-17 23:49:59" is purged
INFO: DELETED backup "2020-11-17 23:49:32" is purged
[postgres@node_206 /postgresql/pgsql]$pg_rman show -B /postgresql/pgsql/pg_rman_backups/
=====================================================================
 StartTime      EndTime       Mode  Size  TLI Status 
=====================================================================
2020-11-17 23:58:53 2020-11-17 23:58:55 FULL  64MB   6 OK
2020-11-17 23:53:43 2020-11-17 23:53:45 FULL  75MB   6 OK
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$psql 
psql (12.3)
Type "help" for help.
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# \dt
     List of relations
 Schema | Name | Type | Owner  
--------+-------+-------+----------
 public | test2 | table | postgres
(1 row)
test=# \c postgres
You are now connected to database "postgres" as user "postgres".
postgres=# drop database test;
DROP DATABASE
postgres=# \q
[postgres@node_206 /postgresql/pgsql]$pg_rman show -B /postgresql/pgsql/pg_rman_backups/
=====================================================================
 StartTime      EndTime       Mode  Size  TLI Status 
=====================================================================
2020-11-17 23:58:53 2020-11-17 23:58:55 FULL  64MB   6 OK
2020-11-17 23:53:43 2020-11-17 23:53:45 FULL  75MB   6 OK
[postgres@node_206 /postgresql/pgsql]$pg_rman restore -B /postgresql/pgsql/pg_rman_backups/ --recovery-target-time='2020-11-17 23:58:53'
ERROR: PostgreSQL server is running
HINT: Please stop PostgreSQL server before executing restore.
[postgres@node_206 /postgresql/pgsql]$pg_ctl stop -m fast
waiting for server to shut down.... done
server stopped
[postgres@node_206 /postgresql/pgsql]$pg_rman restore -B /postgresql/pgsql/pg_rman_backups/ --recovery-target-time='2020-11-17 23:58:53'
INFO: the recovery target timeline ID is not given
INFO: use timeline ID of current database cluster as recovery target: 6
INFO: calculating timeline branches to be used to recovery target point
INFO: searching latest full backup which can be used as restore start point
INFO: found the full backup can be used as base in recovery: "2020-11-17 23:53:43"
INFO: copying online WAL files and server log files
INFO: clearing restore destination
INFO: validate: "2020-11-17 23:53:43" backup and archive log files by SIZE
INFO: backup "2020-11-17 23:53:43" is valid
INFO: restoring database files from the full mode backup "2020-11-17 23:53:43"
INFO: searching incremental backup to be restored
INFO: searching backup which contained archived WAL files to be restored
INFO: backup "2020-11-17 23:53:43" is valid
INFO: restoring WAL files from backup "2020-11-17 23:53:43"
INFO: backup "2020-11-17 23:58:53" is valid
INFO: restoring WAL files from backup "2020-11-17 23:58:53"
INFO: restoring online WAL files and server log files
INFO: add recovery related options to postgresql.conf
INFO: generating recovery.signal
INFO: restore complete
HINT: Recovery will start automatically when the PostgreSQL server is started.
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$pg_rman backup --backup-mode=full -B /postgresql/pgsql/pg_rman_backups
ERROR: could not connect to database postgres: could not connect to server: No such file or directory
	Is the server running locally and accepting
	connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
[postgres@node_206 /postgresql/pgsql]$pg_ctl start 
waiting for server to start....2020-11-17 11:00:30.910 EST [59357] LOG: 00000: starting PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2020-11-17 11:00:30.910 EST [59357] LOCATION: PostmasterMain, postmaster.c:998
2020-11-17 11:00:30.910 EST [59357] LOG: 00000: listening on IPv4 address "0.0.0.0", port 5432
2020-11-17 11:00:30.910 EST [59357] LOCATION: StreamServerPort, pqcomm.c:593
2020-11-17 11:00:30.910 EST [59357] LOG: 00000: listening on IPv6 address "::", port 5432
2020-11-17 11:00:Gholqfbzw30.910 EST [59357] LOCATION: StreamServerPort, pqcomm.c:593
2020-11-17 11:00:30.914 EST [59357] LOG: 00000: listening on Unix socket "/tmp/.s.PGSQL.5432"
2020-11-17 11:00:30.914 EST [59357] LOCATION: StreamServerPort, pqcomm.c:587
2020-11-17 11:00:30.965 EST [59357] LOG: 00000: redirecting log output to logging collector process
2020-11-17 11:00:30.965 EST [59357] HINT: Future log output will appear in directory "/postgresql/pgsql/pg_log".
2020-11-17 11:00:30.965 EST [59357] LOCATION: SysLogger_Start, syslogger.c:675
 done
server started
[postgres@node_206 /postgresql/pgsql]$pg_rman backup --backup-mode=full -B /postgresql/pgsql/pg_rman_backups
INFO: copying database files
ERROR: query failed: ERROR: recovery is in progress
HINT: pg_walfile_name_offset() cannot be executed during recovery.
query was: SELECT * from pg_walfile_name_offset(pg_start_backup($1, $2, $3))
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$rm -f data/recovery.signal 
[postgres@node_206 /postgresql/pgsql]$pg_ctl restart -m fast
waiting for server to shut down.... done
server stopped
waiting for server to start....2020-11-17 11:00:51.204 EST [59374] LOG: 00000: starting PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2020-11-17 11:00:51.204 EST [59374] LOCATION: PostmasterMain, postmaster.c:998
2020-11-17 11:00:51.205 EST [59374] LOG: 00000: listening on IPv4 address "0.0.0.0", port 5432
2020-11-17 11:00:51.205 EST [59374] LOCATION: StreamServerPort, pqcomm.c:593
2020-11-17 11:00:51.205 EST [59374] LOG: 00000: listening on IPv6 address "::", port 5432
2020-11-17 11:00:51.205 EST [59374] LOCATION: StreamServerPort, pqcomm.c:593
2020-11-17 11:00:51.210 EST [59374] LOG: 00000: listening on Unix socket "/tmp/.s.PGSQL.5432"
2020-11-17 11:00:51.210 EST [59374] LOCATION: StreamServerPort, pqcomm.c:587
2020-11-17 11:00:51.270 EST [59374] LOG: 00000: redirecting log output to logging collector process
2020-11-17 11:00:51.270 EST [59374] HINT: Future log output will appear in directory "/postgresql/pgsql/pg_log".
2020-11-17 11:00:51.270 EST [59374] LOCATION: SysLogger_Start, syslogger.c:675
 done
server started
[postgres@node_206 /postgresql/pgsql]$pg_rman backup --backup-mode=full -B /postgresql/pgsql/pg_rman_backups
INFO: copying database files
INFO: copying archived WAL files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.
[postgres@node_206 /postgresql/pgsql]$pg_rman validate -B /postgresql/pgsql/pg_rman_backups
INFO: validate: "2020-11-18 00:00:53" backup and archive log files by CRC
INFO: backup "2020-11-18 00:00:53" is valid
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$pg_rman show -B /postgresql/pgsql/pg_rman_backups/
=====================================================================
 StartTime      EndTime       Mode  Size  TLI Status 
=====================================================================
2020-11-18 00:00:53 2020-11-18 00:00:55 FULL  159MB   6 OK
2020-11-18 00:00:34 2020-11-18 00:00:34 FULL   0B   0 ERROR
2020-11-18 00:00:26 2020-11-18 00:00:26 FULL   0B   0 ERROR
2020-11-17 23:58:53 2020-11-17 23:58:55 FULL  64MB   6 OK
2020-11-17 23:53:43 2020-11-17 23:53:45 FULL  75MB   6 OK
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$psql 
psql (12.3)
Type "help" for help.
postgres=# \l
                 List of databases
  Name  | Owner  | Encoding |  Collate  |  Ctype  |  Access privileges  
-----------+----------+----------+-------------+-------------+-----------------------
 postgres | postgres | UTF8   | en_US.UTF-8 | en_US.UTF-8 | 
 repmgr  | repmgr  | UTF8   | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8   | en_US.UTF-8 | en_US.UTF-8 | =c/postgres     +
      |     |     |       |       | postgres=CTc/postgres
 template1 | postgres | UTF8   | en_US.UTF-8 | en_US.UTF-8 | =c/postgres     +
      |     |     |       |       | postgres=CTc/postgres
(4 rows)
postgres=# \c test
FATAL: database "test" does not exist
Previous connection kept
postgres=# \q
[postgres@node_206 /postgresql/pgsql]$vim $PGDATA/postgresql.conf
[postgres@node_206 /postgresql/pgsql]$pg_rman show -B /postgresql/pgsql/pg_rman_backups/
=====================================================================
 StartTime      EndTime       Mode  Size  TLI Status 
=====================================================================
2020-11-18 00:00:53 2020-11-18 00:00:55 FULL  159MB   6 OK
2020-11-18 00:00:34 2020-11-18 00:00:34 FULL   0B   0 ERROR
2020-11-18 00:00:26 2020-11-18 00:00:26 FULL   0B   0 ERROR
2020-11-17 23:58:53 2020-11-17 23:58:55 FULL  64MB   6 OK
2020-11-17 23:53:43 2020-11-17 23:53:45 FULL  75MB   6 OK
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$psql 
psql (12.3)
Type "help" for help.
postgres=# \l
                 List of databases
  Name  | Owner  | Encoding |  Collate  |  Ctype  |  Access privileges  
-----------+----------+----------+-------------+-------------+-----------------------
 postgres | postgres | UTF8   | en_US.UTF-8 | en_US.UTF-8 | 
 repmgr  | repmgr  | UTF8   | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8   | en_US.UTF-8 | en_US.UTF-8 | =c/postgres     +
      |     |     |       |       | postgres=CTc/postgres
 template1 | postgres | UTF8   | en_US.UTF-8 | en_US.UTF-8 | =c/postgres     +
      |     |     |       |       | postgres=CTc/postgres
(4 rows)
postgres=# \c test
FATAL: database "test" doeGholqfbzws not exist
Previous connection kept
postgres=# \q
[postgres@node_206 /postgresql/pgsql]$vim $PGDATA/postgresql.conf
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$pg_ctl restart -m fast
waiting for server to shut down.... done
server stopped
waiting for server to start....2020-11-17 11:01:30.816 EST [59423] LOG: 00000: starting PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2020-11-17 11:01:30.816 EST [59423] LOCATION: PostmasterMain, postmaster.c:998
2020-11-17 11:01:30.822 EST [59423] LOG: 00000: listening on IPv4 address "0.0.0.0", port 5432
2020-11-17 11:01:30.822 EST [59423] LOCATION: StreamServerPort, pqcomm.c:593
2020-11-17 11:01:30.822 EST [59423] LOG: 00000: listening on IPv6 address "::", port 5432
2020-11-17 11:01:30.822 EST [59423] LOCATION: StreamServerPort, pqcomm.c:593
2020-11-17 11:01:30.837 EST [59423] LOG: 00000: listening on Unix socket "/tmp/.s.PGSQL.5432"
2020-11-17 11:01:30.837 EST [59423] LOCATION: StreamServerPort, pqcomm.c:587
2020-11-17 11:01:30.877 EST [59423] LOG: 00000: redirecting log output to logging collector process
2020-11-17 11:01:30.877 EST [59423] HINT: Future log output will appear in directory "/postgresql/pgsql/pg_log".
2020-11-17 11:01:30.877 EST [59423] LOCATION: SysLogger_Start, syslogger.c:675
 done
server started
[postgres@node_206 /postgresql/pgsql]$ll
total 28
drwxr-xr-x. 2 postgres postgres 4096 Nov 18 00:01 archive_log
drwxrwxr-x. 2 postgres postgres  6 Nov 17 15:51 backups
drwxr-xr-x. 2 postgres postgres 4096 Nov 17 16:43 bin
drwx------. 19 postgres postgres 4096 Nov 18 00:01 data
drwxr-xr-x. 6 postgres postgres 4096 Nov 15 16:14 include
drwxr-xr-x. 4 postgres postgres 4096 Nov 17 06:37 lib
drwxr-xr-x. 2 postgres postgres 4096 Nov 18 00:01 pg_log
drwxrwxr-x. 6 postgres postgres 120 Nov 18 00:00 pg_rman_backups
drwxr-xr-x. 8 postgres postgres 4096 Nov 15 16:14 share
[postgres@node_206 /postgresql/pgsql]$rm -rf archive_log/* pg_rman_backups/*
[postgres@node_206 /postgresql/pgsql]$ll
total 24
drwxr-xr-x. 2 postgres postgres  6 Nov 18 00:01 archive_log
drwxrwxr-x. 2 postgres postgres  6 Nov 17 15:51 backups
drwxr-xr-x. 2 postgres postgres 4096 Nov 17 16:43 bin
drwx------. 19 postgres postgres 4096 Nov 18 00:01 data
drwxr-xr-x. 6 postgres postgres 4096 Nov 15 16:14 include
drwxr-xr-x. 4 postgres postgres 4096 Nov 17 06:37 lib
drwxr-xr-x. 2 postgres postgres 4096 Nov 18 00:01 pg_log
drwxrwxr-x. 2 postgres postgres  6 Nov 18 00:01 pg_rman_backups
drwxr-xr-x. 8 postgres postgres 4096 Nov 15 16:14 share
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$ls
archive_log backups bin data include lib pg_log pg_rman_backups share
[postgres@node_206 /postgresql/pgsql]$ll
total 24
drwxr-xr-x. 2 postgres postgres  6 Nov 18 00:01 archive_log
drwxrwxr-x. 2 postgres postgres  6 Nov 17 15:51 backups
drwxr-xr-x. 2 postgres postgres 4096 Nov 17 16:43 bin
drwx------. 19 postgres postgres 4096 Nov 18 00:01 data
drwxr-xr-x. 6 postgres postgres 4096 Nov 15 16:14 include
drwxr-xr-x. 4 postgres postgres 4096 Nov 17 06:37 lib
drwxr-xr-x. 2 postgres postgres 4096 Nov 18 00:01 pg_log
drwxrwxr-x. 2 postgres postgres  6 Nov 18 00:01 pg_rman_backups
drwxr-xr-x. 8 postgres postgres 4096 Nov 15 16:14 share
[postgres@node_206 /postgresql/pgsql]$ll pg_rman_backups/
total 0
[postgres@node_206 /postgresql/pgsql]$ll archive_log/
total 0
[postgres@node_206 /postgresql/pgsql]$ll
total 24
drwxr-xr-x. 2 postgres postgres  6 Nov 18 00:01 archive_log
drwxrwxr-x. 2 postgres postgres  6 Nov 17 15:51 backups
drwxr-xr-x. 2 postgres postgres 4096 Nov 17 16:43 bin
drwx------. 19 postgres postgres 4096 Nov 18 00:01 data
drwxr-xr-x. 6 postgres postgres 4096 Nov 15 16:14 include
drwxr-xr-x. 4 postgres postgres 4096 Nov 17 06:37 lib
drwxr-xr-x. 2 postgres postgres 4096 Nov 18 00:01 pg_log
drwxrwxr-x. 2 postgres postgres  6 Nov 18 00:01 pg_rman_backups
drwxr-xr-x. 8 postgres postgres 4096 Nov 15 16:14 share
[postgres@node_206 /postgresql/pgsql]$vim $PGDATA/postgresql.conf
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$ls
archive_log backups bin data include lib pg_log pg_rman_backups share
[postgres@node_206 /postgresql/pgsql]$ll
total 24
drwxr-xr-x. 2 postgres postgres  6 Nov 18 00:01 archive_log
drwxrwxr-x. 2 postgres postgres  6 Nov 17 15:51 backups
drwxr-xr-x. 2 postgres postgres 4096 Nov 17 16:43 bin
drwx------. 19 postgres postgres 4096 Nov 18 00:01 data
drwxr-xr-x. 6 postgres postgres 4096 Nov 15 16:14 include
drwxr-xr-x. 4 postgres postgres 4096 Nov 17 06:37 lib
drwxr-xr-x. 2 postgres postgres 4096 Nov 18 00:01 pg_log
drwxrwxr-x. 2 postgres postgres  6 Nov 18 00:01 pg_rman_backups
drwxr-xr-x. 8 postgres postgres 4096 Nov 15 16:14 share
[postgres@node_206 /postgresql/pgsql]$ll pg_rman_backups/
total 0
[postgres@node_206 /postgresql/pgsql]$ll archive_log/
total 0
[postgres@node_206 /postgresql/pgsql]$ll
total 24
drwxr-xr-x. 2 postgres postgres  6 Nov 18 00:01 archive_log
drwxrwxr-x. 2 postgres postgres  6 Nov 17 15:51 backups
drwxr-xr-x. 2 postgres postgres 4096 Nov 17 16:43 bin
drwx------. 19 postgres postgres 4096 Nov 18 00:01 data
drwxr-xr-x. 6 postgres postgres 4096 Nov 15 16:14 include
drwxr-xr-x. 4 postgres postgres 4096 Nov 17 06:37 lib
drwxr-xr-x. 2 postgres postgres 4096 Nov 18 00:01 pg_log
drwxrwxr-x. 2 postgres postgres  6 Nov 18 00:01 pg_rman_backups
drwxr-xr-x. 8 postgres postgres 4096 Nov 15 16:14 share
[postgres@node_206 /postgresql/pgsql]$vim $PGDATA/postgresql.conf
[postgres@node_206 /postgresql/pgsql]$pg_ctl restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2020-11-17 11:08:26.228 EST [59464] LOG: 00000: starting PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2020-11-17 11:08:26.228 EST [59464] LOCATION: PostmasterMain, postmaster.c:998
2020-11-17 11:08:26.233 EST [59464] LOG: 00000: listening on IPv4 address "0.0.0.0", port 5432
2020-11-17 11:08:26.233 EST [59464] LOCATION: StreamServerPort, pqcomm.c:593
2020-11-17 11:08:26.233 EST [59464] LOG: 00000: listening on IPv6 address "::", port 5432
2020-11-17 11:08:26.233 EST [59464] LOCATION: StreamServerPort, pqcomm.c:593
2020-11-17 11:08:26.236 EST [59464] LOG: 00000: listening on Unix socket "/tmp/.s.PGSQL.5432"
2020-11-17 11:08:26.236 EST [59464] LOCATION: StreamServerPort, pqcomm.c:587
2020-11-17 11:08:26.293 EST [59464] LOG: 00000: redirecting log output to logging collector process
2020-11-17 11:08:26.293 EST [59464] HINT: Future log output will appear in directory "/postgresql/pgsql/pg_log".
2020-11-17 11:08:26.293 EST [59464] LOCATION: SysLogger_Start, syslogger.c:675
 done
server started
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$pwd
/postgresql/pgsql
[postgres@node_206 /postgresql/pgsql]$psql
psql (12.3)
Type "help" for help.
postgres=# \l
                 List of databases
  Name  | Owner  | Encoding |  Collate  |  Ctype  |  Access privileges  
-----------+----------+----------+-------------+-------------+-----------------------
 postgres | postgres | UTF8   | en_US.UTF-8 | en_US.UTF-8 | 
 repmgr  | repmgr  | UTF8   | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8   | en_US.UTF-8 | en_US.UTF-8 | =c/postgres     +
      |     |     |       |       | postgres=CTc/postgres
 template1 | postgres | UTF8   | en_US.UTF-8 | en_US.UTF-8 | =c/postgres     +
      |     |     |       |       | postgres=CTc/postgres
(4 rows)
postgres=# \q
[postgres@node_206 /postgresql/pgsql]$pg_rman init -B /postgresql/pgsql/pg_rman_backups/
INFO: ARCLOG_PATH is set to '/postgresql/pgsql/archive_log'
INFO: SRVLOG_PATH is set to '/postgresql/pgsql/pg_log'
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$psql
psql (12.3)
Type "help" for help.
postgres=# create database test;
CREATE DATABASE
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# create table test(id int, crt_time timestamp default clock_timestamp());
CREATE TABLE
test=# insert into test (id) select n from generate_series(1,100) n;
INSERT 0 100
test=# \q
[postgres@node_206 /postgresql/pgsql]$pg_rman backup --backup-mode=full -B /postgresql/pgsql/pg_rman_backups
INFO: copying database files
INFO: copying archived WAL files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.
[postgres@node_206 /postgresql/pgsql]$pg_rman validate -B /postgresql/pgsql/pg_rman_backups
INFO: validate: "2020-11-18 00:09:40" backup and archive log files by CRC
INFO: backup "2020-11-18 00:09:40" is valid
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$psql -d test
psql (12.3)
Type "help" for help.
test=# insert into test (id) select n from generate_series(101,110) n;
INSERT 0 10
test=# \q
[postgres@node_206 /postgresql/pgsql]$pg_rman backup --backup-mode=incremental -B /postgresql/pgsql/pg_rman_backups
INFO: copying database files
INFO: copying archived WAL files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.
[postgres@node_206 /postgresql/pgsql]$pg_rman show -B /postgresql/pgsql/pg_rman_backups/
=====================================================================
 StartTime      EndTime       Mode  Size  TLI Status 
=====================================================================
2020-11-18 00:10:03 2020-11-18 00:10:05 INCR  33MB   6 DONE
2020-11-18 00:09:40 2020-11-18 00:09:42 FULL  64MB   6 OK
[postgres@node_206 /postgresql/pgsql]$pg_rman validate -B /postgresql/pgsql/pg_rman_backups
INFO: validate: "2020-11-18 00:10:03" backup and archive log files by CRC
INFO: backup "2020-11-18 00:10:03" is valid
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$pg_rman show -B /postgresql/pgsql/pg_rman_backups/
=====================================================================
 StartTime      EndTime       Mode  Size  TLI Status 
=====================================================================
2020-11-18 00:10:03 2020-11-18 00:10:05 INCR  33MB   6 OK
2020-11-18 00:09:40 2020-11-18 00:09:42 FULL  64MB   6 OK
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$psql -d test
psql (12.3)
Type "help" for help.
test=# create table test2(like test);
CREATE TABLE
test=# insert into test2 (id) select n from generate_series(1,100) n;
INSERT 0 100
test=# \q
[postgres@node_206 /postgresql/pgsql]$pg_rman backup --backup-mode=full -B /postgresql/pgsql/pg_rman_backups
INFO: copying database files
INFO: copying archived WAL files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.
[postgres@node_206 /postgresql/pgsql]$pg_rman show -B /postgresql/pgsql/pg_rman_backups/
=====================================================================
 StartTime      EndTime       Mode  Size  TLI Status 
=====================================================================
2020-11-18 00:10:49 2020-11-18 00:10:51 FULL  64MB   6 DONE
2020-11-18 00:10:03 2020-11-18 00:10:05 INCR  33MB   6 OK
2020-11-18 00:09:40 2020-11-18 00:09:42 FULL  64MB   6 OK
[postgres@node_206 /postgresql/pgsql]$pg_rman validate -B /postgresql/pgsql/pg_rman_backups
INFO: validate: "2020-11-18 00:10:49" backup and archive log files by CRC
INFO: backup "2020-11-18 00:10:49" is valid
[postgres@node_206 /postgresql/pgsql]$pg_rman show -B /postgresql/pgsql/pg_rman_backups/
=====================================================================
 StartTime      EndTime       Mode  Size  TLI Status 
=====================================================================
2020-11-18 00:10:49 2020-11-18 00:10:51 FULL  64MB   6 OK
2020-11-18 00:10:03 2020-11-18 00:10:05 INCR  33MB   6 OK
2020-11-18 00:09:40 2020-11-18 00:09:42 FULL  64MB   6 OK
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$pg_rman delete -f 2020-11-18 00:10:03 
ERROR: required parameter not specified: BACKUP_PATH (-B, --backup-path)
[postgres@node_206 /postgresql/pgsql]$pg_rman delete -f 2020-11-18 00:10:03 -B /postgresql/pgsql/pg_rman_backups/
WARNING: using force option will make some of the remaining backups unusable
DETAIL: Any remaining incremental backups that are older than the oldest available full backup cannot be restored.
INFO: delete the backup with start time: "2020-11-18 00:10:03"
INFO: delete the backup with start time: "2020-11-18 00:09:40"
[postgres@node_206 /postgresql/pgsql]$pg_rman purge -B /postgresql/pgsql/pg_rman_backups/
INFO: DELETED backup "2020-11-18 00:10:03" is purged
INFO: DELETED backup "2020-11-18 00:09:40" is purged
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$pg_rman show -B /postgresql/pgsql/pg_rman_backups/
=====================================================================
 StartTime      EndTime       Mode  Size  TLI Status 
=====================================================================
2020-11-18 00:10:49 2020-11-18 00:10:51 FULL  64MB   6 OK
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$psql 
psql (12.3)
Type "help" for help.
postgres=# \l
                 List of databases
  Name  | Owner  | Encoding |  Collate  |  Ctype  |  Access privileges  
-----------+----------+----------+-------------+-------------+-----------------------
 postgres | postgres | UTF8   | en_US.UTF-8 | en_US.UTF-8 | 
 repmgr  | repmgr  | UTF8   | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8   | en_US.UTF-8 | en_US.UTF-8 | =c/postgres     +
      |     |     |       |       | postgres=CTc/postgres
 template1 | postgres | UTF8   | en_US.UTF-8 | en_US.UTF-8 | =c/postgres     +
      |     |     |       |       | postgres=CTc/postgres
 test   | postgres | UTF8   | en_US.UTF-8 | en_US.UTF-8 | 
(5 rows)
postgres=# drop database test;
DROP DATABASE
postgres=# \q
[postgres@node_206 /postgresql/pgsql]$pg_ctl stop
waiting for server to shut down.... done
server stopped
[postgres@node_206 /postgresql/pgsql]$pg_rman restore -B /postgresql/pgsql/data/ --recovery-target-time='2020-11-18 00:10:49'
ERROR: required parameter not specified: ARCLOG_PATH (-A, --arclog-path)
[postgres@node_206 /postgresql/pgsql]$pg_rman restore -B /postgresql/pgsql/data/ --recovery-target-time='2020-11-18 00:10:49' -A /postgresql/pgsql/archive_log/
ERROR: required parameter not specified: SRVLOG_PATH (-S, --srvlog-path)
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$pg_rman restore -B /postgresql/pgsql/data/ --recovery-target-time='2020-11-18 00:10:49' -A /postgresql/pgsql/archive_log/ -S /postgresql/pgsql/pg_log/
ERROR: could not open file "/postgresql/pgsql/data//pg_rman.ini": No such file or directory
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$pg_rman restore -B /postgresql/pgsql/data/ --recovery-target-time='2020-11-18 00:10:49'
[postgres@node_206 /postgresql/pgsql]$pg_rman restore -B /postgresql/pgsql/pg_rman_backups/ --recovery-target-time='2020-11-18 00:10:49'
ERROR: cannot do restore
DETAIL: There is no valid full backup which can be used for given recovery condition.
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$pg_rman show -B /postgresql/pgsql/pg_rman_backups/
=====================================================================
 StartTime      EndTime       Mode  Size  TLI Status 
=====================================================================
2020-11-18 00:10:49 2020-11-18 00:10:51 FULL  64MB   6 OK
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$pg_rman restore -B /postgresql/pgsql/pg_rman_backups/ --recovery-target-time='2020-11-18 00:10:00\'
ERROR: cannot do restore
DETAIL: There is no valid full backup which can be used for given recovery condition.
[postgres@node_206 /postgresql/pgsql]$pg_rman restore -B /postgresql/pgsql/pg_rman_backups/ --recovery-target-time='2020-11-18 00:10:00'
ERROR: cannot do restore
DETAIL: There is no valid full backup which can be used for given recovery condition.
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$pg_rman restore -B /postgresql/pgsql/pg_rman_backups/ --recovery-target-time='2020-11-18'
ERROR: cannot do restore
DETAIL: There is no valid full backup which can be used for given recovery condition.
[postgres@node_206 /postgresql/pgsql]$pg_rman restore -B /postgresql/pgsql/pg_rman_backups/ 
INFO: the recovery target timeline ID is not given
INFO: use timeline ID of current database cluster as recovery target: 6
INFO: calculating timeline branches to be used to recovery target point
INFO: searching latest full backup which can be used as restore start point
INFO: found the full backup can be used as base in recovery: "2020-11-18 00:10:49"
INFO: copying online WAL files and server log files
INFO: clearing restore destination
INFO: validate: "2020-11-18 00:10:49" backup and archive log files by SIZE
INFO: backup "2020-11-18 00:10:49" is valid
INFO: restoring database files from the full mode backup "2020-11-18 00:10:49"
INFO: searching incremental backup to be restored
INFO: searching backup which contained archived WAL files to be restored
INFO: backup "2020-11-18 00:10:49" is valid
INFO: restoring WAL files from backup "2020-11-18 00:10:49"
INFO: restoring online WAL files and server log files
INFO: add recovery related options to postgresql.conf
INFO: generating recovery.signal
INFO: restore complete
HINT: Recovery will start automatically when the PostgreSQL server is started.
[postgres@node_206 /postgresql/pgsql]$rm -f data/recovery.signal 
[postgres@node_206 /postgresql/pgsql]$pg_ctl start
waiting for server to start....2020-11-17 11:14:46.684 EST [59594] LOG: 00000: starting PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2020-11-17 11:14:46.684 EST [59594] LOCATION: PostmasterMain, postmaster.c:998
2020-11-17 11:14:46.689 EST [59594] LOG: 00000: listening on IPv4 address "0.0.0.0", port 5432
2020-11-17 11:14:46.689 EST [59594] LOCATION: StreamServerPort, pqcomm.c:593
2020-11-17 11:14:46.689 EST [59594] LOG: 00000: listening on IPv6 address "::", port 5432
2020-11-17 11:14:46.689 EST [59594] LOCATION: StreamServerPort, pqcomm.c:593
2020-11-17 11:14:46.691 EST [59594] LOG: 00000: listening on Unix socket "/tmp/.s.PGSQL.5432"
2020-11-17 11:14:46.691 EST [59594] LOCATION: StreamServerPort, pqcomm.c:587
2020-11-17 11:14:46.732 EST [59594] LOG: 00000: redirecting log output to logging collector process
2020-11-17 11:14:46.732 EST [59594] HINT: Future log output will appear in directory "/postgresql/pgsql/pg_log".
2020-11-17 11:14:46.732 EST [59594] LOCATION: SysLogger_Start, syslogger.c:675
 stopped waiting
pg_ctl: could not start server
Examine the log output.
[postgres@node_206 /postgresql/pgsql]$touch data/recovery.signal
[postgres@node_206 /postgresql/pgsql]$pg_ctl start
waiting for server to start....2020-11-17 11:15:04.292 EST [59600] LOG: 00000: starting PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2020-11-17 11:15:04.292 EST [59600] LOCATION: PostmasterMain, postmaster.c:998
2020-11-17 11:15:04.293 EST [59600] LOG: 00000: listening on IPv4 address "0.0.0.0", port 5432
2020-11-17 11:15:04.293 EST [59600] LOCATION: StreamServerPort, pqcomm.c:593
2020-11-17 11:15:04.293 EST [59600] LOG: 00000: listening on IPv6 address "::", port 5432
2020-11-17 11:15:04.293 EST [59600] LOCATION: StreamServerPort, pqcomm.c:593
2020-11-17 11:15:04.296 EST [59600] LOG: 00000: listening on Unix socket "/tmp/.s.PGSQL.5432"
2020-11-17 11:15:04.296 EST [59600] LOCATION: StreamServerPort, pqcomm.c:587
2020-11-17 11:15:04.347 EST [59600] LOG: 00000: redirecting log output to logging collector process
2020-11-17 11:15:04.347 EST [59600] HINT: Future log output will appear in directory "/postgresql/pgsql/pg_log".
2020-11-17 11:15:04.347 EST [59600] LOCATION: SysLogger_Start, syslogger.c:675
 done
server started
[postgres@node_206 /postgresql/pgsql]$psql 
psql (12.3)
Type "help" for help.
postgres=# \l
                 List of databases
  Name  | Owner  | Encoding |  Collate  |  Ctype  |  Access privileges  
-----------+----------+----------+-------------+-------------+-----------------------
 postgres | postgres | UTF8   | en_US.UTF-8 | en_US.UTF-8 | 
 repmgr  | repmgr  | UTF8   | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8   | en_US.UTF-8 | en_US.UTF-8 | =c/postgres     +
      |     |     |       |       | postgres=CTc/postgres
 template1 | postgres | UTF8   | en_US.UTF-8 | en_US.UTF-8 | =c/postgres     +
      |     |     |       |       | postgres=CTc/postgres
(4 rows)
postgres=# \q
[postgres@node_206 /postgresql/pgsql]$pg_rman 
ERROR: required parameter not specified: BACKUP_PATH (-B, --backup-path)
[postgres@node_206 /postgresql/pgsql]$pg_rman --help
pg_rman manage backup/recovery of PostgreSQL database.
Usage:
 pg_rman OPTION init
 pg_rman OPTION backup
 pg_rman OPTION restore
 pg_rman OPTION show [DATE]
 pg_rman OPTION show detail [DATE]
 pg_rman OPTION validate [DATE]
 pg_rman OPTION delete DATE
 pg_rman OPTION purge
Common Options:
 -D, --pgdata=PATH     location of the database storage area
 -A, --arclog-path=PATH  location of archive WAL storage area
 -S, --srvlog-path=PATH  location of server log storage area
 -B, --backup-path=PATH  location of the backup storage area
 -c, --check        show what would have been done
 -v, --verbose       show what detail messages
 -P, --progress      show progress of processed files
Backup options:
 -b, --backup-mode=MODE  full, incremental, or archive
 -s, --with-serverlog   also backup server log files
 -Z, --compress-data    compress data backup with zlib
 -C, --smooth-checkpoint  do smooth checkpoint before backup
 -F, --full-backup-on-error  switch to full backup mode
                if pg_rman cannot find validate full backup
                on current timeline
   NOTE: this option is only used in --backup-mode=incremental or archive.
 --keep-data-generations=NUM keep NUM generations of full data b编程客栈ackup
 --keep-data-days=NUM    keep enough data backup to recover to N days ago
 --keep-arclog-files=NUM  keep NUM of archived WAL
 --keep-arclog-days=DAY  keep archived WAL modified in DAY days
 --keep-srvlog-files=NUM  keep NUM of serverlogs
 --keep-srvlog-days=DAY  keep serverlog modified in DAY days
 --standby-host=HOSTNAME  standby host when taking backup from standby
 --standby-port=PORT    standby port when taking backup from standby
Restore options:
 --recovery-target-time  time stamp up to which recovery will proceed
 --recovery-target-xid   transaction ID up to which recovery will proceed
 --recovery-target-inclusive whether we stop just after the recovery target
 --recovery-target-timeline recovering into a particular timeline
 --hard-copy         copying archivelog not symbolic link
Catalog options:
 -a, --show-all      show deleted backup too
Delete options:
 -f, --force        forcibly delete backup older than given DATE
Connection options:
 -d, --dbname=DBNAME    database to connect
 -h, --host=HOSTNAME    database server host or socket directory
 -p, --port=PORT      database server port
 -U, --username=USERNAME  user name to connect as
 -w, --no-password     never prompt for password
 -W, --password      force password prompt
Generic options:
 -q, --quiet        don't show any INFO or DEBUG messages
 --debug          show DEBUG messages
 --help          show this help, then exit
 --version         output version information, then exit
Read the website for details. <http://github.com/ossc-db/pg_rman>
Report bugs to <http://github.com/ossc-db/pg_rman/issues>.
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$pwd
/postgresql/pgsql
[postgres@node_206 /postgresql/pgsql]$ls
archive_log backups bin data include lib pg_log pg_rman_backups share
[postgres@node_206 /postgresql/pgsql]$vim $PGDATA/postgresql.conf
Catalog options:
 -a, --show-all      show deleted backup too
Delete options:
 -f, --force        forcibly delete backup older than given DATE
Connection options:
 -d, --dbname=DBNAME    database to connect
 -h, --host=HOSTNAME    database server host or socket directory
 -p, --port=PORT      database server port
 -U, --username=USERNAME  user name to connect as
 -w, --no-password     never prompt for password
 -W, --password      force password prompt
Generic options:
 -q, --quiet        don't show any INFO or DEBUG messages
 --debug          show DEBUG messages
 --help          show this help, then exit
 --version         output version information, then exit
Read the website for details. <http://github.com/ossc-db/pg_rman>
Report bugs to <http://github.com/ossc-db/pg_rman/issues>.
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$pwd
/postgresql/pgsql
[postgres@node_206 /postgresql/pgsql]$ls
archive_log backups bin data include lib pg_log pg_rman_backups share
[postgres@node_206 /postgresql/pgsql]$vim $PGDATA/postgresql.conf
[postgres@node_206 /postgresql/pgsql]$pg_rman show -B /postgresql/pgsql/pg_rman_backups/
=====================================================================
 StartTime      EndTime       Mode  Size  TLI Status 
=====================================================================
2020-11-18 00:10:49 2020-11-18 00:10:51 FULL  64MB   6 OK
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$ll
total 28
drwxr-xr-x. 2 postgres postgres 4096 Nov 18 00:15 archive_log
drwxrwxr-x. 2 postgres postgres  6 Nov 17 15:51 backups
drwxr-xr-x. 2 postgres postgres 4096 Nov 17 16:43 bin
drwx------. 19 postgres postgres 4096 Nov 18 02:41 data
drwxr-xr-x. 6 postgres postgres 4096 Nov 15 16:14 include
drwxr-xr-x. 4 postgres postgres 4096 Nov 17 06:37 lib
drwxr-xr-x. 2 postgres postgres 4096 Nov 18 00:15 pg_log
drwxrwxr-x. 5 postgres postgres 104 Nov 18 00:09 pg_rman_backups
drwxr-xr-x. 8 postgres postgres 4096 Nov 15 16:14 share
[postgres@node_206 /postgresql/pgsql]$vim $PGDATA/postgresql.conf
 --version         output version information, then exit
Read the website for details. <http://github.com/ossc-db/pg_rman>
Report bugs to <http://github.com/ossc-db/pg_rman/issues>.
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$pwd
/postgresql/pgsql
[postgres@node_206 /postgresql/pgsql]$ls
archive_log backups bin data include lib pg_log pg_rman_backups share
[postgres@node_206 /postgresql/pgsql]$vim $PGDATA/postgresql.conf
[postgres@node_206 /postgresql/pgsql]$pg_rman show -B /postgresql/pgsql/pg_rman_backups/
=====================================================================
 StartTime      EndTime       Mode  Size  TLI Status 
=====================================================================
2020-11-18 00:10:49 2020-11-18 00:10:51 FULL  64MB   6 OK
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$ll
total 28
drwxr-xr-x. 2 postgres postgres 4096 Nov开发者_数据库 18 00:15 archive_log
drwxrwxr-x. 2 postgres postgres  6 Nov 17 15:51 backups
drwxr-xr-x. 2 postgres postgres 4096 Nov 17 16:43 bin
drwx------. 19 postgres postgres 4096 Nov 18 02:41 data
drwxr-xr-x. 6 postgres postgres 4096 Nov 15 16:14 include
drwxr-xr-x. 4 postgres postgres 4096 Nov 17 06:37 lib
drwxr-xr-x. 2 postgres postgres 4096 Nov 18 00:15 pg_log
drwxrwxr-x. 5 postgres postgres 104 Nov 18 00:09 pg_rman_backups
drwxr-xr-x. 8 postgres postgres 4096 Nov 15 16:14 share
[postgres@node_206 /postgresql/pgsql]$vim $PGDATA/postgresql.conf
[postgres@node_206 /postgresql/pgsql]$pg_ctl restart -m fast
waiting for server to shut down.... done
server stopped
waiting for server to start....2020-11-17 13:41:36.369 EST [60239] LOG: 00000: starting PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2020-11-17 13:41:36.369 EST [60239] LOCATION: PostmasterMain, postmaster.c:998
2020-11-17 13:41:36.371 EST [60239] LOG: 00000: listening on IPv4 address "0.0.0.0", port 5432
2020-11-17 13:41:36.371 EST [60239] LOCATION: StreamServerPort, pqcomm.c:593
2020-11-17 13:41:36.371 EST [60239] LOG: 00000: listening on IPv6 address "::", port 5432
2020-11-17 13:41:36.371 EST [60239] LOCATION: StreamServerPort, pqcomm.c:593
2020-11-17 13:41:36.377 EST [60239] LOG: 00000: listening on Unix socket "/tmp/.s.PGSQL.5432"
2020-11-17 13:41:36.377 EST [60239] LOCATION: StreamServerPort, pqcomm.c:587
2020-11-17 13:41:36.466 EST [60239] LOG: 00000: redirecting log output to logging collector process
2020-11-17 13:41:36.466 EST [60239] HINT: Future log output will appear in directory "/postgresql/pgsql/pg_log".
2020-11-17 13:41:36.466 EST [60239] LOCATION: SysLogger_Start, syslogger.c:675
 done
server started
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$ls
archive_log backups bin data include lib pg_log pg_rman_backups share
[postgres@node_206 /postgresql/pgsql]$ll
total 28
drwxr-xr-x. 2 postgres postgres 4096 Nov 18 02:41 archive_log
drwxrwxr-x. 2 postgres postgres  6 Nov 17 15:51 backups
drwxr-xr-x. 2 postgres postgres 4096 Nov 17 16:43 bin
drwx------. 19 postgres postgres 4096 Nov 18 02:41 data
drwxr-xr-x. 6 postgres postgres 4096 Nov 15 16:14 include
drwxr-xr-x. 4 postgres postgres 4096 Nov 17 06:37 lib
drwxr-xr-x. 2 postgres postgres 4096 Nov 18 02:41 pg_log
drwxrwxr-x. 5 postgres postgres 104 Nov 18 00:09 pg_rman_backups
drwxr-xr-x. 8 postgres postgres 4096 Nov 15 16:14 share
[postgres@node_206 /postgresql/pgsql]$rm -rf archive_log/*
[postgres@node_206 /postgresql/pgsql]$rm -rf pg_rman_backups/*
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$vim $PGDATA/postgresql.conf
2020-11-17 13:41:36.369 EST [60239] LOCATION: PostmasterMain, postmaster.c:998
2020-11-17 13:41:36.371 EST [60239] LOG: 00000: listening on IPv4 address "0.0.0.0", port 5432
2020-11-17 13:41:36.371 EST [60239] LOCATION: StreamServerPort, pqcomm.c:593
2020-11-17 13:41:36.371 EST [60239] LOG: 00000: listening on IPv6 address "::", port 5432
2020-11-17 13:41:36.371 EST [60239] LOCATION: StreamServerPort, pqcomm.c:593
2020-11-17 13:41:36.377 EST [60239] LOG: 00000: listening on Unix socket "/tmp/.s.PGSQL.5432"
2020-11-17 13:41:36.377 EST [60239] LOCATION: StreamServerPort, pqcomm.c:587
2020-11-17 13:41:36.466 EST [60239] LOG: 00000: redirecting log output to logging collector process
2020-11-17 13:41:36.466 EST [60239] HINT: Future log output will appear in directory "/postgresql/pgsql/pg_log".
2020-11-17 13:41:36.466 EST [60239] LOCATION: SysLogger_Start, syslogger.c:675
 done
server started
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$ls
archive_log backups bin data include lib pg_log pg_rman_backups share
[postgres@node_206 /postgresql/pgsql]$ll
total 28
drwxr-xr-x. 2 postgres postgres 4096 Nov 18 02:41 archive_log
drwxrwxr-x. 2 postgres postgres  6 Nov 17 15:51 backups
drwxr-xr-x. 2 postgres postgres 4096 Nov 17 16:43 bin
drwx------. 19 postgres postgres 4096 Nov 18 02:41 data
drwxr-xr-x. 6 postgres postgres 4096 Nov 15 16:14 include
drwxr-xr-x. 4 postgres postgres 4096 Nov 17 06:37 lib
drwxr-xr-x. 2 postgres postgres 4096 Nov 18 02:41 pg_log
drwxrwxr-x. 5 postgres postgres 104 Nov 18 00:09 pg_rman_backups
drwxr-xr-x. 8 postgres postgres 4096 Nov 15 16:14 share
[postgres@node_206 /postgresql/pgsql]$rm -rf archive_log/*
[postgres@node_206 /postgresql/pgsql]$rm -rf pg_rman_backups/*
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$vim $PGDATA/postgresql.conf
[postgres@node_206 /postgresql/pgsql]$pg_ctl restart -m fast
waiting for server to shut down.... done
server stopped
waiting for server to start....2020-11-17 13:41:51.227 EST [60254] LOG: 00000: starting PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2020-11-17 13:41:51.227 EST [60254] LOCATION: PostmasterMain, postmaster.c:998
2020-11-17 13:41:51.228 EST [60254] LOG: 00000: listening on IPv4 address "0.0.0.0", port 5432
2020-11-17 13:41:51.228 EST [60254] LOCATION: StreamServerPort, pqcomm.c:593
2020-11-17 13:41:51.228 EST [60254] LOG: 00000: listening on IPv6 address "::", port 5432
2020-11-17 13:41:51.228 EST [60254] LOCATION: StreamServerPort, pqcomm.c:593
2020-11-17 13:41:51.239 EST [60254] LOG: 00000: listening on Unix socket "/tmp/.s.PGSQL.5432"
2020-11-17 13:41:51.239 EST [60254] LOCATION: StreamServerPort, pqcomm.c:587
2020-11-17 13:41:51.372 EST [60254] LOG: 00000: redirecting log output to logging collector process
2020-11-17 13:41:51.372 EST [60254] HINT: Future log output will appear in directory "/postgresql/pgsql/pg_log".
2020-11-17 13:41:51.372 EST [60254] LOCATION: SysLogger_Start, syslogger.c:675
 done
server started
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$psql
psql (12.3)
Type "help" for help.
postgres=# \l
                 List of databases
  Name  | Owner  | Encoding |  Collate  |  Ctype  |  Access privileges  
-----------+----------+----------+-------------+-------------+-----------------------
 postgres | postgres | UTF8   | en_US.UTF-8 | en_US.UTF-8 | 
 repmgr  | repmgr  | UTF8   | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8   | en_US.UTF-8 | en_US.UTF-8 | =c/postgres     +
      |     |     |       |       | postgres=CTc/postgres
 template1 | postgres | UTF8   | en_US.UTF-8 | en_US.UTF-8 | =c/postgres     +
      |     |     |       |       | postgres=CTc/postgres
(4 rows)
postgres=# \q
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$history |grep pg_rman |grep init
 519 pg_rman init -B /postgresql/pgsql/pg_rman_backups/
 538 pg_rman init -B /postgresql/pgsql/pg_rman_backups
 544 pg_rman init -B /postgresql/pgsql/pg_rman_backups
 725 ll /postgresql/pgsql/pg_rman_backups/*init
 926 ll /postgresql/pgsql/pg_rman_backups/*init
 927 pg_rman init -B /postgresql/pgsql/pg_rman_backups
 976 pg_rman init -B 
 977 pg_rman init 
 980 pg_rman init 
 981 pg_rman init -B /postgresql/pgsql/pg_rman_backups/
 1029 pg_rman init -B /postgresql/pgsql/pg_rman_backups/
 1102 pg_rman init -B /postgresql/pgsql/pg_rman_backups/
 1152 history |grep pg_rman |grep init
[postgres@node_206 /postgresql/pgsql]$pg_rman init -B /postgresql/pgsql/pg_rman_backups/
INFO: ARCLOG_PATH is set to '/postgresql/pgsql/archive_log'
INFO: SRVLOG_PATH is set to '/postgresql/pgsql/pg_log'
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$psql
psql (12.3)
Type "help" for help.
postgres=# create database test;
CREATE DATABASE
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# create table test(id int, crt_time timestamp default clock_timestamp());
CREATE TABLE
test=# insert into test(id) select n from generate_series
test-# (1,1000) n;
INSERT 0 1000
test=# \q
[postgres@node_206 /postgresql/pgsql]$pg_rman backup --backup-mode=full -B /postgresql/pgsql/pg_rman_backups
INFO: copying database files
INFO: copying archived WAL files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.
[postgres@node_206 /postgresql/pgsql]$pg_rman validate -B /postgresql/pgsql/pg_rman_backups
INFO: validate: "2020-11-18 02:43:17" backup and archive log files by CRC
INFO: backup "2020-11-18 02:43:17" is valid
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$pg_rman show -B /postgresql/pgsql/pg_rman_backups/
=====================================================================
 StartTime      EndTime       Mode  Size  TLI Status 
=====================================================================
2020-11-18 02:43:17 2020-11-18 02:43:19 FULL  64MB  10 OK
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$ls
archive_log backups bin data include lib pg_log pg_rman_backups share
[postgres@node_206 /postgresql/pgsql]$cd pg_rman_backups/
[postgres@node_206 /postgresql/pgsql/pg_rman_backups]$ls
20201118 backup pg_rman.ini system_identifier timeline_history
[postgres@node_206 /postgresql/pgsql/pg_rman_backups]$ll
total 8
drwx------. 3 postgres postgres 20 Nov 18 02:43 20201118
drwx------. 4 postgres postgres 34 Nov 18 02:42 backup
-rw-rw-r--. 1 postgres postgres 84 Nov 18 02:42 pg_rman.ini
-rw-rw-r--. 1 postgres postgres 40 Nov 18 02:42 system_identifier
drwx------. 2 postgres postgres 6 Nov 18 02:42 timeline_history
[postgres@node_206 /postgresql/pgsql/pg_rman_backups]$vim *ini
test=# \q
[postgres@node_206 /postgresql/pgsql]$pg_rman backup --backup-mode=full -B /postgresql/pgsql/pg_rman_backups
INFO: copying database files
INFO: copying archived WAL files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.
[postgres@node_206 /postgresql/pgsql]$pg_rman validate -B /postgresql/pgsql/pg_rman_backups
INFO: validate: "2020-11-18 02:43:17" backup and archive log files by CRC
INFO: backup "2020-11-18 02:43:17" is valid
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$pg_rman show -B /postgresql/pgsql/pg_rman_backups/
=====================================================================
 StartTime      EndTime       Mode  Size  TLI Status 
=====================================================================
2020-11-18 02:43:17 2020-11-18 02:43:19 FULL  64MB  10 OK
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$ls
archive_log backups bin data include lib pg_log pg_rman_backups share
[postgres@node_206 /postgresql/pgsql]$cd pg_rman_backups/
[postgres@node_206 /postgresql/pgsql/pg_rman_backups]$ls
20201118 backup pg_rman.ini system_identifier timeline_history
[postgres@node_206 /postgresql/pgsql/pg_rman_backups]$ll
total 8
drwx------. 3 postgres postgres 20 Nov 18 02:43 20201118
drwx------. 4 postgres postgres 34 Nov 18 02:42 backup
-rw-rw-r--. 1 postgres postgres 84 Nov 18 02:42 pg_rman.ini
-rw-rw-r--. 1 postgres postgres 40 Nov 18 02:42 system_identifier
drwx------. 2 postgres postgres 6 Nov 18 02:42 timeline_history
[postgres@node_206 /postgresql/pgsql/pg_rman_backups]$vim *ini
[postgres@node_206 /postgresql/pgsql/pg_rman_backups]$
[postgres@node_206 /postgresql/pgsql/pg_rman_backups]$
[postgres@node_206 /postgresql/pgsql/pg_rman_backups]$
[postgres@node_206 /postgresql/pgsql/pg_rman_backups]$pg_rman show -B /postgresql/pgsql/pg_rman_backups/
=====================================================================
 StartTime      EndTime       Mode  Size  TLI Status 
=====================================================================
2020-11-18 02:43:17 2020-11-18 02:43:19 FULL  64MB  10 OK
[postgres@node_206 /postgresql/pgsql/pg_rman_backups]$
[postgres@node_206 /postgresql/pgsql/pg_rman_backups]$pg_rman show -B /postgresql/pgsql/pg_rman_backups/ detail
======================================================================================================================
 StartTime      EndTime       Mode  Data ArcLog SrvLog  Total Compressed CurTLI ParentTLI Status 
======================================================================================================================
2020-11-18 02:43:17 2020-11-18 02:43:19 FULL  44MB  33MB  ----  64MB    false   10     0 OK
[postgres@node_206 /postgresql/pgsql/pg_rman_backups]$
[postgres@node_206 /postgresql/pgsql/pg_rman_backups]$
[postgres@node_206 /postgresql/pgsql/pg_rman_backups]$
[postgres@node_206 /postgresql/pgsql/pg_rman_backups]$
[postgres@node_206 /postgresql/pgsql/pg_rman_backups]$psql -U postgres -d test
psql (12.3)
Type "help" for help.
test=# \dt
    List of relations
 Schema | Name | Type | Owner  
--------+------+-------+----------
 public | test | table | postgres
(1 row)
test=# select count(*) from test;
 count 
-------
 1000
(1 row)
test=# insert into test (id ) select n from generate_series(1001, 2000) n ;
INSERT 0 1000
test=# \q
[postgres@node_206 /postgresql/pgsql/pg_rman_backups]$pg_rman backup -bi -B /postgresql/pgsql/pg_rman_backups
INFO: copying database files
INFO: copying archived WAL files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.
INFO: start deleting old archived WAL files from ARCLOG_PATH (keep days = 10)
INFO: the threshold timestamp calculated by keep days is "2020-11-08 00:00:00"
INFO: start deleting old backup (keep generations = 3 AND keep after = 2020-09-19 00:00:00)
INFO: does not include the backup just taken
INFO: backup "2020-11-18 02:43:17" should be kept
DETAIL: This is the 1st latest full backup.
[postgres@node_206 /postgresql/pgsql/pg_rman_backups]$pg_rman validate -B /postgresql/pgsql/pg_rman_backups
INFO: validate: "2020-11-18 02:46:41" backup and archive log files by CRC
INFO: backup "2020-11-18 02:46:41" is valid
[postgres@node_206 /postgresql/pgsql/pg_rman_backups]$date
Wed Nov 18 02:48:04 CST 2020
(reverse-i-search)`': ^C
[postgres@node_206 /postgresql/pgsql/pg_rman_backups]$psql
psql (12.3)
Type "help" for help.
postgres=# select now();
       now       
-------------------------------
 2020-11-18 02:48:18.215429+08
(1 row)
postgres=# \q
[postgres@node_206 /postgresql/pgsql/pg_rman_backups]$exit
logout
[root@node_206 ~]# history | grep ntpdate
 467 history | grep ntpdate
[root@node_206 ~]# ntpdate time.ntp.org
Error resolving time.ntp.org: Name or service not known (-2)
18 Nov 02:49:19 ntpdate[60350]: Can't find host time.ntp.org: Name or service not known (-2)
18 Nov 02:49:19 ntpdate[60350]: no servers can be used, exiting
[root@node_206 ~]# ping baidu.com
PING baidu.com (220.181.38.148) 56(84) bytes of data.
64 bytes from 220.181.38.148 (220.181.38.148): icmp_seq=1 ttl=50 time=61.3 ms
^C
--- baidu.com ping statistics ---
1 packets transmitted, 1 received, 0% packet loss, time 0ms
rtt min/avg/max/mdev = 61.374/61.374/61.374/0.000 ms
[root@node_206 ~]# ntpdate 0.cn.pool.ntp.org
20 Dec 22:36:38 ntpdate[60355]: step time server 5.79.108.34 offset 2836002.583561 sec
[root@node_206 ~]# 
[root@node_206 ~]# date
Sun Dec 20 22:36:40 CST 2020
[root@node_206 ~]# vim /etc/crontab
postgres=# select now();
       now       
-------------------------------
 2020-11-18 02:48:18.215429+08
(1 row)
postgres=# \q
[postgres@node_206 /postgresql/pgsql/pg_rman_backups]$exit
logout
[root@node_206 ~]# history | grep ntpdate
 467 history | grep ntpdate
[root@node_206 ~]# ntpdate time.ntp.org
Error resolving time.ntp.org: Name or service not known (-2)
18 Nov 02:49:19 ntpdate[60350]: Can't find host time.ntp.org: Name or service not known (-2)
18 Nov 02:49:19 ntpdate[60350]: no servers can be used, exiting
[root@node_206 ~]# ping baidu.com
PING baidu.com (220.181.38.148) 56(84) bytes of data.
64 bytes from 220.181.38.148 (220.181.38.148): icmp_seq=1 ttl=50 time=61.3 ms
^C
--- baidu.com ping statistics ---
1 packets transmitted, 1 received, 0% packet loss, time 0ms
rtt min/avg/max/mdev = 61.374/61.374/61.374/0.000 ms
[root@node_206 ~]# ntpdate 0.cn.pool.ntp.org
20 Dec 22:36:38 ntpdate[60355]: step time server 5.79.108.34 offset 2836002.583561 sec
[root@node_206 ~]# 
[root@node_206 ~]# date
Sun Dec 20 22:36:40 CST 2020
[root@node_206 ~]# vim /etc/crontab
[root@node_206 ~]# lsd
-bash: lsd: command not found
[root@node_206 ~]# 
[root@node_206 ~]# ls
anaconda-ks.cfg
[root@node_206 ~]# date
Sun Dec 20 22:37:13 CST 2020
[root@node_206 ~]# psql
-bash: psql: command not found
[root@node_206 ~]# ip ro show
default via 192.168.0.1 dev eth0 proto static metric 100 
192.168.0.0/24 dev eth0 proto kernel scope link src 192.168.0.206 metric 100 
[root@node_206 ~]# psql
-bash: psql: command not found
[root@node_206 ~]# su - postgres
Last login: Tue Nov 17 22:47:52 CST 2020 on pts/1
[postgres@node_206 ~]$pg_rman show -B /postgresql/pgsql/pg_rman_backups/
=====================================================================
 StartTime      EndTime       Mode  Size  TLI Status 
=====================================================================
2020-11-18 02:46:41 2020-11-18 02:46:43 INCR  59kB  10 OK
2020-11-18 02:43:17 2020-11-18 02:43:19 FULL  64MB  10 OK
[postgres@node_206 ~]$
[postgres@node_206 ~]$vim $PGDATA/postgresql.conf
[root@node_206 ~]# ntpdate 0.cn.pool.ntp.org
20 Dec 22:36:38 ntpdate[60355]: step time server 5.79.108.34 offset 2836002.583561 sec
[root@node_206 ~]# 
[root@node_206 ~]# date
Sun Dec 20 22:36:40 CST 2020
[root@node_206 ~]# vim /etc/crontab
[root@node_206 ~]# lsd
-bash: lsd: command not found
[root@node_206 ~]# 
[root@node_206 ~]# ls
anaconda-ks.cfg
[root@node_206 ~]# date
Sun Dec 20 22:37:13 CST 2020
[root@node_206 ~]# psql
-bash: psql: command not found
[root@node_206 ~]# ip ro show
default via 192.168.0.1 dev eth0 proto static metric 100 
192.168.0.0/24 dev eth0 proto kernel scope link src 192.168.0.206 metric 100 
[root@node_206 ~]# psql
-bash: psql: command not found
[root@node_206 ~]# su - postgres
Last login: Tue Nov 17 22:47:52 CST 2020 on pts/1
[postgres@node_206 ~]$pg_rman show -B /postgresql/pgsql/pg_rman_backups/
=====================================================================
 StartTime      EndTime       Mode  Size  TLI Status 
=====================================================================
2020-11-18 02:46:41 2020-11-18 02:46:43 INCR  59kB  10 OK
2020-11-18 02:43:17 2020-11-18 02:43:19 FULL  64MB  10 OK
[postgres@node_206 ~]$
[postgres@node_206 ~]$vim $PGDATA/postgresql.conf
[postgres@node_206 ~]$pg_ctl restart -m fast
waiting for server to shut down....
 done
server stopped
waiting for server to start....2020-12-20 09:37:46.967 EST [60411] LOG: 00000: starting PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2020-12-20 09:37:46.967 EST [60411] LOCATION: PostmasterMain, postmaster.c:998
2020-12-20 09:37:46.969 EST [60411] LOG: 00000: listening on IPv4 address "0.0.0.0", port 5432
2020-12-20 09:37:46.969 EST [60411] LOCATION: StreamServerPort, pqcomm.c:593
2020-12-20 09:37:46.969 EST [60411] LOG: 00000: listening on IPv6 address "::", port 5432
2020-12-20 09:37:46.969 EST [60411] LOCATION: StreamServerPort, pqcomm.c:593
2020-12-20 09:37:46.971 EST [60411] LOG: 00000: listening on Unix socket "/tmp/.s.PGSQL.5432"
2020-12-20 09:37:46.971 EST [60411] LOCATION: StreamServerPort, pqcomm.c:587
2020-12-20 09:37:47.014 EST [60411] LOG: 00000: redirecting log output to logging collector process
2020-12-20 09:37:47.014 EST [60411] HINT: Future log output will appear in directory "/postgresql/pgsql/pg_log".
2020-12-20 09:37:47.014 EST [60411] LOCATION: SysLogger_Start, syslogger.c:675
 done
server started
[postgres@node_206 ~]$
[postgres@node_206 ~]$ls
postgres_dba test.dmp
[postgres@node_206 ~]$ll
total 4
drwxrwxr-x. 10 postgres postgres 198 Nov 17 03:00 postgres_dba
-rw-rw-r--. 1 postgres postgres 1275 Nov 17 14:08 test.dmp
[postgres@node_206 ~]$cd /postgresql/p
-bash: cd: /postgresql/p: No such file or directory
[postgres@node_206 ~]$cd /postgresql/pgsql/
[postgres@node_206 /postgresql/pgsql]$ls
archive_log backups bin data include lib pg_log pg_rman_backups share
[postgres@node_206 /postgresql/pgsql]$ll
total 28
drwxr-xr-x. 2 postgres postgres 262 Dec 20 22:37 archive_log
drwxrwxr-x. 2 postgres postgres  6 Nov 17 15:51 backups
drwxr-xr-x. 2 postgres postgres 4096 Nov 17 16:43 bin
drwx------. 19 postgres postgres 4096 Dec 20 22:37 data
drwxr-xr-x. 6 postgres postgres 4096 Nov 15 16:14 include
drwxr-xr-x. 4 postgres postgres 4096 Nov 17 06:37 lib
drwxr-xr-x. 2 postgres postgres 4096 Dec 20 22:37 pg_log
drwxrwxr-x. 5 postgres postgres 104 Nov 18 02:45 pg_rman_backups
drwxr-xr-x. 8 postgres postgres 4096 Nov 15 16:14 share
[postgres@node_206 /postgresql/pgsql]$rm -rf archive_log/* pg_rman_backups/*
[postgres@node_206 /postgresql/pgsql]$ll
total 28
drwxr-xr-x. 2 postgres postgres  6 Dec 20 22:37 archive_log
drwxrwxr-x. 2 postgres postgres  6 Nov 17 15:51 backups
drwxr-xr-x. 2 postgres postgres 4096 Nov 17 16:43 bin
drwx------. 19 postgres postgres 4096 Dec 20 22:37 data
drwxr-xr-x. 6 postgres postgres 4096 Nov 15 16:14 include
drwxr-xr-x. 4 postgres postgres 4096 Nov 17 06:37 lib
drwxr-xr-x. 2 postgres postgres 4096 Dec 20 22:37 pg_log
drwxrwxr-x. 2 postgres postgres  6 Dec 20 22:37 pg_rman_backups
drwxr-xr-x. 8 postgres postgres 4096 Nov 15 16:14 share
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$ll backups/
total 0
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$vim $PGDATA/postgresql.conf
[postgres@node_206 /postgresql/pgsql]$ls
archive_log backups bin data include lib pg_log pg_rman_backups share
[postgres@node_206 /postgresql/pgsql]$ll
total 28
drwxr-xr-x. 2 postgres postgres 262 Dec 20 22:37 archive_log
drwxrwxr-x. 2 postgres postgres  6 Nov 17 15:51 backups
drwxr-xr-x. 2 postgres postgres 4096 Nov 17 16:43 bin
drwx------. 19 postgres postgres 4096 Dec 20 22:37 data
drwxr-xr-x. 6 postgres postgres 4096 Nov 15 16:14 include
drwxr-xr-x. 4 postgres postgres 4096 Nov 17 06:37 lib
drwxr-xr-x. 2 postgres postgres 4096 Dec 20 22:37 pg_log
drwxrwxr-x. 5 postgres postgres 104 Nov 18 02:45 pg_rman_backups
drwxr-xr-x. 8 postgres postgres 4096 Nov 15 16:14 share
[postgres@node_206 /postgresql/pgsql]$rm -rf archive_log/* pg_rman_backups/*
[postgres@node_206 /postgresql/pgsql]$ll
total 28
drwxr-xr-x. 2 postgres postgres  6 Dec 20 22:37 archive_log
drwxrwxr-x. 2 postgres postgres  6 Nov 17 15:51 backups
drwxr-xr-x. 2 postgres postgres 4096 Nov 17 16:43 bin
drwx------. 19 postgres postgres 4096 Dec 20 22:37 data
drwxr-xr-x. 6 postgres postgres 4096 Nov 15 16:14 include
drwxr-xr-x. 4 postgres postgres 4096 Nov 17 06:37 lib
drwxr-xr-x. 2 postgres postgres 4096 Dec 20 22:37 pg_log
drwxrwxr-x. 2 postgres postgres  6 Dec 20 22:37 pg_rman_backups
drwxr-xr-x. 8 postgres postgres 4096 Nov 15 16:14 share
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$ll backups/
total 0
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$vim $PGDATA/postgresql.conf
[postgres@node_206 /postgresql/pgsql]$pg_ctl restart -m fast
waiting for server to shut down.... done
server stopped
waiting for server to start....2020-12-20 09:38:08.353 EST [60437] LOG: 00000: starting PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2020-12-20 09:38:08.353 EST [60437] LOCATION: PostmasterMain, postmaster.c:998
2020-12-20 09:38:08.358 EST [60437] LOG: 00000: listening on IPv4 address "0.0.0.0", port 5432
2020-12-20 09:38:08.358 EST [60437] LOCATION: StreamServerPort, pqcomm.c:593
2020-12-20 09:38:08.358 EST [60437] LOG: 00000: listening on IPv6 address "::", port 5432
2020-12-20 09:38:08.358 EST [60437] LOCATION: StreamServerPort, pqcomm.c:593
2020-12-20 09:38:08.364 EST [60437] LOG: 00000: listening on Unix socket "/tmp/.s.PGSQL.5432"
2020-12-20 09:38:08.364 EST [60437] LOCATION: StreamServerPort, pqcomm.c:587
2020-12-20 09:38:08.419 EST [60437] LOG: 00000: redirecting log output to logging collector process
2020-12-20 09:38:08.419 EST [60437] HINT: Future log output will appear in directory "/postgresql/pgsql/pg_log".
2020-12-20 09:38:08.419 EST [60437] LOCATION: SysLogger_Start, syslogger.c:675
 done
server started
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$ls
archive_log backups bin data include lib pg_log pg_rman_backups share
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$pg_rman init -B /postgresql/pgsql/pg_rman_backups/
INFO: ARCLOG_PATH is set to '/postgresql/pgsql/archive_log'
INFO: SRVLOG_PATH is set to '/postgresql/pgsql/pg_log'
[postgres@node_206 /postgresql/pgsql]$vim pg_rman_backups/pg_rman.ini 
[postgres@node_206 /postgresql/pgsql]$ll backups/
total 0
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$vim $PGDATA/postgresql.conf
[postgres@node_206 /postgresql/pgsql]$pg_ctl restart -m fast
waiting for server to shut down.... done
server stopped
waiting for server to start....2020-12-20 09:38:08.353 EST [60437] LOG: 00000: starting PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2020-12-20 09:38:08.353 EST [60437] LOCATION: PostmasterMain, postmaster.c:998
2020-12-20 09:38:08.358 EST [60437] LOG: 00000: listening on IPv4 address "0.0.0.0", port 5432
2020-12-20 09:38:08.358 EST [60437] LOCATION: StreamServerPort, pqcomm.c:593
2020-12-20 09:38:08.358 EST [60437] LOG: 00000: listening on IPv6 address "::", port 5432
2020-12-20 09:38:08.358 EST [60437] LOCATION: StreamServerPort, pqcomm.c:593
2020-12-20 09:38:08.364 EST [60437] LOG: 00000: listening on Unix socket "/tmp/.s.PGSQL.5432"
2020-12-20 09:38:08.364 EST [60437] LOCATION: StreamServerPort, pqcomm.c:587
2020-12-20 09:38:08.419 EST [60437] LOG: 00000: redirecting log output to logging collector process
2020-12-20 09:38:08.419 EST [60437] HINT: Future log output will appear in directory "/postgresql/pgsql/pg_log".
2020-12-20 09:38:08.419 EST [60437] LOCATION: SysLogger_Start, syslogger.c:675
 done
server started
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$ls
archive_log backups bin data include lib pg_log pg_rman_backups share
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$pg_rman init -B /postgresql/pgsql/pg_rman_backups/
INFO: ARCLOG_PATH is set to '/postgresql/pgsql/archive_log'
INFO: SRVLOG_PATH is set to '/postgresql/pgsql/pg_log'
[postgres@node_206 /postgresql/pgsql]$vim pg_rman_backups/pg_rman.ini 
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$pg_rman show -B /postgresql/pgsql/pg_rman_backups/
=====================================================================
 StartTime      EndTime       Mode  Size  TLI Status 
=====================================================================
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$psql -Upostgres
psql (12.3)
Type "help" for help.
postgres=# \l
                 List of databases
  Name  | Owner  | Encoding |  Collate  |  Ctype  |  Access privileges  
-----------+----------+----------+-------------+-------------+-----------------------
 postgres | postgres | UTF8   | en_US.UTF-8 | en_US.UTF-8 | 
 repmgr  | repmgr  | UTF8   | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8   | en_US.UTF-8 | en_US.UTF-8 | =c/postgres     +
      |     |     |       |       | postgres=CTc/postgres
 template1 | postgres | UTF8   | en_US.UTF-8 | en_US.UTF-8 | =c/postgres     +
      |     |     |       |       | postgres=CTc/postgres
 test   | postgres | UTF8   | en_US.UTF-8 | en_US.UTF-8 | 
(5 rows)
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# create table test_pg_rman (id int, crt_time timestamp default clock_timestamp());
CREATE TABLE
test=# insert into test_pg_rman (id) select n from generate_series(1,10000) n;
INSERT 0 10000
test=# \q
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$pg_rman backup --backup-mode=full -B /postgresql/pgsql/pg_rman_backups
INFO: copying database files
INFO: copying archived WAL files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.
INFO: start deleting old archived WAL files from ARCLOG_PATH (keep days = 10)
INFO: the threshold timestamp calculated by keep days is "2020-12-10 00:00:00"
INFO: start deleting old backup (keep generations = 3 AND keep after = 2020-10-21 00:00:00)
INFO: does not include the backup just taken
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$pg_rman validate -B /postgresql/pgsql/pg_rman_backups
INFO: validate: "2020-12-20 22:39:35" backup and archive log files by CRC
INFO: backup "2020-12-20 22:39:35" is valid
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$cat pg_rman_backups/*ini
ARCLOG_PATH='/postgresql/pgsql/archive_log'
SRVLOG_PATH='/postgresql/pgsql/pg_log'
COMPRESS_DATA = YES
KEEP_ARCLOG_DAYS = 10
KEEP_DATA_GENERATIONS = 3
KEEP_DATA_DAYS = 60
KEEP_SRVLOG_DAYS = 10
[postgres@node_206 /postgresql/pgsql]$ll /postgresql/pgsql/pg_log/
total 288
-rw-------. 1 postgres postgres 256 Nov 15 17:39 postgresql-2020-11-15_043957.log
-rw-------. 1 postgres postgres 256 Nov 15 18:00 postgresql-2020-11-15_050047.log
-rw-------. 1 postgres postgres 256 Nov 15 18:08 postgresql-2020-11-15_050800.log
-rw-------. 1 postgres postgres  0 Nov 16 13:00 postgresql-2020-11-16_000000.log
-rw-------. 1 postgres postgres 256 Nov 17 07:32 postgresql-2020-11-16_183244.log
-rw-------. 1 postgres postgres 256 Nov 17 07:33 postgresql-2020-11-16_183333.log
-rw-------. 1 postgres postgres 256 Nov 17 09:22 postgresql-2020-11-16_202244.log
-rw-------. 1 postgres postgres 256 Nov 17 09:26 postgresql-2020-11-16_202616.log
-rw-------. 1 postgres postgres 256 Nov 17 09:32 postgresql-2020-11-16_203210.log
-rw-------. 1 postgres postgres  0 Nov 17 13:00 postgresql-2020-11-17_000000.log
-rw-------. 1 postgres postgres 256 Nov 17 16:49 postgresql-2020-11-17_034947.log
-rw-------. 1 postgres postgres 448 Nov 17 17:06 postgresql-2020-11-17_040642.log
-rw-------. 1 postgres postgres 448 Nov 17 17:08 postgresql-2020-11-17_040822.log
-rw-------. 1 postgres postgres 256 Nov 17 17:09 postgresql-2020-11-17_040929.log
-rw-------. 1 postgres postgres 256 Nov 17 17:11 postgresql-2020-11-17_041114.log
-rw-------. 1 postgres postgres 448 Nov 17 17:11 postgresql-2020-11-17_041147.log
-rw-------. 1 postgres postgres 448 Nov 17 17:13 postgresql-2020-11-17_041342.log
-rw-------. 1 postgres postgres 256 Nov 17 17:15 postgresql-2020-11-17_041506.log
-rw-------. 1 postgres postgres 448 Nov 17 17:18 postgresql-2020-11-17_041841.log
-rw-------. 1 postgres postgres 256 Nov 17 17:20 postgresql-2020-11-17_042040.log
-rw-------. 1 postgres postgres 744 Nov 17 17:23 postgresql-2020-11-17_042334.log
-rw-------. 1 postgres postgres 448 Nov 17 17:31 postgresql-2020-11-17_043101.log
-rw-------. 1 postgres postgres 352 Nov 17 17:46 postgresql-2020-11-17_044648.log
-rw-------. 1 postgres postgres 352 Nov 17 17:47 postgresql-2020-11-17_044749.log
-rw-------. 1 postgres postgres 256 Nov 17 17:48 postgresql-2020-11-17_044854.log
-rw-------. 1 postgres postgres 256 Nov 17 18:10 postgresql-2020-11-17_051036.log
-rw-------. 1 postgres postgres 256 Nov 17 18:12 postgresql-2020-11-17_051236.log
-rw-------. 1 postgres postgres 256 Nov 17 18:29 postgresql-2020-11-17_052955.log
-rw-------. 1 postgres postgres 1384 Nov 17 18:32 postgresql-2020-11-17_053216.log
-rw-------. 1 postgres postgres 256 Nov 17 23:25 postgresql-2020-11-17_102510.log
-rw-------. 1 postgres postgres 256 Nov 17 23:27 postgresql-2020-11-17_102736.log
-rw-------. 1 postgres postgres 256 Nov 17 23:32 postgresql-2020-11-17_103209.log
-rw-------. 1 postgres postgres 256 Nov 17 23:34 postgresql-2020-11-17_103402.log
-rw-------. 1 postgres postgres 256 Nov 17 23:34 postgresql-2020-11-17_103422.log
-rw-------. 1 postgres postgres 256 Nov 17 23:36 postgresql-2020-11-17_103641.log
-rw-------. 1 postgres postgres 256 Nov 17 23:45 postgresql-2020-11-17_104513.log
-rw-------. 1 postgres postgres 256 Nov 17 23:53 postgresql-2020-11-17_105341.log
-rw-------. 1 postgres postgres 448 Nov 18 00:00 postgresql-2020-11-17_110030.log
-rw-------. 1 postgres postgres 256 Nov 18 00:00 postgresql-2020-11-17_110051.log
-rw-------. 1 postgres postgres 256 Nov 18 00:01 postgresql-2020-11-17_110130.log
-rw-------. 1 postgres postgres 256 Nov 18 00:08 postgresql-2020-11-17_110826.log
-rw-------. 1 postgres postgres 256 Nov 18 00:14 postgresql-2020-11-17_111446.log
-rw-------. 1 postgres postgres 1136 Nov 18 00:15 postgresql-2020-11-17_111504.log
-rw-------. 1 postgres postgres 256 Nov 18 02:41 postgresql-2020-11-17_134136.log
-rw-------. 1 postgres postgres 256 Nov 18 02:41 postgresql-2020-11-17_134151.log
-rw-------. 1 postgres postgres 256 Nov 17 17:28 postgresql-2020-11-17_172801.log
-rw-------. 1 postgres postgres 256 Nov 17 17:40 postgresql-2020-11-17_174037.log
-rw-------. 1 postgres postgres 256 Nov 17 17:44 postgresql-2020-11-17_174409.log
-rw-------. 1 postgres postgres 560 Nov 17 17:50 postgresql-2020-11-17_175020.log
-rw-------. 1 postgres postgres 560 Nov 17 17:52 postgresql-2020-11-17_175256.log
-rw-------. 1 postgres postgres 360 Nov 17 17:58 postgresql-2020-11-17_175847.log
-rw-------. 1 postgres postgres 256 Nov 17 17:59 postgresql-2020-11-17_175928.log
-rw-------. 1 postgres postgres 360 Nov 17 18:00 postgresql-2020-11-17_180030.log
-rw-------. 1 postgres postgres 360 Nov 17 18:01 postgresql-2020-11-17_180115.log
-rw-------. 1 postgres postgres 256 Nov 17 18:04 postgresql-2020-11-17_180404.log
-rw-------. 1 postgres postgres 360 Nov 17 18:07 postgresql-2020-11-17_180712.log
-rw-------. 1 postgres postgres 360 Nov 17 18:07 postgresql-2020-11-17_180740.log
-rw-------. 1 postgres postgres 360 Nov 17 18:11 postgresql-2020-11-17_181126.log
-rw-------. 1 postgres postgres 360 Nov 17 18:19 postgresql-2020-11-17_181918.log
-rw-------. 1 postgres postgres 256 Nov 17 18:24 postgresql-2020-11-17_182446.log
-rw-------. 1 postgres postgres 256 Nov 17 18:25 postgresql-2020-11-17_182540.log
-rw-------. 1 postgres postgres 256 Nov 17 18:26 postgresql-2020-11-17_182619.log
-rw-------. 1 postgres postgres 256 Nov 17 18:27 postgresql-2020-11-17_182733.log
-rw-------. 1 postgres postgres 256 Nov 17 18:27 postgresql-2020-11-17_182743.log
-rw-------. 1 postgres postgres 256 Nov 17 18:28 postgresql-2020-11-17_182847.log
-rw-------. 1 postgres postgres 256 Nov 17 18:29 postgresql-2020-11-17_182916.log
-rw-------. 1 postgres postgres 256 Nov 17 18:30 postgresql-2020-11-17_183028.log
-rw-------. 1 postgres postgres 256 Nov 17 18:33 postgresql-2020-11-17_183304.log
-rw-------. 1 postgres postgres 256 Nov 17 18:34 postgresql-2020-11-17_183413.log
-rw-------. 1 postgres postgres 256 Nov 17 18:34 postgresql-2020-11-17_183450.log
-rw-------. 1 postgres postgres 256 Nov 17 23:06 postgresql-2020-11-17_230648.log
-rw-------. 1 postgres postgres 256 Nov 17 23:20 postgresql-2020-11-17_232015.log
-rw-------. 1 postgres postgres 256 Dec 20 22:37 postgresql-2020-12-20_093747.log
-rw-------. 1 postgres postgres 256 Dec 20 22:38 postgresql-2020-12-20_093808.log
[postgres@node_206 /postgresql/pgsql]$cat pg_rman_backups/*ini
ARCLOG_PATH='/postgresql/pgsql/archive_log'
SRVLOG_PATH='/postgresql/pgsql/pg_log'
COMPRESS_DATA = YES
KEEP_ARCLOG_DAYS = 10
KEEP_DATA_GENERATIONS = 3
KEEP_DATA_DAYS = 60
KEEP_SRVLOG_DAYS = 10
[postgres@node_206 /postgresql/pgsql]$pg_rman show -B /postgresql/pgsql/pg_rman_backups/
=====================================================================
 StartTime      EndTime       Mode  Size  TLI Status 
=====================================================================
2020-12-20 22:39:35 2020-12-20 22:39:37 FULL 5451kB  10 OK
[postgres@node_206 /postgresql/pgsql]$psql -d test
psql (12.3)
Type "help" for help.
test=# select count(*) from test_pg_rman ;
 count 
-------
 10000
(1 row)
test=# insert into test_pg_rman (id) select n from generate_series(10001,20000) n;
INSERT 0 10000
test=# \q
[postgres@node_206 /postgresql/pgsql]$pg_rman backup -bi -B /postgresql/pgsql/pg_rman_backups
INFO: copying database files
INFO: copying archived WAL files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.
INFO: start deleting old archived WAL files from ARCLOG_PATH (keep days = 10)
INFO: the threshold timestamp calculated by keep days is "2020-12-10 00:00:00"
INFO: start deleting old backup (keep generations = 3 AND keep after = 2020-10-21 00:00:00)
INFO: does not include the backup just taken
INFO: backup "2020-12-20 22:39:35" should be kept
DETAIL: This is the 1st latest full backup.
[postgres@node_206 /postgresql/pgsql]$pg_rman validate -B /postgresql/pgsql/pg_rman_backups
INFO: validate: "2020-12-20 22:41:40" backup and archive log files by CRC
INFO: backup "2020-12-20 22:41:40" is valid
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$pg_rman show -B /postgresql/pgsql/pg_rman_backups/
=====================================================================
 StartTime      EndTime       Mode  Size  TLI Status 
=====================================================================
2020-12-20 22:41:40 2020-12-20 22:41:43 INCR  390kB  10 OK
2020-12-20 22:39:35 2020-12-20 22:39:37 FULL 5451kB  10 OK
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$psql -U test
psql: error: could not connect to server: FATAL: role "test" does not exist
[postgres@node_206 /postgresql/pgsql]$psql -U postgres -d test
psql (12.3)
Type "help" for help.
test=# truncate test_pg_rman ;
TRUNCATE TABLE
test=# \q
[postgres@node_206 /postgresql/pgsql]$pg_ctl stop 
waiting for server to shut down.... done
server stopped
[postgres@node_206 /postgresql/pgsql]$pg_rman restore -B /postgresql/pgsql/pg_rman_backups/ --recovery-target-time='2020-12-20 22:39:35'
ERROR: cannot do restore
DETAIL: There is no valid full backup which can be used for given recovery condition.
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$pg_rman restore -B /postgresql/pgsql/pg_rman_backups/ --recovery-target-time='2020-12-20 22:41:40'
INFO: the recovery target timeline ID is not given
INFO: use timeline ID of current database cluster as recovery target: 10
INFO: calculating timeline branches to be used to recovery target point
INFO: searching latest full backup which can be used as restore start point
INFO: found the full backup can be used as base in recovery: "2020-12-20 22:39:35"
INFO: copying online WAL files and server log files
INFO: clearing restore destination
INFO: validate: "2020-12-20 22:39:35" backup and archive log files by SIZE
INFO: backup "2020-12-20 22:39:35" is valid
INFO: restoring database files from the full mode backup "2020-12-20 22:39:35"
INFO: searching incremental backup to be restored
INFO: searching backup which contained archived WAL files to be restored
INFO: backup "2020-12-20 22:39:35" is valid
INFO: restoring WAL files from backup "2020-12-20 22:39:35"
INFO: backup "2020-12-20 22:41:40" is valid
INFO: restoring WAL files from backup "2020-12-20 22:41:40"
INFO: restoring online WAL files and server log files
INFO: add recovery related options to postgresql.conf
INFO: generating recovery.signal
INFO: restore complete
HINT: Recovery will start automatically when the PostgreSQL server is started.
[postgres@node_206 /postgresql/pgsql]$pg_ctl start
waiting for server to start....2020-12-20 09:43:32.516 EST [60584] LOG: 00000: starting PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2020-12-20 09:43:32.516 EST [60584] LOCATION: PostmasterMain, postmaster.c:998
2020-12-20 09:43:32.518 EST [60584] LOG: 00000: listening on IPv4 address "0.0.0.0", port 5432
2020-12-20 09:43:32.518 EST [60584] LOCATION: StreamServerPort, pqcomm.c:593
2020-12-20 09:43:32.519 EST [60584] LOG: 00000: listening on IPv6 address "::", port 5432
2020-12-20 09:43:32.519 EST [60584] LOCATION: StreamServerPort, pqcomm.c:593
2020-12-20 09:43:32.520 EST [60584] LOG: 00000: listening on Unix socket "/tmp/.s.PGSQL.5432"
2020-12-20 09:43:32.520 EST [60584] LOCATION: StreamServerPort, pqcomm.c:587
2020-12-20 09:43:32.565 EST [60584] LOG: 00000: redirecting log output to logging collector process
2020-12-20 09:43:32.565 EST [60584] HINT: Future log output will appear in directory "/postgresql/pgsql/pg_log".
2020-12-20 09:43:32.565 EST [60584] LOCATION: SysLogger_Start, syslogger.c:675
 done
server started
[postgres@node_206 /postgresql/pgsql]$psql
psql (12.3)
Type "help" for help.
postgres=# select * from pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 t
(1 row)
postgres=# select pg_wal_replay_resume();
 pg_wal_replay_resume 
----------------------
 
(1 row)
postgres=# select * from pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 f
(1 row)
postgres=# \q
[postgres@node_206 /postgresql/pgsql]$pg_rman show -B /postgresql/pgsql/pg_rman_backups/
=====================================================================
 StartTime      EndTime       Mode  Size  TLI Status 
=====================================================================
2020-12-20 22:41:40 2020-12-20 22:41:43 INCR  390kB  10 OK
2020-12-20 22:39:35 2020-12-20 22:39:37 FULL 5451kB  10 OK
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$pg_controldata /postgresql/pgsql/data/
pg_control version number:      1201
Catalog version number:        201909212
Database system identifier:      6895281636782426835
Database cluster state:        in production
pg_control last modified:       Sun 20 Dec 2020 10:43:51 PM CST
Latest checkpoint location:      0/86000028
Latest checkpoint's REDO location:  0/86000028
Latest checkpoint's REDO WAL file:  0000000B0000000000000086
Latest checkpoint's TimeLineID:    11
Latest checkpoint's PrevTimeLineID:  10
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:     0:7753
Latest checkpoint's NextOID:     25080
Latest checkpoint's NextMultiXactId: 1
Latest checkpoint's NextMultiOffset: 0
Latest checkpoint's oldestXID:    479
Latest checkpoint's oldestXID's DB:  1
Latest checkpoint's oldestActiveXID: 0
Latest checkpoint's oldestMultiXid:  1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint:      Sun 20 Dec 2020 10:43:51 PM CST
Fake LSN counter for unlogged rels:  0/3E8
Minimum recovery ending location:   0/0
Min recovery ending loc's timeline:  0
Backup start location:        0/0
Backup end location:         0/0
End-of-backup record required:    no
wal_level setting:          replica
wal_log_hints setting:        on
max_connections setting:       200
max_worker_processes setting:     8
max_wal_senders setting:       10
max_prepared_xacts setting:      0
max_locks_per_xact setting:      64
track_commit_timestamp setting:    off
Maximum data alignment:        8
Database block size:         8192
Blocks per segment of large relation: 131072
WAL block size:            8192
Bytes per WAL segment:        16777216
Maximum length of identifiers:    64
Maximum columns in an index:     32
Maximum size of a TOAST chunk:    1996
Size of a large-object chunk:     2048
Date/time type storage:        64-bit integers
Float4 argument passing:       by value
Float8 argument passing:       by value
Data page checksum version:      0
Mock authentication nonce:      0e803fe626d0bf49403235ba1f4f8a665eb48d4d847cb7863dcad401e65676ca
[postgres@node_206 /postgresql/pgsql]$ll data/pg_wal/
total 180268
-rw-------. 1 postgres postgres    42 Dec 20 22:43 00000002.history
-rw-------. 1 postgres postgres    85 Dec 20 22:43 00000003.history
-rw-------. 1 postgres postgres   128 Dec 20 22:43 00000004.history
-rw-------. 1 postgres postgres   171 Dec 20 22:43 00000005.history
-rw-------. 1 postgres postgres   216 Dec 20 22:43 00000006.history
-rw-------. 1 postgres postgres   261 Dec 20 22:43 00000007.history
-rw-------. 1 postgres postgres   261 Dec 20 22:43 00000008.history
-rw-------. 1 postgres postgres   304 Dec 20 22:43 00000009.history
-rw-------. 1 postgres postgres   349 Dec 20 22:43 0000000A0000000000000085.00000028.backup
-rw-------. 1 postgres postgres   259 Dec 20 22:43 0000000A.history
-rw-------. 1 postgres postgres 16777216 Dec 20 22:43 0000000B0000000000000086
-rw-------. 1 postgres postgres 16777216 Dec 20 22:43 0000000B0000000000000087
-rw-------. 1 postgres postgres 16777216 Dec 20 22:43 0000000B0000000000000088
-rw-------. 1 postgres postgres 16777216 Dec 20 22:43 0000000B0000000000000089
-rw-------. 1 postgres postgres 16777216 Dec 20 22:43 0000000B000000000000008A
-rw-------. 1 postgres postgres 16777216 Dec 20 22:43 0000000B000000000000008B
-rw-------. 1 postgres postgres 16777216 Dec 20 22:43 0000000B000000000000008C
-rw-------. 1 postgres postgres 16777216 Dec 20 22:43 0000000B000000000000008D
-rw-------. 1 postgres postgres 16777216 Dec 20 22:43 0000000B000000000000008E
-rw-------. 1 postgres postgres 16777216 Dec 20 22:43 0000000B000000000000008F
-rw-------. 1 postgres postgres 16777216 Dec 20 22:43 0000000B0000000000000090
-rw-------. 1 postgres postgres   312 Dec 20 22:43 0000000B.history
drwx------. 2 postgres postgres   233 Dec 20 22:43 archive_status
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$psql
psql (12.3)
Type "help" for help.
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# select count(*) from test;
 count 
-------
 2000
(1 row)
test=# select count(*) from test_pg_rman ;
 count 
-------
 20000
(1 row)
test=# insert into test_pg_rman (id) select n from generate_series(20001,30000) n;
INSERT 0 10000
test=# \q
[postgres@node_206 /postgresql/pgsql]$pg_rman show -B /postgresql/pgsql/pg_rman_backups/
=====================================================================
 StartTime      EndTime       Mode  Size  TLI Status 
=====================================================================
2020-12-20 22:41:40 2020-12-20 22:41:43 INCR  390kB  10 OK
2020-12-20 22:39:35 2020-12-20 22:39:37 FULL 5451kB  10 OK
[postgres@node_206 /postgresql/pgsql]$pg_rman backup --backup-mode=full -B /postgresql/pgsql/pg_rman_backups
INFO: copying database files
INFO: copying archived WAL files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.
INFO: start deleting old archived WAL files from ARCLOG_PATH (keep days = 10)
INFO: the threshold timestamp calculated by keep days is "2020-12-10 00:00:00"
INFO: start deleting old backup (keep generations = 3 AND keep after = 2020-10-21 00:00:00)
INFO: does not include the backup just taken
INFO: backup "2020-12-20 22:41:40" should be kept
DETAIL: This belongs to the 1st latest full backup.
INFO: backup "2020-12-20 22:39:35" should be kept
DETAIL: This is the 1st latest full backup.
[postgres@node_206 /postgresql/pgsql]$pg_rman validate -B /postgresql/pgsql/pg_rman_backups
INFO: validate: "2020-12-20 22:51:24" backup and archive log files by CRC
INFO: backup "2020-12-20 22:51:24" is valid
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$pg_rman show -B /postgresql/pgsql/pg_rman_backups/
=====================================================================
 StartTime      EndTime       Mode  Size  TLI Status 
=====================================================================
2020-12-20 22:51:24 2020-12-20 22:51:27 FULL 6184kB  11 OK
2020-12-20 22:41:40 2020-12-20 22:41:43 INCR  390kB  10 OK
2020-12-20 22:39:35 2020-12-20 22:39:37 FULL 5451kB  10 OK
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$pg_ctl stop
waiting for server to shut down.... done
server stopped
[postgres@node_206 /postgresql/pgsql]$ll
total 32
drwxr-xr-x. 2 postgres postgres 4096 Dec 20 22:51 archive_log
drwxrwxr-x. 2 postgres postgres  6 Nov 17 15:51 backups
drwxr-xr-x. 2 postgres postgres 4096 Nov 17 16:43 bin
drwx------. 19 postgres postgres 4096 Dec 20 22:51 data
drwxr-xr-x. 6 postgres postgres 4096 Nov 15 16:14 include
drwxr-xr-x. 4 postgres postgres 4096 Nov 17 06:37 lib
drwxr-xr-x. 2 postgres postgres 4096 Dec 20 22:43 pg_log
drwxrwxr-x. 5 postgres postgres 104 Dec 20 22:39 pg_rman_backups
drwxr-xr-x. 8 postgres postgres 4096 Nov 15 16:14 share
[postgres@node_206 /postgresql/pgsql]$mv data data_origin
[postgres@node_206 /postgresql/pgsql]$pg_ctl start
pg_ctl: directory "/postgresql/pgsql/data" does not exist
[postgres@node_206 /postgresql/pgsql]$pg_ctl start
pg_ctl: directory "/postgresql/pgsql/data" does not exist
[postgres@node_206 /postgresql/pgsql]$mv data_origin/ data
[postgres@node_206 /postgresql/pgsql]$pg_ctl start
waiting for server to start....2020-12-20 09:52:26.732 EST [60723] LOG: 00000: starting PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2020-12-20 09:52:26.732 EST [60723] LOCATION: PostmasterMain, postmaster.c:998
2020-12-20 09:52:26.738 EST [60723] LOG: 00000: listening on IPv4 address "0.0.0.0", port 5432
2020-12-20 09:52:26.738 EST [60723] LOCATION: StreamServerPort, pqcomm.c:593
2020-12-20 09:52:26.738 EST [60723] LOG: 00000: listening on IPv6 address "::", port 5432
2020-12-20 09:52:26.738 EST [60723] LOCATION: StreamServerPort, pqcomm.c:593
2020-12-20 09:52:26.740 EST [60723] LOG: 00000: listening on Unix socket "/tmp/.s.PGSQL.5432"
2020-12-20 09:52:26.740 EST [60723] LOCATION: StreamServerPort, pqcomm.c:587
2020-12-20 09:52:26.791 EST [60723] LOG: 00000: redirecting log output to logging collector process
2020-12-20 09:52:26.791 EST [60723] HINT: Future log output will appear in directory "/postgresql/pgsql/pg_log".
2020-12-20 09:52:26.791 EST [60723] LOCATION: SysLogger_Start, syslogger.c:675
 done
server started
[postgres@node_206 /postgresql/pgsql]$psql -U postgres
psql (12.3)
Type "help" for help.
postgres=# drop database test;
DROP DATABASE
postgres=# \q
[postgres@node_206 /postgresql/pgsql]$pg_ctl stop
waiting for server to shut down.... done
server stopped
[postgres@node_206 /postgresql/pgsql]$mv data/ data_origin
[postgres@node_206 /postgresql/pgsql]$pg_rman restore -B /postgresql/pgsql/pg_rman_backups/ 
WARNING: pg_controldata file "/postgresql/pgsql/data/global/pg_control" does not exist
INFO: the recovery target timeline ID is not given
INFO: use timeline ID of latest full backup as recovery target: 11
INFO: calculating timeline branches to be used to recovery target point
INFO: searching latest full backup which can be used as restore start point
INFO: found the full backup can be used as base in recovery: "2020-12-20 22:51:24"
INFO: copying online WAL files and server log files
INFO: clearing restore destination
INFO: validate: "2020-12-20 22:51:24" backup and archive log files by SIZE
INFO: backup "2020-12-20 22:51:24" is valid
INFO: restoring database files from the full mode backup "2020-12-20 22:51:24"
INFO: searching incremental backup to be restored
INFO: searching backup which contained archived WAL files to be restored
INFO: backup "2020-12-20 22:51:24" is valid
INFO: restoring WAL files from backup "2020-12-20 22:51:24"
INFO: restoring online WAL files and server log files
INFO: add recovery related options to postgresql.conf
INFO: generating recovery.signal
INFO: restore complete
HINT: Recovery will start automatically when the PostgreSQL server is started.
[postgres@node_206 /postgresql/pgsql]$pg_ctl start
waiting for server to start....2020-12-20 09:53:09.888 EST [60773] LOG: 00000: starting PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2020-12-20 09:53:09.888 EST [60773] LOCATION: PostmasterMain, postmaster.c:998
2020-12-20 09:53:09.889 EST [60773] LOG: 00000: listening on IPv4 address "0.0.0.0", port 5432
2020-12-20 09:53:09.889 EST [60773] LOCATION: StreamServerPort, pqcomm.c:593
2020-12-20 09:53:09.890 EST [60773] LOG: 00000: listening on IPv6 address "::", port 5432
2020-12-20 09:53:09.890 EST [60773] LOCATION: StreamServerPort, pqcomm.c:593
2020-12-20 09:53:09.893 EST [60773] LOG: 00000: listening on Unix socket "/tmp/.s.PGSQL.5432"
2020-12-20 09:53:09.893 EST [60773] LOCATION: StreamServerPort, pqcomm.c:587
2020-12-20 09:53:09.947 EST [60773] LOG: 00000: redirecting log output to logging collector process
2020-12-20 09:53:09.947 EST [60773] HINT: Future log output will appear in directory "/postgresql/pgsql/pg_log".
2020-12-20 09:53:09.947 EST [60773] LOCATION: SysLogger_Start, syslogger.c:675
 done
server started
[postgres@node_206 /postgresql/pgsql]$psql
psql (12.3)
Type "help" for help.
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# \dt
      List of relations
 Schema |   Name   | Type | Owner  
--------+--------------+-------+----------
 public | test     | table | postgres
 public | test_pg_rman | table | postgres
(2 rows)
test=# select count(*) from test_pg_Rman;
 count 
-------
 30000
(1 row)
test=# select count(*) from test;
 count 
-------
 2000
(1 row)
test=# 
test=# select * from pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 t
(1 row)
test=# select pg_wal_replay_resume();
 pg_wal_replay_resume 
----------------------
 
(1 row)
test=# select * from pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 f
(1 row)
test=# \q
[postgres@node_206 /postgresql/pgsql]$pg_rman show -B /postgresql/pgsql/pg_rman_backups/
=====================================================================
 StartTime      EndTime       Mode  Size  TLI Status 
=====================================================================
2020-12-20 22:51:24 2020-12-20 22:51:27 FULL 6184kB  11 OK
2020-12-20 22:41:40 2020-12-20 22:41:43 INCR  390kB  10 OK
2020-12-20 22:39:35 2020-12-20 22:39:37 FULL 5451kB  10 OK
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$pg_rman restore -B /postgresql/pgsql/pg_rman_backups/ --recovery-target-time='2020-12-20 22:39:35'
ERROR: PostgreSQL server is running
HINT: Please stop PostgreSQL server before executing restore.
[postgres@node_206 /postgresql/pgsql]$pg_ctl stop
waiting for server to shut down.... done
server stopped
[postgres@node_206 /postgresql/pgsql]$pg_rman restore -B /postgresql/pgsql/pg_rman_backups/ --recovery-target-time='2020-12-20 22:39:35'
ERROR: cannot do restore
DETAIL: There is no valid full backup which can be used for given recovery condition.
[postgres@node_206 /postgresql/pgsql]$pg_rman restore -B /postgresql/pgsql/pg_rman_backups/ --recovery-target-time='2020-12-20 22:39:36'
ERROR: cannot do restore
DETAIL: There is no valid full backup which can be used for given recovery condition.
[postgres@node_206 /postgresql/pgsql]$pg_rman restore -B /postgresql/pgsql/pg_rman_backups/ --recovery-target-time='2020-12-20 22:39:34'
ERROR: cannot do restore
DETAIL: There is no valid full backup which can be used for given recovery condition.
[postgres@node_206 /postgresql/pgsql]$pg_rman restore -B /postgresql/pgsql/pg_rman_backups/ --recovery-target-time='2020-12-20 22:39:20'
ERROR: cannot do restore
DETAIL: There is no valid full backup which can be used for given recovery condition.
[postgres@node_206 /postgresql/pgsql]$pg_rman restore -B /postgresql/pgsql/pg_rman_backups/ --recovery-target-time='2020-12-20 22:39:24
> ^C
[postgres@node_206 /postgresql/pgsql]$pg_rman restore -B /postgresql/pgsql/pg_rman_backups/ --recovery-target-time='2020-12-20 22:39:24'
ERROR: cannot do restore
DETAIL: There is no valid full backup which can be used for given recovery condition.
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$pg_rman restore -B /postgresql/pgsql/pg_rman_backups/ --recovery-target-time='2020-12-20 22:39:24' -P
ERROR: cannot do restore
DETAIL: There is no valid full backup which can be used for given recovery condition.
[postgres@node_206 /postgresql/pgsql]$pg_rman restore -B /postgresql/pgsql/pg_rman_backups/ --recovery-target-time='2020-12-20 22:39:24' -P-v
pg_rman: invalid option -- '-'
ERROR: option is not specified
HINT: Try "pg_rman --help" for more information.
[postgres@node_206 /postgresql/pgsql]$pg_rman restore -B /postgresql/pgsql/pg_rman_backups/ --recovery-target-time='2020-12-20 22:39:24' -Pv
========================================
restore start
ERROR: cannot do restore
DETAIL: There is no valid full backup which can be used for given recovery condition.
[postgres@node_206 /postgresql/pgsql]$pg_rman restore -B /postgresql/pgsql/pg_rman_backups/ --recovery-target-time='2020-12-20 22:39:24' -Pv --debug
========================================
restore start
DEBUG: the current timeline ID of database cluster is 12
ERROR: cannot do restore
DETAIL: There is no valid full backup which can be used for given recovery condition.
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$pg_rman show -B /postgresql/pgsql/pg_rman_backups/ details
ERROR: arguments are invalid. near "details"
[postgres@node_206 /postgresql/pgsql]$pg_rman show -B /postgresql/pgsql/pg_rman_backups/ detail
======================================================================================================================
 StartTime      EndTime       Mode  Data ArcLog SrvLog  Total Compressed CurTLI ParentTLI Status 
======================================================================================================================
2020-12-20 22:51:24 2020-12-20 22:51:27 FULL  45MB  117MB  ---- 6184kB    true   11     10 OK
2020-12-20 22:41:40 2020-12-20 22:41:43 INCR 1237kB  33MB  ----  390kB    true   10     0 OK
2020-12-20 22:39:35 2020-12-20 22:39:37 FULL  44MB  33MB  ---- 5451kB    true   10     0 OK
[postgres@node_206 /postgresql/pgsql]$ls
archive_log backups bin data data_origin include lib pg_log pg_rman_backups share
[postgres@node_206 /postgresql/pgsql]$pg_controldata /postgresql/pgsql/data/
pg_control version number:      1201
Catalog version number:        201909212
Database system identifier:      6895281636782426835
Database cluster state:        shut down
pg_control last modified:       Sun 20 Dec 2020 10:54:14 PM CST
Latest checkpoint location:      0/89000028
Latest checkpoint's REDO location:  0/89000028
Latest checkpoint's REDO WAL file:  0000000C0000000000000089
Latest checkpoint's TimeLineID:    12
Latest checkpoint's PrevTimeLineID:  12
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:     0:7755
Latest checkpoint's NextOID:     25080
Latest checkpoint's NextMultiXactId: 1
Latest checkpoint's NextMultiOffset: 0
Latest checkpoint's oldestXID:    479
Latest checkpoint's oldestXID's DB:  1
Latest checkpoint's oldestActiveXID: 0
Latest checkpoint's oldestMultiXid:  1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint:      Sun 20 Dec 2020 10:54:14 PM CST
Fake LSN counter for unlogged rels:  0/3E8
Minimum recovery ending location:   0/0
Min recovery ending loc's timeline:  0
Backup start location:        0/0
Backup end location:         0/0
End-of-backup record required:    no
wal_level setting:          replica
wal_log_hints setting:        on
max_connections setting:       200
max_worker_processes setting:     8
max_wal_senders setting:       10
max_prepared_xacts setting:      0
max_locks_per_xact setting:      64
track_commit_timestamp setting:    off
Maximum data alignment:        8
Database block size:         8192
Blocks per segment of large relation: 131072
WAL block size:            8192
Bytes per WAL segment:        16777216
Maximum length of identifiers:    64
Maximum columns in an index:     32
Maximum size of a TOAST chunk:    1996
Size of a large-object chunk:     2048
Date/time type storage:        64-bit integers
Float4 argument passing:       by value
Float8 argument passing:       by value
Data page checksum version:      0
Mock authentication nonce:      0e803fe626d0bf49403235ba1f4f8a665eb48d4d847cb7863dcad401e65676ca
[postgres@node_206 /postgresql/pgsql]$pg_rman backup --backup-mode=full -B /postgresql/pgsql/pg_rman_backups
ERROR: could not connect to database postgres: could not connect to server: No such file or directory
	Is the server running locally and accepting
	connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
[postgres@node_206 /postgresql/pgsql]$pg_ctl start
waiting for server to start....2020-12-20 09:56:02.612 EST [60839] LOG: 00000: starting PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2020-12-20 09:56:02.612 EST [60839] LOCATION: PostmasterMain, postmaster.c:998
2020-12-20 09:56:02.613 EST [60839] LOG: 00000: listening on IPv4 address "0.0.0.0", port 5432
2020-12-20 09:56:02.613 EST [60839] LOCATION: StreamServerPort, pqcomm.c:593
2020-12-20 09:56:02.613 EST [60839] LOG: 00000: listening on IPv6 address "::", port 5432
2020-12-20 09:56:02.613 EST [60839] LOCATION: StreamServerPort, pqcomm.c:593
2020-12-20 09:56:02.615 EST [60839] LOG: 00000: listening on Unix socket "/tmp/.s.PGSQL.5432"
2020-12-20 09:56:02.615 EST [60839] LOCATION: StreamServerPort, pqcomm.c:587
2020-12-20 09:56:02.664 EST [60839] LOG: 00000: redirecting log output to logging collector process
2020-12-20 09:56:02.664 EST [60839] HINT: Future log output will appear in directory "/postgresql/pgsql/pg_log".
2020-12-20 09:56:02.664 EST [60839] LOCATION: SysLogger_Start, syslogger.c:675
 done
server started
[postgres@node_206 /postgresql/pgsql]$pg_rman backup --backup-mode=full -B /postgresql/pgsql/pg_rman_backups
INFO: copying database files
INFO: copying archived WAL files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.
INFO: start deleting old archived WAL files from ARCLOG_PATH (keep days = 10)
INFO: the threshold timestamp calculated by keep days is "2020-12-10 00:00:00"
INFO: start deleting old backup (keep generations = 3 AND keep after = 2020-10-21 00:00:00)
INFO: does not include the backup just taken
WARNING: backup "2020-12-20 22:55:57" is not taken into account
DETAIL: This is not a valid backup.
INFO: backup "2020-12-20 22:51:24" should be kept
DETAIL: This is the 1st latest full backup.
INFO: backup "2020-12-20 22:41:40" should be kept
DETAIL: This belongs to the 2nd latest full backup.
INFO: backup "2020-12-20 22:39:35" should be kept
DETAIL: This is the 2nd latest full backup.
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$pg_rman restore -B /postgresql/pgsql/data/ --recovery-target-time='2020-11-18 00:10:49' -A /postgresql/pgsql/archive_log/ -S /postgresql/pgsql/pg_log/
ERROR: could not open file "/postgresql/pgsql/data//pg_rman.ini": No such file or directory
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$pg_rman show -B /postgresql/pgsql/pg_rman_backups/ detail
======================================================================================================================
 StartTime      EndTime       Mode  Data ArcLog SrvLog  Total Compressed CurTLI ParentTLI Status 
======================================================================================================================
2020-12-20 22:56:05 2020-12-20 22:56:09 FULL  45MB  201MB  ---- 6251kB    true   12     11 DONE
2020-12-20 22:55:57 2020-12-20 22:55:57 FULL  ----  ----  ----   0B    true    0     0 ERROR
2020-12-20 22:51:24 2020-12-20 22:51:27 FULL  45MB  117MB  ---- 6184kB    true   11     10 OK
2020-12-20 22:41:40 2020-12-20 22:41:43 INCR 1237kB  33MB  ----  390kB    true   10     0 OK
2020-12-20 22:39:35 2020-12-20 22:39:37 FULL  44MB  33MB  ---- 5451kB    true   10     0 OK
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$pg_ctl start
pg_ctl: another server might be running; trying to start server anyway
waiting for server to start....2020-12-20 10:01:14.923 EST [60948] FATAL: F0001: lock file "postmaster.pid" already exists
2020-12-20 10:01:14.923 EST [60948] HINT: Is another postmaster (PID 60839) running in data directory "/postgresql/pgsql/data"?
2020-12-20 10:01:14.923 EST [60948] LOCATION: CreateLockFile, miscinit.c:1034
 stopped waiting
pg_ctl: could not start server
Examine the log output.
[postgres@node_206 /postgresql/pgsql]$psql -U postgres -d test
psql (12.3)
Type "help" for help.
test=# create table test2 (like test);
CREATE TABLE
test=# \d test2
             Table "public.test2"
 Column |      Type       | Collation | Nullable | Default 
----------+-----------------------------+-----------+----------+---------
 id    | integer           |      |     | 
 crt_time | timestamp without time zone |      |     | 
test=# insert into test2 (id ) select n from generate_series(1,1000000) n;
INSERT 0 1000000
test=# \q
[postgres@node_206 /postgresql/pgsql]$pg_rman backup --backup-mode=incremental -B /postgresql/pgsql/pg_rman_backups
INFO: copying database files
ERROR: cannot take an incremental backup
DETAIL: There is no validated full backup with current timeline.
HINT: Please take a full backup and validate it before doing an incremental backup. Or use with --full-backup-on-error command line option.
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$pg_rman backup --backup-mode=full -B /postgresql/pgsql/pg_rman_backups
INFO: copying database files
INFO: copying archived WAL files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.
INFO: start deleting old archived WAL files from ARCLOG_PATH (keep days = 10)
INFO: the threshold timestamp calculated by keep days is "2020-12-10 00:00:00"
INFO: start deleting old backup (keep generations = 3 AND keep after = 2020-10-21 00:00:00)
INFO: does not include the backup just taken
WARNING: backup "2020-12-20 23:01:58" is not taken into account
DETAIL: This is not a valid backup.
WARNING: backup "2020-12-20 22:56:05" is not taken into account
DETAIL: This is not a valid backup.
WARNING: backup "2020-12-20 22:55:57" is not taken into account
DETAIL: This is not a valid backup.
INFO: backup "2020-12-20 22:51:24" should be kept
DETAIL: This is the 1st latest full backup.
INFO: backup "2020-12-20 22:41:40" should be kept
DETAIL: This belongs to the 2nd latest full backup.
INFO: backup "2020-12-20 22:39:35" should be kept
DETAIL: This is the 2nd latest full backup.
[postgres@node_206 /postgresql/pgsql]$pg_rman validate -B /postgresql/pgsql/pg_rman_backups
INFO: validate: "2020-12-20 22:56:05" backup and archive log files by CRC
INFO: backup "2020-12-20 22:56:05" is valid
INFO: validate: "2020-12-20 23:02:07" backup and archive log files by CRC
INFO: backup "2020-12-20 23:02:07" is valid
[postgres@node_206 /postgresql/pgsql]$pg_rman show -B /postgresql/pgsql/pg_rman_backups/ detail
======================================================================================================================
 StartTime      EndTime       Mode  Data ArcLog SrvLog  Total Compressed CurTLI ParentTLI Status 
======================================================================================================================
2020-12-20 23:02:07 2020-12-20 23:02:13 FULL  82MB  301MB  ----  23MB    true   12     11 OK
2020-12-20 23:01:58 2020-12-20 23:01:58 INCR   0B  ----  ----   0B    true   12     11 ERROR
2020-12-20 22:56:05 2020-12-20 22:56:09 FULL  45MB  201MB  ---- 6251kB    true   12     11 OK
2020-12-20 22:55:57 2020-12-20 22:55:57 FULL  ----  ----  ----   0B    true    0     0 ERROR
2020-12-20 22:51:24 2020-12-20 22:51:27 FULL  45MB  117MB  ---- 6184kB    true   11     10 OK
2020-12-20 22:41:40 2020-12-20 22:41:43 INCR 1237kB  33MB  ----  390kB    true   10     0 OK
2020-12-20 22:39:35 2020-12-20 22:39:37 FULL  44MB  33MB  ---- 5451kB    true   10     0 OK
[postgres@node_206 /postgresql/pgsql]$pg_ctl stop 
waiting for server to shut down.... done
server stopped
[postgres@node_206 /postgresql/pgsql]$pg_rman restore -B /postgresql/pgsql/pg_rman_backups/ --recovery-target-time='2020-12-20 22:39:24' -Pv --debug
[postgres@node_206 /postgresql/pgsql]$pg_rman restore -B /postgresql/pgsql/pg_rman_backups/ --recovery-target-time='2020-12-20 22:39:24' -Pv 
[postgres@node_206 /postgresql/pgsql]$pg_rman restore -B /postgresql/pgsql/pg_rman_backups/ --recovery-target-time='2020-12-20 22:56:05'
INFO: the recovery target timeline ID is not given
INFO: use timeline ID of current database cluster as recovery target: 12
INFO: calculating timeline branches to be used to recovery target point
INFO: searching latest full backup which can be used as restore start point
INFO: found the full backup can be used as base in recovery: "2020-12-20 22:51:24"
INFO: copying online WAL files and server log files
INFO: clearing restore destination
INFO: validate: "2020-12-20 22:51:24" backup and archive log files by SIZE
INFO: backup "2020-12-20 22:51:24" is valid
INFO: restoring database files from the full mode backup "2020-12-20 22:51:24"
INFO: searching incremental backup to be restored
INFO: searching backup which contained archived WAL files to be restored
INFO: backup "2020-12-20 22:51:24" is valid
INFO: restoring WAL files from backup "2020-12-20 22:51:24"
INFO: backup "2020-12-20 22:56:05" is valid
INFO: restoring WAL files from backup "2020-12-20 22:56:05"
INFO: backup "2020-12-20 23:02:07" is valid
INFO: restoring WAL files from backup "2020-12-20 23:02:07"
INFO: restoring online WAL files and server log files
INFO: add recovery related options to postgresql.conf
INFO: generating recovery.signal
INFO: restore complete
HINT: Recovery will start automatically when the PostgreSQL server is started.
[postgres@node_206 /postgresql/pgsql]$pg_ctl start
waiting for server to start....2020-12-20 10:03:39.933 EST [61032] LOG: 00000: starting PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2020-12-20 10:03:39.933 EST [61032] LOCATION: PostmasterMain, postmaster.c:998
2020-12-20 10:03:39.935 EST [61032] LOG: 00000: listening on IPv4 address "0.0.0.0", port 5432
2020-12-20 10:03:39.935 EST [61032] LOCATION: StreamServerPort, pqcomm.c:593
2020-12-20 10:03:39.936 EST [61032] LOG: 00000: listening on IPv6 address "::", port 5432
2020-12-20 10:03:39.936 EST [61032] LOCATION: StreamServerPort, pqcomm.c:593
2020-12-20 10:03:39.941 EST [61032] LOG: 00000: listening on Unix socket "/tmp/.s.PGSQL.5432"
2020-12-20 10:03:39.941 EST [61032] LOCATION: StreamServerPort, pqcomm.c:587
2020-12-20 10:03:39.982 EST [61032] LOG: 00000: redirecting log output to logging collector process
2020-12-20 10:03:39.982 EST [61032] HINT: Future log output will appear in directory "/postgresql/pgsql/pg_log".
2020-12-20 10:03:39.982 EST [61032] LOCATION: SysLogger_Start, syslogger.c:675
 done
server started
[postgres@node_206 /postgresql/pgsql]$psql -d test
psql (12.3)
Type "help" for help.
test=# \dt
      List of relations
 Schema |   Name   | Type | Owner  
--------+--------------+-------+----------
 public | test     | table | postgres
 public | test_pg_rman | table | postgres
(2 rows)
test=# select pg_wal_replay_resume();
 pg_wal_replay_resume 
----------------------
 
(1 row)
test=# 
test=# select * from pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 f
(1 row)
test=# \q
[postgres@node_206 /postgresql/pgsql]$pg_rman show -B /postgresql/pgsql/pg_rman_backups/ detail
======================================================================================================================
 StartTime      EndTime       Mode  Data ArcLog SrvLog  Total Compressed CurTLI ParentTLI Status 
======================================================================================================================
2020-12-20 23:02:07 2020-12-20 23:02:13 FULL  82MB  301MB  ----  23MB    true   12     11 OK
2020-12-20 23:01:58 2020-12-20 23:01:58 INCR   0B  ----  ----   0B    true   12     11 ERROR
2020-12-20 22:56:05 2020-12-20 22:56:09 FULL  45MB  201MB  ---- 6251kB    true   12     11 OK
2020-12-20 22:55:57 2020-12-20 22:55:57 FULL  ----  ----  ----   0B    true    0     0 ERROR
2020-12-20 22:51:24 2020-12-20 22:51:27 FULL  45MB  117MB  ---- 6184kB    true   11     10 OK
2020-12-20 22:41:40 2020-12-20 22:41:43 INCR 1237kB  33MB  ----  390kB    true   10     0 OK
2020-12-20 22:39:35 2020-12-20 22:39:37 FULL  44MB  33MB  ---- 5451kB    true   10     0 OK
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$pg_controldata /postgresql/pgsql/data/
pg_control version number:      1201
Catalog version number:        201909212
Database system identifier:      6895281636782426835
Database cluster state:        in production
pg_control last modified:       Sun 20 Dec 2020 11:03:50 PM CST
Latest checkpoint location:      0/8B000028
Latest checkpoint's REDO location:  0/8B000028
Latest checkpoint's REDO WAL file:  0000000D000000000000008B
Latest checkpoint's TimeLineID:    13
Latest checkpoint's PrevTimeLineID:  12
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:     0:7755
Latest checkpoint's NextOID:     25080
Latest checkpoint's NextMultiXactId: 1
Latest checkpoint's NextMultiOffset: 0
Latest checkpoint's oldestXID:    479
Latest checkpoint's oldestXID's DB:  1
Latest checkpoint's oldestActiveXID: 0
Latest checkpoint's oldestMultiXid:  1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint:      Sun 20 Dec 2020 11:03:50 PM CST
Fake LSN counter for unlogged rels:  0/3E8
Minimum recovery ending location:   0/0
Min recovery ending loc's timeline:  0
Backup start location:        0/0
Backup end location:         0/0
End-of-backup record required:    no
wal_level setting:          replica
wal_log_hints setting:        on
max_connections setting:       200
max_worker_processes setting:     8
max_wal_senders setting:       10
max_prepared_xacts setting:      0
max_locks_per_xact setting:      64
track_commit_timestamp setting:    off
Maximum data alignment:        8
Database block size:         8192
Blocks per segment of large relation: 131072
WAL block size:            8192
Bytes per WAL segment:        16777216
Maximum length of identifiers:    64
Maximum columns in an index:     32
Maximum size of a TOAST chunk:    1996
Size of a large-object chunk:     2048
Date/time type storage:        64-bit integers
Float4 argument passing:       by value
Float8 argument passing:       by value
Data page checksum version:      0
Mock authentication nonce:      0e803fe626d0bf49403235ba1f4f8a665eb48d4d847cb7863dcad401e65676ca
[postgres@node_206 /postgresql/pgsql]$pg_rman backup --backup-mode=full -B /postgresql/pgsql/pg_rman_backups
INFO: copying database files
INFO: copying archived WAL files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.
INFO: start deleting old archived WAL files from ARCLOG_PATH (keep days = 10)
INFO: the threshold timestamp calculated by keep days is "2020-12-10 00:00:00"
INFO: start deleting old backup (keep generations = 3 AND keep after = 2020-10-21 00:00:00)
INFO: does not include the backup just taken
INFO: backup "2020-12-20 23:02:07" should be kept
DETAIL: This is the 1st latest full backup.
WARNING: backup "2020-12-20 23:01:58" is not taken into account
DETAIL: This is not a valid backup.
INFO: backup "2020-12-20 22:56:05" should be kept
DETAIL: This is the 2nd latest full backup.
WARNING: backup "2020-12-20 22:55:57" is not taken into account
DETAIL: This is not a valid backup.
INFO: backup "2020-12-20 22:51:24" should be kept
DETAIL: This is the 3rd latest full backup.
INFO: backup "2020-12-20 22:41:40" should be kept
DETAIL: This is taken after "2020-10-21 00:00:00".
INFO: backup "2020-12-20 22:39:35" should be kept
DETAIL: This is taken after "2020-10-21 00:00:00".
[postgres@node_206 /postgresql/pgsql]$pg_rman show -B /postgresql/pgsql/pg_rman_backups/ detail
======================================================================================================================
 StartTime      EndTime       Mode  Data ArcLog SrvLog  Total Compressed CurTLI ParentTLI Status 
======================================================================================================================
2020-12-20 23:04:19 2020-12-20 23:04:25 FULL  45MB  352MB  ----  19MB    true   13     12 DONE
2020-12-20 23:02:07 2020-12-20 23:02:13 FULL  82MB  301MB  ----  23MB    true   12     11 OK
2020-12-20 23:01:58 2020-12-20 23:01:58 INCR   0B  ----  ----   0B    true   12     11 ERROR
2020-12-20 22:56:05 2020-12-20 22:56:09 FULL  45MB  201MB  ---- 6251kB    true   12     11 OK
2020-12-20 22:55:57 2020-12-20 22:55:57 FULL  ----  ----  ----   0B    true    0     0 ERROR
2020-12-20 22:51:24 2020-12-20 22:51:27 FULL  45MB  117MB  ---- 6184kB    true   11     10 OK
2020-12-20 22:41:40 2020-12-20 22:41:43 INCR 1237kB  33MB  ----  390kB    true   10     0 OK
2020-12-20 22:39:35 2020-12-20 22:39:37 FULL  44MB  33MB  ---- 5451kB    true   10     0 OK
[postgres@node_206 /postgresql/pgsql]$pg_rman validate -B /postgresql/pgsql/pg_rman_backups
INFO: validate: "2020-12-20 23:04:19" backup and archive log files by CRC
INFO: backup "2020-12-20 23:04:19" is valid
[postgres@node_206 /postgresql/pgsql]$pg_rman show -B /postgresql/pgsql/pg_rman_backups/ detail
======================================================================================================================
 StartTime      EndTime       Mode  Data ArcLog SrvLog  Total Compressed CurTLI ParentTLI Status 
======================================================================================================================
2020-12-20 23:04:19 2020-12-20 23:04:25 FULL  45MB  352MB  ----  19MB    true   13     12 OK
2020-12-20 23:02:07 2020-12-20 23:02:13 FULL  82MB  301MB  ----  23MB    true   12     11 OK
2020-12-20 23:01:58 2020-12-20 23:01:58 INCR   0B  ----  ----   0B    true   12     11 ERROR
2020-12-20 22:56:05 2020-12-20 22:56:09 FULL  45MB  201MB  ---- 6251kB    true   12     11 OK
2020-12-20 22:55:57 2020-12-20 22:55:57 FULL  ----  ----  ----   0B    true    0     0 ERROR
2020-12-20 22:51:24 2020-12-20 22:51:27 FULL  45MB  117MB  ---- 6184kB    true   11     10 OK
2020-12-20 22:41:40 2020-12-20 22:41:43 INCR 1237kB  33MB  ----  390kB    true   10     0 OK
2020-12-20 22:39:35 2020-12-20 22:39:37 FULL  44MB  33MB  ---- 5451kB    true   10     0 OK
[postgres@node_206 /postgresql/pgsql]$psql -d test
psql (12.3)
Type "help" for help.
test=# \dt
      List of relations
 Schema |   Name   | Type | Owner  
--------+--------------+-------+----------
 public | test     | table | postgres
 public | test_pg_rman | table | postgres
(2 rows)
test=# create table test2(like test);
CREATE TABLE
test=# insert into test2 select * from test;
INSERT 0 2000
test=# \q
[postgres@node_206 /postgresql/pgsql]$pg_rman backup -bi -B /postgresql/pgsql/pg_rman_backups
INFO: copying database files
INFO: copying archived WAL files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.
INFO: start deleting old archived WAL files from ARCLOG_PATH (keep days = 10)
INFO: the threshold timestamp calculated by keep days is "2020-12-10 00:00:00"
INFO: start deleting old backup (keep generations = 3 AND keep after = 2020-10-21 00:00:00)
INFO: does not include the backup just taken
INFO: backup "2020-12-20 23:04:19" should be kept
DETAIL: This is the 1st latest full backup.
INFO: backup "2020-12-20 23:02:07" should be kept
DETAIL: This is the 2nd latest full backup.
WARNING: backup "2020-12-20 23:01:58" is not taken into account
DETAIL: This is not a valid backup.
INFO: backup "2020-12-20 22:56:05" should be kept
DETAIL: This is the 3rd latest full backup.
WARNING: backup "2020-12-20 22:55:57" is not taken int account
DETAIL: This is not valid backup.
INFO: backup "2020-12-20 22:51:24" should be kept
DETAIL: This is taken after "2020-10-21 00:00:00".
INFO: backup "2020-12-20 22:41:40" should be kept
DETAIL: This is taken after "2020-10-21 00:00:00".
INFO: backup "2020-12-20 22:39:35" should be kept
DETAIL: This is taken after "2020-10-21 00:00:00".
[postgres@node_206 /postgresql/pgsql]$pg_rman validate -B /postgresql/pgsql/pg_rman_backups
INFO: validate: "2020-12-20 23:06:51" backup and archive log files by CRC
INFO: backup "2020-12-20 23:06:51" is valid
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$pg_rman show -B /postgresql/pgsql/pg_rman_backups/ detail
======================================================================================================================
 StartTime      EndTime       Mode  Data ArcLog SrvLog  Total Compressed CurTLI ParentTLI Status 
======================================================================================================================
2020-12-20 23:06:51 2020-12-20 23:06:53 INCR 2465kB  33MB  ----  112kB    true   13     12 OK
2020-12-20 23:04:19 2020-12-20 23:04:25 FULL  45MB  352MB  ----  19MB    true   13     12 OK
2020-12-20 23:02:07 2020-12-20 23:02:13 FULL  82MB  301MB  ----  23MB    true   12     11 OK
2020-12-20 23:01:58 2020-12-20 23:01:58 INCR   0B  ----  ----   0B    true   12     11 ERROR
2020-12-20 22:56:05 2020-12-20 22:56:09 FULL  45MB  201MB  ---- 6251kB    true   12     11 OK
2020-12-20 22:55:57 2020-12-20 22:55:57 FULL  ----  ----  ----   0B    true    0     0 ERROR
2020-12-20 22:51:24 2020-12-20 22:51:27 FULL  45MB  117MB  ---- 6184kB    true   11     10 OK
2020-12-20 22:41:40 2020-12-20 22:41:43 INCR 1237kB  33MB  ----  390kB    true   10     0 OK
2020-12-20 22:39:35 2020-12-20 22:39:37 FULL  44MB  33MB  ---- 5451kB    true   10     0 OK
[postgres@node_206 /postgresql/pgsql]$psql -d test
psql (12.3)
Type "help" for help.
test=# create table test34(like test);
CREATE TABLE
test=# create table test3(like test);
CREATE TABLE
test=# insert into test3 select * from test;
INSERT 0 2000
test=# \q
[postgres@node_206 /postgresql/pgsql]$pg_rman backup --backup-mode=full -B /postgresql/pgsql/pg_rman_backups
INFO: copying database files
INFO: copying archived WAL files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.
INFO: start deleting old archived WAL files from ARCLOG_PATH (keep days = 10)
INFO: the threshold timestamp calculated by keep days is "2020-12-10 00:00:00"
INFO: start deleting old backup (keep generations = 3 AND keep after = 2020-10-21 00:00:00)
INFO: does not include the backup just taken
INFO: backup "2020-12-20 23:06:51" should be kept
DETAIL: This belongs to the 1st latest full backup.
INFO: backup "2020-12-20 23:04:19" should be kept
DETAIL: This is the 1st latest full backup.
INFO: backup "2020-12-20 23:02:07" should be kept
DETAIL: This is the 2nd latest full backup.
WARNING: backup "2020-12-20 23:01:58" is not taken into account
DETAIL: This is not a valid backup.
INFO: backup "2020-12-20 22:56:05" should be kept
DETAIL: This is the 3rd latest full backup.
WARNING: backup "2020-12-20 22:55:57" is not taken int account
DETAIL: This is not valid backup.
INFO: backup "2020-12-20 22:51:24" should be kept
DETAIL: This is taken after "2020-10-21 00:00:00".
INFO: backup "2020-12-20 22:41:40" should be kept
DETAIL: This is taken after "2020-10-21 00:00:00".
INFO: backup "2020-12-20 22:39:35" should be kept
DETAIL: This is taken after "2020-10-21 00:00:00".
[postgres@node_206 /postgresql/pgsql]$pg_rman validate -B /postgresql/pgsql/pg_rman_backups
INFO: validate: "2020-12-20 23:08:23" backup and archive log files by CRC
INFO: backup "2020-12-20 23:08:23" is valid
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$pg_rman show -B /postgresql/pgsql/pg_rman_backups/ detail
======================================================================================================================
 StartTime      EndTime       Mode  Data ArcLog SrvLog  Total Compressed CurTLI ParentTLI Status 
======================================================================================================================
2020-12-20 23:08:23 2020-12-20 23:08:25 FULL  46MB  33MB  ---- 5495kB    true   13     12 OK
2020-12-20 23:06:51 2020-12-20 23:06:53 INCR 2465kB  33MB  ----  112kB    true   13     12 OK
2020-12-20 23:04:19 2020-12-20 23:04:25 FULL  45MB  352MB  ----  19MB    true   13     12 OK
2020-12-20 23:02:07 2020-12-20 23:02:13 FULL  82MB  301MB  ----  23MB    true   12     11 OK
2020-12-20 23:01:58 2020-12-20 23:01:58 INCR   0B  ----  ----   0B    true   12     11 ERROR
2020-12-20 22:56:05 2020-12-20 22:56:09 FULL  45MB  201MB  ---- 6251kB    true   12     11 OK
2020-12-20 22:55:57 2020-12-20 22:55:57 FULL  ----  ----  ----   0B    true    0     0 ERROR
2020-12-20 22:51:24 2020-12-20 22:51:27 FULL  45MB  117MB  ---- 6184kB    true   11     10 OK
2020-12-20 22:41:40 2020-12-20 22:41:43 INCR 1237kB  33MB  ----  390kB    true   10     0 OK
2020-12-20 22:39:35 2020-12-20 22:39:37 FULL  44MB  33MB  ---- 5451kB    true   10     0 OK
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$psql -d test
psql (12.3)
Type "help" for help.
test=# creat table test4(like test);
ERROR: syntax error at or near "creat"
LINE 1: creat table test4(like test);
    ^
test=# create table test4(like test);
CREATE TABLE
test=# insert into test4 select * from test;
INSERT 0 2000
test=# 
test=# \q
[postgres@node_206 /postgresql/pgsql]$pg_rman backup --backup-mode=full -B /postgresql/pgsql/pg_rman_backups
INFO: copying database files
INFO: copying archived WAL files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.
INFO: start deleting old archived WAL files from ARCLOG_PATH (keep days = 10)
INFO: the threshold timestamp calculated by keep days is "2020-12-10 00:00:00"
INFO: start deleting old backup (keep generations = 3 AND keep after = 2020-10-21 00:00:00)
INFO: does not include the backup just taken
INFO: backup "2020-12-20 23:08:23" should be kept
DETAIL: This is the 1st latest full backup.
INFO: backup "2020-12-20 23:06:51" should be kept
DETAIL: This belongs to the 2nd latest full backup.
INFO: backup "2020-12-20 23:04:19" should be kept
DETAIL: This is the 2nd latest full backup.
INFO: backup "2020-12-20 23:02:07" should be kept
DETAIL: This is the 3rd latest full backup.
WARNING: backup "2020-12-20 23:01:58" is not taken int account
DETAIL: This is not valid backup.
INFO: backup "2020-12-20 22:56:05" should be kept
DETAIL: This is taken after "2020-10-21 00:00:00".
WARNING: backup "2020-12-20 22:55:57" is not taken int account
DETAIL: This is not valid backup.
INFO: backup "2020-12-20 22:51:24" should be kept
DETAIL: This is taken after "2020-10-21 00:00:00".
INFO: backup "2020-12-20 22:41:40" should be kept
DETAIL: This is taken after "2020-10-21 00:00:00".
INFO: backup "2020-12-20 22:39:35" should be kept
DETAIL: This is taken after "2020-10-21 00:00:00".
[postgres@node_206 /postgresql/pgsql]$pg_rman validate -B /postgresql/pgsql/pg_rman_backups
INFO: validate: "2020-12-20 23:09:48" backup and archive log files by CRC
INFO: backup "2020-12-20 23:09:48" is valid
[postgres@node_206 /postgresql/pgsql]$pg_rman show -B /postgresql/pgsql/pg_rman_backups/ detail
======================================================================================================================
 StartTime      EndTime       Mode  Data ArcLog SrvLog  Total Compressed CurTLI ParentTLI Status 
======================================================================================================================
2020-12-20 23:09:48 2020-12-20 23:09:50 FULL  46MB  33MB  ---- 5507kB    true   13     12 OK
2020-12-20 23:08:23 2020-12-20 23:08:25 FULL  46MB  33MB  ---- 5495kB    true   13     12 OK
2020-12-20 23:06:51 2020-12-20 23:06:53 INCR 2465kB  33MB  ----  112kB    true   13     12 OK
2020-12-20 23:04:19 2020-12-20 23:04:25 FULL  45MB  352MB  ----  19MB    true   13     12 OK
2020-12-20 23:02:07 2020-12-20 23:02:13 FULL  82MB  301MB  ----  23MB    true   12     11 OK
2020-12-20 23:01:58 2020-12-20 23:01:58 INCR   0B  ----  ----   0B    true   12     11 ERROR
2020-12-20 22:56:05 2020-12-20 22:56:09 FULL  45MB  201MB  ---- 6251kB    true   12     11 OK
2020-12-20 22:55:57 2020-12-20 22:55:57 FULL  ----  ----  ----   0B    true    0     0 ERROR
2020-12-20 22:51:24 2020-12-20 22:51:27 FULL  45MB  117MB  ---- 6184kB    true   11     10 OK
2020-12-20 22:41:40 2020-12-20 22:41:43 INCR 1237kB  33MB  ----  390kB    true   10     0 OK
2020-12-20 22:39:35 2020-12-20 22:39:37 FULL  44MB  33MB  ---- 5451kB    true   10     0 OK
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$psql -d test
psql (12.3)
Type "help" for help.
test=# create table test5(like test);
CREATE TABLE
test=# insert into test5 select * from test;
INSERT 0 2000
test=# \q
[postgres@node_206 /postgresql/pgsql]$pg_rman backup --backup-mode=full -B /postgresql/pgsql/pg_rman_backups
INFO: copying database files
INFO: copying archived WAL files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.
INFO: start deleting old archived WAL files from ARCLOG_PATH (keep days = 10)
INFO: the threshold timestamp calculated by keep days is "2020-12-10 00:00:00"
INFO: start deleting old backup (keep generations = 3 AND keep after = 2020-10-21 00:00:00)
INFO: does not include the backup just taken
INFO: backup "2020-12-20 23:09:48" should be kept
DETAIL: This is the 1st latest full backup.
INFO: backup "2020-12-20 23:08:23" should be kept
DETAIL: This is the 2nd latest full backup.
INFO: backup "2020-12-20 23:06:51" should be kept
DETAIL: This belongs to the 3rd latest full backup.
INFO: backup "2020-12-20 23:04:19" should be kept
DETAIL: This is the 3rd latest full backup.
INFO: backup "2020-12-20 23:02:07" should be kept
DETAIL: This is taken after "2020-10-21 00:00:00".
WARNING: backup "2020-12-20 23:01:58" is not taken int account
DETAIL: This is not valid backup.
INFO: backup "2020-12-20 22:56:05" should be kept
DETAIL: This is taken after "2020-10-21 00:00:00".
WARNING: backup "2020-12-20 22:55:57" is not taken int account
DETAIL: This is not valid backup.
INFO: backup "2020-12-20 22:51:24" should be kept
DETAIL: This is taken after "2020-10-21 00:00:00".
INFO: backup "2020-12-20 22:41:40" should be kept
DETAIL: This is taken after "2020-10-21 00:00:00".
INFO: backup "2020-12-20 22:39:35" should be kept
DETAIL: This is taken after "2020-10-21 00:00:00".
[postgres@node_206 /postgresql/pgsql]$pg_rman validate -B /postgresql/pgsql/pg_rman_backups
INFO: validate: "2020-12-20 23:10:12" backup and archive log files by CRC
INFO: backup "2020-12-20 23:10:12" is valid
[postgres@node_206 /postgresql/pgsql]$pg_rman show -B /postgresql/pgsql/pg_rman_backups/ detail
======================================================================================================================
 StartTime      EndTime       Mode  Data ArcLog SrvLog  Total Compressed CurTLI ParentTLI Status 
======================================================================================================================
2020-12-20 23:10:12 2020-12-20 23:10:14 FULL  46MB  33MB  ---- 5520kB    true   13     12 OK
2020-12-20 23:09:48 2020-12-20 23:09:50 FULL  46MB  33MB  ---- 5507kB    true   13     12 OK
2020-12-20 23:08:23 2020-12-20 23:08:25 FULL  46MB  33MB  ---- 5495kB    true   13     12 OK
2020-12-20 23:06:51 2020-12-20 23:06:53 INCR 2465kB  33MB  ----  112kB    true   13     12 OK
2020-12-20 23:04:19 2020-12-20 23:04:25 FULL  45MB  352MB  ----  19MB    true   13     12 OK
2020-12-20 23:02:07 2020-12-20 23:02:13 FULL  82MB  301MB  ----  23MB    true   12     11 OK
2020-12-20 23:01:58 2020-12-20 23:01:58 INCR   0B  ----  ----   0B    true   12     11 ERROR
2020-12-20 22:56:05 2020-12-20 22:56:09 FULL  45MB  201MB  ---- 6251kB    true   12     11 OK
2020-12-20 22:55:57 2020-12-20 22:55:57 FULL  ----  ----  ----   0B    true    0     0 ERROR
2020-12-20 22:51:24 2020-12-20 22:51:27 FULL  45MB  117MB  ---- 6184kB    true   11     10 OK
2020-12-20 22:41:40 2020-12-20 22:41:43 INCR 1237kB  33MB  ----  390kB    true   10     0 OK
2020-12-20 22:39:35 2020-12-20 22:39:37 FULL  44MB  33MB  ---- 5451kB    true   10     0 OK
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$psql -d test
psql (12.3)
Type "help" for help.
test=# create table test5(like test);
ERROR: relation "test5" already exists
test=# create table test6(like test);
CREATE TABLE
test=# insert into test6 select * from test;
INSERT 0 2000
test=# \q
[postgres@node_206 /postgresql/pgsql]$pg_rman backup -bi -B /postgresql/pgsql/pg_rman_backups
INFO: copying database files
INFO: copying archived WAL files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.
INFO: start deleting old archived WAL files from ARCLOG_PATH (keep days = 10)
INFO: the threshold timestamp calculated by keep days is "2020-12-10 00:00:00"
INFO: start deleting old backup (keep generations = 3 AND keep after = 2020-10-21 00:00:00)
INFO: does not include the backup just taken
INFO: backup "2020-12-20 23:10:12" should be kept
DETAIL: This is the 1st latest full backup.
INFO: backup "2020-12-20 23:09:48" should be kept
DETAIL: This is the 2nd latest full backup.
INFO: backup "2020-12-20 23:08:23" should be kept
DETAIL: This is the 3rd latest full backup.
INFO: backup "2020-12-20 23:06:51" should be kept
DETAIL: This is taken after "2020-10-21 00:00:00".
INFO: backup "2020-12-20 23:04:19" should be kept
DETAIL: This is taken after "2020-10-21 00:00:00".
INFO: backup "2020-12-20 23:02:07" should be kept
DETAIL: This is taken after "2020-10-21 00:00:00".
WARNING: backup "2020-12-20 23:01:58" is not taken int account
DETAIL: This is not valid backup.
INFO: backup "2020-12-20 22:56:05" should be kept
DETAIL: This is taken after "2020-10-21 00:00:00".
WARNING: backup "2020-12-20 22:55:57" is not taken int account
DETAIL: This is not valid backup.
INFO: backup "2020-12-20 22:51:24" should be kept
DETAIL: This is taken after "2020-10-21 00:00:00".
INFO: backup "2020-12-20 22:41:40" should be kept
DETAIL: This is taken after "2020-10-21 00:00:00".
INFO: backup "2020-12-20 22:39:35" should be kept
DETAIL: This is taken after "2020-10-21 00:00:00".
[postgres@node_206 /postgresql/pgsql]$pg_rman validate -B /postgresql/pgsql/pg_rman_backups
INFO: validate: "2020-12-20 23:12:23" backup and archive log files by CRC
INFO: backup "2020-12-20 23:12:23" is valid
[postgres@node_206 /postgresql/pgsql]$pg_rman show -B /postgresql/pgsql/pg_rman_backups/ detail
======================================================================================================================
 StartTime      EndTime       Mode  Data ArcLog SrvLog  Total Compressed CurTLI ParentTLI Status 
======================================================================================================================
2020-12-20 23:12:23 2020-12-20 23:12:25 INCR 2621kB  33MB  ----  140kB    true   13     12 OK
2020-12-20 23:10:12 2020-12-20 23:10:14 FULL  46MB  33MB  ---- 5520kB    true   13     12 OK
2020-12-20 23:09:48 2020-12-20 23:09:50 FULL  46MB  33MB  ---- 5507kB    true   13     12 OK
2020-12-20 23:08:23 2020-12-20 23:08:25 FULL  46MB  33MB  ---- 5495kB    true   13     12 OK
2020-12-20 23:06:51 2020-12-20 23:06:53 INCR 2465kB  33MB  ----  112kB    true   13     12 OK
2020-12-20 23:04:19 2020-12-20 23:04:25 FULL  45MB  352MB  ----  19MB    true   13     12 OK
2020-12-20 23:02:07 2020-12-20 23:02:13 FULL  82MB  301MB  ----  23MB    true   12     11 OK
2020-12-20 23:01:58 2020-12-20 23:01:58 INCR   0B  ----  ----   0B    true   12     11 ERROR
2020-12-20 22:56:05 2020-12-20 22:56:09 FULL  45MB  201MB  ---- 6251kB    true   12     11 OK
2020-12-20 22:55:57 2020-12-20 22:55:57 FULL  ----  ----  ----   0B    true    0     0 ERROR
2020-12-20 22:51:24 2020-12-20 22:51:27 FULL  45MB  117MB  ---- 6184kB    true   11     10 OK
2020-12-20 22:41:40 2020-12-20 22:41:43 INCR 1237kB  33MB  ----  390kB    true   10     0 OK
2020-12-20 22:39:35 2020-12-20 22:39:37 FULL  44MB  33MB  ---- 5451kB    true   10     0 OK
[postgres@node_206 /postgresql/pgsql]$
[postgres@node_206 /postgresql/pgsql]$psql -d test
psql (12.3)
Type "help" for help.
test=# create table test7(like test);
CREATE TABLE
test=# insert into test7 select * from test;
INSERT 0 2000
test=# \q
[postgres@node_206 /postgresql/pgsql]$pg_rman backup -bf -B /postgresql/pgsql/pg_rman_backups
INFO: copying database files
INFO: copying archived WAL files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.
INFO: start deleting old archived WAL files from ARCLOG_PATH (keep days = 10)
INFO: the threshold timestamp calculated by keep days is "2020-12-10 00:00:00"
INFO: start deleting old backup (keep generations = 3 AND keep after = 2020-10-21 00:00:00)
INFO: does not include the backup just taken
INFO: backup "2020-12-20 23:12:23" should be kept
DETAIL: This belongs to the 1st latest full backup.
INFO: backup "2020-12-20 23:10:12" should be kept
DETAIL: This is the 1st latest full backup.
INFO: backup "2020-12-20 23:09:48" should be kept
DETAIL: This is the 2nd latest full backup.
INFO: backup "2020-12-20 23:08:23" should be kept
DETAIL: This is the 3rd latest full backup.
INFO: backup "2020-12-20 23:06:51" should be kept
DETAIL: This is taken after "2020-10-21 00:00:00".
INFO: backup "2020-12-20 23:04:19" should be kept
DETAIL: This is taken after "2020-10-21 00:00:00".
INFO: backup "2020-12-20 23:02:07" should be kept
DETAIL: This is taken after "2020-10-21 00:00:00".
WARNING: backup "2020-12-20 23:01:58" is not taken int account
DETAIL: This is not valid backup.
INFO: backup "2020-12-20 22:56:05" should be kept
DETAIL: This is taken after "2020-10-21 00:00:00".
WARNING: backup "2020-12-20 22:55:57" is not taken int account
DETAIL: This is not valid backup.
INFO: backup "2020-12-20 22:51:24" should be kept
DETAIL: This is taken after "2020-10-21 00:00:00".
INFO: backup "2020-12-20 22:41:40" should be kept
DETAIL: This is taken after "2020-10-21 00:00:00".
INFO: backup "2020-12-20 22:39:35" should be kept
DETAIL: This is taken after "2020-10-21 00:00:00".
[postgres@node_206 /postgresql/pgsql]$pg_rman validate -B /postgresql/pgsql/pg_rman_backups
INFO: validate: "2020-12-20 23:12:47" backup and archive log files by CRC
INFO: backup "2020-12-20 23:12:47" is valid
[postgres@node_206 /postgresql/pgsql]$pg_rman show -B /postgresql/pgsql/pg_rman_backups/ detail
======================================================================================================================
 StartTime      EndTime       Mode  Data ArcLog SrvLog  Total Compressed CurTLI ParentTLI Status 
======================================================================================================================
2020-12-20 23:12:47 2020-12-20 23:12:50 FULL  46MB  33MB  ---- 5546kB    true   13     12 OK
2020-12-20 23:12:23 2020-12-20 23:12:25 INCR 2621kB  33MB  ----  140kB    true   13     12 OK
2020-12-20 23:10:12 2020-12-20 23:10:14 FULL  46MB  33MB  ---- 5520kB    true   13     12 OK
2020-12-20 23:09:48 2020-12-20 23:09:50 FULL  46MB  33MB  ---- 5507kB    true   13     12 OK
2020-12-20 23:08:23 2020-12-20 23:08:25 FULL  46MB  33MB  ---- 5495kB    true   13     12 OK
2020-12-20 23:06:51 2020-12-20 23:06:53 INCR 2465kB  33MB  ----  112kB    true   13     12 OK
2020-12-20 23:04:19 2020-12-20 23:04:25 FULL  45MB  352MB  ----  19MB    true   13     12 OK

总结

在 基于时间点恢复时, 如果之前做过恢复，那么此时DB与之前的备份已经不在同一时间线上。恢复默认只沿着基础备份建立时时间线恢复而不会切换到新的时间线，如果不做处理，恢复结果将和前面的完整恢复一模一样，恢复不出新插入的数据, 所以建议在恢复之后,可以接着做一个全库的备份

恢复之后, 数据库可能处于 read-only状态,此时可以用超户执行select pg_wal_replay_resume(); 或者在启动数据库实例前在postgresql.conf中添加recovery_target_action=‘promote'

pg_rman init 之后会生产 pg_rman.ini文件, 此时可以编辑该文件并添加备份策略,

我们以一个例子来说明：总共保留两周的数据，即14天的数据，每周进行一次全备，每周一和周三的2:00做一次增量备份，每天进行一次归档备份，这样我们需要对pg_rman.init中做如下配置：

KEEP_DATA_GENERATIONS=2
KEEP_DATA_DAYS=14
KEEP_ARCLOG_DAYS=15
KEEP_SRVLOG_DAYS=180

因为我们需要两个全备份，所以KEEP_DATA_GENERATIONS=2

两周的数据所以KEEP_DATA_DAYS=14，而可以把WAL日志多保留一天，所以KEEP_ARCLOG_DAYS=15

至少保留1000个WAL文件，每个WAL为16M，所以大约16G，占用空间不算大。

KEEP_SRVLOG_DAYS=180，通常程序日志不太，所以保留180天的日志，即3个月。

建议备份时添加参数 -C -Z

建议恢复时添加参数–hard-copy

如果没有指定这个参数，pg_rman实际上是把在归档目录中建一个软链接 指向恢复中要用到的WAL日志文件。如果指定了这个参数，则执行真的拷贝。

过程中遇到的问题

Linux 系统时间与 PG 中的时间不一致

解决方法：

rm -f /etc/localtime
ln -sf /usr/share/zoneinfo/Asia/Shanghai /etc/localtime

以上为个人经验，希望能给大家一个参考，也希望大家多多支持我们。如有错误或未考虑完全的地方，望不吝赐教。