warm standby with WAL shipping

On Tue, 2009-06-02 at 15:21 -0400, Geoffrey wrote:

My problem is, I never see a *.history file, thus my script sits in a
loop looking for it. I see the WAL files showing up on the archive
server, but I don't see a *.history file.

What am I missing?

pg_standby it self isn't a solution for warm standby. It is a component
thereof. Also don't use SCP. Use rsync. Take a look at walmgr or
PITRTools it will make your life easier.

Joshua D. Drake

--
PostgreSQL - XMPP: jdrake@jabber.postgresql.org
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997

Joshua D. Drake wrote:

On Tue, 2009-06-02 at 15:21 -0400, Geoffrey wrote:

My problem is, I never see a *.history file, thus my script sits in a
loop looking for it. I see the WAL files showing up on the archive
server, but I don't see a *.history file.

What am I missing?

pg_standby it self isn't a solution for warm standby. It is a component
thereof. Also don't use SCP. Use rsync. Take a look at walmgr or
PITRTools it will make your life easier.

Okay, I'm attempting to check out PITRTools:

svn co https://projects.commandprompt.com/public/pitrtools/repo/branches/1.2

svn: Unrecognized URL scheme for
'https://projects.commandprompt.com/public/pitrtools/repo/branches/1.2'

What am I missing here?

--
Until later, Geoffrey

Those who would give up essential Liberty, to purchase a little
temporary Safety, deserve neither Liberty nor Safety.
- Benjamin Franklin

Geoffrey wrote:

Joshua D. Drake wrote:

On Tue, 2009-06-02 at 15:21 -0400, Geoffrey wrote:

My problem is, I never see a *.history file, thus my script sits in a
loop looking for it. I see the WAL files showing up on the archive
server, but I don't see a *.history file.

What am I missing?

pg_standby it self isn't a solution for warm standby. It is a component
thereof. Also don't use SCP. Use rsync. Take a look at walmgr or
PITRTools it will make your life easier.

Okay, I'm attempting to check out PITRTools:

svn co
https://projects.commandprompt.com/public/pitrtools/repo/branches/1.2

svn: Unrecognized URL scheme for
'https://projects.commandprompt.com/public/pitrtools/repo/branches/1.2'

What am I missing here?

Nevermind, ID 10T error.

--
Until later, Geoffrey

Those who would give up essential Liberty, to purchase a little
temporary Safety, deserve neither Liberty nor Safety.
- Benjamin Franklin

Joshua D. Drake wrote:

On Tue, 2009-06-02 at 15:21 -0400, Geoffrey wrote:

My problem is, I never see a *.history file, thus my script sits in a
loop looking for it. I see the WAL files showing up on the archive
server, but I don't see a *.history file.

What am I missing?

pg_standby it self isn't a solution for warm standby. It is a component
thereof. Also don't use SCP. Use rsync. Take a look at walmgr or
PITRTools it will make your life easier.

So, I'm looking at the PITRTools stuff, but I really want to understand
how this all works. I'm shipping my wall files fine. I've been able to
recreate my database from a backup and the accumulated WAL files. The
problem with my current process is as noted, my script keeps looking for
the *.history file, but never sees it. I see the list of files on my
archive machine growing, so I know WAL shipping is working. I've created
my backup as instructed in the docs. I don't know how/where the
*.history file is generated and/or why it's not showing up in the
archive directory with the WAL files.

--
Until later, Geoffrey

Those who would give up essential Liberty, to purchase a little
temporary Safety, deserve neither Liberty nor Safety.
- Benjamin Franklin

Joshua D. Drake wrote:

On Tue, 2009-06-02 at 15:21 -0400, Geoffrey wrote:

My problem is, I never see a *.history file, thus my script sits in a
loop looking for it. I see the WAL files showing up on the archive
server, but I don't see a *.history file.

What am I missing?

pg_standby it self isn't a solution for warm standby. It is a component
thereof. Also don't use SCP. Use rsync. Take a look at walmgr or
PITRTools it will make your life easier.

I still don't understand why the pg_standby code is looking for the
*.history. Apparently others have seen this same behavior (according to
google), yet I don't see any definitive answer.

I don't mind looking at other tools, but I want to understand this
process. I can't find what files are processed in what order, although
according to the pg_standby.c code, there are at least three different
files it's looking for: *.history, *.backup, and the standard WAL files.

Is this documented anywhere?

It appears the PITRTools use pg_standby binary, thus I'm still confused
as to how these files are processed.

--
Until later, Geoffrey

