Re: streamlined standby procedure

IMHO the #1 priority in the current PITR/WAL shipping system is to make the
standby able to tolerate being shut down and restarted, i.e. actually having
a true standby mode and not the current method of doing it only on startup.

While it is a trivial thing to fool postgres into staying in startup/restore
mode with a restore_command that blocks until more files are available, if
the machine needs to be shut down for whatever reason you have to go back to
the last image and replay to the present, which isn't always convenient. Nor
are you able to shut down the standby, copy it to a second instance to use
for testing/development/whatever, and restart the standby.

(Just to be clear - I _really_ like the flexibility of the customizable
archive and restore structure with PITR in PG, but the lack of a standby
mode always reminds me of whacking my forehead at 3am on the too-low doorway
into my son's bedroom...)

On 2/7/06 10:11 AM, "Csaba Nagy" <nagy(at)ecircle-ag(dot)com> wrote:

> 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 base files, 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.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly