Postgres-12版本数据库主从模式部署实现数据迁移

背景:
公司要求要进行数据库迁移,但是目前已有大量用户使用系统,采用之前方案:
PG数据库备份和迁移,对用户和开发人员影响比较大,于是采用主从模式进行数据迁移。

该方案也是采用三步骤的方式:

  1. 部署数据库
  2. 主从数据库配置文件修改
  3. 主从数据库切换

环境说明:
操作系统:Centos7.4 x86
主数据库:docker-slave-79101 (主节点可读和写)
从数据库:docker-slave-79102(只能读)

部署数据库

(请参照:PG数据库部署

主节点操作

- 创建具有复制操作的数据库用户
[postgres@docker-slave-79101 12]$ psql
psql (12.3)
Type “help” for help.

postgres=# CREATE ROLE rsyn login replication encrypted password ‘rsyn’;
CREATE ROLE
postgres=# \q

- 修改配置文件:pg_hba.conf
增加两行:
#添加信任的从服务器

host    all             rsyn            192.168.79.0/24      trust
host    replication     rsyn            192.168.79.0/24      md5

- 修改配置文件:postgresql.conf

 # 监听所有IP
listen_addresses = '*' 
#最大连接数,从数据库要大于主数据
max_connections = 100
#开启归档
archive_mode = on
#归档文件存放路径 
archive_command = 'cp %p /PG/12/pg_archive/%f'
wal_level = replica 
wal_keep_segments = 32
 #超时设置为60s
wal_sender_timeout = 60s

说明:
wal_level 指定写入到wal中的信息,默认是minimal,只写从crash中恢复或者快速shutdown需要的信息。
#replica 是在minimal的基础上添加wal archiving需要的信息。
#logical 增加逻辑编码需要的信息。minimal wal不包含从base backup和wal log重新构建数据库数据的信息。replica或者logical可以。老版本的参数archive或者hot_standby 在这里被映射到replica模式

  • 修改postgres数据的密码
[postgres@docker-slave-79101 ~]$ psql
psql (12.3)
Type "help" for help.

postgres=# alter user postgres with password 'postgres';
ALTER ROLE
postgres=# select * from pg_shadow;
 usename  | usesysid | usecreatedb | usesuper | userepl | usebypassrls |               passwd                | valuntil | useconfig 
----------+----------+-------------+----------+---------+--------------+-------------------------------------+----------+-----------
 postgres |       10 | t           | t        | t       | t            | md53175bce1d3201d16594cebf9d7eb3f9d |          | 

- 重启数据库

 su - postgres -c "pg_ctl -D /PG/12/ restart"

从节点上操作

- 从主节点上获取数据

pg_basebackup -h 192.168.79.101 -p 5432 -U rsyn -Fp -Xs -Pv -R -D /PG/12
  • -h –指定作为主服务器的主机。
    -D –指定数据目录。
    -U –指定连接用户。
    -P –启用进度报告。
    -v –启用详细模式。
    -R–启用恢复配置的创建:创建一个standby.signal文件,并将连接设置附加到数据目录下的postgresql.auto.conf。
    -X–用于在备份中包括所需的预写日志文件(WAL文件)。流的值表示在创建备份时流式传输WAL。
    -C –在开始备份之前,允许创建由-S选项命名的复制插槽。
    -S –指定复制插槽名称。

- 修改配置文件:standby.signal和postgresql.conf

[postgres@docker-slave-79102 12]$ vim standby.signal 
[postgres@docker-slave-79102 12]$ cat standby.signal 
standby_mode = 'on'

[postgres@docker-slave-79102 12]$ vim postgresql.conf
#增加以下配置(/搜索关键词,进行修改,避免配置重复):
primary_conninfo = 'host=192.168.79.101 port=5432 user=rsyn password=rsyn'
recovery_target_timeline = latest # 同步最新的数据
#从节点连接数要大于主节点
max_connections = 500 
hot_standby = on
max_standby_streaming_delay = 30s
wal_receiver_status_interval = 10s
hot_standby_feedback = on

#启动数据库
[postgres@docker-slave-79102 12]$ pg_ctl start -D /PG/12 
waiting for server to start....2020-08-02 17:18:38.773 CST [13105] LOG:  starting PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
2020-08-02 17:18:38.774 CST [13105] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2020-08-02 17:18:38.774 CST [13105] LOG:  listening on IPv6 address "::", port 5432
2020-08-02 17:18:38.787 CST [13105] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2020-08-02 17:18:38.819 CST [13105] LOG:  redirecting log output to logging collector process
2020-08-02 17:18:38.819 CST [13105] HINT:  Future log output will appear in directory "log".

验证:

主节点:

[postgres@docker-slave-79101 12]$ psql
psql (12.3)
Type "help" for help.

postgres=# select client_addr,sync_state from pg_stat_replication;
  client_addr   | sync_state 
----------------+------------
 192.168.79.102 | async
(1 row)

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 smart     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

postgres=# create database test
postgres-# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 smart     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

postgres=# create database test;
CREATE DATABASE
postgres=# 
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 smart     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 test      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(5 rows)

postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 smart     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 test      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(5 rows)

test=# CREATE TABLE Person(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   TALL           CHAR(50),
   WEIGHT         REAL
);
CREATE TABLE
test=# INSERT INTO Person(  ID, NAME, AGE, TALL, WEIGHT)  
test-# VALUES(2,'张三',18,185,60);
ERROR:  syntax error at or near ",18,185"
LINE 2: VALUES(2,'张三',18,185,60);
                       ^
