[ADMIN] Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton?

[libra dba]

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

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

[libra dba]

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 <[EMAIL PROTECTED]> 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/msg1.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 
>
>

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

[libra dba]

thank you all!

On Feb 19, 2008 10:56 AM, salman <[EMAIL PROTECTED]> 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
>

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

[libra dba]

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 <[EMAIL PROTECTED]> wrote:

> thank you all!
>
>
> On Feb 19, 2008 10:56 AM, salman <[EMAIL PROTECTED]> 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
> >
>
>

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

[libra dba]

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 "000100010032" 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 <[EMAIL PROTECTED]>
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\"
> [EMAIL PROTECTED]:/usr/local/postgresql/archives/recovery/%f.gz && rm
> -f %p.gz'
>
>
>
>
>

[ADMIN] using warm standby with drbd???

[libra dba]

Hi All!

I have configured the warm standby using WAL FILES and DRBD. The wal files
are generated as server A. I am using DRBD to replicate these wal files
between server A and server B. The DRBD service is running as Primary on
Server A and secondary on Server B. The wal_files are written to the server
B, but there is a drawback for DRBD. The filesystme needs to be unmounted on
the server B (where DRBD is running secondary role).

So recovery is not countinuous.(file systme is unmounted). To start the
recovery, i need to change the DRBD role to primary on server B. After that
i have to mount the filesystem. Once i mount the filesystem, the recovery
process starts.

Is there any way in which i need not switch secondary/primary role for DRBD
on server B?
Is there any way in which i can have the file system mounted on server B,
running DRBD secondary role?

How else can i replicate the wal_files? ( i don't want to user common file
system ,,, NFS,,, etc.)?

Another thing which i want to ask is that if we are generating archives
every 1 minute. then what happens to the data which was written to the
server A after 35 seconds after the last wal file generation.(server A
crashes).

Since this data has not been archived (it was supposed to archive after 1
minute), do i think that this is the loss of data in a server A crash as
this 35 second data has not been written to the archived logs and neither
has been transported to server B?

A quick reply is highly appreciated!

Thanks!