一、逻辑备份与恢复

pg_dump -d test >/tmp/test.sql -p 5433   --备份test这个数据库

psql  -p 5433 </tmp/test.sql   --恢复

pg_dump > /tmp/full.sql -p 5433  --备份整库

psql </tmp/full.sql  -p 5433  --恢复

二、物理备份与恢复

1.开启归档  （postgresql.conf文件配置）

wal_level = replica

archive_mode = on  

archive_command = 'test ! -f /var/postgre/archive/%f && cp %p /var/postgre/archive/%f'

（/var/postgre/archive目录是存放归档日志的路径，需要提前建好）

2.备份

pg_basebackup -D /var/postgre/backup/ -Ft -Pv -U postgres -p 5432 -R 或

pg_basebackup -D /var/postgre/backup/ -Ft -Pv   （简单的备份语句）

-D, --pgdata=DIRECTORY receive base backup into directory
-F, --format=p|t       output format (plain (default), tar)
-P, --progress         show progress information
-v, --verbose          output verbose messages
-U, --username=NAME    connect as specified database user
-h, --host=HOSTNAME    database server host or socket directory
-p, --port=PORT        database server port number
 -R, --write-recovery-conf
                         write configuration for replication

3.关闭数据库，删除/var/postgre/data（$PGDATA）下文件

4.恢复

（1）postgresql.conf文件配置

restore_command = 'cp /var/postgre/archive/%f %p'
recovery_target = 'immediate'

（2）在/var/postgre/data（$PGDATA）文件下新建recovery.signal文件

（3）备份文件解压

tar -xvf  base.tar  -C  /var/postgre/data

tar -xvf pg_wal.tar -c /var/postgre/archive

（4）启动数据库

pg_ctl start -D $PGDATA

此时的数据库是只读的数据库

可以用pg_controldata命令查看数据库簇状态

可以用pg_ctl promote切换数据库状态为读写状态 或者执行select pg_wal_replay_resume();

补充：创建恢复点

test=# select pg_create_restore_point('fll20221214');
2022-12-14 17:06:12.915 CST [32208] LOG:  restore point "fll20221214" created at 0/8019070
2022-12-14 17:06:12.915 CST [32208] STATEMENT:  select pg_create_restore_point('fll20221214');
 pg_create_restore_point
-------------------------
 0/8019070
(1 row)

test=#

对应的参数是配置文件中的recovery_target_name = ''

三、物理备份举例

1.备份
[postgres@v-vlxsz-devdb01 backup]$ pg_basebackup -D /var/postgre/backup/ -Ft -Pv
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/7000028 on timeline 2
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_30716"
26242/26242 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/7000100
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed
[postgres@v-vlxsz-devdb01 backup]$ ls -al
total 42768
drwxrwxr-x 2 postgres postgres       63 Dec 14 16:25 .
drwxrwxr-x 8 postgres postgres      113 Dec 14 16:24 ..
-rw------- 1 postgres postgres   137781 Dec 14 16:25 backup_manifest
-rw------- 1 postgres postgres 26872832 Dec 14 16:25 base.tar
-rw------- 1 postgres postgres 16780288 Dec 14 16:25 pg_wal.tar
[postgres@v-vlxsz-devdb01 backup]$

[postgres@v-vlxsz-devdb01 postgre]$ cd archive
[postgres@v-vlxsz-devdb01 archive]$ ls -al
total 65540
drwxrwxr-x 2 postgres postgres      182 Dec 14 16:25 .
drwxrwxr-x 8 postgres postgres      113 Dec 14 16:24 ..
-rw------- 1 postgres postgres 16777216 Dec 14 16:25 000000020000000000000004
-rw------- 1 postgres postgres 16777216 Dec 14 16:25 000000020000000000000005
-rw------- 1 postgres postgres 16777216 Dec 14 16:25 000000020000000000000006
-rw------- 1 postgres postgres 16777216 Dec 14 16:25 000000020000000000000007
-rw------- 1 postgres postgres      338 Dec 14 16:25 000000020000000000000007.00000028.backup
[postgres@v-vlxsz-devdb01 archive]$

2.创建test的数据库，在test数据库中创建t1表，删除test数据库

[postgres@v-vlxsz-devdb01 archive]$ psql
psql (14.1)
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 t1 (a int);
CREATE TABLE
test=# insert into t1 values(1);
INSERT 0 1
test=# select * from t1;
 a
---
 1
(1 row)

[postgres@v-vlxsz-devdb01 archive]$ psql
psql (14.1)
Type "help" for help.

postgres=# drop database test;
DROP DATABASE
postgres=#

3.切换日志

[postgres@v-vlxsz-devdb01 archive]$ psql
psql (14.1)
Type "help" for help.

postgres=# select pg_switch_wal();
 pg_switch_wal
---------------
 0/8019618
(1 row)

postgres=# exit
[postgres@v-vlxsz-devdb01 archive]$ ls -al
total 81924
drwxrwxr-x 2 postgres postgres      214 Dec 14 16:34 .
drwxrwxr-x 8 postgres postgres      113 Dec 14 16:24 ..
-rw------- 1 postgres postgres 16777216 Dec 14 16:25 000000020000000000000004
-rw------- 1 postgres postgres 16777216 Dec 14 16:25 000000020000000000000005
-rw------- 1 postgres postgres 16777216 Dec 14 16:25 000000020000000000000006
-rw------- 1 postgres postgres 16777216 Dec 14 16:25 000000020000000000000007
-rw------- 1 postgres postgres      338 Dec 14 16:25 000000020000000000000007.00000028.backup
-rw------- 1 postgres postgres 16777216 Dec 14 16:34 000000020000000000000008
[postgres@v-vlxsz-devdb01 archive]$

4.需求：恢复到test数据库删除前。

wal日志备份的时候是000000020000000000000007这个日志，故创建和删除test数据库的动作应该记录在000000020000000000000008日志中。用pg_waldump 000000020000000000000008分析wal日志。

drop dir的动作是在736这个事务中，故应该恢复在735这个事务中。

5.具体恢复过程

$ pg_ctl stop关闭数据库

$ tar -xvf base.tar  -C /var/postgre/data/

$ tar -xvf pg_wal.tar -C /var/postgre/archive

$ vi postgresql.conf

restore_command = 'cp /var/postgre/archive/%f %p' 

recovery_target_xid = '735'

$ touch recovery.signal

 select  pg_wal_replay_resume();  --将数据库设置为只读