怎么在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 23: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/ ===================================================================== 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:30.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" does 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 backup --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
关于怎么在PostgreSQL中利用pg_rman实现物理备份恢复问题的解答就分享到这里了，希望以上内容可以对大家有一定的帮助，如果你还有很多疑惑没有解开，可以关注亿速云行业资讯频道了解更多相关知识。
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
