PGSQL主从配置教程

主从服务器IP：
192.168.11.131 postgreSQL master
192.168.11.132 postgreSQL slave
 
服务器系统版本：
CENTOS7.4
 
PG版本：
默认9.2 
 
1、安装软件、配置环境变量
主从服务器：
# yum install postgresql -y
# yum install postgresql-server -y
 
# mkdir /data/pg_data
# chown postgres:postgres /data/pg_data
 
# vi /etc/profile
export PGDATA=/data/pg_data
 
# source /etc/profile
 
主服务器：
2、初始化数据库、启动数据库服务并设置为开机启动
# initdb -D /data/pg_data
 
注意：
-D是指定数据存放目录，默认情况下是存放在/var/lib/pgsql/data目录下，但是生产环境通常有单独的数据存放分区。
 
# su postgres
bash-4.2$ pg_ctl -D /data/pg_data start
 
bash-4.2$ exit
 
# vi /etc/rc.d/rc.local
/usr/bin/postgres -D /data/pg_data
 
3、创建同步用户
# su postgres
bash-4.2$ psql
 
postgres=# create role repuser login replication encrypted password 'password123';
 
postgres=# \q
 
注意：
这里创建的用户是repuser，密码是password123，你可以根据需要随意配置。
 
4、修改配置文件pg_hba.conf、postgresql.conf
bash-4.2$ vi /data/pg_data/pg_hba.conf
host    replication     repuser            192.168.11.0/8             md5
host    all             all             192.168.11.0/8              trust
 
bash-4.2$ vi /data/pg_data/postgresql.conf
添加下面配置，配置文件有下面配置的要删除（包括前边有警号'#'的）
listen_addresses = '192.168.11.131'
wal_level = hot_standby
max_wal_senders= 6
wal_keep_segments = 10240
max_connections = 512
archive_mode = on 
archive_command = 'cp %p /data/pg_data/pg_archive/%f'
 
bash-4.2$ mkdir /data/pg_data/pg_archive
注意：
这里的配置archive_command需要根据实际配置做出改变。
 
重新加载使配置生效
bash-4.2$ pg_ctl -D /data/pg_data reload
 
从服务器：
5、同步数据
bash-4.2$ pg_basebackup -h 192.168.11.131 -U repuser -D /data/pg_data -X stream -P
Password: 
36413/36413 kB (100%), 1/1 tablespace
 
6、修改配置文件recovery.conf、postgresql.conf
bash-4.2$ cp /usr/share/pgsql/recovery.conf.sample /data/pg_data/recovery.conf
 
bash-4.2$ vi /data/pg_data/recovery.conf
添加下面配置，配置文件有下面配置的要删除（包括前边有警号'#'的）
standby_mode = on
primary_conninfo = 'host=192.168.11.131 port=5432 user=repuser password=password123 keepalives_idle=60'
recovery_target_timeline = 'latest
 
注意：
这里的user就是之前创建的同步数据的用户，密码也是响应的密码。
 
bash-4.2$ vi /data/pg_data/postgresql.conf
添加下面配置，配置文件有下面配置的要删除（包括前边有警号'#'的）
listen_addresses = '192.168.11.132'
wal_level = hot_standby
max_connections = 1000
hot_standby = on
max_standby_streaming_delay = 30s
wal_receiver_status_interval = 10s
hot_standby_feedback = on
 
bash-4.2$ exit
 
7、启动服务并设置开机启动
# chmod 700 /data/pg_data
 
# vi /etc/rc.d/rc.local
/usr/bin/postgres -D /data/pg_data
 
# su postgres
 
bash-4.2$ pg_ctl -D /data/pg_data start
 
8、验证
主服务器：
bash-4.2$ psql
 
postgres=# select client_addr,sync_state from pg_stat_replication;
 client_addr  | sync_state 
--------------+------------
 192.168.11.132 | async
 
 postgres=# create database test;
CREATE DATABASE
 
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | 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 | 
(4 rows)
 
从服务器：
# su postgres
 
bash-4.2$ psql
 
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | 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 | 
(4 rows)
 
9、创建数据库、访问用户并给数据库赋权
主服务器
postgres=# create user pgone with password 'password321'; 
CREATE ROLE
 
postgres=# create database pgdata owner pgone;
CREATE DATABASE
 
postgres=# grant all privileges on database pgdata to pgone;
GRANT
 
10、设置本地登录数据库需要输入密码，设置客户端工具登录数据库需要输入密码
10.1、如果有需求的话，可以设置本地登录数据库需要输入密码
主服务器
修改账户登录数据库密码
# su postgres
 
bash-4.2$ psql
postgres=# alter user postgres with password 'password321';
ALTER ROLE
 
修改配置文件pg_hba.conf 
postgres=# vi /data/pg_data/pg_hba.conf 
local   all             all                                     trust
host    all             all             127.0.0.1/32            trust
 
>>
 
local   all             all                                     md5
host    all             all             127.0.0.1/32            md5
 
重新加载配置文件 
bash-4.2$ pg_ctl -D /data/pg_data reload
 
从服务器只需要修改配置文件pg_hba.conf 并使其生效就可以了
 
10.2、设置远程登录数据库需要输入密码，用于客户端工具连接
主服务器
修改配置文件pg_hba.conf 
postgres=# vi /data/pg_data/pg_hba.con
host    all             all             192.168.11.0/24            md5
对于192.168.11.0网段的服务器登录数据库需要输入密码登录
 
注意 其实pg_hba.conf只需要改一条就行
host    all             all             0.0.0.0/24            md5