PostgreSQL主备库搭建

2022年10月11日 341点热度 0人点赞 0条评论

pg主备库的搭建,首先需在2个节点安装pg软件,然后依次在2个节点配置主备。
本文采用os为CentOS7.6,pg版本使用14.2,以下为详细部署步骤。
本文两个节点的ip地址如下:

[root@node1:0 ~]# cat /etc/hosts
#CentOS Linux release 7.6.1810 (Core)
192.168.222.11 node1
192.168.222.12 node2

■■■ 安装、配置、启动

■■ 官网下载安装【不推荐采用此方式安装】

■ Install the repository RPM

yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

■ Install PostgreSQL

yum install -y postgresql14-server

■ Optionally initialize the database and enable automatic start

/usr/pgsql-14/bin/postgresql-14-setup initdb
systemctl start postgresql-14

■ Stop and uninstall

systemctl stop postgresql-14
yum remove -y postgresql
yum remove -y pgdg

■■ 编译安装【推荐采用此方式安装】

可参考pg中文社区
cd /u01/pg
wget https://ftp.postgresql.org/pub/source/v14.2/postgresql-14.2.tar.bz2 --no-check-certificate
tar xvfj postgresql*.bz2
cd postgresql-14.2
./configure --prefix=/u01/pg/pgsql
此时如果缺少readline,则yum install -y readline-devel
mkdir -p /u01/pg/pgsql
make install-world

■■ 配置

adduser postgres
passwd postgres
mkdir /u01/pg/pgsql/data
chown -R postgres:postgres /u01/pg
su - postgres

■ 修改默认数据路径

export PGDATA=/u01/pg/pgsql/data
export PATH=/u01/pg/pgsql/bin:$PATH

■ 初始化数据库

initdb

■ 修改其他默认配置

查看配置文件:
grep -Pv "^#|^$|^\t" $PGDATA/postgresql.conf

vim $PGDATA/postgresql.conf

# 默认监听在127.0.0.1
#listen_addresses = 'localhost'         # what IP address(es) to listen on;
# 配置监听在任意ip
listen_addresses = '*'
# 打开log收集
logging_collector = on
注:日志文件默认在log目录下

vim $PGDATA/pg_hba.conf

# IPv4 local connections:
# 默认配置,只允许本机访问
host    all             all             127.0.0.1/32            trust
# 允许所有ip访问
host    all             all             0.0.0.0/0               trust
# 允许指定网段访问
host    all             all             192.168.222.0/24        trust

■ 启停数据库

pg_ctl status
pg_ctl start
pg_ctl stop

■ 客户端工具使用开源的pgAdmin

Set Master Password: postgres

■■■ 主从配置

■■ 主节点

■ 创建用于主从访问的用户, 修改postgres用户的密码,用于远程登录

su - postgres

psql
# 创建 postgres 密码
ALTER USER postgres WITH PASSWORD 'postgres';
# 创建 从库 replica 用户密码
CREATE ROLE replica login replication encrypted password 'replica';
# 检查账号
SELECT usename from pg_user;
SELECT rolname from pg_roles;

■ 修改 pg_hba.conf 配置

vim $PGDATA/pg_hba.conf

# 添加从库网段
host    all             all             0.0.0.0/0               trust
# replication privilege.
local   replication     all                                     peer
host    replication     replica         192.168.222.12/24       md5
注意此处 192.168.222.12/24 需修改为从库的 IP 段

■ 修改 postgresql.conf 配置
vim $PGDATA/postgresql.conf

listen_addresses = '*'
wal_level = hot_standby
synchronous_commit = remote_write
# synchronous_commit 参考文档可选其他 on
max_wal_senders = 32     #同步最大的进程数量
wal_sender_timeout = 60s #流复制主机发送数据的超时时间
max_connections = 100    #最大连接数,从库的max_connections必须要大于主库的

■■ 从节点

■ 从主库同步数据

pg_basebackup -D $PGDATA -h node1 -p 5432 -U replica -X stream -P
注:从库无需初始化

■ 修改 postgresql.conf 配置

从 PostgreSQL 12 开始已移除了 recovery.conf 文件,相关配置合并到了 postgresql.conf 中,由于从主库同步数据库,其中配置也需要移除和修改
vim $PGDATA/postgresql.conf

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

■ 创建 standby.signal

创建 standby.signal 文件,声明从库。
vim $PGDATA/standby.signal

# 声明从库
standby_mode = on

■ 确认数据目录权限,避免踩坑

chown -R postgres.postgres $PGDATA

■ 启动

pg_ctl start

■■ 确认同步

■ 主库查看

ps aux |grep sender
# 返回 postgres: walsender replica 192.168.222.12(64218) streaming 3/27000148

select application_name, state, sync_priority, sync_state from pg_stat_replication;
 application_name |   state   | sync_priority | sync_state 
