Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton?

Hello ,

Go through this below mentioned link

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

_____

From: pgsql-admin-owner(at)postgresql(dot)org
[mailto:pgsql-admin-owner(at)postgresql(dot)org] On Behalf Of libra dba
Sent: Friday, February 15, 2008 4:11 AM
To: pgsql-admin(at)postgresql(dot)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

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

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(at)2ndquadrant(dot)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/>
>
>

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

thank you all!

On Feb 19, 2008 10:56 AM, salman <salmanb(at)quietcaresystems(dot)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
>

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(dot)dba(at)gmail(dot)com> wrote:

> thank you all!
>
>
> On Feb 19, 2008 10:56 AM, salman <salmanb(at)quietcaresystems(dot)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
> >
>
>

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(at)rhostname:/usr/local/postgresql/archives/recovery/%f.gz && rm
-f %p.gz'

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(at)quietcaresystems(dot)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(at)rhostname:/usr/local/postgresql/archives/recovery/%f.gz && rm
> -f %p.gz'
>
>
>
>
>

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

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(at)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