pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton?
11+ messages / 5 participants
[nested] [flat]

* Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton?
@ 2008-02-14 22:41 libra dba <libra.dba@gmail.com>
  0 siblings, 0 replies; 11+ messages in thread

From: libra dba @ 2008-02-14 22:41 UTC (permalink / raw)
  To: pgsql-admin

Hello all,

I am new to postgresql. i am working on the PITR replication system. I have
successfully implemented the standby database. I have tested the log
shipment and the recovery process on the standby. everything is workign
fine.

Please guide me how to bring the standby database in open mode (failover).
Also it would be great if you could provide a sample trigger file.

thanks

^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton?
@ 2008-02-19 04:23 Praveen Kumar (TUV) <praveen.k@renaissance-it.com>
  2008-02-19 08:19 ` Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? Simon Riggs <simon@2ndquadrant.com>
  0 siblings, 1 reply; 11+ messages in thread

From: Praveen Kumar \(TUV\) @ 2008-02-19 04:23 UTC (permalink / raw)
  To: libra dba <libra.dba@gmail.com>; pgsql-admin

 

Hello ,

Go through this below mentioned link

 

http://archives.postgresql.org/sydpug/2006-10/msg00001.php

  _____  

From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of libra dba
Sent: Friday, February 15, 2008 4:11 AM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] Failover of the Primary database and starting the
standby database in Postgresql in PITR configuraiton?

 

Hello all,

 

I am new to postgresql. i am working on the PITR replication system. I
have successfully implemented the standby database. I have tested the
log shipment and the recovery process on the standby. everything is
workign fine.

 

Please guide me how to bring the standby database in open mode
(failover). Also it would be great if you could provide a sample trigger
file.

 

thanks

Attachments:

standby_database.txt (text/plain, 8.7K) download The Warm standby Setup is from the below given link. http://archives.postgresql.org/sydpug/2006-10/msg00001.php ------------------------------------------------------------------------ Here are the notes on putting together an 8.2 based warm standby demonstration system. I hope people find them useful. As I mentioned in the presentation, the basic idea behind warm standby is to employ a shell script which periodically polls the WAL archive directory, executing the archive recovery procedure when it detects that a new WAL segment has arrived. The standby server thus processes archived segments as and when they are sent from the master. The shell script is specified in recovery.conf in the 'restore_command' parameter in recovery.conf. The problem of yet-to-be-archived transactions being lost in the event of a failure on the master is addressed by the addition of the 'archive_timeout' parameter in postgresql.conf, which will force a WAL segment to be archived every <archive_timeout> seconds. The procedure outlined below is just intended to produce a demonstration system - a production HA setup would be very different. 1) Obtain the 8.2 beta source distribution from http://www.postgresql.org/ftp/source/v8.2beta1/ 2) Create a directory tree into which you can install the 'master' and 'slave' postgres servers. mkdir -p ~/pg82demo/master mkdir -p ~/pg82demo/slave mkdir -p ~/pg82demo/wal_archive 3) Extract the postgres source archive. From the top level, execute: ./configure --prefix=/home/test/pg82demo/master make make install And then install the slave: ./configure --prefix=/home/test/pg82demo/slave make make install (Replace 'test' in the above with the appropriate string for your configuration. Throughout the rest of the notes, I'll specify the user 'test'.) 4) Go to the 'master' postgres installation, and initialise a database cluster: cd ~/pg82demo/master bin/initdb data/ Make the following entries in data/postgresql.conf: archive_command = 'rsync -arv %p /home/test/pg82demo/wal_archive/%f </dev/null' archive_timeout = 60 The reason for using 'rsync' instead of 'cp' will be explained later. 5) Start the master with bin/pg_ctl -D data/ start -l <logfile> 6) Connect to the master with psql, and initiate a base backup with: select pg_start_backup('test1'); Put psql in the background with ctrl-z, and take a backup of the data directory: tar cvzf base.tgz data Bring back psql with 'fg', and finalise the base backup: select pg_stop_backup(); Check that postgres has written the history file to the specified archive directory. These operations have to be performed as the database superuser. 7) Go to the 'slave' installation. Copy the base backup archive over from the master, and extract it: cd ../slave cp ../master/base.tgz . tar xvzf base.tgz rm -f base.tgz 8) Modify the slave's postgresql.conf as follows: port = 5433 The 'port' entry is modified so that the slave won't try to bind to the same port that the master has already bound to. Also, comment out the 'archive_command' and 'archive_timeout' entries in the slave's 'postgresql.conf' file. 9) Delete the 'postmaster.pid' file from the slave's data directory: rm -f data/postmaster.pid 10) Copy the recovery.conf.sample file from the 'share/postgresql/' direcory into the slave's data directory as 'recovery.conf' 11) Save the attached shell script 'restore.sh' to ~/pg82demo/, and make sure it is set executable. 12) Edit the shell script, and make sure the directory paths for TRIGGER_FILE are correct. Don't worry about what the 'trigger file' is yet exactly. The TRIGGER_FILE assignment statement in the shell script should look like: TRIGGER_FILE="/home/<username>/pg82demo/trigger" With <username> replaced with your configuration's path, of course. 13) Add the following to the data/recovery.conf on the slave: restore_command = '/home/test/pg82demo/restore.sh /home/test/pg82demo/wal_archive/%f "%p"' 14) Start the slave server with: bin/pg_ctl -D data/ -l <logfile> 15) Connect to the master, and trigger some WAL archive activity by creating a large table: create table foo as (select a,b from pg_class as a, pg_class as b); The master's log should show output like: LOG: archived transaction log file "000000010000000000000000" LOG: archived transaction log file "000000010000000000000000.00399A30.backup" And the slave's log should contain entries like: `/home/test/pg82test/wal_archive/000000010000000000000000.00399A30.backup' -> `pg_xlog/RECOVERYHISTORY' LOG: restored log file "000000010000000000000000.00399A30.backup" from archive `/home/test/pg82test/wal_archive/000000010000000000000000' -> `pg_xlog/RECOVERYXLOG' LOG: restored log file "000000010000000000000000" from archive LOG: checkpoint record is at 0/399A30 LOG: redo record is at 0/399A30; undo record is at 0/0; shutdown FALSE LOG: next transaction ID: 0/595; next OID: 10818 LOG: next MultiXactId: 1; next MultiXactOffset: 0 LOG: automatic recovery in progress LOG: redo starts at 0/399A78 As a new segment is archived by the master, it will log a message like: LOG: archived transaction log file "000000010000000000000001" Immediately the archive is generated, there should be a corresponding message on the slave which looks like: `/home/test/pg82demo/wal_archive/000000010000000000000001' -> `pg_xlog/RECOVERYXLOG' LOG: restored log file "000000010000000000000001" from archive 16) To initiate a failover from the master to the slave, create the 'trigger file': touch ~/pg82demo/trigger This should immediately cause the slave to finish processing archived segments, exit recovery mode, and come up ready for use. To repeat the test, it is not necessary to repeat the entire procedure above. Stop the slave, and delete its data directory. Then, continue the procedure from step (7) - that is, start the recovery procedure on the slave, beginning with extracting the base backup. The slave will process all the WAL segments in the archive directory, and stop after the last one. Until the failover is triggered, it will then wait for more segments to process, as described above. The reason rsync is used in the archive_command is that rsync features an 'atomic copy' - that is, the in-progress destination file is created as a temp file, and then renamed when the copy is complete. In the situation above, where segments are archived straight to the directory that the slave reads from, 'cp' can cause an error whereby the slave attempts to process a partially-copied WAL segment. If this happens, postgres will emit an error like: PANIC: archive file "000000010000000000000031" has wrong size: 1810432 instead of 16777216 LOG: startup process (PID 11356) was terminated by signal 6 LOG: aborting startup due to startup process failure Another wrinkle in the whole setup is the fact that when in recovery mode, postgres will ask for files to be recovered that do not, and will not, exist. This would seem to conflict with the idea of using the parameters to the restore_command shell script to poll the archive directory, since the script will hang indefinitely if it cannot find the file it is asked for. First off, it seems postgres will always ask for the file '00000001.history'. As a workaround, the supplied shell script looks for the string 'history' in the filename parameter. If detected, the script immediately tries to copy the file, returning cp's error code to postgres. I had thought this might be a bug, but see the post: http://archives.postgresql.org/pgsql-bugs/2006-06/msg00072.php The documentation doesn't quite make it clear whether or not the shell script is supposed to determine the expected WAL archive filename by looking at the sequence of previously archived files, or just by the passed parameter. This is an issue that requires a bit more investigation and experimentation. Thanks, Charles Duffy ============================================================================================================ The script #!/bin/bash RESTORE_FROM=$1 RESTORE_TO=$2 DELAY=100000 TRIGGERED=0 TRIGGER_FILE="/home/test/pg82demo/trigger" copyfunc() { if [ "$TRIGGERED" -eq "0" ]; then cp -v -i $RESTORE_FROM $RESTORE_TO fi } k=`expr $1 : '.*\(history\)'` if [ "$k" == "history" ]; then copyfunc; exit $?; fi while [ ! -f "$RESTORE_FROM" -a "$TRIGGERED" -eq "0" ]; do usleep $DELAY; if [ -e $TRIGGER_FILE ]; then TRIGGERED=1; fi done copyfunc; =========================================================================================================
^ permalink raw reply [nested|flat] 11+ messages in thread

* Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton?
  2008-02-19 04:23 Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? Praveen Kumar (TUV) <praveen.k@renaissance-it.com>
@ 2008-02-19 08:19 ` Simon Riggs <simon@2ndquadrant.com>
  2008-02-19 14:23   ` Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? libra dba <libra.dba@gmail.com>
  0 siblings, 1 reply; 11+ messages in thread

From: Simon Riggs @ 2008-02-19 08:19 UTC (permalink / raw)
  To: Praveen Kumar (TUV) <praveen.k@renaissance-it.com>; +Cc: libra dba <libra.dba@gmail.com>; pgsql-admin

On Tue, 2008-02-19 at 09:53 +0530, Praveen Kumar (TUV) wrote:

> Go through this below mentioned link
> 
>  
> 
> http://archives.postgresql.org/sydpug/2006-10/msg00001.php

I think people should be reading the manual, rather than reading an old
email. We have improved and corrected the manual, but old emails stay
the same. We can also change the manual some more in response to
clarifications.


-- 
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com 




^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton?
  2008-02-19 04:23 Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? Praveen Kumar (TUV) <praveen.k@renaissance-it.com>
  2008-02-19 08:19 ` Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? Simon Riggs <simon@2ndquadrant.com>
