PostgreSQL 12 数据库安装

环境

• 系统版本： CentOS Linux release 7.7.1908 (Core)

安装配置PostgreSQL

下载PostgreSQL安装包

下载地址：https://yum.postgresql.org/repopackages.php
下载最新版即可，本文档示例使用PostgreSQL12

安装PostgreSQL

将下载的RPM包上传至服务器中
安装rpm包

[root@postgresql ~]# yum install -y pgdg-redhat-repo-latest.noarch.rpm

查看PostgreSQL源

[root@postgresql ~]# yum list | grep postgresql

在展示出的列表中查找，需要安装两个包postgresql12-contrib，postgresql12-server
使用yum源直接安装

[root@postgresql ~]# yum install -y postgresql12-contrib postgresql12-server

初始化数据库

在初始化之前，需要先确定数据库安装文件存储的位置；PostgreSQL的系统安装目录是：/usr/pgsql-版本号；数据存储目录：/var/lib/pgsql/版本号/data目录。在安装系统的时候，如果分配的/var文件夹存储空间够大，则不需要修改安装文件，否则就需要修改数据库数据存储位置，以防后续空间不足。
本示例将数据的存储位置改为/home/pgdata文件夹：

# 自定义systemctl服务
[root@postgresql ~]# systemctl edit postgresql-12

# 添加以下命令行
[Service]
Environment=PGDATA=/home/pgdata/12/data # 更改已创建完成的目录

# 重新加载系统systemctl配置
[root@postgresql ~]# systemctl daemon-reload

# 初始化PostgreSQL数据目录
[root@postgresql ~]# /usr/pgsql-12/bin/postgresql-12-setup initdb

# 数据库启动并设置自启
[root@postgresql ~]# systemctl enable postgresql-12
[root@postgresql ~]# systemctl start postgresql-12

修改数据库登录密码

# 使用postgres用户登录数据库系统
[root@postgresql ~]# su - postgres
-bash-4.2$ psql
psql (12.1)
输入 "help" 来获取帮助信息.

postgres=#


# 修改数据库用户密码
postgres=# alter user postgres with password '123456';
ALTER ROLE

退出：\q
列出所有库：\l
列出所有用户：\du
列出库下所有表：\d

修改配置，支持远程登录

默认情况下PostgreSQL是不用密码不支持远程登录的，需要修改配置文件支持远程的登录。

# 修改配置文件
[root@postgresql ~]# vi /home/pgdata/12/data/pg_hba.conf

将配置文件修改为如下：

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     all                                     md5
#host    replication     all             127.0.0.1/32            md5
#host    replication     all             ::1/128                 md5
host     all             all             0.0.0.0/0               md5

修改远程访问

# 编辑配置文件
[root@postgresql ~]# vi /home/pgdata/12/data/postgresql.conf

将配置文件修改为如下：

# - Connection Settings -

listen_addresses = '*'          # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
port = 5432                             # (change requires restart)
max_connections = 100                   # (change requires restart)
#superuser_reserved_connections = 3     # (change requires restart)
#unix_socket_directories = '/var/run/postgresql, /tmp'  # comma-separated list of directories
                                        # (change requires restart)
#unix_socket_group = ''                 # (change requires restart)
#unix_socket_permissions = 0777         # begin with 0 to use octal notation
                                        # (change requires restart)
#bonjour = off                          # advertise server via Bonjour
                                        # (change requires restart)
#bonjour_name = ''                      # defaults to the computer name
                                        # (change requires restart)

重启PostgreSQL

[root@postgresql ~]# systemctl restart postgresql-12

再使用工具远程登录即可

PostgreSQL配置主从

---

PostgreSql在9.0之后引入了主从的流复制机制，所谓流复制，就是从服务器通过tcp流从主服务器中同步相应的数据。这样当主服务器数据丢失时从服务器中仍有备份。
与基于文件日志传送相比，流复制允许保持从服务器更新。 从服务器连接主服务器，其产生的流WAL记录到从服务器， 而不需要等待主服务器写完WAL文件。
PostgreSQL流复制默认是异步的。在主服务器上提交事务和从服务器上变化可见之间有一个小的延迟，这个延迟远小于基于文件日志传送，通常1秒能完成。如果主服务器突然崩溃，可能会有少量数据丢失。
同步复制必须等主服务器和从服务器都写完WAL后才能提交事务。这样在一定程度上会增加事务的响应时间。
配置同步复制仅需要一个额外的配置步骤： synchronous_standby_names必须设置为一个非空值。synchronous_commit也必须设置为on。
注: 主从服务器所在节点的系统、环境等最好一致。PostgreSQL版本也最好一致，否则可能会有问题。

