Re: Forcing current WAL file to be archived

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