Re: Streaming Rep - 2-phase backups and reducing time to full replication

On Thu, 2009-12-24 at 14:40 +0900, Fujii Masao wrote:
> On Wed, Dec 23, 2009 at 3:54 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> > 9. Create a recovery command file in the standby server with parameters
> > required for streaming replication.
> >
> > 7. (a) Make a base backup of minimal essential files from primary
> > server, load this data onto the standby.
> >
> > 10. Start postgres in the standby server. It will start streaming
> > replication.
> >
> > 7. (b) Continue with second phase of base backup, copying all remaining
> > files, ending with pg_stop_backup()
>
> This manual procedure seems to be more complicated for me than just
> using restore_command which retrieves the WAL files from the master's
> archival area. Supposing that we adopt this idea, we should implement
> an automatic replication of a base backup at the same time, to prevent
> the users from doing the complicated two-phase-backup?

Either way works for async, but only the 2-phase backup works for synch
rep.

It's slightly more complicated for the base backup, but not so it isn't
still very practical. The list of files is very short: *.conf and the
global directory. But overall, ISTM it would actually be easier to do
this than to set up both streaming and restore_command.

I see it would work like this: Add a new option to recovery.conf,
perhaps two_phase_backup = on. Startup creates a file called
backup_in_progress then waits. When second phase of backup is complete
(7b), delete the file and then Startup process will continue. Very few
lines of code to make this work.

--
Simon Riggs www.2ndQuadrant.com