---

安装部署

本示例采用两台相同配置的服务器进行部署，PostgreSQL12安装详情请参考《安装配置PostgreSQL12》

角色	IP地址	系统版本	数据库版本
主服务器	192.168.0.73	CentOS Linux release 7.7.1908	PostgreSQL12
从服务器	192.168.0.74	CentOS Linux release 7.7.1908	PostgreSQL12

配置主服务器

切换成postgres用户，进入数据库，创建一个普通数据库用户，只赋予登录和复制的权限即可

[root@postgresql ~]# su - postgres
上一次登录：二 1月 14 10:24:59 CST 2020pts/0 上
-bash-4.2$ psql
用户 postgres 的口令：
psql (12.1)
输入 "help" 来获取帮助信息.

postgres=# create role rsyncd login replication encrypted password '123456';
CREATE ROLE
postgres=# \q

用户创建完成后，修改pg_hba.conf文件，在结尾添加如下配置：

host     replication     rsyncd          192.168.0.74/24         md5

## 即使之前添加过所有用户不限制IP地址登录，也要添加此配置项，否则数据同步不好用
## IP地址可配置成 0.0.0.0/0 根据具体的业务场景配置

修改postgresql.conf文件

-bash-4.2$ vi /home/pgdata/12/data/postgresql.conf

## 直接修改以下参数
archive_mode = on                                   ## 开启归档模式
archive_command = 'cp %p /home/pgdata/12/data/%f'   ## 归档命令
wal_level = replica                                 ## 热备模式
max_wal_senders = 2                                 ## 最多有2个流复制连接
wal_sender_timeout = 60s                            ## 流复制超时时间
max_connections = 100                               ## 最大连接时间，必须要小于从库的配置

退出保存后，重启数据库服务

[root@postgresql ~]# systemctl restart postgresql-12

配置从服务器

测试从服务器是否能够连接到主服务器

[root@postgresql ~]# psql -h 192.168.0.73  -U  postgres
用户 postgres 的口令：
psql (12.1)
输入 "help" 来获取帮助信息.

postgres=# 

如果可以正常连接，说明主服务器配置正常，否则检查下主服务器的pg_hba.conf文件

切换成postgres用户，清空数据文件夹内所有的文件【这步需要做"基础备份"，首次使用建议做好备份工作】

-bash-4.2$ rm -rf /home/pgdata/12/data/*

在主服务器上备份数据到从服务器中，这步操作为”基础备份“，使用主服务器创建的备份用户进行操作

-bash-4.2$ pg_basebackup -h 192.168.0.73 -p 5432 -U rsyncd -Fp -Xs -Pv -R -D /home/pgdata/12/data/
口令: 
pg_basebackup: 开始基础备份，等待检查点完成
pg_basebackup: 已完成检查点
pg_basebackup: 预写日志起始于时间点: 0/4000028, 基于时间轴1
pg_basebackup: 启动后台 WAL 接收进程
pg_basebackup: 已创建临时复制槽"pg_basebackup_4087"
25317/25317 kB (100%), 1/1 表空间                                         
pg_basebackup: 预写日志结束点: 0/4000100
pg_basebackup: 等待后台进程结束流操作...
pg_basebackup: 同步数据到磁盘...
pg_basebackup: 基础备份已完成

在数据文件夹中创建恢复配置文件

-bash-4.2$ vi /home/pgdata/12/data/recovery.signal

# 将以下写在恢复文件中
standby_mode=on                                                                 ## 表示该节点是从库
primary_conninfo='host=192.168.0.73 post=5432 user=rsyncd password=123456'      ## 主服务器信息和连接用户
recovery_target_timelint='latest'                                               ## 说明恢复到最新状态

修改postgresql.conf文件

max_connections = 300                                ## 最大连接时间，必须要小于从库的配置
hot_standby = on                                     ## 说明这台机器不仅用于数据归档，还可以用于数据查询
max_standby_streaming_delay = 30s                    ## 流备份的最大延迟时间
wal_receiver_status_interval = 10s                   ## 向主机汇报本机状态的间隔时间
hot_standby_feedback = on                            ## 如出现错误复制，向主机反馈

退出保存后，重启数据库服务，验证从服务器是否可以同步主服务器数据

[root@postgresql ~]# systemctl restart postgresql-12