Getting Started Tutorial: Pgsql streaming Sync

Source: Internet
Author: User
Tags postgresql psql

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.