@ 2008-02-19 14:23   ` libra dba <libra.dba@gmail.com>
  2008-02-19 15:56     ` Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? salman <salmanb@quietcaresystems.com>
  0 siblings, 1 reply; 11+ messages in thread

From: libra dba @ 2008-02-19 14:23 UTC (permalink / raw)
  To: Simon Riggs <simon@2ndquadrant.com>; +Cc: Praveen Kumar (TUV) <praveen.k@renaissance-it.com>; pgsql-admin

Hello all,

Thanks for your response. The example shown by Charles Duffy, is quite
impressive. Actually i built my replication based on this.

But, i want to test the failover. In the example shown by Charles, mentions
'a trigger file' as it says:
-------------------------------------------------------------------------------
*touch ~/pg82demo/trigger*
**
*This should immediately cause the slave to finish processing archived
segments, exit recovery mode, and come up ready for use.
*
-------------------------------------------------------------------------------
what shold be the content of the trigger file. How do we cause the slave to
finish the processing of the archived segments, exit the recovery mode and
come up ready for use???

Please help in the the FAILOVER of the primary and promoting the slave to
the primary mode.

Thanks!
On Feb 19, 2008 3:19 AM, Simon Riggs <simon@2ndquadrant.com> wrote:

> On Tue, 2008-02-19 at 09:53 +0530, Praveen Kumar (TUV) wrote:
>
> > Go through this below mentioned link
> >
> >
> >
> > http://archives.postgresql.org/sydpug/2006-10/msg00001.php
>
> I think people should be reading the manual, rather than reading an old
> email. We have improved and corrected the manual, but old emails stay
> the same. We can also change the manual some more in response to
> clarifications.
>
>
> --
>  Simon Riggs
>  2ndQuadrant  http://www.2ndQuadrant.com <http://www.2ndquadrant.com/;
>
>

