PostgreSQL 介绍

PostgreSQL是一款高级的企业级开源关系数据库，支持 SQL（关系型）和 JSON（非关系型）查询。本文介绍如何在 ubuntu 部署 PostgreSQL 主从集群

介绍

• PostgreSQL 可用作很多 Web、移动、地理空间和分析应用程序的主要数据存储或数据仓库
• PostGIS 是一个开源程序，它为对象－关系型数据库 PostgreSQL 提供存储空间地理数据的支持，使 PostgreSQL 成为了一个空间数据库，能够进行空间数据管理、数量测量与几何拓扑分析。
  • PostGIS 实现了开放地理空间协会所提出的基本要素类的 SQL 实现参考。

部署

• https://www.postgresql.org/download/

apt install postgresql postgresql-client

• 配置

$ vim /etc/postgresql/14/main/pg_hba.conf
# 允许任意用户从任意机器上以密码方式访问数据库
host    all             all             0.0.0.0/0               md5

# 针对 512M 内存配置
$ vim /etc/postgresql/14/main/postgresql.conf
listen_addresses = '*'
max_connections = 100
# shared_buffers = 128MB			# min 128kB
shared_buffers = 64MB
# temp_buffers = 8MB			# min 800kB
temp_buffers = 4MB
# work_mem 会让排序操作快，计算公式：Total RAM * 0.25 / max_connections
# work_mem = 4MB				# min 64kB
work_mem = 2MB
# maintenance_work_mem 参数设置维护操作的最大内存数，计算公式：Total RAM * 0.05
#maintenance_work_mem = 64MB		# min 1MB
maintenance_work_mem = 32MB
#max_stack_depth = 2MB			# min 100kB
max_stack_depth = 1MB

• 重启数据库

sudo systemctl restart postgresql

docker

k8s

sorintlab/stolon：PostgreSQL 云原生高可用性

集群

版本环境

• 服务器系统: Ubuntu 20.04.5 LTS
• PostgreSQL 版本: 14
• 主数据库内网 IP : 10.0.0.2
• 从数据库内网 IP : 10.0.0.3
• 说明
  • 数据目录 /var/lib/postgresql/14/main
  • 配置文件目录 /etc/postgresql/14/main/
  • 日志文件 /var/log/postgresql/postgresql-14-main.log

主数据库部署

• 安装（主从均需要）

# 配置源，参考 https://download.postgresql.org/pub/repos/apt/README
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
apt-get update

# 安装，参考 https://wiki.postgresql.org/wiki/Apt
apt-get install postgresql-14 -y

# 安装时 initdb 的日志
...
Creating new PostgreSQL cluster 14/main ...
/usr/lib/postgresql/14/bin/initdb -D /var/lib/postgresql/14/main --auth-local peer --auth-host scram-sha-256 --no-instructions
...

• 主节点初始化

$ su - postgres
$ psql

# 创建 postgres 密码
ALTER USER postgres WITH PASSWORD '123456';

# 创建 从库 replica 用户密码
CREATE ROLE replica login replication encrypted password 'replica';

# 检查账号
postgres=# SELECT usename from pg_user;
 usename
----------
 postgres
 replica
(2 rows)

# 查看权限
postgres=# SELECT rolname from pg_roles;
          rolname
---------------------------
 pg_database_owner
 pg_read_all_data
 pg_write_all_data
 pg_monitor
 pg_read_all_settings
 pg_read_all_stats
 pg_stat_scan_tables
 pg_read_server_files
 pg_write_server_files
 pg_execute_server_program
 pg_signal_backend
 postgres
 replica
(13 rows)

• 配置 /etc/postgresql/14/main/pg_hba.conf

$ vim /etc/postgresql/14/main/pg_hba.conf
# 添加从库网段
host    all             all             0.0.0.0/0               trust

# replication privilege.
local   replication     all                                     peer  # 原来就有
host    replication     replica         10.0.0.3/24             md5

• 配置 /etc/postgresql/14/main/postgresql.conf

$ vim /etc/postgresql/14/main/postgresql.conf

# 监听地址
listen_addresses = '*'

# 最大连接数，从库的max_connections必须要大于主库的
max_connections = 100

# 启用热备模式
wal_level = hot_standby

# 开启同步复制
synchronous_commit = on

# 同步最大的进程数量
max_wal_senders = 32

# 流复制主机发送数据的超时时间
wal_sender_timeout = 60s

• 重新启动（安装后，默认是启动的）

sudo systemctl enable postgresql.service
sudo systemctl restart postgresql.service

从节点部署

# 配置源，参考 https://download.postgresql.org/pub/repos/apt/README
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
apt-get update

# 安装，参考 https://wiki.postgresql.org/wiki/Apt
apt-get install postgresql-14 -y

• 从主库同步数据

# 停止服务
sudo systemctl stop postgresql.service

# 清理数据
rm -rf /var/lib/postgresql/14/main/*

# 同步数据
$ pg_basebackup -D /var/lib/postgresql/14/main/ -h <master-ip> -p 5432 -U replica -X stream -P
Password:
26251/26251 kB (100%), 1/1 tablespace

# 修改权限
$ chown -R postgres.postgres /var/lib/postgresql/14/main/

# 同步配置文件
scp <master-ip>:/etc/postgresql/14/main/postgresql.conf /etc/postgresql/14/main/postgresql.conf

• 配置 vim /etc/postgresql/14/main/postgresql.conf，(recovery.conf 自 PostgreSQL 12 移除)

$ vim /etc/postgresql/14/main/postgresql.conf

## 移除或注释 wal_level
wal_level = xxx
## 修改或添加以下
primary_conninfo = 'host=10.0.0.2 port=5432 user=replica password=replica'
recovery_target_timeline = 'latest'

# 最大连接数，从库的max_connections必须要大于主库的
max_connections = 1000
# 如果有错误的数据复制向主进行反馈
hot_standby_feedback = on

• 创建 standby.signal (与 /var/lib/postgresql/14/main/postgresql.auto.conf 的关系？存疑)

$ cat << EOF > /etc/postgresql/14/main/standby.signal
standby_mode = on
EOF

$ chown -R postgres.postgres /etc/postgresql/14/main/standby.signal

• 启动服务

sudo systemctl start postgresql.service

测试同步

• 主数据库，async 表示已同步

$ ps -ef | grep walsender
postgres   84941   84075  0 18:58 ?        00:00:00 postgres: 14/main: walsender replica 10.0.0.3(43966) streaming 0/3000148

$ su - postgres
-bash: /usr/local/bin/ki: No such file or directory
[postgres@ubuntu 18:59 ~]
$ psql
psql (14.6 (Ubuntu 14.6-1.pgdg20.04+1))
Type "help" for help.

postgres=# select application_name, state, sync_priority, sync_state from pg_stat_replication;
 application_name |   state   | sync_priority | sync_state
------------------+-----------+---------------+------------
 14/main          | streaming |             0 | async
(1 row)

postgres=# select pid,state,client_addr,sync_priority,sync_state from pg_stat_replication;
  pid  |   state   |  client_addr   | sync_priority | sync_state
-------+-----------+----------------+---------------+------------
 84941 | streaming | 10.0.0.3       |             0 | async
(1 row)

• 从数据库

$ ps aux |grep receiver
postgres    7861  0.1  0.1 229088 10568 ?        Ss   02:58   0:00 postgres: 14/main: walreceiver streaming 0/3000148