Postgresql9.2 Primary and Standby settings

1.1 Host Description

Primary server:192.168.1.120

standby server:192.168.1.121

1.2 Configuring the primary server

Description: Only database software is installed from the library and database initialization is not performed.

1) Create an archive directory

Mkdir/usr/local/pgsql/archive

Chown postgres:postgres/usr/local/pgsql/archive

2) Modify the PostgreSQL configuration of the master server . /data/postgresql.conf

VI postgresql.conf

listen_address = ' localhost,192.168.1.120,192.168.1.121 '

Wal_level = Hot_standby

Max_wal_senders = ten ( self-setting according to the actual situation ) # number of client connections

Archive_mode = On

Archive_command = ' cp%p/usr/local/pgsql/archive/%f '

wal_keep_segments = 8 # Keep the number of archives, each 16M

3) Configure The parameters in the pg_hba.conf file in the master server's PostgreSQL

VI pg_hba.conf

Host replication All 192.168.1.121/32 Trust

Create a replication user

Create User REPL2 Replication login encrypted password ' REPL2 ';

4) overloaded configuration

Service PostgreSQL Restart or do not reboot to perform pg_ctl reload overloaded configuration

5) login psql start Backup

Psql-u Postgres

Perform

Select Pg_start_backup (' Hot_backup '); # use any symbol to make a backup mark

Copying data files to a standby machine

Scp-r/usr/local/pgsql/data/* 192.168.1.121:/usr/local/pgsql/data

Select Pg_stop_backup ();

1.3 Configuring the Standby server

1) Modify the configuration file

VI postgresql.conf

Hot_standby = On

Create recovery.conf in the data directory

VI recovery.conf

Add to

Standby_mode = ' on '

Primary_conninfo = ' host=192.168.1.120 user=repl2 port=5432 '

Modify file Permissions

Chown-r Postgres:postgres/usr/local/pgsql

Restart the primary server, and then start the standby server

Postgresql9.2 primary and Standby settings