^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton?
  2008-02-19 04:23 Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? Praveen Kumar (TUV) <praveen.k@renaissance-it.com>
  2008-02-19 08:19 ` Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? Simon Riggs <simon@2ndquadrant.com>
  2008-02-19 14:23   ` Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? libra dba <libra.dba@gmail.com>
@ 2008-02-19 15:56     ` salman <salmanb@quietcaresystems.com>
  2008-02-20 16:49       ` Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? libra dba <libra.dba@gmail.com>
  0 siblings, 1 reply; 11+ messages in thread

From: salman @ 2008-02-19 15:56 UTC (permalink / raw)
  To: libra dba <libra.dba@gmail.com>; +Cc: pgsql-admin



libra dba wrote:
> Hello all,
> 
> Thanks for your response. The example shown by Charles Duffy, is quite
> impressive. Actually i built my replication based on this.
> 
> But, i want to test the failover. In the example shown by Charles, mentions
> 'a trigger file' as it says:
> -------------------------------------------------------------------------------
> *touch ~/pg82demo/trigger*
> **
> *This should immediately cause the slave to finish processing archived
> segments, exit recovery mode, and come up ready for use.
> *
> -------------------------------------------------------------------------------
> what shold be the content of the trigger file. How do we cause the slave to
> finish the processing of the archived segments, exit the recovery mode and
> come up ready for use???
> 
> Please help in the the FAILOVER of the primary and promoting the slave to
> the primary mode.
> 

