本次实验带有额外的表空间，注意后面的操作！

db3=# \db
            List of tablespaces
    Name    |  Owner   |      Location      
------------+----------+--------------------
 db3_space  | postgres | /data/pg/db3_space
 pg_default | postgres | 
 pg_global  | postgres | 
(3 rows)

db3=#

1）先插入一条数据做测试用，同时观察wal

db3=# truncate table user3.t1;

TRUNCATE TABLE

db3=# select * from user3.t1;

id | name

----+------

(0 rows)

db3=# insert into user3.t1 values(1,'before full backup');

INSERT 0 1

db3=# select * from pg_ls_waldir() order by 3;

name | size | modification

------------------------------------------+----------+------------------------

000000010000000000000028 | 16777216 | 2026-01-22 09:46:35+08

000000010000000000000029 | 16777216 | 2026-01-22 09:46:36+08

000000010000000000000025 | 16777216 | 2026-01-22 09:53:31+08

000000010000000000000026 | 16777216 | 2026-01-22 09:53:31+08

000000010000000000000026.00000028.backup | 398 | 2026-01-22 09:53:31+08

000000010000000000000027 | 16777216 | 2026-01-22 09:57:40+08

(6 rows)

db3=#

2）pg_basebackup全备

--full backup

[postgres@linux010 pg_backup]$ pg_basebackup -D /data/pg_backup/full -Ft -z -P

31590/31590 kB (100%), 2/2 tablespaces

[postgres@linux010 pg_backup]$

db3=# select * from pg_ls_waldir() order by 3;

name | size | modification

------------------------------------------+----------+------------------------

00000001000000000000002A | 16777216 | 2026-01-22 09:53:31+08

00000001000000000000002B | 16777216 | 2026-01-22 09:53:31+08

000000010000000000000027 | 16777216 | 2026-01-22 09:59:04+08

000000010000000000000028 | 16777216 | 2026-01-22 09:59:05+08

000000010000000000000028.00000028.backup | 341 | 2026-01-22 09:59:05+08

000000010000000000000029 | 16777216 | 2026-01-22 09:59:13+08

(6 rows)

db3=#

db3=# insert into user3.t1 values(2,'after full,before inc');

INSERT 0 1

db3=# select * from user3.t1;

id | name

----+-----------------------

1 | before full backup

2 | after full,before inc

(2 rows)

db3=# select * from pg_ls_waldir() order by 3;

name | size | modification

------------------------------------------+----------+------------------------

00000001000000000000002A | 16777216 | 2026-01-22 09:53:31+08

00000001000000000000002B | 16777216 | 2026-01-22 09:53:31+08

000000010000000000000027 | 16777216 | 2026-01-22 09:59:04+08

000000010000000000000028 | 16777216 | 2026-01-22 09:59:05+08

000000010000000000000028.00000028.backup | 341 | 2026-01-22 09:59:05+08

000000010000000000000029 | 16777216 | 2026-01-22 10:00:29+08

(6 rows)

db3=#

3）pg_basebackup增量备

--inc backup

--

-D /data/pg_backup/inc -i /data/pg_backup/full/backup_manifest -Ft -z -P

[postgres@linux010 pg_backup]$ pg_basebackup -D /data/pg_backup/inc -i /data/pg_backup/full/backup_manifest -Ft -z -P

4940/31578 kB (15%), 2/2 tablespaces

[postgres@linux010 pg_backup]$

db3=# select * from pg_ls_waldir() order by 3;

name | size | modification

------------------------------------------+----------+------------------------

00000001000000000000002C | 16777216 | 2026-01-22 09:59:04+08

000000010000000000000028 | 16777216 | 2026-01-22 09:59:05+08

000000010000000000000029 | 16777216 | 2026-01-22 10:02:10+08

00000001000000000000002A | 16777216 | 2026-01-22 10:02:10+08

00000001000000000000002A.00000028.backup | 398 | 2026-01-22 10:02:10+08

00000001000000000000002B | 16777216 | 2026-01-22 10:02:17+08

(6 rows)

