Обсуждение: Need help in Postgres log shipping replication
We are having a huge database and need replication with load balancing for Postgres 9.0
We have chose Log shipping method for replication and trying out that approach.
Based on the method given for Log shipping, I have configured the following
On Master server the following parameters are set
wal_level = 'archive'
archive_mode = on
archive_command = ‹scp %p $STANDBYNODE:$PGARCHIVE/%f›
archive_timeout = 30
Started taking backup using :
select pg_start_backup('base backup for log shipping')
Then the data files excluding the pg_xlog are copied to the slave using rsync
rsync -cva --inplace --exclude=*pg_xlog* \${PGDATA}/ $STANDBYNODE:$PGDATA
Then the backup process is stopped using the following:
select pg_stop_backup(), current_timestamp
Recovery parameter in the slave server are set as followS
standby_mode = 'on'
restore_command = ‹cp $PGARCHIVE/%f %p›
archive_cleanup_command = ‹pg_archivecleanup $PGARCHIVE %r›
trigger_file = ‹/tmp/postgresql.trigger.5432›
Replication is taking place. THe log in the slave server is showing that the pg_xlog files
from master server are being copied and applied.
But these files are getting copied continuously and they are not stopping.
Master is generating pg_xlog files even when there is no activity on the database.
and the slave server is continuously applying these new files depening on the time set in checkpoint_timeout parameter of the master
PGADMIN is throwing a message' Database is starting up' when trying to connect to the slave database.
When I shut down database servers on both machines, and then log into the slave server after removing the recover.conf file, then
the slave server is started with the data already replicated from master server/
But can you please let me know how the two servers can be online and still the replication continue via log shipping?
Regards
Jahnavi
DISCLAIMER:
This email may contain confidential information and is intended only for the use of the specific individual(s) to which it is addressed. If you are not the intended recipient of this email, you are hereby notified that any unauthorized use, dissemination or copying of this email or the information contained in it or attached to it is strictly prohibited. If you received this message in error, please immediately notify the sender at Infotech or Mail.Admin@infotech-enterprises.com and delete the original message.
On Wed, Jul 06, 2011 at 03:02:22PM +0530, Jahnavi Chintakunta wrote: > > But these files are getting copied continuously and they are not stopping. > > Master is generating pg_xlog files even when there is no activity on the database. How did you determine there was no activity? Have you queried pg_stat_activity during this xlog generation?
-----Original Message----- From: Jahnavi Chintakunta Sent: Wednesday, July 06, 2011 10:03 PM To: 'Ray Stell' Subject: RE: [ADMIN] Need help in Postgres log shipping replication Thanks Ray for the reply. Actually I am testing replication in a test Database & I'm the sole user of that . No connections or no activity is happening in the database.pg_stat_activity just shows my connections with the status as'IDLE'. For more information on this, When the standy server is stopped, then the pg_xlog's in the master server are stagnant. As soon as I start the standby server in recovery mode and as soon as that server starts reading the data from Master server, the pg_xlog files in Master server are getting created. And this looping of standy server reading the files & Masterserver creating new log's is continuing for hours. SO I doubt somewhere in the set up the process is looping and hereI need the help. Thanks Jahnavi -----Original Message----- From: Ray Stell [mailto:stellr@cns.vt.edu] Sent: Wednesday, July 06, 2011 9:55 PM To: Jahnavi Chintakunta Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Need help in Postgres log shipping replication On Wed, Jul 06, 2011 at 03:02:22PM +0530, Jahnavi Chintakunta wrote: > > But these files are getting copied continuously and they are not stopping. > > Master is generating pg_xlog files even when there is no activity on the database. How did you determine there was no activity? Have you queried pg_stat_activity during this xlog generation? DISCLAIMER: This email may contain confidential information and is intended only for the use of the specific individual(s) to which itis addressed. If you are not the intended recipient of this email, you are hereby notified that any unauthorized use, disseminationor copying of this email or the information contained in it or attached to it is strictly prohibited. If youreceived this message in error, please immediately notify the sender at Infotech or Mail.Admin@infotech-enterprises.comand delete the original message.
On Wed, Jul 06, 2011 at 07:34:11PM +0530, Jahnavi Chintakunta wrote: > > > When the standy server is stopped, then the pg_xlog's in the master server are stagnant. Sorry, I've never seen this before. What is archive_timeout set to? template1=# show archive_timeout; archive_timeout ----------------- 15min (1 row) Also, this may be completely bogus, but I remember asking myself if this was valid: archive_command = <scp %p $STANDBYNODE:$PGARCHIVE/%f> I've always seen it inside single quotes.