|
背景：在两个或多个部署了postgresql数据库上，进行配置流复制。或基于原来的流复制架构进行扩展备用节点。出现问题可进行手动切换主库 环境如下 postgresql 版本为11.7 master 172.16.1.11 主库 slave 172.16.1.12 备库 建议：数据库服务器之间配置ntp进行时间定期（加到定期任务crontab -e）同步，否则可能会导致数据异常 一、搭建master服务器主库master1、修改 master服务器 pg_hba.conf ,添加如下 # TYPE DATABASE USER ADDRESS METHOD # 表示允许任意网段的用户通过MD5进行认证连接 host all all 0.0.0.0/0 md5 # 表示允许该网段172.16.1.0 的repl 用户进行流复制 host replication repl 172.16.1.0/0 trust host replication all ::1/128 trust 修改master服务器 postgresqlconf 参数配置如下, 为注释 listen_addresses = '*' port = 5432 max_connections = 2000 superuser_reserved_connections = 10 wal_level = logical full_page_writes = on wal_log_hints = off archive_mode = on archive_command = '/bin/true' max_wal_senders = 50 hot_standby = on log_destination = 'csvlog' logging_collector = on log_directory = 'log' log_filename = 'postgresql-%Y-%m-%d_%H%M%S' log_rotation_age = 1d log_rotation_size = 10MB log_statement = 'mod' master服务器启动pg服务 postgres@MASTER > pg_ctl -D /data/postgresql/ start waiting for server to start....2020-09-18 15:00:21.403 CST [9609] LOG: listening on IPv4 address "0.0.0.0", port 5432 2020-09-18 15:00:21.403 CST [9609] LOG: listening on IPv6 address "::", port 5432 2020-09-18 15:00:21.405 CST [9609] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" 2020-09-18 15:00:21.412 CST [9609] LOG: redirecting log output to logging collector process 2020-09-18 15:00:21.412 CST [9609] HINT: Future log output will appear in directory "log". done server started master服务器修改默认用户 postgres 的密码和创建用于 流复制的用户 repl postgres=# ALTER USER postgres WITH PASSWORD 'p@ssw0rd'; ALTER ROLE postgres=# CREATE USER repl WITH PASSWORD 'p@ssw0rd' REPLICATION; CREATE ROLE postgres=# \du List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} repl | Replication | {} 创建使用的测试数据库 pgpool 和 表 pgpool postgres=# CREATE DATABASE pgpool ; CREATE DATABASE postgres=# \c pgpool You are now connected to database "pgpool" as user "postgres". pgpool=# pgpool=# CREATE TABLE pgpool (id serial,age bigint,insertTime timestamp default now()); CREATE TABLE pgpool=# insert into pgpool (age) values (1); INSERT 0 1 pgpool=# select * from pgpool; id | age | inserttime ----+-----+---------------------------- 1 | 1 | 2020-09-18 15:07:03.329849 (1 row) 查看数据库是否为主库f 表示为主库 postgres=# select * from pg_is_in_recovery(); pg_is_in_recovery ------------------- f (1 row) 至此master服务器主库搭建完毕 二、搭建slave服务器备库 slaveslave服务器创建数据库 data 目录 postgres@SLAVE:/date> mkdir postgresql slave服务器使用 pg_basebackup 命令在线创建一个备库,使用该命令请确保 主库已经启动 postgres@SLAVE:/data/postgresql> pg_basebackup -h 172.16.1.11 -p 5432 -U repl -w -Fp -Xs -Pv -R -D /data/postgresql/ pg_basebackup: initiating base backup, waiting for checkpoint to complete pg_basebackup: checkpoint completed pg_basebackup: write-ahead log start point: 0/2000028 on timeline 1 pg_basebackup: starting background WAL receiver 31133/31133 kB (100%), 1/1 tablespace pg_basebackup: write-ahead log end point: 0/20000F8 pg_basebackup: waiting for background process to finish streaming ... pg_basebackup: base backup completed 参数说明 -h 启动的主库数据库地址 -p 主库数据库端口 -U 流复制用户 -w 不使用密码验证 -Fp 备份输出正常的数据库目录 -Xs 使用流复制的方式进行复制 -Pv 输出复制过程的详细信息 -R 为备库创建recovery.conf文件 -D 指定创建的备库的数据库目录 slave服务器 在 recovery.conf 添加 application_name 为slave,配置如下 注：12版本以上无须此操作（且无此文件），默认使用IP做为名称 postgres@SLAVE:/data/postgresql> vim recovery.conf standby_mode = 'on' primary_conninfo = 'application_name=slave user=repl passfile=''/home/postgres/.pgpass'' host=172.16.1.11 port=5432 sslmode=disable sslcompression=1 target_session_attrs=any' slave服务器修改postgresql.conf参数如下 max_connections = 2000 # 允许的最大数据库连接数 max_wal_senders = 100 # 该参数需要大于主库，否则可能导致备库无法读操作 slave服务器分配700 给数据库data目录 postgres@SLAVE:/data/postgresql> chmod 700 /data/postgresql/ slave服务器启动备库 postgres@SLAVE:/data/postgresql> pg_ctl -D /data/postgresql/ start waiting for server to start....2020-09-18 15:20:19.966 CST [23907] LOG: listening on IPv4 address "0.0.0.0", port 5432 2020-09-18 15:20:19.966 CST [23907] LOG: listening on IPv6 address "::", port 5432 2020-09-18 15:20:19.970 CST [23907] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" 2020-09-18 15:20:20.007 CST [23907] LOG: redirecting log output to logging collector process 2020-09-18 15:20:20.007 CST [23907] HINT: Future log output will appear in directory "log". done server started slave服务器连接数据库pgpool，查看数据是否同步 postgres@SLAVE:/data/postgresql> psql -h 172.16.1.12 -p 5432 -U postgres pgpool Password for user postgres: psql (10.3) Type "help" for help. pgpool=# \dt List of relations Schema | Name | Type | Owner --------+--------+-------+---------- public | pgpool | table | postgres (1 row) pgpool=# select * from pgpool; id | age | inserttime ----+-----+---------------------------- 1 | 1 | 2020-09-18 15:07:03.329849 (1 row) pgpool=# 参数说明 -h 备库数据库服务器地址 -p 备库数据库端口 -U 连接数据库的用户 slave服务器查看数据库是否为备库，t 表示为备库 pgpool=# select * from pg_is_in_recovery(); pg_is_in_recovery ------------------- t (1 row) master服务器查看数据库的状态 postgres=# select client_addr,usename,backend_start,application_name,sync_state,sync_priority FROM pg_stat_replication; client_addr | usename | backend_start | application_name | sync_state | sync_priority -------------+---------+-------------------------------+------------------+------------+--------------- 172.16.1.12 | repl | 2020-09-18 15:20:20.066431+08 | slave | async | 0 (1 row) postgres=# 参数说明 client_addr：备库服务器的地址 usename：使用的流复制用户 backend_start：流复制开始的时间 application_name：备库的名称 sync_state：备库与主库的同步状态 sync_priority：备库与主库变成同步状态的优先级 至此slave服务器备库slave搭建完毕 三、故障迁移思路1、由于流复制为主备架构，默认只有主库才会写入数据，可通过命令把备库改为主库进行写入数据 在备库执行后，可在备库写入数据 [postgres@slave ~]#/postgresql/bin/pg_ctl promote -D /data/postgresql 思路：2台服务器配置流复制，通过pgpool或者keepalive进行VIP迁移。脚本通过测试连接VIP进入数据库，当出现无法连接时，主库把VIP关闭，备库执行上述命令，进行写入就数据，使得备库可写入数据，并启用VIP；
技术交流欢迎加入Q群：177428068
| 