It's an empty file -- doesn't have to have anything in it. The script 
simply checks to see if the file exists, and if it does, the recovery 
loop is stopped.

-salman



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton?
  2008-02-19 04:23 Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? Praveen Kumar (TUV) <praveen.k@renaissance-it.com>
  2008-02-19 08:19 ` Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? Simon Riggs <simon@2ndquadrant.com>
  2008-02-19 14:23   ` Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? libra dba <libra.dba@gmail.com>
  2008-02-19 15:56     ` Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? salman <salmanb@quietcaresystems.com>
@ 2008-02-20 16:49       ` libra dba <libra.dba@gmail.com>
  2008-02-27 20:54         ` Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? libra dba <libra.dba@gmail.com>
  0 siblings, 1 reply; 11+ messages in thread

From: libra dba @ 2008-02-20 16:49 UTC (permalink / raw)
  To: salman <salmanb@quietcaresystems.com>; +Cc: pgsql-admin

thank you all!

On Feb 19, 2008 10:56 AM, salman <salmanb@quietcaresystems.com> wrote:

>
>
> libra dba wrote:
> > Hello all,
> >
> > Thanks for your response. The example shown by Charles Duffy, is quite
> > impressive. Actually i built my replication based on this.
> >
> > But, i want to test the failover. In the example shown by Charles,
> mentions
> > 'a trigger file' as it says:
> >
> -------------------------------------------------------------------------------
> > *touch ~/pg82demo/trigger*
> > **
> > *This should immediately cause the slave to finish processing archived
> > segments, exit recovery mode, and come up ready for use.
> > *
> >
> -------------------------------------------------------------------------------
> > what shold be the content of the trigger file. How do we cause the slave
> to
> > finish the processing of the archived segments, exit the recovery mode
> and
> > come up ready for use???
> >
> > Please help in the the FAILOVER of the primary and promoting the slave
> to
> > the primary mode.
> >
>
> It's an empty file -- doesn't have to have anything in it. The script
> simply checks to see if the file exists, and if it does, the recovery
> loop is stopped.
>
> -salman
>

^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton?
  2008-02-19 04:23 Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? Praveen Kumar (TUV) <praveen.k@renaissance-it.com>
  2008-02-19 08:19 ` Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? Simon Riggs <simon@2ndquadrant.com>
  2008-02-19 14:23   ` Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? libra dba <libra.dba@gmail.com>
  2008-02-19 15:56     ` Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? salman <salmanb@quietcaresystems.com>
  2008-02-20 16:49       ` Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? libra dba <libra.dba@gmail.com>
@ 2008-02-27 20:54         ` libra dba <libra.dba@gmail.com>
  2008-02-27 21:05           ` Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? salman <salmanb@quietcaresystems.com>
  0 siblings, 1 reply; 11+ messages in thread

From: libra dba @ 2008-02-27 20:54 UTC (permalink / raw)
  To: salman <salmanb@quietcaresystems.com>; +Cc: pgsql-admin

continuing with the postgres replication using WARM STANDBY, what happens to
the wal_files which keeps on generating on the primary?

Do we have to manually remove them or they are removed automatically, once
they have been recovered on the warm standby?

Also a quick question, do we have to have a NFS for the wal_files, which
will be mounted on both the servers, or we can have folders on each server
and configure DRBD between them, so that whatever happens on one is copied
automatically on to the other.

Thanks!

On Wed, Feb 20, 2008 at 11:49 AM, libra dba <libra.dba@gmail.com> wrote:

> thank you all!
>
>
> On Feb 19, 2008 10:56 AM, salman <salmanb@quietcaresystems.com> wrote:
>
> >
> >
> > libra dba wrote:
> > > Hello all,
> > >
> > > Thanks for your response. The example shown by Charles Duffy, is quite
> > > impressive. Actually i built my replication based on this.
> > >
> > > But, i want to test the failover. In the example shown by Charles,
> > mentions
> > > 'a trigger file' as it says:
> > >
> > -------------------------------------------------------------------------------
> > > *touch ~/pg82demo/trigger*
> > > **
> > > *This should immediately cause the slave to finish processing archived
> > > segments, exit recovery mode, and come up ready for use.
> > > *
> > >
> > -------------------------------------------------------------------------------
> > > what shold be the content of the trigger file. How do we cause the
> > slave to
> > > finish the processing of the archived segments, exit the recovery mode
> > and
> > > come up ready for use???
> > >
> > > Please help in the the FAILOVER of the primary and promoting the slave
> > to
> > > the primary mode.
> > >
> >
> > It's an empty file -- doesn't have to have anything in it. The script
> > simply checks to see if the file exists, and if it does, the recovery
> > loop is stopped.
> >
> > -salman
> >
>
>

^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton?
  2008-02-19 04:23 Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? Praveen Kumar (TUV) <praveen.k@renaissance-it.com>
  2008-02-19 08:19 ` Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? Simon Riggs <simon@2ndquadrant.com>
  2008-02-19 14:23   ` Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? libra dba <libra.dba@gmail.com>
  2008-02-19 15:56     ` Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? salman <salmanb@quietcaresystems.com>
  2008-02-20 16:49       ` Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? libra dba <libra.dba@gmail.com>
  2008-02-27 20:54         ` Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? libra dba <libra.dba@gmail.com>
