Getting Started Tutorial: Pgsql streaming Sync

The following is a brief introduction to the PostgreSQL9.1 synchronization steps.

Installation and Configuration PgSQL9.1

Here the Node1 as Pgsql master server, node2 as pgsql slave server.

1. Yum Installation

Execute command server: Node1,node2

# wget-p/usr/local/src http://yum.postgresql.org/9.1/redhat/rhel-5-x86_64/pgdg-#centos91 -9.1-4.noarch.rpm
# rpm-ivh/usr/local/src/pgdg-centos91-9.1-4.noarch.rpm
# yum Install postgresql91 postgresql91-devel postgresql91-libs postgresql91-server Postgresql91-docs
2. Initialization of the database

Execute command server: Node1

# service postgresql-9.1 Initdb--encoding=utf8
Initializing database: [OK]
Data Directory is not empty!

If the above error occurs, delete all pgsql data.

# Cd/var/lib/pgsql/9.1/data
# RM-FR *
3. Configure postgresql.conf

Postgresql.conf is the most important configuration file (like MySQL's my.cnf), where only a few parameters related to streaming synchronization are configured.

Execute command server: Node1

# vi/var/lib/pgsql/9.1/data/postgresql.conf
Modify the following 4 parameters.
----
listen_addresses = ' * '
Wal_level = Hot_standby
Max_wal_senders = 2
Hot_standby = On
----
4. Configure pg_hda.conf

Execute command server: Node1

# vi/var/lib/pgsql/9.1/data/pg_hda.conf
Append the following 2 lines (★ append)
----
# TYPE DATABASE USER Address method
Local All
# Ipv4-style Local connections:
Host All 127.0.0.1/32 Trust
Host all All trust★ append
Host all All trust★ append
# IPV6 Local connections:
Host All:: 1/128
----
5. Create streaming Sync User

When you start Master's Pgsql, create a user to use when synchronizing.

Execute command server: Node1

# service postgresql-9.1 Start
# Su-postgres
$ psql
CREATE role Repluser LOGIN REPLICATION PASSWORD ' zabbixcc ';
\q
6. Get data from master server

Execute command server: Node2

# cd/var/lib/pgsql/9.1/data/
# RM-RF *
# Su-postgres
$ pg_basebackup-d/var/lib/pgsql/9.1/data/-x-p-h-u repluser
7. Configure recovery.conf

Execute command server: Node2

# cp-p/usr/pgsql-9.1/share/recovery.conf.sample/var/lib/pgsql/9.1/data/recovery.conf
# vi/var/lib/pgsql/9.1/data/recovery.conf
Modify the following 2 parameters.
----
Standby_mode = On
Primary_conninfo = ' host= port=5432 user=repluser password=zabbixcc '
----
Configure to start later, Node2 's pgsql.

# service postgresql-9.1 Start
8. Configure Boot Boot

Execute command server: Node1,node2

# Chkconfig postgresql-9.1 on
Confirm Streaming Sync

1. Insert Data (Master)

Execute command server: Node1

$ psql
CREATE TABLE tbl (id int);
INSERT into TBL VALUES (1), (2);
SELECT * from TBL;
\q
2. View data (Slave)

View the data inserted in master, there is no sync to slave.

Execute command server: Node2

$ psql
SELECT * from TBL;
\q