PostgreSQL主备库搭建

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

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

# 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

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;

# 添加从库网段
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 段

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必须要大于主库的

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

# 声明从库
standby_mode = on

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

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