postgresql 读写分离

一、postgresql数据库安装

1.数据库安装包下载（本次使用12.2版本）

Index of /pub/source/v12.2/

2.数据库安装包依赖下载安装

yum install -y perl-ExtUtils-Embed readline-devel zlib-devel pam-devel libxml2-devel libxslt-devel openldap-devel python-devel gcc-c++ openssl-devel cmake

3.创建pgsql文件夹，将下载的数据库安装包移动到目录中

#创建文件夹目录

mkdir /opt/module/pgsql

#文件上传通过命令 rz 选择上传文件

#文件上传通过ftp

#直接将数据库安装包拖动到对应目录中

4.解压数据库压缩包

tar -zxvf postgresql-12.2.tar.gz

5.编译postgres源码

#进入解压后文件目录

cd /opt/module/pgsql/postgresql-12.2

#设置配置文件

./configure --prefix=/opt/module/pgsql/postgresql

6.编译安装

make

make install

二、数据库配置

1.创建用户组postgres并创建用户postgres

#添加用户组

groupadd postgres

#添加用户并设置密码

useradd -g postgres postgres

#查询用户

id postgres

2.创建postgres数据库的数据主目录并修改文件所有者

#进入数据库配置主目录

cd /opt/module/pgsql/

#创建数据主目录

mkdir data

#修改文件所有者

chown postgres:postgres data

3.配置环境变量

#打开配置文件

vi /etc/profile

#添加以下内容

export PGHOME=/opt/module/pgsql/postgresql 

export PGDATA=/opt/module/pgsql/data

export PGPORT=5432

export PATH=$PATH:$PGHOME/bin

#保存并退出配置文件，执行下方命令刷新配置文件使其生效

source /etc/profile

4.初始化数据库

#切换到postgres用户

su - postgres

#初始化数据库

initdb

#切换到数据库数据主目录

cd /opt/module/pgsql/data

5.配置服务

#修改/opt/module/pgsql/data目录下的两个文件

#postgresql.conf   配置PostgreSQL数据库服务器的相应的参数 #pg_hba.conf       配置对数据库的访问权限

vi postgresql.conf

#参数“listen_addresses”表示监听的IP地址，默认是在localhost处监听， #也就是127.0.0.1的ip地址上监听，

#只接受来自本机localhost的连接请求，

#这会让远程的主机无法登陆这台数据库，

#如果想从其他的机器上登陆这台数据库，

#需要把监听地址改为实际网络的地址，

#一种简单的方法是，将行开头的#去掉，把这个地址改为*，

#表示在本地的所有地址上监听。

listen_addresses = '*'

vi pg_hba.conf 

#添加以下内容，这样局域网的人才能访问

# IPv4 remote address connections:

host    all             all             0.0.0.0/0               trust 

6.设置开机自启动

6.1.进入脚本目录

cd /opt/module/pgsql/postgresql-12.2/contrib/start-scripts

6.2.切换为root用户，修改linux文件属性，添加X属性

chmod a+x linux

6.3.复制linux文件到/etc/init.d目录下，更名为postgresql

cp linux /etc/init.d/postgresql

6.4.修改/etc/init.d/postgresql文件的两个变量

prefix设置为postgresql的安装路径：/opt/module/pgsql/postgresql

PGDATA设置为postgresql的数据目录路径：/opt/module/pgsql/data

6.5.设置postgresql服务开机自启动

chkconfig --add postgresql

6.6、查看开机自启动服务设置成功

chkconfig

请与以下信息进行对比：

postgresql         0:关闭    1:关闭    2:启用    3:启用    4:启用    5:启用    6:关闭

6.7.开放5432端口 （若需开启防火墙）

firewall-cmd --zone=public --add-port=5432/tcp --permanent

6.8.重启防火墙

firewall-cmd --reload

6.9.查看防火墙开放端口

firewall-cmd --zone=public --list-ports

• 数据库读写分离

（一）前期准备工作

1.主机选择

192.168.70.52 master

192.168.70.40 slave

2.修改主机名称

#将主备库的主机名修改为master，slave(可以自定义)修改如下文件的主机名

主从库都需要配置

[postgres@master ~]$ vim /etc/hosts

192.168.70.52 master     #主机中的配置

192.168.70.40 slave      #从机中的配置

主库机器配置

[root@master ~]$ vim /etc/hostname

master

从库机器配置

[root@slave ~]# vim /etc/hostname

slave

#查看主机名

hostname ; hostname -i

3.ssh互信免密操作

3.1自动方式

(1) 创建服务器公钥以及密钥（主从都需要）

#执行命令生成id_rsa.pub文件

ssh-keygen -t rsa -P ''

(2) 发送公钥至登陆服务器 （若以下命令执行失败，请按照3.2手动方式进行配置）