test=# INSERT INTO Person(  ID, NAME, AGE, TALL, WEIGHT)  
VALUES(2,'张三',18,185, 60);
ERROR:  syntax error at or near ",18,185"
LINE 2: VALUES(2,'张三',18,185, 60);
                       ^
test=# INSERT INTO Person(  ID, NAME, AGE, TALL, WEIGHT)  
VALUES(2,'张三',18,185, 60);
INSERT 0 1
test=# 
test=# 
test=# select * from Person;
 id | name | age |                        tall                        | weight 
----+------+-----+----------------------------------------------------+--------
  2 | 张三 |  18 | 185                                                |     60
(1 row)

test=# INSERT INTO Person(  ID, NAME, AGE, TALL, WEIGHT)  
test-# VALUES(2,'李四',18,185, 60);
ERROR:  duplicate key value violates unique constraint "person_pkey"
DETAIL:  Key (id)=(2) already exists.
test=# INSERT INTO Person(  ID, NAME, AGE, TALL, WEIGHT)  
VALUES(3,'李四',18,185, 60);
INSERT 0 1
test=# select * from Person;
 id | name | age |                        tall                        | weight 
----+------+-----+----------------------------------------------------+--------
  2 | 张三 |  18 | 185                                                |     60
  3 | 李四 |  18 | 185                                                |     60
(2 rows)

test=# select * from Person;
 id | name | age |                        tall                        | weight 
----+------+-----+----------------------------------------------------+--------
  2 | 张三 |  18 | 185                                                |     60
  3 | 李四 |  18 | 185                                                |     60
(2 rows)

test=# \du Person
           List of roles
 Role name | Attributes | Member of 
-----------+------------+-----------

test=# \du Person;
           List of roles
 Role name | Attributes | Member of 
-----------+------------+-----------

test=# \tPerson;
invalid command \tPerson;
Try \? for help.
test=# \t Person;
unrecognized value "Person" for "tuples_only": Boolean expected
test=# 
test=# 
test=# 
test=# 
test=# select * from Person;
 id | name | age |                        tall                        | weight 
----+------+-----+----------------------------------------------------+--------
  2 | 张三 |  18 | 185                                                |     60
  3 | 李四 |  18 | 185                                                |     60
(2 rows)

test=# INSERT INTO Person(  ID, NAME, AGE, TALL, WEIGHT)  
test-# VALUES(1,'王五',18,'185cm','60');
INSERT 0 1
test=# select * from Person;
 id | name | age |                        tall                        | weight 
----+------+-----+----------------------------------------------------+--------
  2 | 张三 |  18 | 185                                                |     60
  3 | 李四 |  18 | 185                                                |     60
  1 | 王五 |  18 | 185cm                                              |     60
