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

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

From
Simon Riggs
Date:
Some ideas to improve current behaviour of SR

http://wiki.postgresql.org/wiki/Streaming_Replication

The current startup process is copied below. (7) gives some issues if it
is a very long step, notably that the master may fill with data and then
break off the connection before replication is fully configured.

7. Make a base backup of the primary server, load this data onto the
standby.

8. Set up XLOG archiving, connections and authentication in the standby
server like the primary, so that the standby might work as a primary
after failover.

9. Create a recovery command file in the standby server; the following
parameters are required for streaming replication.

10. Start postgres in the standby server. It will start streaming
replication.


It occurs to me to ask which files we need as a minimum before we can
begin step (10)? If we could start step (10) before step (7) is complete
then we would avoid many of our space problems and replication would
enter "safe" mode considerably sooner, in some cases dozens of hours
earlier.

We read the recovery.conf at the start of StartupXLog(). So by that
point we need only the following files
* All *.conf files
* pg_control (and probably much of the rest of global/ directory)

Some very quick surgery on a current-version data directory shows this
is correct, apart from the call to RelationCacheInitFileRemove() which
can be altered to accept a missing directory as proof that the file has
been removed.

If we then think of the starting procedure as happening in two parts:
i) sufficient startup to get to the point where we bring up the
walreceiver, while startup process waits further confirmation
ii) following further confirmation startup process now begins recovering
database

So if we do the base backup in two stages the sequence of actions could
become

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()

* Next step is to waken startup process so it can continue recovery


We don't need to introduce another call, we just need to have a
mechanism for telling the startup process to sleep because we are doing
a two-phase backup and another mechanism for waking it when the whole
backup is complete. That sounds like a recovery.conf parameter and an
additional kind of trigger file, perhaps the backup file?

This seems like a simple and useful option for 8.5

-- Simon Riggs           www.2ndQuadrant.com



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

From
decibel
Date:
On Dec 22, 2009, at 12:54 PM, Simon Riggs 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()

Dumb question: could the WAL streaming code be made to also ship base files? That would make setting up a streaming
replicasuper-simple. 
--
Jim C. Nasby, Database Architect                   jim@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net




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

From
Fujii Masao
Date:
On Wed, Dec 23, 2009 at 6:33 AM, decibel <decibel@decibel.org> wrote:
> Dumb question: could the WAL streaming code be made to also ship base files?

No.

> That would make setting up a streaming replica super-simple.

Agreed, but it's not easy to implement that. So I'm thinking
that it's out of the scope of the development for v8.5.

Of course, anyone is welcome to try that ;-)

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


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

From
Fujii Masao
Date:
On Wed, Dec 23, 2009 at 3:54 AM, Simon Riggs <simon@2ndquadrant.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?

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


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

From
Simon Riggs
Date:
On Thu, 2009-12-24 at 14:40 +0900, Fujii Masao wrote:
> On Wed, Dec 23, 2009 at 3:54 AM, Simon Riggs <simon@2ndquadrant.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



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

From
Simon Riggs
Date:
On Tue, 2009-12-22 at 15:33 -0600, decibel wrote:

> Dumb question: could the WAL streaming code be made to also ship base files? That would make setting up a streaming
replicasuper-simple.
 

That was a possible design, but that's not will be there for this
release.

I opposed adding the "we do the base backup for you" feature because
there are many ways of doing a base backup and it would likely have
restricted your options to do so. One issue that would cause is limiting
the speed of the base backup to a single libpq connection, which would
cause performance problems. So yes, super-simple, but not super-good for
many big users.

-- Simon Riggs           www.2ndQuadrant.com



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

From
Andreas 'ads' Scherbaum
Date:
On Thu, 24 Dec 2009 09:58:20 +0000 Simon Riggs wrote:

> On Tue, 2009-12-22 at 15:33 -0600, decibel wrote:
> 
> > Dumb question: could the WAL streaming code be made to also ship base files? That would make setting up a streaming
replicasuper-simple.
 
> 
> That was a possible design, but that's not will be there for this
> release.
> 
> I opposed adding the "we do the base backup for you" feature because
> there are many ways of doing a base backup and it would likely have
> restricted your options to do so. One issue that would cause is limiting
> the speed of the base backup to a single libpq connection, which would
> cause performance problems. So yes, super-simple, but not super-good for
> many big users.

The big users will always have other options. But for normal users who
just want to enable a replication - this feature would be awesome: the
entire replication is done by the database.

So +1 for integrating such a feature in a future version.


Bye

--             Andreas 'ads' Scherbaum
German PostgreSQL User Group
European PostgreSQL User Group - Board of Directors
Volunteer Regional Contact, Germany - PostgreSQL Project


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

From
Fujii Masao
Date:
On Thu, Dec 24, 2009 at 6:03 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> 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.

Where do you think the WAL files shipped before doing (7b) are stored?
If it's pg_xlog, the disk full failure would occur in the standby. If
it's an archive, restore_command would have to be supplied the same as
my idea.

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


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

From
Simon Riggs
Date:
On Fri, 2009-12-25 at 14:33 +0900, Fujii Masao wrote:
> On Thu, Dec 24, 2009 at 6:03 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> > 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.
> 
> Where do you think the WAL files shipped before doing (7b) are stored?
> If it's pg_xlog, the disk full failure would occur in the standby. If
> it's an archive, restore_command would have to be supplied the same as
> my idea.

Yes, agreed.

I am still concerned about the interactions at the start of replication.
It isn't clear how these things work exactly and as a result, I feel we
may be missing some better ideas.

Two points concern me
* When would sync rep be able to start?
* How do we avoid sending WAL twice?

-- Simon Riggs           www.2ndQuadrant.com