db3=#

db3=# insert into user3.t1 values(3,'after inc backup');

INSERT 0 1

db3=# select * from user3.t1;

id | name

----+-----------------------

1 | before full backup

2 | after full,before inc

3 | after inc backup

(3 rows)

db3=# select * from pg_ls_waldir() order by 3;

name | size | modification

------------------------------------------+----------+------------------------

00000001000000000000002C | 16777216 | 2026-01-22 09:59:04+08

000000010000000000000028 | 16777216 | 2026-01-22 09:59:05+08

000000010000000000000029 | 16777216 | 2026-01-22 10:02:10+08

00000001000000000000002A | 16777216 | 2026-01-22 10:02:10+08

00000001000000000000002A.00000028.backup | 398 | 2026-01-22 10:02:10+08

00000001000000000000002B | 16777216 | 2026-01-22 10:03:48+08

(6 rows)

db3=#

[postgres@linux010 pg_backup]$ pwd

/data/pg_backup

[postgres@linux010 pg_backup]$ ls -ltrh

total 0

drwx------ 2 postgres postgres 89 Jan 22 09:59 full

drwx------ 2 postgres postgres 89 Jan 22 10:02 inc

[postgres@linux010 pg_backup]$

--

[postgres@linux010 pg_backup]$ pg_ctl stop

waiting for server to shut down.... done

server stopped

[postgres@linux010 pg_backup]$

4）还原前的准备

--停服务

[postgres@linux010 pg_backup]$ pg_ctl stop

waiting for server to shut down.... done

server stopped

[postgres@linux010 pg_backup]$

--备份一下原来的data目录及额外的表空间

mv data data_bak

mv /data/pg/db3_space /data/pg/db3_space_bak

--各自解压全备，增量备的base.tar.gz文件

tar xvf base.tar.gz

--解压额外表空间到新的目录

mkdir -p /data/pg_backup/tbs

cd /data/pg_backup/full

tar xvf 16441.tar.gz -C /data/pg_backup/full_ts16441

cd /data/pg_backup/inc

tar xvf 16441.tar.gz -C /data/pg_backup/inc_ts16441

--建立软链接

cd /data/pg_backup/full/pg_tblspc

ln -s /data/pg_backup/full_ts16441 16441

cd /data/pg_backup/inc/pg_tblspc

ln -s /data/pg_backup/inc_ts16441 16441

--运行pg_combinebackup

pg_combinebackup /data/pg_backup/full /data/pg_backup/inc -o /data/pg_backup/restored_db -d \

-T /data/pg_backup/inc_ts16441=/data/pg_backup/tbs

--运行完后会生成restored_db和tbs两个文件夹

--移动或复制文件夹

cp -rp restored_db ~/18/data

cp -rp tbs /data/pg/db3_space

cd ~/18/data

touch recovery.signal

--重建tablespace软链接

cd ~/18/data/pg_tblspc

rm 16441

ln -s /data/pg/db3_space 16441

--启动服务
[postgres@linux010 data]$ pg_ctl start
waiting for server to start....2026-01-22 11:01:43.904 CST [2439] LOG:  redirecting log output to logging collector process
2026-01-22 11:01:43.904 CST [2439] HINT:  Future log output will appear in directory "log".
. done
server started
[postgres@linux010 data]$

[postgres@linux010 archivedir]$ psql

psql (18.1)

Type "help" for help.

postgres=# \c db3

You are now connected to database "db3" as user "postgres".

db3=# select * from user3.t1;

id | name

----+-----------------------

1 | before full backup

2 | after full,before inc

3 | after inc backup

(3 rows)

db3=# select * from pg_ls_waldir() order by 3;

name | size | modification

--------------------------+----------+------------------------

00000001000000000000002A | 16777216 | 2026-01-22 11:01:45+08

00000001000000000000002B | 16777216 | 2026-01-22 11:01:45+08

00000002.history | 42 | 2026-01-22 11:01:45+08

00000002000000000000002C | 16777216 | 2026-01-22 11:02:39+08

(4 rows)

db3=#