@ 2008-02-27 21:05           ` salman <salmanb@quietcaresystems.com>
  2008-02-28 14:36             ` Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? libra dba <libra.dba@gmail.com>
  2008-03-26 00:25             ` Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? Michael Monnerie <michael.monnerie@it-management.at>
  0 siblings, 2 replies; 11+ messages in thread

From: salman @ 2008-02-27 21:05 UTC (permalink / raw)
  To: libra dba <libra.dba@gmail.com>; +Cc: pgsql-admin



libra dba wrote:
> continuing with the postgres replication using WARM STANDBY, what happens to
> the wal_files which keeps on generating on the primary?
> 
> Do we have to manually remove them or they are removed automatically, once
> they have been recovered on the warm standby?
> 
> Also a quick question, do we have to have a NFS for the wal_files, which
> will be mounted on both the servers, or we can have folders on each server
> and configure DRBD between them, so that whatever happens on one is copied
> automatically on to the other.
> 
> Thanks!
> 

This is what I use -- may not be the best approach, but it has worked 
quite well for me so far:

archive_command = 'gzip %p; rsync -av %p.gz -e \"ssh -p portNum -i 
/home/postgres/.ssh/id_dsa\" 
postgres@rhostname:/usr/local/postgresql/archives/recovery/%f.gz && rm 
-f %p.gz'







^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton?
  2008-02-19 04:23 Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? Praveen Kumar (TUV) <praveen.k@renaissance-it.com>
  2008-02-19 08:19 ` Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? Simon Riggs <simon@2ndquadrant.com>
  2008-02-19 14:23   ` Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? libra dba <libra.dba@gmail.com>
  2008-02-19 15:56     ` Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? salman <salmanb@quietcaresystems.com>
  2008-02-20 16:49       ` Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? libra dba <libra.dba@gmail.com>
  2008-02-27 20:54         ` Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? libra dba <libra.dba@gmail.com>
  2008-02-27 21:05           ` Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? salman <salmanb@quietcaresystems.com>
@ 2008-02-28 14:36             ` libra dba <libra.dba@gmail.com>
  2008-02-28 16:06               ` Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? salman <salmanb@quietcaresystems.com>
  1 sibling, 1 reply; 11+ messages in thread

From: libra dba @ 2008-02-28 14:36 UTC (permalink / raw)
  To: salman <salmanb@quietcaresystems.com>; +Cc: pgsql-admin

Hey Salman,

Thanks a lot for  all your guidance and help. It has really been a great
help. I appreciate your quick replies.

Which replication in postgres is better  ?
SLONY or WARM STANDBY (usning wal).

Another thing which haunts me is that, if we have a warm standby, the
recovery process on the standby server reads an archived file only if it is
16MB, if it is small, the standby server goes down as shown

*quote from the logfile on standby*
~~~~~~~~~~~~~~~~~~~~~~~~~~
*FATAL:  archive file "000000010000000100000032" has wrong size: 491520
instead of 16777216
LOG:  startup process (PID 13177) exited with exit code 1
LOG:  aborting startup due to startup process failure
LOG:  database system was interrupted while in recovery at log time
2008-02-28 00:47:39 EST
*
Usually, an archive file is written every minute. Now, if the last archive
was written at 12:05:00, and the primary server goes down at 12:05:37, the
archive is not generated for the last 37 seconds. The archive file which was
written at 12:05:00 would be applied to the standby. But what will happen to
transactions which happen in the last 37 seconds before the crash???

Do we loose that data???

Thanks!

On Wed, Feb 27, 2008 at 4:05 PM, salman <salmanb@quietcaresystems.com>
wrote:

>
>
> libra dba wrote:
> > continuing with the postgres replication using WARM STANDBY, what
> happens to
> > the wal_files which keeps on generating on the primary?
> >
> > Do we have to manually remove them or they are removed automatically,
> once
> > they have been recovered on the warm standby?
> >
> > Also a quick question, do we have to have a NFS for the wal_files, which
> > will be mounted on both the servers, or we can have folders on each
> server
> > and configure DRBD between them, so that whatever happens on one is
> copied
> > automatically on to the other.
> >
> > Thanks!
> >
>
> This is what I use -- may not be the best approach, but it has worked
> quite well for me so far:
>
> archive_command = 'gzip %p; rsync -av %p.gz -e \"ssh -p portNum -i
> /home/postgres/.ssh/id_dsa\"
> postgres@rhostname:/usr/local/postgresql/archives/recovery/%f.gz && rm
> -f %p.gz'
>
>
>
>
>

^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton?
  2008-02-19 04:23 Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? Praveen Kumar (TUV) <praveen.k@renaissance-it.com>
  2008-02-19 08:19 ` Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? Simon Riggs <simon@2ndquadrant.com>
  2008-02-19 14:23   ` Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? libra dba <libra.dba@gmail.com>
  2008-02-19 15:56     ` Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? salman <salmanb@quietcaresystems.com>
  2008-02-20 16:49       ` Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? libra dba <libra.dba@gmail.com>
  2008-02-27 20:54         ` Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? libra dba <libra.dba@gmail.com>
  2008-02-27 21:05           ` Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? salman <salmanb@quietcaresystems.com>
  2008-02-28 14:36             ` Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? libra dba <libra.dba@gmail.com>
