PostgreSQL数据库学习--流复制配置

主备节点环境

主节点ip地址：192.168.214.132

备节点ip地址：192.168.214.133

 

主机配置

1、配置postgressql.conf

#配置连接参数

listen_addresses = '*'

port = 9410

#开启流复制模式

wal_level = hot_standy

#开启归档模式

archive_mode = on

archive_command = '/bin/date'

#配置流复运行需要的参数，3个Wal sender+1000个wal保存

max_wal_senders = 3

wal_keep_segments = 1000

log_destination = 'csvlog'

logging_collector = on

 

2、配置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             md5

host     all              all              0.0.0.0                  md5

# IPv6 local connections:

#host     all              all              ::1/128                  trust

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

# replication privilege.

#local    replication      postgres                                 trust

host     replication      postgres         192.168.214.0/24         md5

#host     replication      postgres         ::1/128                  trust

 

3、重启服务--让配置生效

[postgres@centos data]$ pg_ctl restart -D ./

waiting for server to shut down.... done

server stopped

server starting

[postgres@dywl data_tmp]$ LOG:   could not create IPv6 socket: Address family not supported by protocol

LOG:   redirecting logoutput to logging collector process

HINT:   Future log output will appear in directory "pg_log".

 

4、连接数据库

[postgres@centos data]$ psql -h 127.0.0.1

Password:

psql (9.2.3)

Type "help" for help.

postgres=# \l

                                  List of databases

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

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

 cqs       | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |

 mydb      | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |

 postgres  | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |

 template0 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +

           |          |          |             |             | postgres=CTc/postgres

 template1 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +

           |          |          |             |             | postgres=CTc/postgres

(5 rows)

 

 

备机配置

1、安装pgsql，同个版本，可不需要初始化，删除data目录

[root@cqs postgres]# rm data -Rf

2、把主机的data目录整个备份到对应的目录中

[postgres@dywl ~]$ pg_basebackup -h 192.168.214.132 -U postgres -D /pgdata/data

Password:

NOTICE:   pg_stop_backup complete, all required WAL segments have beenarchived

[postgres@dywl ~]$ cd /pgdata/data

[postgres@centos data]$ ll

总用量 112

-rw-rw-r--. 1 postgres postgres    44 9月  10 21:40 1

-rw-------. 1 postgres postgres   206 9月  10 21:40 backup_label

drwx------. 7 postgres postgres  4096 9月  10 21:40 base

drwx------. 2 postgres postgres  4096 9月  10 21:40 global

-rw-------. 1 postgres postgres 12075 9月  10 21:40 logfile.txt

drwx------. 2 postgres postgres  4096 9月  10 21:40 pg_clog

-rw-------. 1 postgres postgres  4541 9月  10 21:40 pg_hba.conf

-rw-------. 1 postgres postgres  1636 9月  10 21:40 pg_ident.conf

drwx------. 2 postgres postgres  4096 9月  10 21:40 pg_log

  wx------. 4 postgres postgres  4096 9月  10 21:40 pg_multixact

drwx------. 2 postgres postgres  4096 9月  10 21:40 pg_notify

drwx------. 2 postgres postgres  4096 9月  10 21:40 pg_serial

drwx------. 2 postgres postgres  4096 9月  10 21:40 pg_snapshots

drwx------. 2 postgres postgres  4096 9月  10 21:40 pg_stat_tmp

drwx------. 2 postgres postgres  4096 9月  10 21:40 pg_subtrans

drwx------. 2 postgres postgres  4096 9月  10 21:40 pg_tblspc

drwx------. 2 postgres postgres  4096 9月  10 21:40 pg_twophase

-rw-------. 1 postgres postgres     4 9月  10 21:40 PG_VERSION

drwx------. 2 postgres postgres  4096 9月  10 21:40 pg_xlog

-rw-------. 1 postgres postgres 19714 9月  10 21:40 postgresql.conf

 

3、在备用服务器的集群数据目录下创建恢复命令文件recovery.conf，并进行配置

[postgres@centos data]$ vim recovery.conf

standby_mode = 'on'

primary_conninfo = 'host=192.168.214.132 port=5432 user=postgres password=000'

archive_cleanup_command = '/pgdata/bin/pg_archivecleanup /pgdata/data/pg_xlog %r'

trigger_file='/pgdata/data/trigger_active.5432'

 

 

注意：-D 就是存储的对应目录

 

4、配置postgresql.conf

hot_standby = on

max_standby_archive_delay = 300s

max_standby_streaming_delay = 300s

 

5、启动备节点服务

[postgres@centos data]$ pg_ctl  restart -D ./

waiting for server to shut down.... done

server stopped

server starting

[postgres@centos data]$

 

6、备节点连接测试

[postgres@centos data]$ psql -h 127.0.0.1

Password:

psql (9.2.3)

Type "help" for help.

 

postgres=# \l

                                  List of databases

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

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

 cqs       | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |

 mydb      | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |

 postgres  | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |

 template0 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +

           |          |          |             |             | postgres=CTc/postgres

 template1 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +

           |          |          |             |             | postgres=CTc/postgres