(3 rows)

test=# 
test=# select * from Person;
 id | name | age |                        tall                        | weight 
----+------+-----+----------------------------------------------------+--------
  2 | 张三 |  18 | 185                                                |     60
  3 | 李四 |  18 | 185                                                |     60
  1 | 王五 |  18 | 185cm                                              |     60
(3 rows)

test=# 

从节点

[postgres@docker-slave-79102 12]$ ps -ef | grep postgres
root      12177   1820  0 16:14 pts/1    00:00:00 su - postgres
postgres  12178  12177  0 16:14 pts/1    00:00:01 -bash
postgres  13105      1  0 17:18 ?        00:00:00 /opt/postgres/bin/postgres -D /PG/12
postgres  13106  13105  0 17:18 ?        00:00:00 postgres: logger   
postgres  13107  13105  0 17:18 ?        00:00:00 postgres: startup   recovering 00000001000000000000000D
postgres  13108  13105  0 17:18 ?        00:00:00 postgres: checkpointer   
postgres  13109  13105  0 17:18 ?        00:00:00 postgres: background writer   
postgres  13110  13105  0 17:18 ?        00:00:00 postgres: stats collector   
postgres  13111  13105  1 17:18 ?        00:00:00 postgres: walreceiver   streaming 0/D000148
postgres  13112  12178  0 17:18 pts/1    00:00:00 ps -ef
postgres  13113  12178  0 17:18 pts/1    00:00:00 grep --color=auto postgres
[postgres@docker-slave-79102 12]$ 
[postgres@docker-slave-79102 12]$ vim pg_hba.conf 
[postgres@docker-slave-79102 12]$ pg_ctl reload -D /PG/12 
server signaled
[postgres@docker-slave-79102 12]$ psql
WARNING: password file "/home/postgres/.pgpass" has group or world access; permissions should be u=rw (0600) or less
psql (12.3)
Type "help" for help.

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 smart     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 test      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(5 rows)

postgres=# lc test
postgres-# \c test
WARNING: password file "/home/postgres/.pgpass" has group or world access; permissions should be u=rw (0600) or less
You are now connected to database "test" as user "postgres".
test-# INSERT INTO Person(  ID, NAME, AGE, TALL, WEIGHT)  
test-# VALUES(2,'李四',18,185, 60);
ERROR:  syntax error at or near "lc"
LINE 1: lc test
        ^
test=# INSERT INTO Person(  ID, NAME, AGE, TALL, WEIGHT)  
VALUES(4,'李四',18,185, 60);
ERROR:  cannot execute INSERT in a read-only transaction
test=# \c test
WARNING: password file "/home/postgres/.pgpass" has group or world access; permissions should be u=rw (0600) or less
You are now connected to database "test" as user "postgres".
test=# 
test=# 
test=# 
test=# select * from Person;
 id | name | age |                        tall                        | weight 
----+------+-----+----------------------------------------------------+--------
  2 | 张三 |  18 | 185                                                |     60
  3 | 李四 |  18 | 185                                                |     60
(2 rows)

test=# select * from Person;
 id | name | age |                        tall                        | weight 
----+------+-----+----------------------------------------------------+--------
  2 | 张三 |  18 | 185                                                |     60
  3 | 李四 |  18 | 185                                                |     60
(2 rows)

test=# 
test=# select * from Person;
 id | name | age |                        tall                        | weight 
----+------+-----+----------------------------------------------------+--------
  2 | 张三 |  18 | 185                                                |     60
  3 | 李四 |  18 | 185                                                |     60
(2 rows)

test=# INSERT INTO Person(  ID, NAME, AGE, TALL, WEIGHT)  
VALUES(1,'王五',18,'185cm','60');
ERROR:  cannot execute INSERT in a read-only transaction

主从数据库切换

1、主库停止
在这里插入图片描述
2、从库状态查看
在这里插入图片描述
在这里插入图片描述
切换成功,即实现数据迁移!!!

原来主节点配置从节点,从节点配置成主节点,实现读写分离,将在下一篇博文记录~~

©️2020 CSDN 皮肤主题: 黑客帝国 设计师:上身试试 返回首页