#执行ssh-copy-id命令将/root/.ssh/目录下的id_rsa.pub公钥文件发送到登陆服务器中

ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.70.40  #主机操作

ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.70.52  #从机操作

(3) 测试免密登录

ssh root@192.168.70.40 #主机操作

ssh root@192.168.70.52 #从机操作

3.2手动方式

1. 在主机和从机上分别创建服务器公钥以及密钥

#执行命令生成id_rsa.pub文件

ssh-keygen -t rsa -P ''

1. 在主机和从机上分别创建authorized_keys文件

#进入 /root/.ssh目录

cd /root/.ssh

#执行命令创建文件 authorized_keys

touch authorized_keys

1. 主从机交换公钥信息

将主机的id_rsa.pub文件内容附加到从机的authorized_keys

将从机的id_rsa.pub文件内容附加到主机的authorized_keys

1. 测试免密登录

ssh root@192.168.70.40 #主机操作

ssh root@192.168.70.52 #从机操作

（二）master主机操作

1、修改配置文件

#设置好用于复制的访问权限非常重要，这样只有受信的用户可以读取 WAL 流，

#因为很容易从 WAL 流中抽取出需要特权才能访问的信息。

#后备服务器必须作为一个超级用户或一个具有REPLICATION特权的账户向主服务器认证。

#推荐为复制创建一个专用的具有REPLICATION和LOGIN特权的用户账户。

#虽然REPLICATION特权给出了非常高的权限，但它不允许用户修改主系统上的任何数据，

#而SUPERUSER特权则可以。

cd /opt/module/pgsql/data

vim pg_hba.conf

#向文件中添加配置，如下：

host    replication     all             192.168.70.52/32       trust

#修改配置postgresql.conf以下几处 内存大小按需求更改

vi postgresql.conf

#主要修改

listen_addresses = '*'

port = 5432

wal_level = hot_standby

wal_log_hints = on

max_wal_senders = 10

hot_standby = on

logging_collector = on

log_directory = 'pg_log'

archive_mode =on

archive_command ='DATE=`date +%Y%m%d`;DIR="/opt/module/pgsql/postgresql/arch/$DATE";(test -d $DIR || mkdir -p $DIR)&& cp %p $DIR/%f'

2、创建归档路径(路径与上方archive_command中DIR对应)

mkdir  -p /opt/module/pgsql/postgresql/arch/

chown -R postgres:postgres /opt/module/pgsql/postgresql/arch/

3、主库中创建流复制用户和pgpool用户

#这里的流复制用户要和上面pg_hba.conf里面写的相同。

#PGPool用户是在之后用到的，在这里先创建了

#进入数据库

psql postgres

CREATE USER stream_replication replication LOGIN CONNECTION LIMIT 5 ENCRYPTED PASSWORD '1qaz@WSX3edc';

CREATE USER pgpool replication LOGIN CONNECTION LIMIT 5 ENCRYPTED PASSWORD '1qaz@WSX3edc';

4、重启数据库

#切换到postgres用户

su postgres

#重启数据库服务

pg_ctl -D  /opt/module/pgsql/data/ restart

（三）slave 从机操作

1、基础备份复制到备份服务器

#用户切换

su postgres

#停止数据库服务

pg_ctl  -D /opt/module/pgsql/data/  stop

#删除从库数据文件

rm -rf /opt/module/pgsql/data # 如果没有重要数据可操作，主要为同步主库路径

#如果postgres用户没有新建文件夹权限则需要用root用户创建一个新的data文件夹

mkdir /opt/module/pgsql/data

#修改所属组

chown -R postgres:postgres /opt/module/pgsql

#从主库拉取数据文件

pg_basebackup -h 192.168.70.52 -U stream_replication  -F p  -P -R -D /opt/module/pgsql/data

2、配置文件修改

su postgres

vi postgresql.conf

#修改以下内容,主库如果已经自选修改过以后无需修改

hot_standby = on

3、主库信息查看

#postgresql.auto.conf里面存放了主库连接信息

vi /opt/module/pgsql/data/postgresql.auto.conf

4、启动数据库服务

#启动数据库服务

pg_ctl -D /opt/module/pgsql/data/ start

可能遇到的错误：

#报错内容

waiting for server to start....2023-02-01 09:54:36.742 CST [6406] FATAL:  data directory "/opt/module/pgsql/data" has invalid permissions

2023-02-01 09:54:36.742 CST [6406] DETAIL:  Permissions should be u=rwx (0700) or u=rwx,g=rx (0750).

 stopped waiting

pg_ctl: could not start server

Examine the log output.

#解决方案

#修改权限，再启动即可

chmod 0750 /opt/module/pgsql/data/

pg_ctl -D /opt/module/pgsql/data/ start

5、流复制信息查看

#查看流复制的信息可以使用主库上的视图

select pid,state,client_addr,sync_priority,sync_state from pg_stat_replication;