------------------+-----------+---------------+------------
 walreceiver      | streaming |             0 | async

select pid,state,client_addr,sync_priority,sync_state from pg_stat_replication;
  pid  |   state   |  client_addr   | sync_priority | sync_state 
-------+-----------+----------------+---------------+------------
 33328 | streaming | 192.168.222.12 |             0 | async

■ 从库查看

ps aux |grep receiver
# 返回 postgres: walreceiver streaming 3/27000148

■■■ 监控

■■ Sampler

Sampler 是一个用于 shell 命令执行、可视化和警报的工具,配置了一个简单的 YAML 文件。

wget https://github.com/sqshq/sampler/releases/download/v1.1.0/sampler-1.1.0-linux-amd64 -O sampler
chmod +x sampler

■ 在yaml文件中定义shell命令

cat > config.yml <<-'EOF'
variables:
    PGPASSWORD: postgres
    postgres_connection: psql -h localhost -U postgres --no-align --tuples-only
runcharts:
  - title: Data write(Byte)
    position: [[0, 8], [20, 12]]
    rate-ms: 500
    legend:
        enabled: true
        details: false
    scale: 2
    items:
      - label: background writer
        color: 178
        sample: psql -At -U postgres -c "select 8 * (buffers_checkpoint + buffers_clean
            + buffers_backend)/1024 as total_writen from pg_stat_bgwriter;"
      - label: checkpoint write
        color: 162
        sample: psql -At -U postgres -c "select buffers_checkpoint * 8 / (checkpoints_timed
            + checkpoints_req) as checkpoint_write_avg from pg_stat_bgwriter"
  - title: PostgreSQL connections
    position: [[40, 8], [40, 12]]
    rate-ms: 500
    legend:
        enabled: true
        details: false
    scale: 2
    items:
      - label: active connections
        color: 178
        sample: psql -At -U postgres -c "select count(*) from pg_stat_activity where
            state = 'active' and pid <> pg_backend_pid();"
      - label: idle connections
        color: 162
        sample: psql -At -U postgres -c "select count(*) from pg_stat_activity where
            state = 'idle' and pid <> pg_backend_pid();"
      - label: idle in transaction connections
        color: 32
        sample: psql -At -U postgres -c "select count(*) from pg_stat_activity where
            state = 'idle in transaction' and pid <> pg_backend_pid();"
barcharts:
  - title: PostgreSQL Database Status
    position: [[0, 0], [40, 8]]
    rate-ms: 500
    scale: 0
    items:
      - label: tuple insert
        init: $postgres_connection
        sample: select tup_inserted from pg_stat_database where datname = current_database();
      - label: tuple delete
        init: $postgres_connection
        sample: select tup_deleted from pg_stat_database where datname = current_database();
      - label: tuple update
        init: $postgres_connection
        sample: select tup_updated from pg_stat_database where datname = current_database();
      - label: tuple fetch
        init: $postgres_connection
        sample: select tup_fetched from pg_stat_database where datname = current_database();
gauges:
  - title: PostgreSQL Database Age
    position: [[0, 32], [40, 8]]
    rate-ms: 500
    scale: 2
    color: 122
    percent-only: false
    cur:
        sample: psql -At -U postgres -c "select age(datfrozenxid) from pg_database
            where datname = current_database();"
    max:
        sample: psql -At -U postgres -c "select 210000"
    min:
        sample: psql -At -U postgres -c "select 100"
sparklines:
  - title: CPU usage
    position: [[0, 20], [40, 12]]
    rate-ms: 200
    scale: 0
    sample: ps -A -o %cpu | awk '{s+=$1} END {print s}'
  - title: PostgreSQL cache hit ratio
    position: [[40, 20], [40, 12]]
    init: $postgres_connection
    sample: select round(sum(blks_hit)*100/sum(blks_hit+blks_read),2)::numeric from
        pg_stat_database where datname = current_database();
  - title: PostgreSQL transaction commit ratio
    position: [[40, 0], [40, 8]]
    init: $postgres_connection
    sample: select round(100*(xact_commit::numeric/(case when xact_commit > 0 then
        xact_commit else 1 end + xact_rollback)),2)::numeric as commit_ratio from
        pg_stat_database where datname = current_database();
textboxes:
  - title: Server status
    position: [[20, 8], [20, 12]]
    rate-ms: 500
    sample: top -bn 1 | head -n 5
asciiboxes:
  - title: PostgreSQL Version
    position: [[40, 32], [40, 8]]
    rate-ms: 500
    color: 43
    sample: psql -At -U postgres -c "select version()"
    border: false
    font: 2d
EOF

■ 运行

/u01/pg/monitor/sampler -c /u01/pg/monitor/config.yml

liking

我是雪人

文章评论