Re: Streaming rep - why log shipping is necessary?

marcin mank wrote:
> I was reading the SR docs, and have the following question:
> Is there a fundamental reason why archive_command etc. is required in
> streaming replication mode?
>
> Can`t setting up the standby be more like:
> pg_start_streaming_backup() on the master (this will be queuing up
> files in pg_xlog)
> copy the data dir
> set up the slave to connect to the master via streaming protocol
> set up the master to allow connections from the slave
> start slave (slave pulls the necessary WAL records from the master via
> streaming, and signals the master that it`s done backing up)
> When standby starts accepting connections, we know that the standby is OK.
>
> archive_command, restore_command, etc. would be configured empty in this mode.
>
> The failure mode for this is the pg_xlog directory filling up on the
> master before the backup is done. But then, we can tell people to use
> the more combersome, current setup.

The problem is first of all that there is no pg_start_streaming_backup()
command, but it's not only an issue during backup; the standby needs to
fall back to the archive if it falls behind so that the WAL files it
needs have already been recycled in the master.

There was the idea of adding a 'replication_lag_segments' setting, so
that the master always keeps n megabytes of WAL available for the
standby servers. See
http://archives.postgresql.org/pgsql-hackers/2010-01/msg02073.php. Not
sure what happened to it, Fujii was working on it but I guess he got
busy with other things.

If you're adventurous enough, it's actually possible to set an
archive_command that checks the status of the standby and returns
failure as long as the standby still needs the given WAL segment. That
way the primary doesn't recycle segments that are still needed by the
standby, and you can get away without restore_command in the standby.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com