Those who would give up essential Liberty, to purchase a little
temporary Safety, deserve neither Liberty nor Safety.
- Benjamin Franklin

On Tue, 2009-06-02 at 19:44 -0400, Geoffrey wrote:

pg_standby it self isn't a solution for warm standby. It is a component
thereof. Also don't use SCP. Use rsync. Take a look at walmgr or
PITRTools it will make your life easier.

I still don't understand why the pg_standby code is looking for the
*.history. Apparently others have seen this same behavior (according to
google), yet I don't see any definitive answer.

http://www.postgresql.org/docs/8.3/static/warm-standby.html
http://www.postgresql.org/docs/8.3/static/continuous-archiving.html#BACKUP-ARCHIVING-WAL

I think will have what you want.

It appears the PITRTools use pg_standby binary, thus I'm still confused
as to how these files are processed.

Yes it does use pg_standby. It just wraps everything that is missing for
warm standby into a single utility.

Joshua D. Drake

--
PostgreSQL - XMPP: jdrake@jabber.postgresql.org
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997

On Tue, 2 Jun 2009, Geoffrey wrote:

The problem with my current process is as noted, my script keeps looking
for the *.history file, but never sees it.

From the restore_command section of the documentation:

"The command will be asked for log files that are not present in the
archive; it must return nonzero when so asked. This is not an error
condition."

So if you're asked for a .history file, and you don't have one, return an
error state saying as much and the right thing will happen--recovery
continues. More comments about the path everyone wanders down when trying
to build their own tools here are at
http://archives.postgresql.org/sydpug/2006-10/msg00001.php , you'll
probably get some more insight into the details here reading that early
commentary.

But you still want to know where they might come from, right? Those
history files show up when you've started your backup server after
recovering files from the original system. You need to bring the backup
system out of standby before you'll see one. That results in a new
timeline:
http://www.postgresql.org/docs/8.3/static/continuous-archiving.html#BACKUP-TIMELINES

Think about for a second: if the original server is still running, but
you've started the standby system too, there are two separate histories
with a common ancestor possible. One history has the original data plus
what happened afterwards on the master, the other has the originals plus
what happened afterwards on the standby, after it was started. The fun
part is that you can return to copying files from the master again, so
that you've got both sets of files available. You then choose which
history to follow by adjusting the recovery_target_timeline parameter in
the recovery.conf file.

Anyway, while getting your hands dirty so you understand what's happening
is a good idea, trying to fully reinvent pg_standby is an exercise
destined to have a whole stack of little issues like these. Don't do
that; it's taken years to get that code as mature as it is, and while
you'll progress faster because you can stare at its source it will still
take you a while. Returning to your original motivation for doing that, I
threw a suggestion for how to combine pg_standby with using scp as the
transport mechanism into http://wiki.postgresql.org/wiki/Warm_Standby ,
you just need to buffer transfers into a holding area to get around the
atomic copy issues here. This requires using a non-trivial
archive_command process though, you'll need to call a real script there to
handle the multiple steps involved rather than just getting away with a
one-line command for that setting. I reinvent that wheel periodically for
sites that can't or won't install rsync for the job instead (always some
variant on "for security reasons"). Unfortunately those sites also don't
like releasing the resulting code to the world at large, so I don't have a
full sample to show you.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

Joshua D. Drake wrote:

On Tue, 2009-06-02 at 19:44 -0400, Geoffrey wrote:

pg_standby it self isn't a solution for warm standby. It is a component
thereof. Also don't use SCP. Use rsync. Take a look at walmgr or
PITRTools it will make your life easier.

I still don't understand why the pg_standby code is looking for the
*.history. Apparently others have seen this same behavior (according to
google), yet I don't see any definitive answer.

http://www.postgresql.org/docs/8.3/static/warm-standby.html
http://www.postgresql.org/docs/8.3/static/continuous-archiving.html#BACKUP-ARCHIVING-WAL

I think will have what you want.

I have read this documentation, unfortunately a couple of times. Is the
section '24.3.4 Timelines' referencing these *.history files?

Is there any documentation out there that specifically lays out what the
files are and how they are processed? Or, is that what I'm looking at
in 24.3.4?

It appears the PITRTools use pg_standby binary, thus I'm still confused
as to how these files are processed.

Yes it does use pg_standby. It just wraps everything that is missing for
warm standby into a single utility.

Looking at the pg_standby.c I have, I don't see a reference to scp or
rsync. If I use the PITRTools, is there a different version or do I
need to modify this version to use rsync.

