streamlined standby procedure - Mailing list pgsql-hackers

Hi all,

I decided to start implementing a streamlined WAL shipping based standby
building procedure. My aim is fairly simple: to be able to build a
standby as automated as possible. 

The ultimate simplicity would be for me: - install postgres on the standby machine;- create a directory for the data
basefiles, containing
 
postgresql.conf and pg_hba.conf, and a standby.conf file;- start up the postmaster with a "--build-standby" option;

All the rest should be done automatically by postgres.

The procedure should be something similar to the one available today if
you do it manually. The main difference would be that the standby
postmaster should connect to the primary server, and get all table data
and WAL record stream through normal data base connections...

To facilitate this process, I thought about why not expose the WAL files
through a system view ? Something along the lines of:

pg_wal ( name text, walrecords blob, iscurrent boolean
)

Then anybody interested in the WAL record stream could easily find out
which is the current WAL record, and get any of the existing WAL records
by streaming the blob. Closed WAL files would be streamed completely,
and the current WAL file could be streamed in realtime as it is
created... this would facilitate an always as up to date as possible
standby, as it could get the WAL records in real time.

To make it possible to reliably get closed WAL records, a WAL
subscription system could be created, where a subscriber (the standby)
could signal which is the oldest WAL file it did not get yet. The
primary machine would keep all the WAL files extending back to the
oldest subscribed one. Then each time the subscriber finishes processing
a WAL file, it can signal it's interest in the next one. This could be
implemented by a table like:

pg_wal_subscription ( subscriber text, name text
)

The subscribers would insert a record in this table, and update it to
the next WAL file after they processed one. The subscriber names should
be unique across subscribers, this should be managed by the admin who
sets up the subscribers. When the subscriber is not interested anymore,
it can delete it's subscription record. That could be done by the DBA
too if things go haywire...

To build a stand by based on log shipping it is necessary to get over
all the data base files too. That could be also done by exposing them
through some view, which in turn might take advantage of knowledge of
the table structure to compress the data to be transferred. The main
idea is to do all transfers through normal DB connections, so the only
configuration to be done is to point the standby to the master
machine...

So, all this said, I'm not too familiar with either C programming or the
postgres sources, but I'm willing to learn. And the project as a whole
seems a bit too much to do it in one piece, so my first aim is to expose
the WAL records in a system view.

I would really appreciate any comments you have...

Thanks,
Csaba.