postgresql 15.2一主一备集群数据库安装与配置实践

skylines 2023-05-05

257

一、搭建postgresql集群主备数据库

##主备库添加postgresql的安装用户postgres

useradd postgres

--这个安装用户也可以不用创建，安装的时候自动创建

##使用官方推荐的方法安装主备postgresql数据库

sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo yum install -y postgresql15-server
sudo usr/pgsql-15/bin/postgresql-15-setup initdb
sudo systemctl enable postgresql-15
sudo systemctl start postgresql-15

##主备库初始化postgresql数据库

[root@mytest01 ~]# usr/pgsql-15/bin/postgresql-15-setup initdb
Initializing database ... OK

##启动主库

[root@mytest01 ~]# systemctl enable postgresql-15
Created symlink from etc/systemd/system/multi-user.target.wants/postgresql-15.service to usr/lib/systemd/system/postgresql-15.service.
[root@mytest01 ~]#

##查看postgresql数据库的进程

[postgres@mytest01 ~]$ ps -ef |grep postgres
root     24076 23794  0 09:26 pts/3    00:00:00 su - postgres
postgres 24077 24076  0 09:26 pts/3    00:00:00 -bash
postgres 24290 24077  0 09:27 pts/3    00:00:00 ps -ef
postgres 24291 24077  0 09:27 pts/3    00:00:00 grep --color=auto postgres
postgres 28286     1  0 Apr29 ?        00:00:06 usr/pgsql-15/bin/postmaster -D var/lib/pgsql/15/data/
postgres 28288 28286  0 Apr29 ?        00:00:00 postgres: logger 
postgres 28294 28286  0 Apr29 ?        00:00:00 postgres: checkpointer 
postgres 28295 28286  0 Apr29 ?        00:00:01 postgres: background writer 
postgres 28297 28286  0 Apr29 ?        00:00:02 postgres: walwriter 
postgres 28298 28286  0 Apr29 ?        00:00:02 postgres: autovacuum launcher 
postgres 28299 28286  0 Apr29 ?        00:00:00 postgres: archiver last was 000000010000000000000006.00000028.backup
postgres 28300 28286  0 Apr29 ?        00:00:00 postgres: logical replication launcher 
postgres 29671 28286  0 Apr29 ?        00:00:01 postgres: walsender replica 192.168.0.11(55064) streaming 0/70197C0

--这里是我已经搭建好主备同步时候的进程情况，其中进号为28286的/usr/pgsql-15/bin/postmaster -D var/lib/pgsql/15/data/ 是为postgresql主进程。

##使用psql登录postgresql数据库

[root@mytest01 ~]# su - postgres


Last failed login: Thu Apr 27 16:51:08 CST 2023 from 139.59.74.143 on ssh:notty
There were 44 failed login attempts since the last successful login.
[postgres@mytest01 ~]$ 
[postgres@mytest01 ~]$ 
[postgres@mytest01 ~]$ 
[postgres@mytest01 ~]$ psql
psql (15.2)
Type "help" for help.
postgres=# 
postgres=# \l
                                                List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    | ICU Locale | Locale Provider |   Access privileges   
-----------+----------+----------+------------+------------+------------+-----------------+-----------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | 
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | =c/postgres          +
           |          |          |            |            |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | =c/postgres          +
           |          |          |            |            |            |                 | postgres=CTc/postgres
(3 rows)




postgres=#

--可以看到，目前有一个postgres数据库，template0和template1都是模板库。

##修改postgresql数据库管理用户postgres的密码

ALTER USER postgres WITH PASSWORD '123456';

--安全起见，建议在建库之后进行修改，特别是云服务器上的数据库。

二、主备同步配置

##主库创建主备同步用户replica

CREATE USER replica login replication encrypted password 'replica520A';

##主库配置可信任客户端信息

echo "host     all             all          192.168.0.0/24          trust"   >> var/lib/pgsql/15/data/pg_hba.conf
echo "host   replication      replica       192.168.0.0/24          md5"  >>/var/lib/pgsql/15/data/pg_hba.conf

##主库修改postgresql.conf配置文件以下参数

vim $PGDATA/postgresql.conf

listen_addresses = '*'

wal_level = replica

max_wal_senders = 32 --最大发送进程，默认 10，读写分离一写多服务读，请设置为读数据库的数量

synchronous_commit = on --将事务提交方式改为本地提交，默认为 on，在 on 模式下事务需要等备份数据库一起提交，这里改为 local 让备份数据库不影响主库，如若是先配置读写分离，请设置为默认 on

archive_command = 'cp %p var/lib/pgsql/15/data/pg_archive/%f' --归档日志同步位置与格式

##备库进行初始化，将主库数据目录备份到备库上

pg_basebackup -h 192.168.0.16 -p 5002 -D var/lib/pgsql/15/data -U replica -P -v  -R -X stream -C -S pgstandby1 


pg_basebackup: initiating base backup, waiting for checkpoint to complete


pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/20000028 on timeline 8
pg_basebackup: starting background WAL receiver
pg_basebackup: created replication slot "pgstandby1"
506910/506910 kB (100%), 1/1 tablespace                                         
pg_basebackup: write-ahead log end point: 0/20000130
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: base backup completed

