简介

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

下载

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

安装

tar zxvf pg_rman*.tar.gzchown postgres.postgres -R pg_rman*su - postgrescd xxx/pg_rman*make && make install

使用

开启归档

[postgres@node_206 /postgresql/pgsql/archive_log]$tail -3 /postgresql/pgsql/data/postgresql.conf #for pg_Rmanarchive_mode = on # enables archiving; off, on, or alwaysarchive_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]$psqlpsql (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 testYou 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 10test=# \q[postgres@node_206 /postgresql/pgsql]$pg_rman backup --backup-mode=full -B /postgresql/pgsql/pg_rman_backupsINFO: copying database filesINFO: copying archived WAL filesINFO: backup completeINFO: 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_backupsINFO: validate: "2020-11-17 23:30:25" backup and archive log files by CRCINFO: 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]$psqlpsql (12.3)Type "help" for help.postgres=# \c testYou are now connected to database "test" as user "postgres".test=# insert into test (id) select n from generate_series(21,30) n;INSERT 0 10test=# \q[postgres@node_206 /postgresql/pgsql]$pg_rman backup --backup-mode=incremental -B /postgresql/pgsql/pg_rman_backupsINFO: copying database filesINFO: copying archived WAL filesINFO: backup completeINFO: 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_backupsINFO: validate: "2020-11-17 23:31:08" backup and archive log files by CRCINFO: 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 OK2020-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]$psqlpsql (12.3)Type "help" for help.postgres=# \c testYou are now connected to database "test" as user "postgres".test=# drop table test;DROP TABLEtest=# \q[postgres@node_206 /postgresql/pgsql]$pg_ctl stop -m fastwaiting for server to shut down.... doneserver stopped[postgres@node_206 /postgresql/pgsql]$pg_ctl startwaiting 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-bit2020-11-17 10:32:09.637 EST [58824] LOCATION: PostmasterMain, postmaster.c:9982020-11-17 10:32:09.637 EST [58824] LOG: 00000: listening on IPv4 address "0.0.0.0", port 54322020-11-17 10:32:09.637 EST [58824] LOCATION: StreamServerPort, pqcomm.c:5932020-11-17 10:32:09.637 EST [58824] LOG: 00000: listening on IPv6 address "::", port 54322020-11-17 10:32:09.637 EST [58824] LOCATION: StreamServerPort, pqcomm.c:5932020-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:5872020-11-17 10:32:09.688 EST [58824] LOG: 00000: redirecting log output to logging collector process2020-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 doneserver started[postgres@node_206 /postgresql/pgsql]$psql -d testpsql (12.3)Type "help" for help.test=# insert into test (id ) select n from generate_series(21,30) n;ERROR: relation "test" does not existLINE 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 existLINE 1: insert into test (id ) select n from generate_series(21,30) ... ^test=# \d testDid 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 unreachableHINT: 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 OK2020-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 specifiedHINT: 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 runningHINT: Please stop PostgreSQL server before executing restore.[postgres@node_206 /postgresql/pgsql]$pg_ctl stop -m fastwaiting for server to shut down.... doneserver 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 restoreDETAIL: 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 restoreDETAIL: There is no valid full backup which can be used for given recovery condition.[postgres@node_206 /postgresql/pgsql]$pg_ctl startwaiting 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-bit2020-11-17 10:34:02.558 EST [58862] LOCATION: PostmasterMain, postmaster.c:9982020-11-17 10:34:02.559 EST [58862] LOG: 00000: listening on IPv4 address "0.0.0.0", port 54322020-11-17 10:34:02.559 EST [58862] LOCATION: StreamServerPort, pqcomm.c:5932020-11-17 10:34:02.559 EST [58862] LOG: 00000: listening on IPv6 address "::", port 54322020-11-17 10:34:02.559 EST [58862] LOCATION: StreamServerPort, pqcomm.c:5932020-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:5872020-11-17 10:34:02.608 EST [58862] LOG: 00000: redirecting log output to logging collector process2020-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 doneserver 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 OK2020-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 stopwaiting for server to shut down.... doneserver 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 restoreDETAIL: 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 startwaiting 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-bit2020-11-17 10:34:22.842 EST [58881] LOCATION: PostmasterMain, postmaster.c:9982020-11-17 10:34:22.842 EST [58881] LOG: 00000: listening on IPv4 address "0.0.0.0", port 54322020-11-17 10:34:22.842 EST [58881] LOCATION: StreamServerPort, pqcomm.c:5932020-11-17 10:34:22.842 EST [58881] LOG: 00000: listening on IPv6 address "::", port 54322020-11-17 10:34:22.842 EST [58881] LOCATION: StreamServerPort, pqcomm.c:5932020-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:5872020-11-17 10:34:22.888 EST [58881] LOG: 00000: redirecting log output to logging collector process2020-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 doneserver started[postgres@node_206 /postgresql/pgsql]$pg_rman backup --backup-mode=full -B /postgresql/pgsql/pg_rman_backupsINFO: copying database filesINFO: copying archived WAL filesINFO: backup completeINFO: 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_backupsINFO: validate: "2020-11-17 23:34:26" backup and archive log files by CRCINFO: 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 OK2020-11-17 23:31:08 2020-11-17 23:31:10 INCR 33MB 6 OK2020-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 testYou are now connected to database "test" as user "postgres".test=# \dtDid not find any relations.test=# create table test(id int); CREATE TABLEtest=# 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.confpostgres=# \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 testYou are now connected to database "test" as user "postgres".test=# \dtDid not find any relations.test=# create table test(id int); CREATE TABLEtest=# 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 testYou are now connected to database "test" as user "postgres".test=# \dtDid not find any relations.test=# create table test(id int); CREATE TABLEtest=# 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 fastwaiting for server to shut down.... doneserver stoppedwaiting 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-bit2020-11-17 10:36:41.191 EST [58933] LOCATION: PostmasterMain, postmaster.c:9982020-11-17 10:36:41.192 EST [58933] LOG: 00000: listening on IPv4 address "0.0.0.0", port 54322020-11-17 10:36:41.192 EST [58933] LOCATION: StreamServerPort, pqcomm.c:5932020-11-17 10:36:41.192 EST [58933] LOG: 00000: listening on IPv6 address "::", port 54322020-11-17 10:36:41.192 EST [58933] LOCATION: StreamServerPort, pqcomm.c:5932020-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:5872020-11-17 10:36:41.229 EST [58933] LOG: 00000: redirecting log output to logging collector process2020-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 doneserver started[postgres@node_206 /postgresql/pgsql]$[postgres@node_206 /postgresql/pgsql]$[postgres@node_206 /postgresql/pgsql]$lltotal 28drwxr-xr-x. 2 postgres postgres 4096 Nov 17 23:36 archive_logdrwxrwxr-x. 2 postgres postgres 6 Nov 17 15:51 backupsdrwxr-xr-x. 2 postgres postgres 4096 Nov 17 16:43 bindrwx------. 19 postgres postgres 4096 Nov 17 23:36 datadrwxr-xr-x. 6 postgres postgres 4096 Nov 15 16:14 includedrwxr-xr-x. 4 postgres postgres 4096 Nov 17 06:37 libdrwxr-xr-x. 2 postgres postgres 4096 Nov 17 23:36 pg_logdrwxrwxr-x. 5 postgres postgres 104 Nov 17 23:30 pg_rman_backupsdrwxr-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]$lltotal 24drwxr-xr-x. 2 postgres postgres 6 Nov 17 23:36 archive_logdrwxrwxr-x. 2 postgres postgres 6 Nov 17 15:51 backupsdrwxr-xr-x. 2 postgres postgres 4096 Nov 17 16:43 bindrwx------. 19 postgres postgres 4096 Nov 17 23:36 datadrwxr-xr-x. 6 postgres postgres 4096 Nov 15 16:14 includedrwxr-xr-x. 4 postgres postgres 4096 Nov 17 06:37 libdrwxr-xr-x. 2 postgres postgres 4096 Nov 17 23:36 pg_logdrwxrwxr-x. 2 postgres postgres 6 Nov 17 23:36 pg_rman_backupsdrwxr-xr-x. 8 postgres postgres 4096 Nov 15 16:14 share[postgres@node_206 /postgresql/pgsql]$pwd/postgresql/pgsql[postgres@node_206 /postgresql/pgsql]$lsarchive_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]$lltotal 24drwxr-xr-x. 2 postgres postgres 6 Nov 17 23:36 archive_logdrwxrwxr-x. 2 postgres postgres 6 Nov 17 15:51 backupsdrwxr-xr-x. 2 postgres postgres 4096 Nov 17 16:43 bindrwx------. 19 postgres postgres 4096 Nov 17 23:36 datadrwxr-xr-x. 6 postgres postgres 4096 Nov 15 16:14 includedrwxr-xr-x. 4 postgres postgres 4096 Nov 17 06:37 libdrwxr-xr-x. 2 postgres postgres 4096 Nov 17 23:36 pg_logdrwxrwxr-x. 2 postgres postgres 6 Nov 17 23:36 pg_rman_backupsdrwxr-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.conftotal 24drwxr-xr-x. 2 postgres postgres 6 Nov 17 23:36 archive_logdrwxrwxr-x. 2 postgres postgres 6 Nov 17 15:51 backupsdrwxr-xr-x. 2 postgres postgres 4096 Nov 17 16:43 bindrwx------. 19 postgres postgres 4096 Nov 17 23:36 datadrwxr-xr-x. 6 postgres postgres 4096 Nov 15 16:14 includedrwxr-xr-x. 4 postgres postgres 4096 Nov 17 06:37 libdrwxr-xr-x. 2 postgres postgres 4096 Nov 17 23:36 pg_logdrwxrwxr-x. 2 postgres postgres 6 Nov 17 23:36 pg_rman_backupsdrwxr-xr-x. 8 postgres postgres 4096 Nov 15 16:14 share[postgres@node_206 /postgresql/pgsql]$pwd/postgresql/pgsql[postgres@node_206 /postgresql/pgsql]$lsarchive_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]$lltotal 24drwxr-xr-x. 2 postgres postgres 6 Nov 17 23:36 archive_logdrwxrwxr-x. 2 postgres postgres 6 Nov 17 15:51 backupsdrwxr-xr-x. 2 postgres postgres 4096 Nov 17 16:43 bindrwx------. 19 postgres postgres 4096 Nov 17 23:36 datadrwxr-xr-x. 6 postgres postgres 4096 Nov 15 16:14 includedrwxr-xr-x. 4 postgres postgres 4096 Nov 17 06:37 libdrwxr-xr-x. 2 postgres postgres 4096 Nov 17 23:36 pg_logdrwxrwxr-x. 2 postgres postgres 6 Nov 17 23:36 pg_rman_backupsdrwxr-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_Rmanarchive_mode = on # enables archiving; off, on, or alwaysarchive_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 fastwaiting for server to shut down.... doneserver stoppedwaiting 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-bit2020-11-17 10:45:13.636 EST [59035] LOCATION: PostmasterMain, postmaster.c:9982020-11-17 10:45:13.638 EST [59035] LOG: 00000: listening on IPv4 address "0.0.0.0", port 54322020-11-17 10:45:13.638 EST [59035] LOCATION: StreamServerPort, pqcomm.c:5932020-11-17 10:45:13.638 EST [59035] LOG: 00000: listening on IPv6 address "::", port 54322020-11-17 10:45:13.638 EST [59035] LOCATION: StreamServerPort, pqcomm.c:5932020-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:5872020-11-17 10:45:13.696 EST [59035] LOG: 00000: redirecting log output to logging collector process2020-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 doneserver started[postgres@node_206 /postgresql/pgsql]$[postgres@node_206 /postgresql/pgsql]$lltotal 24drwxr-xr-x. 2 postgres postgres 6 Nov 17 23:36 archive_logdrwxrwxr-x. 2 postgres postgres 6 Nov 17 15:51 backupsdrwxr-xr-x. 2 postgres postgres 4096 Nov 17 16:43 bindrwx------. 19 postgres postgres 4096 Nov 17 23:45 datadrwxr-xr-x. 6 postgres postgres 4096 Nov 15 16:14 includedrwxr-xr-x. 4 postgres postgres 4096 Nov 17 06:37 libdrwxr-xr-x. 2 postgres postgres 4096 Nov 17 23:45 pg_logdrwxrwxr-x. 2 postgres postgres 6 Nov 17 23:36 pg_rman_backupsdrwxr-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 testYou 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 testtest-# ;DROP TABLEtest=# create table test(id int, crt_time timestamp);CREATE TABLEtest=# insert into test(id) select n from generate_series(1,100) n;INSERT 0 100test=# \q[postgres@node_206 /postgresql/pgsql]$pg_rman backup --backup-mode=full -B /postgresql/pgsql/pg_rman_backupsINFO: copying database filesINFO: copying archived WAL filesINFO: backup completeINFO: 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_backupsINFO: validate: "2020-11-17 23:46:22" backup and archive log files by CRCINFO: 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 testYou 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 10test=# \q[postgres@node_206 /postgresql/pgsql]$pg_rman backup --backup-mode=incremental -B /postgresql/pgsql/pg_rman_backupsINFO: copying database filesINFO: copying archived WAL filesINFO: backup completeINFO: 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_backupsINFO: validate: "2020-11-17 23:47:09" backup and archive log files by CRCINFO: 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 OK2020-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 testpsql (12.3)Type "help" for help.test=# create table test2( like test );CREATE TABLEtest=# insert into test2(id) select n from generate_serires(1,100) n;ERROR: function generate_serires(integer, integer) does not existLINE 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 100test=# \q[postgres@node_206 /postgresql/pgsql]$pg_rman backup --backup-mode=full -B /postgresql/pgsql/pg_rman_backupsINFO: copying database filesINFO: copying archived WAL filesINFO: backup completeINFO: 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_backupsINFO: validate: "2020-11-17 23:49:32" backup and archive log files by CRCINFO: backup "2020-11-17 23:49:32" is valid[postgres@node_206 /postgresql/pgsql]$psql -d testpsql (12.3)Type "help" for help.test=# insert into test(id) select n from generate_series(101,100) n;INSERT 0 0test=# \q[postgres@node_206 /postgresql/pgsql]$pg_rman backup --backup-mode=incremental -B /postgresql/pgsql/pg_rman_backupsINFO: copying database filesINFO: copying archived WAL filesINFO: backup completeINFO: 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_backupsINFO: validate: "2020-11-17 23:49:59" backup and archive log files by CRCINFO: 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 OK2020-11-17 23:49:32 2020-11-17 23:49:35 FULL 64MB 6 OK2020-11-17 23:47:09 2020-11-17 23:47:12 INCR 33MB 6 OK2020-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 OK2020-11-17 23:49:32 2020-11-17 23:49:35 FULL 64MB 6 OK2020-11-17 23:47:09 2020-11-17 23:47:12 INCR 33MB 6 OK2020-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 unusableDETAIL: 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 8drwx------. 6 postgres postgres 62 Nov 17 23:49 20201117drwx------. 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_identifierdrwx------. 2 postgres postgres 6 Nov 17 23:45 timeline_history[postgres@node_206 /postgresql/pgsql]$ll pg_rman_backups/20201117/total 0drwx------. 2 postgres postgres 89 Nov 17 23:50 234622drwx------. 2 postgres postgres 89 Nov 17 23:50 234709drwx------. 5 postgres postgres 133 Nov 17 23:49 234932drwx------. 5 postgres postgres 133 Nov 17 23:50 234959[postgres@node_206 /postgresql/pgsql]$ll pg_rman_backups/20201117/234622/total