Thread: 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.