PostgreSQL流复制热备

一、前提

分别在两台主机上安装好pg数据库，安装过程参考之前文章Centos安装PostgreSQL

二、节点信息

节点信息

三、部署流复制

#设置hosts（master&&slave）

[root@localhost ~]# cat >> /etc/hosts << EOF

10.1.83.136 master

10.1.83.135 slave

EOF

#初始化master数据库

[root@localhost ~]# su - postgres

[postgres@localhost ~]$ pg_ctl stop -D $PGDATA

[postgres@localhost ~]$ rm -rf /app/postgres/data/

[postgres@localhost ~]$ initdb -D $PGDATA

[postgres@localhost ~]$ pg_ctl start -D $PGDATA

[postgres@localhost ~]$ psql

psql (9.6.0)

Type "help" for help.

postgres=# ALTER ROLE postgres password 'postgres';

ALTER ROLE

postgres=# \q

[postgres@localhost ~]$ vim $PGDATA/postgresql.conf

# - Connection Settings -

listen_addresses = '*'

port = 5432

log_destination = 'stderr'

logging_collector = on

log_directory = '/app/postgres/log/'

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

log_file_mode = 0600

log_rotation_age = 1d

log_rotation_size = 100MB

log_min_messages = error

max_wal_senders = 2

wal_level = replica

archive_mode = on

archive_command = 'cd ./'

hot_standby = on

wal_keep_segments = 64

full_page_writes = on

wal_log_hints = on

[postgres@localhost ~]$ vim $PGDATA/pg_hba.conf

# "local" is for Unix domain socket connections only

local all all md5

# IPv4 local connections:

host all all 127.0.0.1/32 md5

# IPv6 local connections:

host all all ::1/128 md5

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

# replication privilege.

#local replication postgres trust

#host replication postgres 127.0.0.1/32 trust

#host replication postgres ::1/128 trust

host all all 0.0.0.0/0 md5

host replication repuser slave md5

[postgres@localhost ~]$ pg_ctl restart -D $PGDATA

[postgres@localhost ~]$ psql

Password:

psql (9.6.0)

Type "help" for help.

postgres=# CREATE USER repuser replication LOGIN CONNECTION LIMIT 3 ENCRYPTED PASSWORD 'repuser';

CREATE ROLE

postgres=# \q

#部署slave数据库

[root@localhost ~]# su - postgres

[postgres@localhost ~]$ rm -rf /app/postgres/data/

[postgres@localhost ~]$ pg_basebackup -D $PGDATA -Fp -Xs -v -P -h master -p 5432 -U repuser

[postgres@localhost ~]$ vim $PGDATA/pg_hba.conf

host replication repuser master md5

#配置master的recovery.done

[postgres@localhost ~]$ cd $PGHOME

[postgres@localhost postgres]$ cp share/recovery.conf.sample data/recovery.done

[postgres@localhost postgres]$ vim data/recovery.done

recovery_target_timeline='latest'

standby_mode=on

primary_conninfo='host=slave port=5432 user=repuser password=repuser'

trigger_file='/app/postgres/data/trigger_file'

#配置slave的recovery.conf

[postgres@localhost ~]$ cd $PGHOME

[postgres@localhost postgres]$ cp share/recovery.conf.sample data/recovery.conf

[postgres@localhost postgres]$ vim data/recovery.conf

recovery_target_timeline='latest'

standby_mode=on

primary_conninfo='host=master port=5432 user=repuser password=repuser'

trigger_file='/app/postgres/data/trigger_file'

#配置master的.pgpass

[postgres@localhost postgres]$ echo 'slave:5432:postgres:repuser:repuser' > /home/postgres/.pgpass;chmod 0600 /home/postgres/.pgpass

#配置slave的.pgpass

[postgres@localhost postgres]$ echo 'master:5432:postgres:repuser:repuser' > /home/postgres/.pgpass;chmod 0600 /home/postgres/.pgpass

四、流复制数据同步测试

分别重启master，slave数据库

#在master插入数据

[postgres@localhost ~]$ psql

Password:

psql (9.6.0)

Type "help" for help.

postgres=# create database test;

CREATE DATABASE

postgres=# \c test

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

test=# create table tt(id serial not null,name text);

CREATE TABLE

test=# insert into tt(name) values ('china');

INSERT 0 1

test=# \q

#在slave查看master插入的数据是否同步过来

[postgres@localhost ~]$ psql

Password:

psql (9.6.0)

Type "help" for help.

postgres=# \c test

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

test=# select * from tt;

id | name

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

1 | china

(1 row)

test=# \q

五、流复制管理

一般可以通过若干命令查询数据库的主备属性，主数据库是读写的，备数据库是只读的。当主数据库宕机了，可以通过建立触发文件，备数据库将被提升为主数据库，实现一些基本的HA应用。

#查看主备属性

[postgres@localhost ~]$ pg_controldata | grep 'Database cluster state'

主机的cluster state是in production，备机的cluster state是in archive recovery。

#字典表pg_stat_replication

[postgres@localhost ~]$ psql

Password:

psql (9.6.0)

Type "help" for help.

postgres=# select pid,application_name,client_addr,client_port,state,sync_state from pg_stat_replication;

pid | application_name | client_addr | client_port | state | sync_state

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

1401 | walreceiver | 10.1.83.135 | 48860 | streaming | async

在主机字典表中是能查到记录，备机中是查询不到的。

#进程信息识别

[postgres@localhost ~]$ ps -ef | grep postgres | grep 'wal sender\|wal receiver' | grep -v grep

进程中显示wal sender的是主机，显示wal receiver的是备机

#pg函数

[postgres@localhost ~]$ psql

Password:

psql (9.6.0)

Type "help" for help.

postgres=# select pg_is_in_recovery();

备机是t，主机是f。

#切换主从

1、master关闭