--
Until later, Geoffrey

Those who would give up essential Liberty, to purchase a little
temporary Safety, deserve neither Liberty nor Safety.
- Benjamin Franklin

Thank you Greg for taking the time to explain this as throughly as you
have. I have found a logic problem in my code. I still don't know if
we will use pg_standby as the wrapper code in PITRTools is python and we
are not a python shop. Kinda want to stick with what we know (C, perl,
shell). I'm certainly looking at rsync rather then scp, which really
makes more sense.

Greg Smith wrote:

On Tue, 2 Jun 2009, Geoffrey wrote:

The problem with my current process is as noted, my script keeps
looking for the *.history file, but never sees it.

From the restore_command section of the documentation:

"The command will be asked for log files that are not present in the
archive; it must return nonzero when so asked. This is not an error
condition."

So if you're asked for a .history file, and you don't have one, return
an error state saying as much and the right thing will happen--recovery
continues. More comments about the path everyone wanders down when
trying to build their own tools here are at
http://archives.postgresql.org/sydpug/2006-10/msg00001.php , you'll
probably get some more insight into the details here reading that early
commentary.

But you still want to know where they might come from, right? Those
history files show up when you've started your backup server after
recovering files from the original system. You need to bring the backup
system out of standby before you'll see one. That results in a new
timeline:
http://www.postgresql.org/docs/8.3/static/continuous-archiving.html#BACKUP-TIMELINES

Think about for a second: if the original server is still running, but
you've started the standby system too, there are two separate histories
with a common ancestor possible. One history has the original data plus
what happened afterwards on the master, the other has the originals plus
what happened afterwards on the standby, after it was started. The fun
part is that you can return to copying files from the master again, so
that you've got both sets of files available. You then choose which
history to follow by adjusting the recovery_target_timeline parameter in
the recovery.conf file.

Anyway, while getting your hands dirty so you understand what's
happening is a good idea, trying to fully reinvent pg_standby is an
exercise destined to have a whole stack of little issues like these.
Don't do that; it's taken years to get that code as mature as it is, and
while you'll progress faster because you can stare at its source it will
still take you a while. Returning to your original motivation for doing
that, I threw a suggestion for how to combine pg_standby with using scp
as the transport mechanism into
http://wiki.postgresql.org/wiki/Warm_Standby , you just need to buffer
transfers into a holding area to get around the atomic copy issues
here. This requires using a non-trivial archive_command process though,
you'll need to call a real script there to handle the multiple steps
involved rather than just getting away with a one-line command for that
setting. I reinvent that wheel periodically for sites that can't or
won't install rsync for the job instead (always some variant on "for
security reasons"). Unfortunately those sites also don't like releasing
the resulting code to the world at large, so I don't have a full sample
to show you.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

--
Until later, Geoffrey

Those who would give up essential Liberty, to purchase a little
temporary Safety, deserve neither Liberty nor Safety.
- Benjamin Franklin

On Wed, Jun 03, 2009 at 08:13:48AM -0400, Geoffrey wrote:

I'm certainly looking at rsync rather then scp, which really makes more
sense.

You can mix ssh and rsync. The man pages has this example:
rsync -az -e ssh --delete ~ftp/pub/samba/ nimbus:"~ftp/pub/tridge/samba"

On Jun 3, 2009, at 5:13 AM, Geoffrey wrote:

Thank you Greg for taking the time to explain this as throughly as
you have. I have found a logic problem in my code. I still don't
know if we will use pg_standby as the wrapper code in PITRTools is
python and we are not a python shop. Kinda want to stick with what
we know (C, perl, shell). I'm certainly looking at rsync rather
then scp, which really makes more sense.

pg_standby is in no way dependent on PITRTools. PITRTools is,
however, dependent on pg_standby. Put another way: you do not need
to use PITRTools to use pg_standby. In fact, you also don't need any
perl or shell scripts to use pg_standby, just use rsync directly in
the archive_command on the master and pg_standby in the
recovery_command on the standby. The wiki link Greg provided (http://wiki.postgresql.org/wiki/Warm_Standby
) has all of the info needed to set things up manually.

Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k

Erik Jones wrote:

On Jun 3, 2009, at 5:13 AM, Geoffrey wrote:

Thank you Greg for taking the time to explain this as throughly as you
have. I have found a logic problem in my code. I still don't know if
we will use pg_standby as the wrapper code in PITRTools is python and
we are not a python shop. Kinda want to stick with what we know (C,
perl, shell). I'm certainly looking at rsync rather then scp, which
really makes more sense.