@ 2008-02-28 16:06               ` salman <salmanb@quietcaresystems.com>
  0 siblings, 0 replies; 11+ messages in thread

From: salman @ 2008-02-28 16:06 UTC (permalink / raw)
  To: libra dba <libra.dba@gmail.com>; +Cc: pgsql-admin

libra dba wrote:
> Hey Salman,
> 
> Thanks a lot for  all your guidance and help. It has really been a great
> help. I appreciate your quick replies.
> 
> Which replication in postgres is better  ?
> SLONY or WARM STANDBY (usning wal).
> 

I use both. Between our two production machines, we replicate data with 
slony -- in addition to that, the current master sends its WALs to our 
dev machine which has two instances of postgres installed on it (one 
used for testing by our dev team, the other is constantly doing a restore).

> Another thing which haunts me is that, if we have a warm standby, the
> recovery process on the standby server reads an archived file only if it is
> 16MB, if it is small, the standby server goes down as shown
> 
> *quote from the logfile on standby*
> ~~~~~~~~~~~~~~~~~~~~~~~~~~
> *FATAL:  archive file "000000010000000100000032" has wrong size: 491520
> instead of 16777216
> LOG:  startup process (PID 13177) exited with exit code 1
> LOG:  aborting startup due to startup process failure
> LOG:  database system was interrupted while in recovery at log time
> 2008-02-28 00:47:39 EST
> *
> Usually, an archive file is written every minute. Now, if the last archive
> was written at 12:05:00, and the primary server goes down at 12:05:37, the
> archive is not generated for the last 37 seconds. The archive file which was
> written at 12:05:00 would be applied to the standby. But what will happen to
> transactions which happen in the last 37 seconds before the crash???
> 
> Do we loose that data???

I believe so. Looking at the docs 
(http://www.postgresql.org/docs/8.1/static/backup-online.html), in such 
a case, you can restore to 'within a minute' of the master server. 
Perhaps someone else can provide a better answer.

-salman



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton?
  2008-02-19 04:23 Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? Praveen Kumar (TUV) <praveen.k@renaissance-it.com>
  2008-02-19 08:19 ` Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? Simon Riggs <simon@2ndquadrant.com>
  2008-02-19 14:23   ` Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? libra dba <libra.dba@gmail.com>
  2008-02-19 15:56     ` Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? salman <salmanb@quietcaresystems.com>
  2008-02-20 16:49       ` Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? libra dba <libra.dba@gmail.com>
  2008-02-27 20:54         ` Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? libra dba <libra.dba@gmail.com>
  2008-02-27 21:05           ` Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? salman <salmanb@quietcaresystems.com>
@ 2008-03-26 00:25             ` Michael Monnerie <michael.monnerie@it-management.at>
  1 sibling, 0 replies; 11+ messages in thread

