## 安装postgresql15

```bash
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf -qy module disable postgresql
dnf install -y postgresql15-server
dnf install -y postgresql15-plpython3.x86_64 postgresql15-odbc.x86_64 postgresql15-llvmjit.x86_64
```

- 主从节点都安装一下
- 安装完成后,bin文件路径 `/usr/pgsql-15/bin/`

## 一. 主节点配置

1. 更改数据目录

```bash
# vim /usr/lib/systemd/system/postgresql-15.service
# 更改数据目录
Environment=PGDATA=/mnt/localFC1/pgsqlData/
```

2. 初始化数据库

```bash
/usr/pgsql-15/bin/postgresql-15-setup initdb
```

3. 创建流复制的用户

```bash
su - postgres
pgsql
CREATE ROLE replica login replication encrypted password 'replica';
```

4. 配置文件修改-postgresql.conf

```bash
# vim /mnt/localFC1/pgsqlData/postgresql.conf
# 更改以下及项:
listen_addresses = '*' # 设置监听的ip，* 为允许所有
wal_level = replica # wal日志写入级别，要使用流复制，必须使用replica或更高级别
full_page_writes = on # 可以防止意外宕机后部分数据无法写入
synchronous_commit = on
synchronous_standby_names = '*'
hot_standby = on  # 打开热备
```


5. 配置文件修改-pg_hba.conf

```bash
# vim /mnt/localFC1/pgsqlData/pg_hba.conf
# 新增以下:
host    all             all             0.0.0.0/0               trust
host    replication     replica         0.0.0.0/0               md5
```

6. 为避免从库复制失败,建议设置

```
# vim /mnt/localFC1/pgsqlData/postgresql.conf
# 更改以下及项:
wal_keep_size = 10G # 根据自己情况
archive_mode = on # 开启归档
archive_command = 'gzip < %p > /mnt/localFC1/pg_archive/%f.gz' # 归档方式及路径
archive_timeout = 600s  # 强制归档时间
```
 
 -  其他参数可根据自己情况进行配置

7. 启动数据库

```bash
sudo systemctl enable postgresql-15  # 设置开机自启
sudo systemctl start postgresql-15 # 启动
sudo systemctl status postgresql-15   # 查看运行状态
```

## 二. 从节点配置

> 重点是`standby.signal`文件和`primary_conninfo`配置,以及目录权限

1. 从主节点备份数据及配置到从节点

```bash
pg_basebackup -h 192.168.3.97 -p 5432 -U replica --password -X stream -Fp --progress -D /mnt/localFC1/pgsqlData/ -R
```

同步完成后，就可以看到备库服务器上自动生成了`standby.signal`文件。同时，也看到在`/mnt/localFC1/pgsqlData/`路径下，数据库自动帮我们配置了关于流复制的主库的信息：

2. 更改从节点配置-postgresql.conf

```bash
# 更改配置
# vi /mnt/localFC1/pgsqlData/postgresql.conf
# 确保以下设置
listen_addresses = '*'    
hot_standby = on
# 主节点连接信息在postgresql.auto.conf文件,当然也可以手动修改
#primary_conninfo = ''
```

3. 查看主节点连接信息-postgresql.auto.conf

```
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = 'user=replica password=replica channel_binding=prefer host=192.168.3.97 port=5432 sslmode=prefer sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any'
```

4. 更改用户组

```bash
# 更改目录用户组
chown -R postgres.postgres /mnt/localFC1/pgsqlData/
```

5. 更改数据目录

```
# vim /usr/lib/systemd/system/postgresql-15.service
# 更改数据目录
Environment=PGDATA=/mnt/localFC1/pgsqlData/
```

6. 启动

```bash
sudo systemctl enable postgresql-15  # 设置开机自启
sudo systemctl start postgresql-15 # 启动
sudo systemctl status postgresql-15   # 查看运行状态
```

## 三. 查看主从同步状态

- 查看状态`pg_controldata `

```bash
pg_controldata --pgdata=/mnt/localFC1/pgsqlData/
# 主库:
Database cluster state:               in production
# 备库:
Database cluster state:               in archive recovery
```

- 主库使用 pg_stat_replication 监控流复制

```sql
select * from pg_stat_replication;
```

- 备库使用 pg_stat_wal_receiver 监控流复制

```sql
select * from pg_stat_wal_receiver;
```

- 查看备库落后主库多少字节的WAL日志

```sql
select pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_flush_lsn(), write_lsn)) delay_wal_size, * from pg_stat_replication;
```

- 查看备库接收WAL日志和应用WAL日志的状态

```sql
select * from pg_last_wal_receive_lsn();
select * from pg_last_wal_replay_lsn();
select * from pg_last_xact_replay_timestamp();
```

## 四. 主从切换

1. 查看主备库状态: `pg_controldata`

```bash
pg_controldata
主库:
Database cluster state:               in production
备库:
Database cluster state:               in archive recovery
```

2. 停止主库

```bash
systemctl stop postgresql-15.service
# 查看状态,应该为: shut down
pg_controldata --pgdata=/mnt/localFC1/pgsqlData/
```

3. 原备库切换为主库: `pg_ctl promote`

```bash
pg_controldata --pgdata=/mnt/localFC1/pgsqlData/
# 确保备库状态为：in archive recovery
su - postgres
# 切换为主节点
pg_ctl promote -D /mnt/localFC1/pgsqlData/
```

4. 原主库切换从库

> 重点: 从库需要`standby.signal`文件和`primary_conninfo`配置

- 创建`standby.signal`

```bash
cd /mnt/localFC1/pgsqlData/
su - postgres
# 创建
touch standby.signal  
# 确保权限为:  postgres postgres
-rw-------  1 postgres postgres       0 Jan 26 16:08 standby.signal
```

- 修改`primary_conninfo`

```bash
# vim /mnt/localFC1/pgsqlData/postgresql.conf
# 修改以下信息
primary_conninfo = 'user=replica password=replica channel_binding=prefer host=192.168.3.97 port=5432 sslmode=prefer sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any'
```

- 启动新从库

```bash
sudo systemctl start postgresql-15
```

## 小结

```
备库提升为主库的命令：pg_ctl promote;
新主库(原备库)的pg_hba.conf文件，要开放允许流复制访问数据库的信息给原主库的IP地址；
原主库配置为新备库的时候，务必要创建$PGDATA/standby.signal文件；
原主库配置为新备库的时候，务必要修改$PGDATA/postgresql.auto.conf文件，添加主库primary_conninfo的信息；
```

参考链接:
https://cloud.tencent.com/developer/article/2013763?from=15425
https://www.cnblogs.com/a120608yby/p/17312376.html
https://blog.51cto.com/u_175779/6010446
https://blog.csdn.net/hqwang4/article/details/124833185