Timescaledb异步流复制标准步骤

Timescaledb环境信息

配置流复制步骤

3.1 异步流

3.1.1主库创建流复制用户

CREATE USER repuser

REPLICATION

LOGIN

CONNECTION LIMIT 10

ENCRYPTED PASSWORD 'rep123456';

3.1.2主库配置pg_hba.conf

-bash-4.2$ cat pg_hba.conf |grep -v "^$"|grep -v "#"

local   all             all                                     peer

host    all             all             127.0.0.1/32            ident

host    all             all             ::1/128                 ident

local   replication     all                                     peer

host    replication     all             127.0.0.1/32            ident

host    replication     all             ::1/128                 ident

host    all             all             all                     md5

host    replication     postgres          all                   trust

-bash-4.2$

trust：

无条件地允许连接。这种方法允许任何可以与PostgreSQL数据库服务器连接的用户以他们期望的任意PostgreSQL数据库用户身份登入，而不需要口令或者其他任何认证。用于备份命令pg_basebackup

md5

要求客户端提供一个双重 MD5 加密的口令进行认证。#用于postgres远程登陆认证

psql -h 192.168.111.136 -p 5432 -U postgres

3.1.3主备库配置.pgpass

-bash-4.2$ cat ~/.pgpass

192.168.111.136:5432:replication:repuser:rep123456

192.168.111.134:5432:replication:repuser:rep123456

-bash-4.2$

#其它方式

postgresql.auto.conf 与

[root@vm-128-7-centos data]# cat postgresql.auto.conf

# Do not edit this file manually!

# It will be overwritten by the ALTER SYSTEM command.

recovery_target_timeline = 'latest'

primary_conninfo = 'host=172.16.128.26 port=5433 user=postgres'

[root@vm-128-7-centos data]#

3.1.4备份主库

pg_basebackup  /app/timescaledb/data1 -Fp -Xs -v -P -h 192.168.111.136 -p 5432 -U postgres

-h 启动的主库数据库地址

-p 主库数据库端口

-U 流复制用户

-W 使用密码验证，要用replica的密码

-Fp 备份输出正常的数据库目录

-Xs 使用流复制的方式进行复制

-Pv 输出复制过程的详细信息

-R 为备库创建recovery.conf文件。但是pgsql 10以后的新版本的pgsql不需要这个文件了。

-D 指定创建的备库的数据库目录

#提示：如果是纯净环境，直接备份data目录到目标服务器即可

3.1.5启动备库

$pg_ctl start

$psql

alter system set recovery_target_timeline = 'latest';

alter system set primary_conninfo = 'host=192.168.111.136 port=5432 user=repuser';

$pg_ctl stop

$pg_ctl start

验证查看WAL 接收进程

$ps –ef|grep walreceiver

[root@node7 timescaledb]# ps -ef|grep walreceiver

postgres  67593  67585  0 17:07 ?        00:00:04 postgres: walreceiver   streaming 0/3003EA0

root      71225  55866  0 17:58 pts/1    00:00:00 grep --color=auto walreceiver

[root@node7 timescaledb]#

3.1.6主库查看同步状态

主库查询：

select * from pg_stat_replication;

postgres=# select * from pg_stat_replication;

 pid  | usesysid | usename  | application_name |   client_addr   | client_hostname | client_port |         backend_start         | backend_xmin |   state   | sent_lsn  | w

rite_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state |          reply_time          

------+----------+----------+------------------+-----------------+-----------------+-------------+-------------------------------+--------------+-----------+-----------+--

---------+-----------+------------+-----------+-----------+------------+---------------+------------+-------------------------------

 5643 |       10 | postgres | walreceiver      | 192.168.111.134 |                 |       60305 | 2023-08-03 17:07:01.652445+08 |              | streaming | 0/3003EA0 | 0

/3003EA0 | 0/3003EA0 | 0/3003EA0  |           |           |            |             0 | async      | 2023-08-03 18:01:09.341432+08

(1 行记录)

postgres=#

备库查询：

select * from pg_stat_wal_receiver ;

postgres=# select * from pg_stat_wal_receiver ;

  pid  |  status   | receive_start_lsn | receive_start_tli | received_lsn | received_tli |      last_msg_send_time       |     last_msg_receipt_time     | latest_end_lsn |

        latest_end_time        | slot_name |   sender_host   | sender_port |                                                                                              

                conninfo                                                                                                              

