PostgreSQL pg_rman使用 PostgreSQL物理备份恢复之 pg_rman的用法说明
岳麓丹枫001 人气:0想了解PostgreSQL物理备份恢复之 pg_rman的用法说明的相关内容吗，岳麓丹枫001在本文为您仔细讲解PostgreSQL pg_rman使用的相关知识和一些Code实例，欢迎阅读和指正，我们先划重点：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
以上为个人经验，希望能给大家一个参考，也希望大家多多支持。如有错误或未考虑完全的地方，望不吝赐教。
加载全部内容