record-based log shipping

Hello,

I have a question about log shipping. The documentation from
PostgreSQL 8.4, in section 24.4.4, describes how to archive partial
WAL files by using the pg_xlogfile_name_offset() function, which it
calls "record-based log shipping". Although it seems that this section
of documentation has been removed in 9.0, the capability is apparently
still there. (Of course 9.0 comes with streaming replication, but the
ability to perform archiving this way is attractive as it can be used
to replicate the database to a non-sql data storage system.)

Well, I have successfully written an archiving script to copy the
partial WAL segments as described, but I am confused as to how I would
actually use this data for a recovery.

Section 24.4.4 states that the "restore_command scripts still deal in
whole WAL files, so the incrementally copied data is not ordinarily
made available to the standby servers. It is of use only when the
primary dies — then the last partial WAL file is fed to the standby
before allowing it to come up."

So how would one "feed" incrementally copied partial WAL file data to
a standby (actually a new server used for recovery) as suggested
above?

Thanks.

On Sun, Aug 21, 2011 at 08:23, Dondi Michael Stroma <dstroma@gmail.com> wrote:

Hello,

I have a question about log shipping. The documentation from
PostgreSQL 8.4, in section 24.4.4, describes how to archive partial
WAL files by using the pg_xlogfile_name_offset() function, which it
calls "record-based log shipping". Although it seems that this section
of documentation has been removed in 9.0, the capability is apparently
still there. (Of course 9.0 comes with streaming replication, but the
ability to perform archiving this way is attractive as it can be used
to replicate the database to a non-sql data storage system.)

Well, I have successfully written an archiving script to copy the
partial WAL segments as described, but I am confused as to how I would
actually use this data for a recovery.

Section 24.4.4 states that the "restore_command scripts still deal in
whole WAL files, so the incrementally copied data is not ordinarily
made available to the standby servers. It is of use only when the
primary dies — then the last partial WAL file is fed to the standby
before allowing it to come up."

So how would one "feed" incrementally copied partial WAL file data to
a standby (actually a new server used for recovery) as suggested
above?

Pad it with zeroes up to the normal segment limit (16Mb), and feed
that. PostgreSQL will detect when the correct data ends and the
padding begins.

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

On Sun, Aug 21, 2011 at 7:23 AM, Dondi Michael Stroma <dstroma@gmail.com> wrote:

Hello,

I have a question about log shipping. The documentation from
PostgreSQL 8.4, in section 24.4.4, describes how to archive partial
WAL files by using the pg_xlogfile_name_offset() function, which it
calls "record-based log shipping". Although it seems that this section
of documentation has been removed in 9.0, the capability is apparently
still there. (Of course 9.0 comes with streaming replication, but the
ability to perform archiving this way is attractive as it can be used
to replicate the database to a non-sql data storage system.)

Well, I have successfully written an archiving script to copy the
partial WAL segments as described, but I am confused as to how I would
actually use this data for a recovery.

Section 24.4.4 states that the "restore_command scripts still deal in
whole WAL files, so the incrementally copied data is not ordinarily
made available to the standby servers. It is of use only when the
primary dies — then the last partial WAL file is fed to the standby
before allowing it to come up."

So how would one "feed" incrementally copied partial WAL file data to
a standby (actually a new server used for recovery) as suggested
above?

Using walmgr
http://skytools.projects.postgresql.org/doc/walmgr.html

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

On Sun, Aug 21, 2011 at 3:14 AM, Magnus Hagander <magnus@hagander.net> wrote:

On Sun, Aug 21, 2011 at 08:23, Dondi Michael Stroma <dstroma@gmail.com> wrote:

So how would one "feed" incrementally copied partial WAL file data to
a standby

Pad it with zeroes up to the normal segment limit (16Mb), and feed
that. PostgreSQL will detect when the correct data ends and the
padding begins.

Thanks, that is what I was looking for. Worked perfectly.