Re: Forcing current WAL file to be archived

OK, makes sense. That is much more sophisticated that I imagined.

---------------------------------------------------------------------------

Csaba Nagy wrote:
> > OK, "offset" added to TODO item. What would the offset give us?
>
> The last offset could be remembered by the external program, and it only
> has to transfer from the last offset to the new one. It allows
> incremental streaming of the WAL files... of course the external program
> will be a lot more complex than the current shell scripts which can be
> used for WAL archiving...
>
> The problems I see with this is if in this case the normal postgres WAL
> archiving won't conflict with this streaming ? And if yes, wouldn't it
> be better to have a separate mechanism for the stream based archiving ?
> I mean what will happen if postgres successfully archives the WAL file
> and then moves it out of way before the streaming process finishes with
> it, the streaming process will have a problem...
>
> A few months ago I spent some time thinking about a solution where a WAL
> based standby could be built using only normal data base connections to
> the master server, and one of the ideas was to create a WAL subscription
> mechanism where the standby subscribes for getting WAL files, and
> updates it's subscription status with the last processed WAL file after
> each processed file. The master can then recycle the WAL files only
> after they were confirmed by all current subscriptions... and to avoid
> excessive WAL file bloat if a slave goes offline, the subscription could
> be canceled automatically if it gets too much behind.
>
> If this mechanism is in place, it would be also nice if the slave could
> ask for the WAL records to be streamed on a normal data base connection.
> The function which would do it could be smart enough to stream the
> current WAL file too up to the current offset and then wait for new
> records. The slave would invoke the function for each WAL file it needs
> to transfer, and then when finished it would update it's subscription
> status and continue with the next one. The streaming function should not
> update the subscription status as this way the slave can ask for the
> file again if something goes wrong with the transfer.
>
> The third thing needed to create a facility for one-connection-standby
> building is to be able to stream the OS files of the DB through a DB
> connection - I guess that can be done with a relatively simple C
> function...
>
> With all these things in place, a program could be written which would
> run on the standby machine and completely automatically set up the
> standby, only needing a simple connection string to the master...
>
> Cheers,
> Csaba.
>

--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +