Postgres Plus Hot-standby Streaming replication

PPAS

Postgres Plus Advanced Server (PPAS) 9.2

Installation, initial configuration,  Hot-standby Streaming replication

Step 1:-- unzip the installation archive file.

Step 2:-- Make the installer directory your current directory

Note 1:-- while installation when asked for either an oracle or postgresql installation select oracle will give you all of the oracle performance and scability features not found in community postgreSQL.

Note 2:-- Enter the password for the database super user. Be sure remember this you will need the password   later.

Install the PPAS on both the primary and secondary server.

On primary server

Installation  directory :/opt/PostgresPlus/9.2AS

Data Directory : /opt/PostgrePlus/9.2AS/data

WAL Directory : /opt/PostgresPlus/9.2AS/data/pg_xlog

Database  port :5444

Database  Superuser : enterprisedb

Operating System Account: enterprisedb

Database  Service :ppas-9.2

Pg_bouncer: 6432

On secondary server

Installation directory: /opt/PostgresPlus/9.2AS

Data Directory: /opt/PostgrePlus/9.2AS/data

WAL Directory: /opt/PostgresPlus/9.2AS/data/pg_xlog

Database  port :5445

Database  Superuser : enterprisedb

Operating System Account: eEnterprise

Database  Service :ppas-9.2

Pg_bouncer: 6432

Step 3 :--Make changes in the primary server

open the Postgresql.conf configuration file and modify it

#vim  /opt/PostgrePlus/9.2AS/data/postgresql.conf

 listen_addresses = '*'

wal_level = hot_standby

max_wal_senders = 1

wal_keep_segments = 5

rchive_mode = on

archive_command = 'cp %p "/opt/archive/%f"'

step 4: Create an archive directory under the opt

step 5:--change the pg_hba.conf file

#vim pg_hba.conf

host     replication     repuser             Secondary-Server-IP/32        trust

step 6:-- make changes in the stand by server

change in the postgresql.conf  file

#vim  /opt/PostgrePlus/9.2AS/data/postgresql.conf

hot_standby = on

step 7:--copy recovery.conf.sample  as recovery.conf

#cp  /opt/PostgresPlus/9.2AS/share/recovery.conf.sample  /opt/PostgresPlus/9.2AS/data/recovery.conf

#vim /opt/PostgresPlus/9.2AS/data/recovery.conf

standby_mode = on

primary_conninfo = 'host=Primary-Server-IP  port=5444 user=repuser password=admin'

restore_command = 'cp /opt/archive/%f %p'

step 8:-- Create an archive directory in the opt.

step 9:-- Create a replication user as repuser and password

connecting  to the database

#cd  /opt/PostgresPlus/9.2AS/bin

#./psql  -U (username)  -d  (database name)

edb=#create user repuser with replication password  ’**password**’;

(creating a user in the database)

edb=#select  pg_start_backup(‘base backup’)

Meanwhile we need copy all the content of dats directory from primary to secondary .

#cd  /opt/PostgresPlus/9.2AS/data

#rsync –av  --exclude pg_xlog  --exclude  postgresql.conf  --exclude postmaster.pid    .   Primary-Server-IP:/opt/PostgresPlus/9.2AS/data

Edb=#select  pg_stop_backup();

Step 10:--  connect to the database and run the following command to see the current wal  location(on the primary server)

edb=#SELECT  pg_current_xlog_location();(to see the current wal location)

edb=#select * from pg_stat_replication;(to check the status of replication)

step 11:-- connect to the database and u can run (on the standby server)

edb=#SELECT  pg_last_xlog_receive_location();

edb=#SELECT pg_last_xlog_replay_location();