1 总体规划

	Master库	Slave库
操作系统	CentOS Linux release 7.5.1804	CentOS Linux release 7.5.1804
处理器	1	1
内存	4G	4G
硬盘	38G	38G
主机名称	SHSNC-DB01	SHSNC-DB02
IP地址	192.168.1.61	192.168.1.62

具体安装步骤，可以查看《PostgreSQL数据库的安装》，不在本文的介绍范围内。

2 PostgreSQL主从异步流复制搭建

2.1 参数检查

检查主库postgresql.conf文件是否已经配置以下参数：

listen_addresses = '*'

port =

log_destination = 'csvlog'

logging_collector = on

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

max_connections =

max_connections =

max_connections = hot_standby

若以上参数没有配置，配置完后需要重启PostgreSQL数据库。

2.2 创建同步账号

在主库创建同步账号以及相应的数据库

$ psql -p  -U postgres postgres

psql (9.6.)

Type "help" for help.

postgres=# CREATE DATABASE pocdb;

CREATE DATABASE

postgres=# \c pocdb

You are now connected to database "pocdb" as user "postgres".

pocdb=#

pocdb=# CREATE USER repl ENCRYPTED PASSWORD '' REPLICATION;

CREATE ROLE

检查创建用户的权限：

$ psql -p  -U postgres postgres

psql (9.6.)

Type "help" for help.

postgres=# \du+

                                          List of roles

 Role name |                         Attributes                         | Member of | Description

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

 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}        |

 repl      | Replication                                                | {}        |

2.3 修改pg_hba.conf文件

在主库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/            trust

host    all             all             192.168.1.0/          trust

# IPv6 local connections:

host    all             all             ::/                 trust

# Allow replication connections from localhost, by a user with the

# replication privilege.

#local   replication     postgres                                trust

#host    replication     postgres        127.0.0.1/            trust

#host    replication     postgres        ::/                 trust

host    replication     repl     192.168.1.61/                 md5

host    replication     repl     192.168.1.62/                 md5

需要注意此处用户名repl是刚才创建同步的用户，不是postgres。

2.4 reload配置文件

reload主库配置文件：

$ pg_ctl -D /postgres/pgdata/ reload

server signaled

master配置成功后，slave 安装基本环境同 master ,区别在于 slave 从库不需要进行 initdb 初始化数据库

2.5 备库数据复制

在备库使用postgres主机账号进行操作：

$ pg_basebackup -h 192.168.1.61 -U repl -W -Fp -Pv -Xs -R -D /postgres/pgdata

Password:

pg_basebackup: initiating base backup, waiting for checkpoint to complete

pg_basebackup: checkpoint completed

transaction log start point: / on timeline

pg_basebackup: starting background WAL receiver

/ kB (%), / tablespace

transaction log end point: /20000F8

pg_basebackup: waiting for background process to finish streaming ...

pg_basebackup: base backup completed

上述表示同步成功

2.6 检查recovery配置文件

在备库检查recovery.conf配置文件

$ cat recovery.conf

standby_mode = 'on'

primary_conninfo = 'user=repl password=123456 host=192.168.1.61 port=5432 sslmode=disable sslcompression=1'

2.7 启动备库

$ pg_ctl -D /postgres/pgdata start

server starting

2.8 搭建后验证

启动后在主库创建一个数据库并在备库查看是否已同步：

主库：

$ psql

psql (9.6.)

Type "help" for help.

postgres=# create database shsnc;

CREATE DATABASE

postgres=# \list

                                  List of databases

   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges

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

 chenzxdb  | postgres | UTF8     | en_US.UTF- | en_US.UTF- |

 pocdb     | postgres | UTF8     | en_US.UTF- | en_US.UTF- |

 postgres  | postgres | UTF8     | en_US.UTF- | en_US.UTF- |

 shsnc     | postgres | UTF8     | en_US.UTF- | en_US.UTF- |

 template0 | postgres | UTF8     | en_US.UTF- | en_US.UTF- | =c/postgres          +

           |          |          |             |             | postgres=CTc/postgres

 template1 | postgres | UTF8     | en_US.UTF- | en_US.UTF- | =c/postgres          +

           |          |          |             |             | postgres=CTc/postgres

( rows)

postgres=# \x

Expanded display is on.

postgres=# select * from pg_stat_replication;

-[ RECORD  ]----+------------------------------

pid              |

usesysid         |

usename          | repl

application_name | walreceiver

client_addr      | 192.168.1.162                                     ------>从备库连接上主库

client_hostname  |

client_port      |

backend_start    | -- ::54.269887+

backend_xmin     |

state            | streaming

sent_location    | /4032A78

write_location   | /4032A78

flush_location   | /4032A78

replay_location  | /4032A78

sync_priority    |

sync_state       | async

postgres=#

备库：

$ psql

psql (9.6.)

Type "help" for help.

postgres=# \list

                                  List of databases

   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges

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

 chenzxdb  | postgres | UTF8     | en_US.UTF- | en_US.UTF- |

 pocdb     | postgres | UTF8     | en_US.UTF- | en_US.UTF- |

 postgres  | postgres | UTF8     | en_US.UTF- | en_US.UTF- |

 shsnc     | postgres | UTF8     | en_US.UTF- | en_US.UTF- |

 template0 | postgres | UTF8     | en_US.UTF- | en_US.UTF- | =c/postgres          +

           |          |          |             |             | postgres=CTc/postgres

 template1 | postgres | UTF8     | en_US.UTF- | en_US.UTF- | =c/postgres          +

           |          |          |             |             | postgres=CTc/postgres

( rows)

postgres=#

另外，可查看pg_log查看关键字"database system is ready to accept read only connections"

$ grep "database system " ./pg_log/*csv

./pg_log/postgresql-2018-11-15_171206.csv:2018-11-15 17:12:06.999 CST,,,4324,,5bed3866.10e4,2,,2018-11-15 17:12:06 CST,,0,LOG,00000,"database system is ready to accept read only connections",,,,,,,,,""  ----->连接上主库并使用read only模式打开数据库

3 搭建过程中遇到的问题QA

pg_basebackup: could not connect to server: FATAL: number of requested standby connections exceeds max_wal_senders (currently 0)

解决方案：在主库postgres.conf文件中添加以下参数

max_connections =

max_connections =

max_connections = hot_standby

psql: FATAL: the database system is starting up

解决方案：在备库postgres.conf文件中添加以下参数

hot_standby = on

巴特西

PostgreSQL异步主从流复制搭建