pg_basebackup备份恢复(带自定义表空间)

环境:OS:Centos 7DB:13.8#####################################创建自定义表空间##############################1.创建表空间自定义目录su - postgresmkdir -p /opt/pg13/mytps2.创建表空间postgres# create tablespace hxltps location /opt/pg13/mytps;CREATE TABLESPACE这个时候在pg_tblspc目录有个符号链接[postgreshost134 pg_tblspc]$ pwd/opt/pg13/data/pg_tblspc[postgreshost134 pg_tblspc]$ ls -altotal 4drwx------ 2 postgres postgres 19 Oct 26 09:08 .drwx------ 19 postgres postgres 4096 Oct 26 00:00 ..lrwxrwxrwx 1 postgres postgres 15 Oct 26 09:08 32780 - /opt/pg13/mytps并将该表空间权限赋予特定用户postgres# GRANT CREATE ON TABLESPACE hxltps TO hxl;GRANT或是修改数据库的默认表空间CREATE DATABASE db_test owner hxl;ALTER DATABASE db_test SET TABLESPACE hxltps;4.登录并创建表[postgreshost134 pg_wal]$ psql -h 192.168.1.134 -U hxl -d db_test -p15432 db_test create table tb_mytps(i int,name varchar(32)) tablespace hxltps; CREATE TABLE insert into tb_mytps(i,name) values(1,name1); insert into tb_mytps(i,name) values(2,name2); insert into tb_mytps(i,name) values(3,name3); insert into tb_mytps(i,name) values(4,name4); insert into tb_mytps(i,name) values(5,name5); insert into tb_mytps(i,name) values(6,name6); insert into tb_mytps(i,name) values(7,name7); insert into tb_mytps(i,name) values(8,name8); insert into tb_mytps(i,name) values(9,name9); insert into tb_mytps(i,name) values(10,name10);5.查看已有的表空间postgres# select * from pg_tablespace; oid | spcname | spcowner | spcacl | spcoptions ------------------------------------------------------------------------------- 1663 | pg_default | 10 | | 1664 | pg_global | 10 | | 32780 | hxltps | 10 | {postgresC/postgres,hxlC/postgres} | (3 rows) 查看表空间对应的路径 postgres# \db List of tablespaces Name | Owner | Location | Access privileges | Options | Size | Description ------------------------------------------------------------------------------------------- hxltps | postgres | /opt/pg13/mytps | postgresC/postgres| | 0 bytes | | | | hxlC/postgres | | | pg_default | postgres | | | | 31 MB | pg_global | postgres | | | | 559 kB | (3 rows)#####################################备份数据库#######################################su - postgres[postgreshost134 pg_backup]$ mkdir -p /tmp/pg_backup/mybak[postgreshost134 pg_backup]$ pg_basebackup -D /tmp/pg_backup/mybak -Ft -Pv -U postgres -h 192.168.1.134 -p15432查看备份文件[postgreshost134 mybak]$ ls -altotal 48824drwxrwxr-x 2 postgres postgres 80 Oct 26 09:34 .drwxrwxr-x 4 postgres postgres 34 Oct 26 09:33 ..-rw------- 1 postgres postgres 10752 Oct 26 09:34 32780.tar-rw------- 1 postgres postgres 178483 Oct 26 09:34 backup_manifest-rw------- 1 postgres postgres 33021952 Oct 26 09:34 base.tar-rw------- 1 postgres postgres 16780288 Oct 26 09:34 pg_wal.tar可以看到这里多出了一个压缩包文件32780.tar,该文件就是表空间对应的备份文件查看里面的内容:[postgreshost134 mybak]$ tar -tvf 32780.tardrwx------ postgres/postgres 0 2022-10-26 09:18 PG_13_202007201/drwx------ postgres/postgres 0 2022-10-26 09:18 PG_13_202007201/16385/-rw------- postgres/postgres 8192 2022-10-26 09:34 PG_13_202007201/16385/32781说明:解压base.tar包会有该文件tablespace_map,文件内容如下:[postgreshost134 data]$ more tablespace_map32780 /opt/pg13/mytps#########################################恢复##########################################1.停掉当前数据库su - postgres[postgreshost134 pg_backup]$ pg_ctl -D /opt/pg13/data -l /opt/pg13/log/postgres.log stop -m fastwaiting for server to shut down.... doneserver stopped2.删除data目录和归档目录,以及表空间目录[postgreshost134 pg13]$ cd /opt/pg13[postgreshost134 pg13]$ mv data bakdata[postgreshost134 pg13]$ mv archivelog bakarchivelog[postgreshost134 pg13]$ mv mytps bakmytps[postgreshost134 pg13]$ mkdir data[postgreshost134 pg13]$ mkdir archivelog[postgreshost134 pg13]$ mkdir mytps ##这里的前提是需要之前表空间所在的目录3.解压压缩包到相应目录解压备份包到相应的目录[postgreshost134 pg_backup]$cd /tmp/pg_backup/mybak[postgreshost134 pg_backup]$tar -xvf base.tar -C /opt/pg13/data ##解压数据目录[postgreshost134 pg_backup]$tar -xvf pg_wal.tar -C /opt/pg13/archivelog ##解压到归档目录[postgreshost134 pg_backup]$tar -xvf 32780.tar -C /opt/pg13/mytps ##解压表空间解压发现pg_tblspc目录是没有任何内容的[postgreshost134 pg_tblspc]$ pwd/opt/pg13/data/pg_tblspc[postgreshost134 pg_tblspc]$ ls4.修改配置文件vi /opt/pg13/data/postgresql.conf 修改如下参数restore_command cp /opt/pg13/archivelog/%f %p##下面这两个参数也是正确的##restore_command cp /opt/pg13/archivelog/%f %p##restore_command cp /opt/pg13/archivelog/%f /opt/pg13/data/pg_wal/##recovery_target immediate ##该选择只能恢复到备份的时间点,无法使用备份后生成的walrecovery_target_timeline latest##这里我们使用timeline恢复到最近的wal,因为备份的时候还会产生wal,若使用recovery_target immediate##就无法使用到备份之后产生的wal日志,达不到恢复到最近wal的目的5.生成recovery.signal标识文件[postgreshost134 data]$ cd /opt/pg13/data[postgreshost134 data]$ touch recovery.signal6.修改权限su - root[roothost134 ~]# chmod 0700 /opt/pg13/data7.将备份后产生的wal日志拷贝到归档目录(目的是恢复到最近的时间点,不拷贝的话只能恢复到备份的时间点)拷贝备份后的归档日志cp /opt/pg13/bakarchivelog/20221025/00000004000000000000001B /opt/pg13/archivelog/cp /opt/pg13/bakarchivelog/20221025/00000004000000000000001C /opt/pg13/archivelog/cp /opt/pg13/bakarchivelog/20221025/00000004000000000000001D /opt/pg13/archivelog/cp /opt/pg13/bakarchivelog/20221025/00000004000000000000001E /opt/pg13/archivelog/拷贝备份后的wal日志cp /opt/pg13/bakdata/pg_wal/00000004000000000000001F /opt/pg13/archivelog/cp /opt/pg13/bakdata/pg_wal/000000040000000000000020 /opt/pg13/archivelog/cp /opt/pg13/bakdata/pg_wal/000000040000000000000021 /opt/pg13/archivelog/cp /opt/pg13/bakdata/pg_wal/000000040000000000000022 /opt/pg13/archivelog/cp /opt/pg13/bakdata/pg_wal/000000040000000000000023 /opt/pg13/archivelog/cp /opt/pg13/bakdata/pg_wal/000000040000000000000024 /opt/pg13/archivelog/cp /opt/pg13/bakdata/pg_wal/000000040000000000000025 /opt/pg13/archivelog/8.启动数据库[postgreshost134 data]$pg_ctl -D /opt/pg13/data -l /opt/pg13/log/postgres.log start9.检查表空间文件情况[postgreshost134 pg_tblspc]$ cd /opt/pg13/data/pg_tblspc[postgreshost134 pg_tblspc]$ ls -altotal 4drwx------ 2 postgres postgres 19 Oct 26 10:25 .drwx------ 19 postgres postgres 4096 Oct 26 10:25 ..lrwxrwxrwx 1 postgres postgres 15 Oct 26 10:25 32780 - /opt/pg13/mytps这个时候可以看到pg_tblspc目录下自动创建了指向表空间目录的软链接10.数据验证