环境:
OS:Centos 7
DB:13.8
#####################################创建自定义表空间##############################
1.创建表空间自定义目录
su - postgres
mkdir -p /opt/pg13/mytps
2.创建表空间
postgres=# create tablespace hxltps location '/opt/pg13/mytps';
CREATE TABLESPACE
这个时候在pg_tblspc目录有个符号链接
[postgres@host134 pg_tblspc]$ pwd
/opt/pg13/data/pg_tblspc
[postgres@host134 pg_tblspc]$ ls -al
total 4
drwx------ 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.登录并创建表
[postgres@host134 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 | {postgres=C/postgres,hxl=C/postgres} |
(3 rows)
查看表空间对应的路径
postgres=# \db+
List of tablespaces
Name | Owner | Location | Access privileges | Options | Size | Description
------------+----------+-----------------+---------------------+---------+---------+-------------
hxltps | postgres | /opt/pg13/mytps | postgres=C/postgres+| | 0 bytes |
| | | hxl=C/postgres | | |
pg_default | postgres | | | | 31 MB |
pg_global | postgres | | | | 559 kB |
(3 rows)
#####################################备份数据库#######################################
su - postgres
[postgres@host134 pg_backup]$ mkdir -p /tmp/pg_backup/mybak
[postgres@host134 pg_backup]$ pg_basebackup -D /tmp/pg_backup/mybak -Ft -Pv -U postgres -h 192.168.1.134 -p15432
查看备份文件
[postgres@host134 mybak]$ ls -al
total 48824
drwxrwxr-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,该文件就是表空间对应的备份文件
查看里面的内容:
[postgres@host134 mybak]$ tar -tvf 32780.tar
drwx------ 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,文件内容如下:
[postgres@host134 data]$ more tablespace_map
32780 /opt/pg13/mytps
#########################################恢复##########################################
1.停掉当前数据库
su - postgres
[postgres@host134 pg_backup]$ pg_ctl -D /opt/pg13/data -l /opt/pg13/log/postgres.log stop -m fast
waiting for server to shut down.... done
server stopped
2.删除data目录和归档目录,以及表空间目录
[postgres@host134 pg13]$ cd /opt/pg13
[postgres@host134 pg13]$ mv data bakdata
[postgres@host134 pg13]$ mv archivelog bakarchivelog
[postgres@host134 pg13]$ mv mytps bakmytps
[postgres@host134 pg13]$ mkdir data
[postgres@host134 pg13]$ mkdir archivelog
[postgres@host134 pg13]$ mkdir mytps ##这里的前提是需要之前表空间所在的目录
3.解压压缩包到相应目录
解压备份包到相应的目录
[postgres@host134 pg_backup]$cd /tmp/pg_backup/mybak
[postgres@host134 pg_backup]$tar -xvf base.tar -C /opt/pg13/data ##解压数据目录
[postgres@host134 pg_backup]$tar -xvf pg_wal.tar -C /opt/pg13/archivelog ##解压到归档目录
[postgres@host134 pg_backup]$tar -xvf 32780.tar -C /opt/pg13/mytps ##解压表空间
解压发现pg_tblspc目录是没有任何内容的
[postgres@host134 pg_tblspc]$ pwd
/opt/pg13/data/pg_tblspc
[postgres@host134 pg_tblspc]$ ls
4.修改配置文件
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' ##该选择只能恢复到备份的时间点,无法使用备份后生成的wal
recovery_target_timeline = 'latest'
##这里我们使用timeline恢复到最近的wal,因为备份的时候还会产生wal,若使用recovery_target = 'immediate'
##就无法使用到备份之后产生的wal日志,达不到恢复到最近wal的目的
5.生成recovery.signal标识文件
[postgres@host134 data]$ cd /opt/pg13/data
[postgres@host134 data]$ touch recovery.signal
6.修改权限
su - root
[root@host134 ~]# chmod 0700 /opt/pg13/data
7.将备份后产生的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.启动数据库
[postgres@host134 data]$pg_ctl -D /opt/pg13/data -l /opt/pg13/log/postgres.log start
9.检查表空间文件情况
[postgres@host134 pg_tblspc]$ cd /opt/pg13/data/pg_tblspc
[postgres@host134 pg_tblspc]$ ls -al
total 4
drwx------ 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.数据验证
标签：opt,postgres,自定义,pg13,host134,pg,mytps,basebackup From： https://www.cnblogs.com/hxlasky/p/16829284.html