(5 rows)

 

测试

在主节点建立数据库表并插入数据看看同步情况

[postgres@centos data]$ psql -h 127.0.0.1

Password:

psql (9.2.3)

Type "help" for help.

postgres=# \c mydb

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

mydb=# \dt

        List of relations

 Schema | Name | Type  |  Owner   

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

 public | t1   | table | postgres

(1 row)

 

mydb=# create table t2 (id integer);

CREATE TABLE

mydb=# insert into t2 values(1),(2);

INSERT 0 2

mydb=#

 

在备节点查询数据（备节点只允许查询数据，不能执行插入和修改删除等操作）

postgres=# \c mydb

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

mydb=# \dt

        List of relations

 Schema | Name | Type  |  Owner   

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

 public | t1   | table | postgres

 public | t2   | table | postgres

(2 rows)

 

mydb=# select * from t2;

 id

----

  1

  2

(2 rows)

 

mydb=# create table t3 (id integer);

ERROR:  cannot execute CREATE TABLE in a read-only transaction

mydb=# ^C

 

查看有多少备节点连接上主节点（主节点上进行操作）

mydb=# \x

Expanded display is on.

注：\x表示切换展示数据，横向或者竖向

mydb=# select * from pg_stat_replication;

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

pid              | 1462

usesysid         | 10

usename          | postgres

application_name | walreceiver

client_addr      | 192.168.214.133

client_hostname  |

client_port      | 57709

backend_start    | 2015-09-10 21:42:31.726843+08

state            | streaming

sent_location    | 0/B014C40

write_location   | 0/B014C40

flush_location   | 0/B014C40

replay_location  | 0/B014C40

sync_priority    | 0

sync_state       | async

 

查看备机的恢复情况（主节点上进行操作）

mydb=# select application_name,pg_size_pretty(pg_xlog_location_diff(pg_current_xlog_location(),replay_location))as diff from pg_stat_replication;

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

application_name | walreceiver

diff             | 0 bytes

 

 

配置主备同步复制模式

1、修改主节点postgresql.conf

[postgres@centos data]$ vim postgresql.conf

#synchronous_standby_names = ''

修改成

synchronous_standby_names = 'synccluster1,synccluster2'

#参数内容说明，*代表全部，其它内容指定备库中的应用名称如“synccluster1,synccluster2”就是指备库同步上的application_name=synccluster1 或者是application_name=synccluster2时采用同步复制，其它的采用异步复制

:wq进行修改保存，reload一下主节点

[postgres@centos data]$ pg_ctl reload -D ./

server signaled

2、修改备节点recovery.conf

[postgres@centos data]$ vim recovery.conf

primary_conninfo = 'host=192.168.214.132 port=5432 user=postgres password=000'

修改成

primary_conninfo='host=192.168.214.132 port=5432 user=postgres password=000 application_name=synccluster1’

:wq进行修改保存，restart重启一下主节点

[postgres@centos data]$ pg_ctl restart -D ./

waiting for server to shut down.... done

server stopped

server starting

3、测试节点之间是否是同步备份

关闭备节点的服务器连接

[postgres@centos data]$ pg_ctl stop -D ./

之后连接主节点的数据库

[postgres@centos data]$

[postgres@centos data]$ psql -h 127.0.0.1 -U postgres

Password for user postgres:

psql (9.2.3)

Type "help" for help.

postgres=# psql -h 127.0.0.1 -U postgres -d mydb

mydb=# insert into t1(id,myname) values (2,'abc');

cancel request sent

WARNING:  canceling wait for synchronous replication due to user request

DETAIL:  The transaction has already committed locally, but might not have been replicated to the standby.

  SERT 0 1

因为是同步复制模式，所以当备机出现故障的时候，主机才出现一直等待状态，直到备机恢复才能恢复正常。

4、同步复制与异步复制之间的区别。

运行下面语句

 [postgres@centos data]$ psql -h 192.168.214.132 -d postgres -x -c "select * from pg_stat_replication"

同步复制的结果是

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

pid              | 18382

usesysid         | 10

usename          | postgres

application_name | synccluster1

client_addr      | 192.168.214.133

client_hostname  |

client_port      | 57713

backend_start    | 2015-09-11 00:25:32.393243+08

state            | streaming

sent_location    | 0/B015820

write_location   | 0/B015820

flush_location   | 0/B015820

replay_location  | 0/B015820

sync_priority    | 1

sync_state       | sync

异步复制的结果是

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

pid              | 18451

usesysid         | 10

usename          | postgres

application_name | synccluster1

client_addr      | 192.168.214.133

client_hostname  |

client_port      | 57714

backend_start    | 2015-09-11 00:36:16.734304+08

state            | streaming

sent_location    | 0/B015A30

write_location   | 0/B015A30

flush_location   | 0/B015A30

replay_location  | 0/B015A30

sync_priority    | 0

sync_state       | async