前言：
postgresql数据库说实话是真心好用，但，想用好是比较困难的。
那么，造成该数据库使用困难的是它的内置工具非常的多，并且整体优化是比较难的。
比如，自带的备份工具pg_basebackup，pg_dump，pg_restore，而这些工具还需要配合WAL日志，当然，不使用归档日志WAL也可以满足一些备份需求，但对于数据的保护力度就可能不是太精确了。
OK，pg_basebackup这个postgresql自带的工具不仅仅是做备份，还可以利用此工具搭建简单的主从复制。
这里需要简单解释一下，主从复制的postgresql，主库可读可写，从库只读不可写。因为，整体逻辑架构是从库通过读取主库的WAL归档文件来进行流复制的，因此，从库只读不可写，否则将会造成数据错乱。
下面就基于pg_basebackup的主从复制部署和pg_basebackup的备份做一个简单的介绍。
一，
postgresql的一些主要配置文件和主要目录
1，
postgresql里的重要目录
一般情况下，我们会定义一个系统的全局变量$PGDATA ，它的值就是postgresql数据库存放数据文件的目录。其目录下有两个比较重要的子目录
pg_wal 目录
存放的是归档文件和归档状态，例如，我已经部署好的一个单实例postgresql：
[root@k8s-node2 ~]# echo $PGDATA /usr/local/pgsql/data [root@k8s-node2 ~]# ls -al $PGDATA/pg_wal/ total 49156 drwx------ 3 postgres postgres 124 Mar 18 21:22 . drwx------ 19 postgres postgres 4096 Mar 19 01:04 .. -rw------- 1 postgres postgres 16777216 Mar 19 01:10 000000010000000000000005 -rw------- 1 postgres postgres 16777216 Mar 18 18:59 000000010000000000000006 -rw------- 1 postgres postgres 16777216 Mar 18 18:59 000000010000000000000007 drwx------ 2 postgres postgres 6 Mar 18 21:22 archive_status [root@k8s-node2 ~]# file $PGDATA/pg_wal/000000010000000000000005 /usr/local/pgsql/data/pg_wal/000000010000000000000005: data
这些文件不是文本文件，类型是data，因此，不能使用cat，vim等命令查看，而是通过pg_waldump 这个小工具来查看的，文件名称，文件内容等等这些就不在这里说了，因为如果要想讲清楚的话，内容是比较多得，并且和主从部署关系不是太紧密。
base目录和global目录
存放的是基础tablespace的物理文件，等价于MySQL数据库的基础库mysql的物理文件，比如，13590这个文件夹里存放的是某一个库的物理文件
[root@k8s-node2 global]# cd ../base/ [root@k8s-node2 base]# ll total 84 drwx------ 2 postgres postgres 8192 Mar 18 18:24 1 drwx------ 2 postgres postgres 8192 Mar 18 18:24 13590 drwx------ 2 postgres postgres 8192 Mar 19 01:04 13591 drwx------ 2 postgres postgres 8192 Mar 18 18:24 16384 drwx------ 2 postgres postgres 8192 Mar 18 18:24 24576 drwx------ 2 postgres postgres 8192 Mar 18 21:22 32768 drwx------ 2 postgres postgres 8192 Mar 19 01:05 32769
比如，进入13590，可以看到N个文件，这些文件就是数据库下的函数，表，索引等等文件，这些文件和WAL归档文件一样，是data格式的，但需要逆向工程工具查看，普通的文本程序无法查看。
[root@k8s-node2 base]# ls 13590/ Display all 302 possibilities? (y or n) 112 13436 1418 2605_vm 2616 2661 2689 2836 3350 3503 3603_vm 4153 549 113 13436_fsm 174 2606 2616_fsm 2662 2690 2837 3351 3534 3604 4154 6102 1247 13436_vm 175 2606_fsm 2616_vm 2663 2691 2838 3379 3541 3605 4155 6104 1247_fsm 13438 2187 2606_vm 2617 2664 2692 2838_fsm 3380 3541_fsm 3606 4156 6106
命令行查看表空间，和这两个文件夹是对应的：
postgres=# \db+ List of tablespaces Name | Owner | Location | Access privileges | Options | Size | Description ------------+-------+----------+-------------------+---------+--------+------------- pg_default | pg1 | | | | 54 MB | pg_global | pg1 | | | | 623 kB | [root@k8s-node2 base]# pwd /usr/local/pgsql/data/base [root@k8s-node2 base]# du -sh ./ 54M ./
2，
配置文件
比较重要的是三个，第一个是postgresql.conf
该文件内容比较多，主要是配置数据库的动作，例如，远程连接，WAL日志动作，vacuum动作，log日志动作等等。
第二个是postgre.auto.conf ，该文件是定义一些附加功能，本文中的主从复制就是由该文件定义，但此文件不推荐手动编写，一般是通过命令搭配参数的形式定义，并且该文件配置的等级高于postgresql.conf 。比如，在命令行执行SQL语句：
alter system set max_wal_size=2500;
此时，打开postgresql.auto.conf 文件将会看到写入了配置：
[root@EULER1 pg_wal]# cat ../postgresql.auto.conf # Do not edit this file manually! # It will be overwritten by the ALTER SYSTEM command. max_wal_size = '2500'
第三个就是pg_hba.conf ，该文件主要是安全方面的定义，例如，远程连接是否输入密码，哪些ip可以连接该数据库服务等等内容，在此就不介绍了。
其它可能需要关注的文件就是标识文件了，例如，standby.signal
二，
postgresql的主从流复制搭建和测试
【1】
环境介绍
两台VMware虚拟机
第一台服务器IP地址：192.168.123.60（计划是主节点）操作系统centos7或者其它类似操作系统，比如，欧拉。2c 4g
第二台服务器IP地址：192.168.123.13（计划是从节点）操作系统centos7或者其它类似操作系统，比如，欧拉。2c 4g
【2】
安装介质
使用的是在欧拉服务器上编译的postgresql-12.5，等于是二进制安装包
链接：https://pan.baidu.com/s/19Fgdo8fBaz9K64db_REw4g?pwd=pgsq
提取码：pgsq
--来自百度网盘超级会员V5的分享
【3】
以在192.168.123.60上安装为例
解压以上的百度网盘压缩包，将解压的文件放置到 /usr/local/目录下：
新建一个普通用户，我这里是使用pg1，当然，随便一个普通用户都可以，并将以上目录赋予该普通用户：
[root@k8s-node1 ~]# tar zxf pgsql-12.4-bianry.tar.gz [root@k8s-node1 ~]# du -sh pgsql/ 211M pgsql/ [root@k8s-node1 ~]# mv pgsql /usr/local/ [root@k8s-node1 ~]# useradd pg1 [root@k8s-node1 ~]# chown -Rf pg1. /usr/local/pgsql/
[root@EULER1 pg_twophase]# ls -al /usr/local/pgsql/ total 1264 drwxrwxrwx. 11 pg1 pg1 4096 Mar 18 06:41 . drwxr-xr-x. 13 root root 4096 Mar 18 07:06 .. -rw-r--r--. 1 pg1 pg1 522 Nov 10 2020 aclocal.m4 drwx------. 2 pg1 pg1 4096 Mar 9 00:54 bin drwxrwxrwx. 2 pg1 pg1 4096 Nov 10 2020 config -rw-------. 1 pg1 pg1 434222 Mar 9 00:42 config.log -rwx------. 1 pg1 pg1 40061 Mar 9 00:42 config.status -rwxr-xr-x. 1 pg1 pg1 575329 Nov 10 2020 configure -rw-r--r--. 1 pg1 pg1 84108 Nov 10 2020 configure.in drwxrwxrwx. 56 pg1 pg1 4096 Nov 10 2020 contrib -rw-r--r--. 1 pg1 pg1 1192 Nov 10 2020 COPYRIGHT drwx------. 20 pg1 pg1 4096 Mar 19 03:41 data -rw-r--r--. 1 pg1 pg1 730 Nov 10 2020 .dir-locals.el drwxrwxrwx. 3 pg1 pg1 4096 Nov 10 2020 doc -rw-r--r--. 1 pg1 pg1 1547 Nov 10 2020 .gitattributes -rw-r--r--. 1 pg1 pg1 504 Nov 10 2020 .gitignore -rw-------. 1 pg1 pg1 3998 Mar 9 00:42 GNUmakefile -rw-r--r--. 1 pg1 pg1 3998 Nov 10 2020 GNUmakefile.in -rw-r--r--. 1 pg1 pg1 284 Nov 10 2020 HISTORY drwx------. 6 pg1 pg1 4096 Mar 9 00:54 include -rw-r--r--. 1 pg1 pg1 61479 Nov 10 2020 INSTALL drwx------. 4 pg1 pg1 4096 Mar 9 00:54 lib -rw-r--r--. 1 pg1 pg1 1665 Nov 10 2020 Makefile -rw-r--r--. 1 pg1 pg1 1212 Nov 10 2020 README drwx------. 6 pg1 pg1 4096 Mar 9 00:54 share drwxrwxrwx. 16 pg1 pg1 4096 Mar 9 00:42 src
添加环境变量/etc/profile：
export PGDATA=/usr/local/pgsql/data export PGDATA PGHOME=/usr/local/pgsql export PGHOME PATH=$PATH:$PGHOME/bin:$PGDATA export PATH PGHOME PGDATA
新建数据库的物理文件存放文件，并赋权普通用户：
[root@k8s-node1 pgsql]# mkdir /usr/local/pgsql/data [root@k8s-node1 pgsql]# chown -Rf pg1. /usr/local/pgsql/ [root@k8s-node1 pgsql]# source /etc/profile
初始化数据库：
[root@k8s-node1 pgsql]# su - pg1 -c "initdb -D /usr/local/pgsql/data" The files belonging to this database system will be owned by user "pg1". This user must also own the server process. The database cluster will be initialized with locale "en_US.UTF-8". The default database encoding has accordingly been set to "UTF8". The default text search configuration will be set to "english". Data page checksums are disabled. fixing permissions on existing directory /usr/local/pgsql/data ... ok creating subdirectories ... ok selecting dynamic shared memory implementation ... posix selecting default max_connections ... 100 selecting default shared_buffers ... 128MB selecting default time zone ... PRC creating configuration files ... ok running bootstrap script ... ok performing post-bootstrap initialization ... ok syncing data to disk ... ok initdb: warning: enabling "trust" authentication for local connections You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb. Success. You can now start the database server using: pg_ctl -D /usr/local/pgsql/data -l logfile start
启动数据库，并查看进程：
[root@k8s-node1 pgsql]# su - pg1 -c "pg_ctl -D /usr/local/pgsql/data -l logfile start" waiting for server to start.... done server started [root@k8s-node1 pgsql]# ps -ef |grep postgres pg1 6379 1 0 04:07 ? 00:00:00 /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data pg1 6381 6379 0 04:07 ? 00:00:00 postgres: checkpointer pg1 6382 6379 0 04:07 ? 00:00:00 postgres: background writer pg1 6383 6379 0 04:07 ? 00:00:00 postgres: walwriter pg1 6384 6379 0 04:07 ? 00:00:00 postgres: autovacuum launcher pg1 6385 6379 0 04:07 ? 00:00:00 postgres: stats collector pg1 6386 6379 0 04:07 ? 00:00:00 postgres: logical replication launcher root 6861 5755 0 04:09 pts/0 00:00:00 grep --color=auto postgres
【4】
配置远程连接，添加postgres用户，给postgres用户分分配权限：
这里由于没有使用常用的postgres用户作为数据库的管理用户而是pg1，因此，需要先建立一个名称为pg1的临时数据库，等配置完了在删除这个数据库
[root@k8s-node1 pgsql]# createdb -h localhost -Upg1 pg1 [root@k8s-node1 pgsql]# su - pg1 -c "psql -Upg1 -p 5432 -h localhost" psql (12.5) Type "help" for help. pg1=#
新建用户postgres，赋予该用户超级管理权限 ，最后查看目前数据库内的用户权限
[root@k8s-node1 pgsql]# su - pg1 -c "psql -Upg1 -p 5432 -h localhost" psql (12.5) Type "help" for help. pg1=# create role postgres; CREATE ROLE pg1=# alter role postgres superuser; ALTER ROLE pg1=# alter role postgres with passworddfdsf '要设定的密码';###沙雕CSDN不让文章内出现passd字样 ALTER ROLE pg1=# alter role postgres login; ALTER ROLE pg1=# \dg+ List of roles Role name | Attributes | Member of | Description -----------+------------------------------------------------------------+-----------+------------- pg1 | Superuser, Create role, Create DB, Replication, Bypass RLS | {} | postgres | Superuser | {} |
删除前面创建的pg1数据库，并确认删除成功。
[root@k8s-node1 pgsql]# su - pg1 -c "psql -Upostgres -p 5432 -h localhost" psql (12.5) Type "help" for help. postgres=# drop database pg1; DROP DATABASE postgres=# \l+ List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description -----------+-------+----------+-------------+-------------+-------------------+---------+------------+-------------------------------------------- postgres | pg1 | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7953 kB | pg_default | default administrative connection database template0 | pg1 | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/pg1 +| 7809 kB | pg_default | unmodifiable empty database | | | | | pg1=CTc/pg1 | | | template1 | pg1 | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/pg1 +| 7809 kB | pg_default | default template for new databases | | | | | pg1=CTc/pg1 | | | (3 rows)
后面的远程连接权限，也就是pg_hba.conf 这些就不在这废话了。同样的操作在192.168.123.13也就是从服务器上在来一遍，也不废话了。
三，
主从配置
1，
在从服务器上（本例是192.168.123.13）使用pg_basebackup命令拉取主服务器的数据库文件：
[root@k8s-node1 pgsql]# mkdir /opt/backup [root@k8s-node1 pgsql]# chown -Rf pg1. /opt/backup [root@k8s-node1 pgsql]# su - pg1 -c "pg_basebackup -D /opt/backup -Xstream -cfast -P -R -h 192.168.123.60" 56434/56434 kB (100%), 1/1 tablespace
2，
编辑/usr/local/pgsql/data/pg_hba.conf，末尾添加流复制权限：
这样是不安全的，但因为是测试性质，因此无所谓了
host replication all 0.0.0.0/0 trust
安全点的做法（本例没有使用这个）：
在主库创建复制用户，并给予权限
psql -c "CREATE ROLE replicator login replication encrypted passworddfdsf 'postgres';" -bash-4.2$ vim /usr/local/pgsql/data/pg_hba.conf ... #在最下面添加 host replication replicator 192.168.123.13/24 md5 ...
2，
停止从数据库，删除从数据库的数据文件，拷贝主服务器的数据文件，再次赋权pg1用户，启动从数据库：
[root@k8s-node1 pgsql]# su - pg1 -c "pg_ctl -D /usr/local/pgsql/data -l logfile stop" waiting for server to shut down.... done server stopped [root@k8s-node1 pgsql]# rm -rf /usr/local/pgsql/data/* [root@k8s-node1 pgsql]# cp -r /opt/backup/* /usr/local/pgsql/data/ [root@k8s-node1 pgsql]# chown -Rf pg1. /usr/local/pgsql/ [root@k8s-node1 pgsql]# su - pg1 -c "pg_ctl -D /usr/local/pgsql/data -l logfile start" waiting for server to start.... done server started
3，
确认并测试主从服务器是否搭建完毕
在主服务器上操作：
第一个SQL语句可以查询出从服务器的状态，第二个SQL语句，输出为 f 表明该节点是主节点，如果在从节点执行，应该输出的是t
在60服务器上：
select application_name,client_addr,sync_priority,sync_state,reply_time from pg_stat_replication; select pg_is_in_recovery();
-rw------- 1 pg1 pg1 80 Mar 18 22:23 postmaster.pid [root@EULER1 data]# su - pg1 -c "psql -U postgres -p 5432" psql (12.5) Type "help" for help. postgres=# select application_name,client_addr,sync_priority,sync_state,reply_time from pg_stat_replication; application_name | client_addr | sync_priority | sync_state | reply_time ------------------+----------------+---------------+------------+------------------------------- walreceiver | 192.168.123.12 | 0 | async | 2023-03-19 05:04:43.449649+08 postgres=# select pg_is_in_recovery();+ pg_is_in_recovery ------------------- f (1 row)
查看进程，可以看到一个walsender进程：
[root@EULER1 data]# ps -ef |grep postgres root 579 10198 0 05:09 pts/0 00:00:00 grep --color=auto postgres pg1 21167 88426 0 Mar18 ? 00:00:00 postgres: postgres postgres 192.168.123.1(63451) idle pg1 88426 1 0 Mar18 ? 00:00:00 /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data pg1 88427 88426 0 Mar18 ? 00:00:00 postgres: logger pg1 88429 88426 0 Mar18 ? 00:00:00 postgres: checkpointer pg1 88430 88426 0 Mar18 ? 00:00:00 postgres: background writer pg1 88431 88426 0 Mar18 ? 00:00:00 postgres: walwriter pg1 88432 88426 0 Mar18 ? 00:00:00 postgres: autovacuum launcher pg1 88433 88426 0 Mar18 ? 00:00:02 postgres: stats collector pg1 88434 88426 0 Mar18 ? 00:00:00 postgres: logical replication launcher pg1 119397 88426 0 04:53 ? 00:00:00 postgres: walsender pg1 192.168.123.13(60256) streaming 0/7000148 pg1 122529 88426 0 04:58 ? 00:00:00 postgres: walsender pg1 192.168.123.1(64880) idle
通过pg_controldata 命令可以查询到集群状态，在生产表示是主节点
[root@EULER1 data]# pg_controldata |grep "cluster state" Database cluster state: in production
在13服务器上：
[root@k8s-node2 base]# su - postgres -c "/usr/local/pgsql/bin/psql -U postgres -p5432" psql (12.5) Type "help" for help. postgres=# select pg_is_in_recovery(); pg_is_in_recovery ------------------- t (1 row)
查看从服务器的进程，可以看到一个walreceiver：
[root@k8s-node2 base]# ps -ef |grep postgres postgres 16919 25843 0 04:53 ? 00:00:01 postgres: walreceiver streaming 0/7000148 postgres 20240 25843 0 05:02 ? 00:00:00 postgres: postgres postgres 192.168.123.1(56948) idle root 22292 2846 0 05:07 pts/0 00:00:00 grep --color=auto postgres postgres 25843 1 0 01:04 ? 00:00:00 /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data postgres 25844 25843 0 01:04 ? 00:00:00 postgres: startup recovering 000000010000000000000007 postgres 25845 25843 0 01:04 ? 00:00:00 postgres: checkpointer postgres 25846 25843 0 01:04 ? 00:00:00 postgres: background writer postgres 25847 25843 0 01:04 ? 00:00:00 postgres: stats collector postgres 25922 25843 0 01:04 ? 00:00:00 postgres: postgres postgres 192.168.123.1(59962) idle
通过pg_controldata 命令可以查询到集群状态，在备份表示此节点是一个从节点：
[root@k8s-node2 base]# pg_controldata |grep "cluster state" Database cluster state: in archive recovery
OK，简单的一个主从复制小集群就完成了，可以看到，主节点可读可写，从节点只读不写：
主节点新建一个数据库：
刷新后，从节点可以看到了
删除刚才新建的数据库，刷新后，从节点也看不到刚才新建的数据库了：
从节点新建数据库（模拟写数据，无法写入，报错，只读模式）
四，
主从切换
1，
主库停止（在192.168.123.60上操作）
[root@EULER1 data]# bash ~/stop-pgsql.sh waiting for server to shut down.... done server stopped [root@EULER1 data]# pg_controldata pg_control version number: 1201 Catalog version number: 201909212 Database system identifier: 7211655189372047015 Database cluster state: shut down
2，
从库提升为主
从库状态查询确认后，提升为主（在192.168.123.13上操作），确认状态后，启动数据库：
[root@k8s-node2 base]# pg_controldata |grep "cluster state" Database cluster state: in archive recovery [root@k8s-node2 base]# su - postgres -c "pg_ctl promote" waiting for server to promote.... done server promoted [root@k8s-node2 base]# pg_controldata |grep "cluster state" Database cluster state: in production
3，
原主库切换为从库（在192.168.123.60上操作）后，查询主从状态并启动数据库
新建标识文件standby.signal 非常重要
touch $PGDATA/standby.signal [root@EULER1 data]# pg_controldata |grep "cluster state" Database cluster state: in archive recovery
读写测试就不在这里废话了。
小结
- 随着新版本的发行，数据库的配置和使用也越来越简单顺手了。
- 备库提升为主库的命令：pg_ctl promote;
- 新主库(原备库)的pg_hba.conf文件，要开放允许流复制访问数据库的信息给原主库的IP地址；
- 原主库配置为新备库的时候，务必要创建$PGDATA/standby.signal文件；
- 原主库配置为新备库的时候，务必要修改$PGDATA/postgresql.auto.conf文件，添加主库primary_conninfo的信息；