From: Michael Monnerie @ 2008-03-26 00:25 UTC (permalink / raw)
  To: pgsql-admin

On Mittwoch, 27. Februar 2008 salman wrote:
> archive_command = 'gzip %p; rsync -av %p.gz -e \"ssh -p portNum -i
> /home/postgres/.ssh/id_dsa\"
> postgres@rhostname:/usr/local/postgresql/archives/recovery/%f.gz &&
> rm -f %p.gz'

This works as long as there are no transmission errors. What can be done 
to work around network problems? Example: when one rsync above does not 
work, that log is never transmitted to the other host. Does somebody 
have a sophisticated script for that?

mfg zmi
-- 
// Michael Monnerie, Ing.BSc    -----      http://it-management.at
// Tel: 0676/846 914 666                      .network.your.ideas.
// PGP Key:         "curl -s http://zmi.at/zmi.asc | gpg --import"
// Fingerprint: AC19 F9D5 36ED CD8A EF38  500E CE14 91F7 1C12 09B4
// Keyserver: www.keyserver.net                   Key-ID: 1C1209B4

Attachments:

  signature.asc (application/pgp-signature, 193B) download

^ permalink  raw  reply  [nested|flat] 11+ messages in thread


end of thread, other threads:[~2008-03-26 00:25 UTC | newest]

Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-02-14 22:41 Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? libra dba <libra.dba@gmail.com>
2008-02-19 04:23 Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? Praveen Kumar (TUV) <praveen.k@renaissance-it.com>
2008-02-19 08:19 ` Simon Riggs <simon@2ndquadrant.com>
2008-02-19 14:23   ` libra dba <libra.dba@gmail.com>
2008-02-19 15:56     ` salman <salmanb@quietcaresystems.com>
2008-02-20 16:49       ` libra dba <libra.dba@gmail.com>
2008-02-27 20:54         ` libra dba <libra.dba@gmail.com>
2008-02-27 21:05           ` salman <salmanb@quietcaresystems.com>
2008-02-28 14:36             ` libra dba <libra.dba@gmail.com>
2008-02-28 16:06               ` salman <salmanb@quietcaresystems.com>
2008-03-26 00:25             ` Michael Monnerie <michael.monnerie@it-management.at>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox