1.pgsql笔记

模式 Schema
PostgreSQL 模式SCHEMA 可以看做是一个表的集合。一个模式可以包含视图，索引，数据类型，函数操作符等。

相同的对象名称可以被用于不同的模式中而不会出现冲突，例如schema1和 myschema都可以包含名为mytable的表
使用模式的优势：
允许多个用户使用同一个数据库并且不会互相干扰。
将数据库对象组织成逻辑组以便更容易管理。
第三方应用的对象可以放在独立的模式中，这样他们就不会与其他的对象的名称发生冲突。
模式类似于操作系统层的目录，但是模式不能嵌套。更多关于大数据库PostrageSQL

pgsql备份工具

image.png

物理备份
冷备冷恢复（第一种物理备份方式）
此种方式是直接备份数据库物理文件，在pg数据库里，这通常指的是PGDATA变量定义的文件夹，例如：

[root@EULEER ~]# echo $PGDATA/usr/local/pgsql/data

我的这个实例库安装在了/usr/local/pgsql 目录下，那么，备份如下，文件备份到/media目录下了：

cp -r /usr/local/pgsql/data /media/

但是需要注意的是，此种方式由于比较直接，不管是否数据库有无IO的情况，因此，备份的时候需要先停止数据库，恢复的时候要先删除原数据库文件，重新覆盖回去后，才可以启动数据库，如果在数据库启动的时候备份，那么，可能会造成数据备份不全，也可以了解为冷备份。

物理备份的恢复

[root@EULEER ~]# rm -rf /usr/local/pgsql/data/*
[root@EULEER ~]# cp -r /media/data/* /usr/local/pgsql/data/
[root@EULEER ~]# bash start-pgsql.sh 
waiting for server to start....2023-03-09 08:18:58.143 CST [23561] LOG:  starting PostgreSQL 12.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (EulerOS 4.8.5-28), 64-bit
2023-03-09 08:18:58.144 CST [23561] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2023-03-09 08:18:58.144 CST [23561] LOG:  listening on IPv6 address "::", port 5432
2023-03-09 08:18:58.148 CST [23561] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2023-03-09 08:18:58.224 CST [23562] LOG:  database system was shut down at 2023-03-09 08:04:04 CST
2023-03-09 08:18:58.232 CST [23561] LOG:  database system is ready to accept connections
 done
server started

那么此种方式比较霸道，因此优点是简单，直接，高效，缺点是需要停机维护，不能时时备份。。另一个缺点 是，一般生产环境的数据库由于经年累月的使用，数据库一般还会比较大，而cp命令的效率和文件大小是有关联的，受限于此，这时候的备份和恢复是比较困难的。

热备冷恢复（第二种物理备份方式）
pg_basebackup 基本原理和步骤

pg_basebackup 也算是物理方式，是可以使用流协议。并且热备范畴，备份的时候不需要停数据库，但恢复的时候需要停数据库（备注，某些项目的数据库是24小时不能停的，如果停了，可能接受不了，因此备份前最好搞清楚自己是否能够接受冷备份）。

备份：使用pg_basebackup 命令进行备份，这个命令可以将postragesql的数据文件备份为2个压缩文件：base.tar 和pg_wal.tar 。分别是数据文件和归档文件。恢复的时候，需要设置按照归档文件来恢复。那么，此种方式的备份可以备份自定义表空间。

恢复： 需要先把备份的压缩文件替换当前的数据文件，然后修改postgresql.conf。因为这个配置文件在data文件夹中，所以只能是在把base.tar解压到当前库当前数据位置，也就是我们默认初始化指定的数据保存位置data文件夹中，才能修改配置，在配置好难归档以后，可以启动pgsql服务，进行启动恢复。

在恢复过程中，会拷贝归档文件，进行数据恢复。

恢复成功，也就是数据库服务启动成功。这个时候我们访问数据库，他是作为归档状态存在的，所以只能读，不能写操作。

为了恢复数据库写操作，我们需要在命令下执行切换数据库状态的指令，切换成功后，才可以进行读写操作。
实操实例：

环境介绍：数据库主节点，IP：192.168.123.60，数据库端口：5432，数据库安装路径为/usr/local/pgsql ，管理用户为pg1。

创建一个自定义表空间，创建表空间存放路径。

[root@EULER1 ~]# mkdir /opt/custome-tablespace
[root@EULER1 ~]# chown -Rf pg1. /opt/custome-tablespace/

登陆命令行，创建表空间

[root@EULER1 ~]# su - pg1 -c "psql -Upostgres -p 5432 -h 192.168.123.60"
Password for user postgres: 
psql (12.5)
Type "help" for help.
 
postgres=# create tablespace mytbs location '/opt/custome-tablespace';
CREATE TABLESPACE
postgres=# \db+
                                          List of tablespaces
    Name    |  Owner   |        Location         | Access privileges | Options |  Size   | Description 
------------+----------+-------------------------+-------------------+---------+---------+-------------
 mytbs      | postgres | /opt/custome-tablespace |                   |         | 0 bytes | 
 pg_default | pg1      |                         |                   |         | 23 MB   | 
 pg_global  | pg1      |                         |                   |         | 623 kB  | 
(3 rows)

此时的$PGDATA目录下的pg_tblspc目录下有一个软链接：

[root@EULER1 ~]# ls -al /usr/local/pgsql/data/pg_tblspc/
total 8
drwx------.  2 pg1 pg1 4096 Mar 19 20:06 .
drwx------. 20 pg1 pg1 4096 Mar 19 18:39 ..
lrwxrwxrwx   1 pg1 pg1   23 Mar 19 20:06 32771 -> /opt/custome-tablespace

创建一个新库和新表在此表空间内：

postgres=# create database test;
CREATE DATABASE
postgres=# alter database test set tablespace mytbs;
ALTER DATABASE
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# create table tb_mytps(i int,name varchar(32)) tablespace mytbs;
CREATE TABLE

插入实验数据

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');

查看是否正确插入数据:

postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# select * from tb_mytps ;
 i  |  name  
----+--------
  1 | name1
  2 | name2
  3 | name3
  4 | name4
  5 | name5
  6 | name6
  7 | name7
  8 | name8
  9 | name9
 10 | name10
(10 rows)

开始备份
建立备份文件存放路径

[root@EULER1 ~]# mkdir /opt/backup
[root@EULER1 ~]# chown -Rf pg1. /opt/backup