PostgreSQL 介绍

发布时间: 更新时间: 总字数:1043 阅读时间:3m 作者: 分享 复制网址

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

介绍

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

部署

docker

集群

版本环境

  • 服务器系统: 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

参考

  1. 时空数据库实践(含 纽约TAXI数据透视分析) - PostGIS + TimescaleDB => PostgreSQL
  2. PostgresSQL+postgis+timescaledb 集群搭建部署图
Home Archives Categories Tags Statistics
本文总阅读量 次 本站总访问量 次 本站总访客数