pg_standby is in no way dependent on PITRTools. PITRTools is, however,
dependent on pg_standby. Put another way: you do not need to use
PITRTools to use pg_standby. In fact, you also don't need any perl or
shell scripts to use pg_standby, just use rsync directly in the
archive_command on the master and pg_standby in the recovery_command on
the standby. The wiki link Greg provided
(http://wiki.postgresql.org/wiki/Warm_Standby) has all of the info
needed to set things up manually.

Our current scenario is that we are archiving from machine A to machine
B. Our hot spare is machine C, thus we are pulling the files via
network from machine B to machine C, hence the reason I don't believe
db_standby will work as it has no facility (rsync,scp) to retrieve the
files from another machine.

--
Until later, Geoffrey

Those who would give up essential Liberty, to purchase a little
temporary Safety, deserve neither Liberty nor Safety.
- Benjamin Franklin

On Wed, 2009-06-03 at 14:43 -0400, Geoffrey wrote:

pg_standby is in no way dependent on PITRTools. PITRTools is, however,
dependent on pg_standby. Put another way: you do not need to use
PITRTools to use pg_standby. In fact, you also don't need any perl or
shell scripts to use pg_standby, just use rsync directly in the
archive_command on the master and pg_standby in the recovery_command on
the standby. The wiki link Greg provided
(http://wiki.postgresql.org/wiki/Warm_Standby) has all of the info
needed to set things up manually.

Our current scenario is that we are archiving from machine A to machine
B. Our hot spare is machine C, thus we are pulling the files via
network from machine B to machine C, hence the reason I don't believe
db_standby will work as it has no facility (rsync,scp) to retrieve the
files from another machine.

The point that is being made is that pg_standby doesn't need pitrtools
to do its job. That is all. It is also why I said that pg_standby is
just a component of a PITR solution and not a PITR Solution in itself.
You are still going to need to either:

A. Reinvent the wheel, by scripting it all yourself
B. Use solutions that are already used by others such as walmgr or
pitrtools

Joshua D. Drake

--
PostgreSQL - XMPP: jdrake@jabber.postgresql.org
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997

Joshua D. Drake wrote:

On Wed, 2009-06-03 at 14:43 -0400, Geoffrey wrote:

pg_standby is in no way dependent on PITRTools. PITRTools is, however,
dependent on pg_standby. Put another way: you do not need to use
PITRTools to use pg_standby. In fact, you also don't need any perl or
shell scripts to use pg_standby, just use rsync directly in the
archive_command on the master and pg_standby in the recovery_command on
the standby. The wiki link Greg provided
(http://wiki.postgresql.org/wiki/Warm_Standby) has all of the info
needed to set things up manually.

Our current scenario is that we are archiving from machine A to machine
B. Our hot spare is machine C, thus we are pulling the files via
network from machine B to machine C, hence the reason I don't believe
db_standby will work as it has no facility (rsync,scp) to retrieve the
files from another machine.

The point that is being made is that pg_standby doesn't need pitrtools
to do its job. That is all. It is also why I said that pg_standby is
just a component of a PITR solution and not a PITR Solution in itself.

I understand his point very clearly.

You are still going to need to either:

A. Reinvent the wheel, by scripting it all yourself
B. Use solutions that are already used by others such as walmgr or
pitrtools

My assumption was that since pg_standby does not have the scp/rsync
functionality, I would have to either modify it, change the way we do
things, or 'reinvent' a little different wheel.

There is also an objection to using the python tools as we are small
shop and do not have anyone who is versed in python.

I have not had a chance to look at walmgr, I will do that shortly.

--
Until later, Geoffrey

Those who would give up essential Liberty, to purchase a little
temporary Safety, deserve neither Liberty nor Safety.
- Benjamin Franklin

On Wed, 3 Jun 2009, Geoffrey wrote:

My assumption was that since pg_standby does not have the scp/rsync
functionality, I would have to either modify it, change the way we do things,
or 'reinvent' a little different wheel.

There are three things to setup here:

1) archive_command on the master
2) Transport between master and standby(s)
3) recovery_command. pg_standby is the reference implementation here.

You can combine (1) and (2) by putting some sort of network copy command
into the archive_command, but better practice here (and probably required
practice in your case) is to write a script that does that instead.
That's the part you need to worry about.

There is no need for you to reinvent (3) just because you have different
requirements than most for (2). As you've noticed, pg_standby doesn't
actually do the network transport part, and that also means that it's
decoupled from what choices you make for that layer. Focus on writing
scripts to atomically copy the files into the right destination on the
standbys, and pg_standby will take care of applying the shipped log files
to the database.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

