（Linux）Postgres10版本主从复制实现数据迁移

操作系统：Centos7.4
主节点：docker-slave-79101
从节点：docker-slave-79102
安装包：postgres-10.3

注意主从节点的安装路径要保持一致!

主节点操作

[root@docker-slave-79101 ~]# ll
total 95280
-rw-------. 1 root root     1831 Jul 16 16:54 anaconda-ks.cfg
drwxr-xr-x. 3 root root      107 Aug  3 11:36 Postgres-10.12.1v-20200803
-rw-r--r--. 1 root root 47562657 Aug  3 11:36 Postgres-10.12.1v-20200803.tar.gz
drwxr-xr-x. 2 root root      100 Aug  3 11:25 Postgres-12.3v
[root@docker-slave-79101 ~]# cd Postgres-10.12.1v-20200803/
[root@docker-slave-79101 Postgres-10.12.1v-20200803]# sh install.sh 
输入程序安装路径:
        eg: /opt/postgres
        /opt/pgsql
/opt/pgsql
输入数据存放路径:
        eg: /data/12
        /PG/10
/PG/10
postgres 用户不存在，现在增加

 ====================================================
 =               安装成功 !!!                 =
 ====================================================
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /PG/10 ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default timezone ... PRC
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D /PG/10 -l logfile start
 ====================================================== 
su - postgres
启动
       pg_ctl -D /PG/10 start
       
[root@docker-slave-79101 Postgres-10.12.1v-20200803]# su - postgres
Last login: Mon Aug  3 14:39:33 CST 2020 on pts/2
[postgres@docker-slave-79101 ~]$ pg_ctl -D /PG/10 start
[postgres@docker-slave-79101 ~]$ pg_ctl -D /PG/10 status
pg_ctl: server is running (PID: 25359)
/opt/pgsql/bin/postgres "-D" "/PG/10"
[postgres@docker-slave-79101 10]$ psql
psql (10.13)
Type "help" for help.

postgres=# create user believer superuser password 'believer'; 
CREATE ROLE
postgres=# \q
[postgres@docker-slave-79101 10]$ vim postgresql.conf 
[root@docker-slave-79101 Postgres-10.12.1v-20200803]# 
主节点修改配置：
#vim postgresql.conf
listen_addresses = '*'
max_connections = 1000
#shared_buffer = 512MB
wal_level = hot_standby
synchronous_commit = on
checkpoint_timeout = 5min
archive_mode = on
archive_command = 'test ! -f /PG/10/archivedir/%f && cp %p /PG/10/archivedir/%f'
max_wal_senders = 10
wal_keep_segments = 16
hot_standby = on
max_standby_archive_delay = 30s
max_standby_streaming_delay = 30s
wal_receiver_status_interval = 1s
hot_standby_feedback = on

[postgres@docker-slave-79101 10]$ vim pg_hba.conf 
#vim pg_hba.conf
#修改成：

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
#host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                    trust
host     replication    believer          192.168.79.0/24       md5
host    replication     all               127.0.0.1/32          trust
host    replication     believer          192.168.79.0/24       trust
#host    replication     all             ::1/128                trust

[postgres@docker-slave-79101 10]$ mkdir -p /PG/10/archivedir/
[postgres@docker-slave-79101 10]$ pg_ctl -D /PG/10 restart

从节点

[root@docker-slave-79102 Postgres-10.12.1v-20200803]# sh install.sh
[root@docker-slave-79102 Postgres-10.12.1v-20200803]# cd /PG
[root@docker-slave-79102 PG]# ll
total 4
drwx------. 19 postgres postgres 4096 Aug  4 01:07 10
[root@docker-slave-79102 PG]# mv 10/ 10.bak20200803
[root@docker-slave-79102 PG]# mkdir 10
[root@docker-slave-79102 PG]# chown postgres.postgres /PG -R
[root@docker-slave-79102 PG]#su - postgres
[postgres@docker-slave-79102 10]$ pg_basebackup -h 192.168.79.101 -p 5432 -U believer -Fp -Xs -Pv -R -D /PG/10
输入密码：believer
[postgres@docker-slave-79102 10]$ vim postgresql.conf
#改成以下配置：
listen_addresses = 'localhost'
max_connections = 1100
hot_standby = on
max_standby_streaming_delay = 30s
wal_receiver_status_interval = 10s
hot_standby_feedback = on
wal_receiver_timeout = 60s
#--------------------------------
[postgres@docker-slave-79102 10]$ vim pg_hba.conf
#改成以下配置：
# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
#host    all             all             ::1/128                trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host     replication    believer          192.168.79.101/24      md5
host    replication     all               127.0.0.1/32          trust
#host    replication     believer          192.168.79.0/24      trust
#host    replication     all             ::1/128                trust

#启动服务
[postgres@docker-slave-79102 10]$ pg_ctl -D /PG/10/ start
waiting for server to start....2020-08-04 01:15:01.851 CST [20629] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2020-08-04 01:15:01.852 CST [20629] LOG:  listening on IPv6 address "::", port 5432
2020-08-04 01:15:01.855 CST [20629] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2020-08-04 01:15:01.894 CST [20630] LOG:  database system was interrupted; last known up at 2020-08-03 17:09:28 CST
2020-08-04 01:15:01.904 CST [20630] LOG:  entering standby mode
2020-08-04 01:15:01.906 CST [20630] LOG:  redo starts at 0/8000028
2020-08-04 01:15:01.909 CST [20630] LOG:  consistent recovery state reached at 0/80000F8
2020-08-04 01:15:01.910 CST [20629] LOG:  database system is ready to accept read only connections
2020-08-04 01:15:01.919 CST [20634] LOG:  started streaming WAL from primary at 0/9000000 on timeline 1
 done
server started

出现如图，说明主从模式开启：

若启动失败是没有权限的问题，解决方法如图：

主从切换：

- 待数据传输完成之后,先将recovery.conf文件重命名为recovery.conf.bak；

- 重启从节点数据库
–停止主节点数据库

补充：

主从节点存活的情况下，主节点能读写，从节点只能读，不能写。