Which version of postgresql supports replication on RHEL6?

On Apr 21, 2011, at 4:42 PM, Tiruvenkatasamy Baskaran wrote:

Which version of postgresql supports replication on RHEL6?
RHEL version : 2.6.32-71.el6.x86_64

If you are talking about inbuld replication, then from PG9.0 onwards. Else slony-I replication tool is available for replication of PG Database

Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
vibhor.kumar@enterprisedb.com
Blog:http://vibhork.blogspot.com

Hi Vibhor Kumar,

We downloaded postgresql-9.0.4.tar source from postgresql.org.

We installed postgresql db from source on machine1(master) and machine2(slave).

RHEL version : 2.6.32-71.el6.x86_64

In order to enable replication between master and slave, we did necessary configuration
changes on master DB then started the master DB.

Also did necessary configuration changes on slave DB then started the slave DB.

Got the following messages in log file on the master DB.
LOG: database system was shut down at 2011-04-01 14:27:37 IST
LOG: database system is ready to accept connections
LOG: autovacuum launcher started
LOG: replication connection authorized: user=postgres host=10.128.16.52 port=52324
cp: cannot stat `/usr/local/pgsql/data/pg_xlogarch/000000010000000000000001': No such file or directory
LOG: archive command failed with exit code 1
Got the following messages in log file on the slave DB.
LOG: database system was interrupted; last known up at 2011-03-24 12:29:15 IST
LOG: entering standby mode
cp: cannot stat `/usr/local/pgsql/data/pg_xlogarch/000000010000000000000002': No such file or directory
LOG: redo starts at 0/2000020
LOG: record with zero length at 0/20000B0
cp: cannot stat `/usr/local/pgsql/data/pg_xlogarch/000000010000000000000002': No such file or directory
LOG: streaming replication successfully connected to primary
FATAL: the database system is starting up
FATAL: the database system is starting up
Master DB is running and able to query table. But slave DB is not running and not able to query table.

Could you tell me why slave DB is not running?
If you need more details on how we configured master and slave DB in replication mode , we will provide.

Regards,
Tiru

-----Original Message-----
From: Vibhor Kumar [mailto:vibhor.kumar@enterprisedb.com]
Sent: Thursday, April 21, 2011 5:17 PM
To: Tiruvenkatasamy Baskaran
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Which version of postgresql supports replication on RHEL6?

On Apr 21, 2011, at 4:42 PM, Tiruvenkatasamy Baskaran wrote:

Which version of postgresql supports replication on RHEL6?
RHEL version : 2.6.32-71.el6.x86_64

If you are talking about inbuilt replication, then from PG9.0 onwards. Else slony-I replication tool is available for replication of PG Database

Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
vibhor.kumar@enterprisedb.com
Blog:http://vibhork.blogspot.com

________________________________
::DISCLAIMER::
-----------------------------------------------------------------------------------------------------------------------

The contents of this e-mail and any attachment(s) are confidential and intended for the named recipient(s) only.
It shall not attach any liability on the originator or HCL or its affiliates. Any views or opinions presented in
this email are solely those of the author and may not necessarily reflect the opinions of HCL or its affiliates.
Any form of reproduction, dissemination, copying, disclosure, modification, distribution and / or publication of
this message without the prior written consent of the author of this e-mail is strictly prohibited. If you have
received this email in error please delete it and notify the sender immediately. Before opening any mail and
attachments please check them for viruses and defect.

-----------------------------------------------------------------------------------------------------------------------

On Apr 21, 2011, at 6:35 PM, Tiruvenkatasamy Baskaran wrote:

Got the following messages in log file on the master DB.
LOG: database system was shut down at 2011-04-01 14:27:37 IST
LOG: database system is ready to accept connections
LOG: autovacuum launcher started
LOG: replication connection authorized: user=postgres host=10.128.16.52 port=52324
cp: cannot stat `/usr/local/pgsql/data/pg_xlogarch/000000010000000000000001': No such file or directory
LOG: archive command failed with exit code 1
Got the following messages in log file on the slave DB.
LOG: database system was interrupted; last known up at 2011-03-24 12:29:15 IST
LOG: entering standby mode
cp: cannot stat `/usr/local/pgsql/data/pg_xlogarch/000000010000000000000002': No such file or directory
LOG: redo starts at 0/2000020
LOG: record with zero length at 0/20000B0
cp: cannot stat `/usr/local/pgsql/data/pg_xlogarch/000000010000000000000002': No such file or directory
LOG: streaming replication successfully connected to primary
FATAL: the database system is starting up
FATAL: the database system is starting up
Master DB is running and able to query table. But slave DB is not running and not able to query table.

Please confirm if you have made Slave DB using PITR/Hot Backup of Master. Also, please let me know the steps which you have followed.

Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
vibhor.kumar@enterprisedb.com
Blog:http://vibhork.blogspot.com