On Wed, 2009-06-03 at 15:07 -0400, Geoffrey wrote:

You are still going to need to either:

A. Reinvent the wheel, by scripting it all yourself
B. Use solutions that are already used by others such as walmgr or
pitrtools

My assumption was that since pg_standby does not have the scp/rsync
functionality, I would have to either modify it, change the way we do
things, or 'reinvent' a little different wheel.

You wouldn't modify pg_standby. You would create a new utility that
ships logs (or pulls logs) for pg_standby to utilize.

There is also an objection to using the python tools as we are small
shop and do not have anyone who is versed in python.

At best this seems like a very odd requirement. You aren't doing any
development. You are using a utility which is written in a specific
language.

I have not had a chance to look at walmgr, I will do that shortly.

I believe it is written in Python.

Anyway good luck!

Joshua D. Drkae

--
PostgreSQL - XMPP: jdrake@jabber.postgresql.org
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997

On Wednesday 03 June 2009 15:26, Greg Smith wrote:

On Wed, 3 Jun 2009, Geoffrey wrote:

My assumption was that since pg_standby does not have the scp/rsync
functionality, I would have to either modify it, change the way we do
things, or 'reinvent' a little different wheel.

There are three things to setup here:

1) archive_command on the master
2) Transport between master and standby(s)
3) recovery_command. pg_standby is the reference implementation here.

You can combine (1) and (2) by putting some sort of network copy command
into the archive_command, but better practice here (and probably required
practice in your case) is to write a script that does that instead.
That's the part you need to worry about.

There is no need for you to reinvent (3) just because you have different
requirements than most for (2). As you've noticed, pg_standby doesn't
actually do the network transport part, and that also means that it's
decoupled from what choices you make for that layer. Focus on writing
scripts to atomically copy the files into the right destination on the
standbys, and pg_standby will take care of applying the shipped log files
to the database.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

Our circumstance here is that we will be feeding multiple warm stand-by
servers; one local and the rest remote, that is, at least one in other state
and possibly another in another city. We didn't want the WAL shipping process
to fail because one of the nodes might be down. To circumvent that, we
thought the best approach to take was to pump the WAL logs to a central
machine on-site, and have the warm stand-by servers pick up their files from
the central storage device. This is why we were thinking about changing
pg_standby.

Thanks for all the help...
--

Work: 1-336-372-6812
Cell: 1-336-404-6987
email: terry@chosen-ones.org

On Wed, 2009-06-03 at 16:45 -0400, Terry Lee Tucker wrote:

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

Our circumstance here is that we will be feeding multiple warm stand-by
servers; one local and the rest remote, that is, at least one in other state
and possibly another in another city. We didn't want the WAL shipping process
to fail because one of the nodes might be down. To circumvent that, we
thought the best approach to take was to pump the WAL logs to a central
machine on-site, and have the warm stand-by servers pick up their files from
the central storage device. This is why we were thinking about changing
pg_standby.

PITRTools 1.2 has queuing which can deal with this problem.

I don't know if walmgr does.

Joshua D. Drake

Thanks for all the help...
--

Work: 1-336-372-6812
Cell: 1-336-404-6987
email: terry@chosen-ones.org

--
PostgreSQL - XMPP: jdrake@jabber.postgresql.org
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997

On Wednesday 03 June 2009 17:11, Joshua D. Drake wrote:

On Wed, 2009-06-03 at 16:45 -0400, Terry Lee Tucker wrote:

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore,
MD

Our circumstance here is that we will be feeding multiple warm stand-by
servers; one local and the rest remote, that is, at least one in other
state and possibly another in another city. We didn't want the WAL
shipping process to fail because one of the nodes might be down. To
circumvent that, we thought the best approach to take was to pump the WAL
logs to a central machine on-site, and have the warm stand-by servers
pick up their files from the central storage device. This is why we were
thinking about changing pg_standby.

PITRTools 1.2 has queuing which can deal with this problem.

I don't know if walmgr does.

We will look into PITRTools then. I'm nervous about re-inventing the wheel as
has been pointed out.

Thanks for the input...

Joshua D. Drake

Thanks for all the help...
--

Work: 1-336-372-6812
Cell: 1-336-404-6987
email: terry@chosen-ones.org

--
Work: 1-336-372-6812
Cell: 1-336-404-6987
email: terry@leetuckert.net