--复制后从节点的配置文件会和主节点的一样，并且一并复制的还有数据库文件，里面的数据也一样，意味着主节点有什么用户数据从节点也有。

-h –指定作为主服务器的主机；

-D –指定数据目录；

-U –指定连接用户；

-P –启用进度报告；

-v –启用详细模式；

-R–启用恢复配置的创建：创建一个standby.signal文件，并将连接设置附加到数据目录下的postgresql.auto.conf；

-X–用于在备份中包括所需的预写日志文件（WAL文件）。流的值表示在创建备份时流式传输WAL；

-C –在开始备份之前，允许创建由-S选项命名的复制插槽；

-S –指定复制插槽名称。

--备库的$PGDATA数据目录下新增了两个文件

ls -lrt var/lib/pgsql/15/data
-rw------- 1 postgres postgres      0 Apr 29 16:33 standby.signal
-rw------- 1 postgres postgres 138665 Apr 29 16:33 backup_manifest

##在操作系统层查看主备库角色信息

pg_controldata var/lib/pgsql/15/data| grep 'Database cluster state'

--在我环境的15.2的版本中，未能使用pg_controldata该工具，如果正常使用的情况下，主库的集群状态为in production，备库的集群状态为in archive recovery。

##主库查看复制槽信息

postgres=# SELECT * FROM pg_replication_slots;
-[ RECORD 1 ]-------+-----------
slot_name           | pgstandby1
plugin              | 
slot_type           | physical
datoid              | 
database            | 
temporary           | f
active              | t
active_pid          | 29671
xmin                | 
catalog_xmin        | 
restart_lsn         | 0/70197C0
confirmed_flush_lsn | 
wal_status          | reserved
safe_wal_size       | 
two_phase           | f

##主库查看备库同步信息

postgres=# 
postgres=# SELECT * FROM pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 29671
usesysid         | 16389
usename          | replica
application_name | walreceiver
client_addr      | 192.168.0.11
client_hostname  | 
client_port      | 55064
backend_start    | 2023-04-29 16:36:12.571908+08
backend_xmin     | 
state            | streaming
sent_lsn         | 0/70197C0
write_lsn        | 0/70197C0
flush_lsn        | 0/70197C0
replay_lsn       | 0/70197C0
write_lag        | 
flush_lag        | 
replay_lag       | 
sync_priority    | 0
sync_state       | async
reply_time       | 2023-04-29 16:43:26.26406+08




postgres=# 
postgres=# 
postgres=#

##备库查看同步信息

postgres=# SELECT * FROM pg_stat_wal_receiver;
-[ RECORD 1 ]---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid                   | 9629
status                | streaming
receive_start_lsn     | 0/7000000
receive_start_tli     | 1
written_lsn           | 0/70197C0
flushed_lsn           | 0/70197C0
received_tli          | 1
last_msg_send_time    | 2023-04-29 16:43:46.291075+08
last_msg_receipt_time | 2023-04-29 16:43:46.290339+08
latest_end_lsn        | 0/70197C0
latest_end_time       | 2023-04-29 16:43:16.250571+08
slot_name             | pgstandby1
sender_host           | 192.168.0.16
sender_port           | 5002
conninfo              | user=replica password=******** channel_binding=prefer dbname=replication host=192.168.0.16 port=5002 fallback_application_name=walreceiver sslmode=prefer sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any

##主库创建测试表

postgres=# create table t1(id serial,user_name varchar(20));


CREATE TABLE
postgres=# 
postgres=# \dt
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | t1   | table | postgres
(1 row)




postgres=# 
postgres=# insert into t1(user_name) values('nia');
INSERT 0 1
postgres=# select * from t1;
 id | user_name 
----+-----------
  1 | nia
(1 row)

##备库查看测试表同步情况

postgres=#  \dt
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | t1   | table | postgres
(1 row)




postgres=# select * from t1;
 id | user_name 
----+-----------
  1 | nia
(1 row)

--postgresql主备同步配置成功。

##本次的postgresql 15.2主备同步与我上次做postgresql 11beta2 主备同步的变化

1、数据库安装方法：其实本次我选择的rpm包方法安装，上次使用二进制tar包安装postgresql 11beta2的，这个不属于版本间的差异；

2、主备同步参数配置：wal_level本次要设置为replica，上次11.2的时候我设置为hot_standby，如果在15.2设置为hot_standby就不能进行同步；

3、主备同步后产生的文件：15.2配置了主备同步后，在备库的$PGDATA的目录下生成了standby.signal和backup_manifest两个文件，standby.signal为一个空文件，这在11.2的主备同步中备库是没有的文件。

4、备库中primary_conninfo连接信息，在15.2中存放在postgresql.auto.conf，而在11.2中存放在recovery.conf文件中，15.2版本中没有recovery.conf文件。

5、目前我体验到的，11.2中可以使用pg_controldata查看主备库的角色信息，主库的集群状态为in production，备库的集群状态为in archive recovery，但在15.2中不能使用该pg_controldata工具。

接下来还会写一个15.2版本的主备切换的文章。

（文毕）

postgresql 数据库集群

文章转载自skylines，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

postgresql 15.2一主一备集群数据库安装与配置实践

评论