[ Please don't do top posting]

20. cd /usr/local/pgsql/
21 . tar -czf data.tar.gz data/

After 21. step, seems you forgot to execute pg_stop_backup() command.

With this, I would recommend you to follow the documentation given below:
http://wiki.postgresql.org/wiki/Streaming_Replication

Thanks & Regards,
Vibhor

On Apr 22, 2011, at 11:08 AM, Tiruvenkatasamy Baskaran wrote:

Hi Vibhor Kumar,

Here is the configuration changes.
Changes made on Master DB
-------------------------
1. tar –xzvf postgresql-9.0.4.tar
2. cd postgresql-9.0.4
3. ./configure
4. gmake
5. su
6. gmake install
7. adduser postgres
8. mkdir -p /usr/local/pgsql/data
9. chown -R postgres /usr/local/pgsql
10. su - postgres
11. /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data

“Modify the following attributes and their values in postgresql.conf as follows”
------------------------------------------------------------------------------
12. vi /usr/local/postgres/data/postgresql.conf

listen_addresses = '*'
max_connections = 500
shared_buffers = 32MB
wal_level = hot_standby
archive_mode = on
archive_command = 'cp %p /usr/local/pgsql/data/pg_xlogarch/%f'
max_wal_senders = 1 ####( this setting for 1 slave can connect with Master)
wal_keep_segments = 32
datestyle = 'iso, mdy'
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
default_text_search_config = 'pg_catalog.english'

13. mkdir /usr/local/pgsql/data/pg_xlogarch/
14. su – postgres
15. cd /usr/local/pgsql/

“Modify the following attributes and their values in pg_hba.conf as follows”
-------------------------------------------------------------------------------
16. vi data/pg_hba.conf
host replication all 10.128.16.52/32 (slave IPaddress) trust

17. /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
18. cd /usr/local/pgsql/bin/
19. /psql –c "SELECT pg_start_backup('label')" PICOEMSDB
20. cd /usr/local/pgsql/
21 . tar -czf data.tar.gz data/
Changes made on Slave DB
-------------------------
• tar –xzvf postgresql-9.0.4.tar
• cd postgresql-9.0.4
• ./configure
• gmake
• su
• gmake install
• adduser postgres
• mkdir -p /usr/local/pgsql/data
• chown -R postgres /usr/local/pgsql
• cd /usr/local/pgsql
• Copy the tar file which we have created in master machine to the respected dir in the slave m/c step 21
• tar xzf data.tar.gz
• chown –R postgres:postgres /usr/local/pgsql/data/pg_xlogarch
• cd data
• rm –rf postmaster.pid
“Modify the following attributes and their values in Postgresql.conf as follows”
----------------------------------------------------------------------------------------------
• vi /usr/local/pgsql/data/Postgresql.conf
listen_addresses = '*' # what IP address(es) to listen on;
max_connections = 500 # (change requires restart)
shared_buffers = 32MB # min 128kB
hot_standby = on
datestyle = 'iso, mdy'
lc_messages = 'en_US.UTF-8' # locale for system error message
lc_monetary = 'en_US.UTF-8' # locale for monetary formatting
lc_numeric = 'en_US.UTF-8' # locale for number formatting
lc_time = 'en_US.UTF-8' # locale for time formatting
default_text_search_config = 'pg_catalog.english'

• create recovery.conf file in the following directory /usr/local/pgsql/data/
Add the below lines in the recovery.conf file
standby_mode = 'on'
primary_conninfo = 'host=master ip port=5432 user=postgres'
restore_command = 'cp /usr/local/pgsql/data/pg_xlogarch/%f "%p"'

• /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start

Regards,
Tiru

-----Original Message-----
From: Vibhor Kumar [mailto:vibhor.kumar@enterprisedb.com]
Sent: Thursday, April 21, 2011 6:53 PM
To: Tiruvenkatasamy Baskaran
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Which version of postgresql supports replication on RHEL6?

On Apr 21, 2011, at 6:35 PM, Tiruvenkatasamy Baskaran wrote:

Got the following messages in log file on the master DB.
LOG: database system was shut down at 2011-04-01 14:27:37 IST
LOG: database system is ready to accept connections
LOG: autovacuum launcher started
LOG: replication connection authorized: user=postgres host=10.128.16.52 port=52324
cp: cannot stat `/usr/local/pgsql/data/pg_xlogarch/000000010000000000000001': No such file or directory
LOG: archive command failed with exit code 1
Got the following messages in log file on the slave DB.
LOG: database system was interrupted; last known up at 2011-03-24 12:29:15 IST
LOG: entering standby mode
cp: cannot stat `/usr/local/pgsql/data/pg_xlogarch/000000010000000000000002': No such file or directory
LOG: redo starts at 0/2000020
LOG: record with zero length at 0/20000B0
cp: cannot stat `/usr/local/pgsql/data/pg_xlogarch/000000010000000000000002': No such file or directory
LOG: streaming replication successfully connected to primary
FATAL: the database system is starting up
FATAL: the database system is starting up
Master DB is running and able to query table. But slave DB is not running and not able to query table.

Please confirm if you have made Slave DB using PITR/Hot Backup of Master. Also, please let me know the steps which you have followed.

Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
vibhor.kumar@enterprisedb.com
Blog:http://vibhork.blogspot.com

________________________________
::DISCLAIMER::
-----------------------------------------------------------------------------------------------------------------------

The contents of this e-mail and any attachment(s) are confidential and intended for the named recipient(s) only.
It shall not attach any liability on the originator or HCL or its affiliates. Any views or opinions presented in
this email are solely those of the author and may not necessarily reflect the opinions of HCL or its affiliates.
Any form of reproduction, dissemination, copying, disclosure, modification, distribution and / or publication of
this message without the prior written consent of the author of this e-mail is strictly prohibited. If you have
received this email in error please delete it and notify the sender immediately. Before opening any mail and
attachments please check them for viruses and defect.

-----------------------------------------------------------------------------------------------------------------------

Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
vibhor.kumar@enterprisedb.com
Blog:http://vibhork.blogspot.com