-------+-----------+-------------------+-------------------+--------------+--------------+-------------------------------+-------------------------------+----------------+

-------------------------------+-----------+-----------------+-------------+-----------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------------------------

 67593 | streaming | 0/3000000         |                 1 | 0/3003EA0    |            1 | 2023-08-03 18:00:59.335263+08 | 2023-08-03 18:00:59.337494+08 | 0/3003EA0      |

 2023-08-03 17:33:56.210656+08 |           | 192.168.111.136 |        5432 | user=postgres passfile=/var/lib/pgsql/.pgpass dbname=replication host=192.168.111.136 port=543

2 fallback_application_name=walreceiver sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any

(1 行记录)

postgres=#

主库查询同步延迟：【自需要做个监控，防止同步挂掉】

postgres=# select

postgres-# pid,

postgres-# usename,

postgres-# client_addr,

postgres-# state,

postgres-# pg_wal_lsn_diff(pg_current_wal_lsn(),write_lsn) write_delay,

postgres-# pg_wal_lsn_diff(pg_current_wal_lsn(),flush_lsn) flush_delay,

postgres-# pg_wal_lsn_diff(pg_current_wal_lsn(),replay_lsn) replay_delay

postgres-# from pg_stat_replication;

 pid  | usename  |   client_addr   |   state   | write_delay | flush_delay | replay_delay

------+----------+-----------------+-----------+-------------+-------------+--------------

 5643 | postgres | 192.168.111.134 | streaming |           0 |           0 |            0

(1 行记录)

postgres=# te from pg_stat_replication;

postgres=# select client_addr,sync_state from pg_stat_replication;

   client_addr   | sync_state

-----------------+------------

 192.168.111.134 | async

(1 行记录)

3.1.7延迟备库

$psql

alter system set alter system set recovery_min_apply_delay=60000;

--延迟1 分钟，默认（如果要求时效性可修改30s）

postgres=#

3.1.8 同步验证

#slave（主从模式从只能读，除非做主从切换）

postgres=# CREATE DATABASE slave;

错误:  不能在一个只读模式的事务中执行CREATE DATABASE

postgres=#

CREATE DATABASE zabbix;

#创建数据库并授权

postgres=# CREATE USER artifactory WITH PASSWORD 'renzhiyuan';

postgres=# CREATE DATABASE artifactory WITH OWNER=artifactory ENCODING='UTF8';

postgres=# GRANT ALL PRIVILEGES ON DATABASE artifactory TO artifactory;

postgres=# select datname from pg_database;

postgres=# select datname from pg_database;

   datname  

-------------

 postgres

 zabbix

 template1

 template0

 artifactory

(5 行记录)

postgres=#

3.2 同步流（不修改暂时）

主库修改参数

$psql

alter system set synchronous_standby_names = slave1;

$pg_ctl reload

主库验证：

select * from pg_stat_repliaction;

-- sync_state 字段为sync 说明是同步流。

主配置文件demo

#测试环境pgconf

listen_addresses = '*'

max_wal_size = 1GB

min_wal_size = 512MB

effective_cache_size = 24008MB

log_timezone = 'PRC'

datestyle = 'iso, mdy'

timezone = 'PRC'

default_text_search_config = 'pg_catalog.english'

timescaledb.telemetry_level=basic

timescaledb.max_background_workers = 8

timescaledb.last_tuned = '2023-05-18T18:52:30+08:00'

timescaledb.last_tuned_version = '0.9.0'

#测试hba

[root@test-tengxun-apm-app01 pg12data]# cat pg_hba.conf |grep -v "#"|grep -v "^$"

local   all             all                                     trust

host    all             all             127.0.0.1/32            trust

host    all             all             ::1/128                 trust

local   replication     all                                     trust

host    replication     all             127.0.0.1/32            trust

host    replication     all             ::1/128                 trust

host    replication     postgres        all                     trust

host all all all md5

[root@test-tengxun-apm-app01 pg12data]#

主从复制相关参数优化：

# 最多有16个流复制连接。

max_wal_senders = 16

wal_keep_segments =

max_connections =

autovacuum_max_workers =

max_worker_processes =

max_logical_replication_workers =