Обсуждение: Setting up streaming replication with new server as master?

* Dave Johansen (davejohansen@gmail.com) wrote:
> We would like to start using streaming replication, but the catch is that
> we want the new server to be the master. The server is still in use and
> receiving new data, so we would like to minimize down time, and I would
> prefer to be able to copy all, or at least the large majority of the data
> to the new server before doing the final sync to make it the master.
>
> Will pg_basebackup work?

Partially.

> Can it be run multiple times? (i.e. start it now to copy all of the data
> over the weekend and then a second time on Monday before doing the switch)

No.

> Do I need to manually copy the files with rsync?

I wouldn't suggest rsync, unless you do it when the database is shut
down and you checksum the files.

> What's the recommended way to handle this?

I'd suggest pg_basebackup and then set up the new system as a follower
of the primary using a recovery.conf.  When you shut down the current
server, all of the WAL changes should be streamed over to the new server
and then you can trigger it to become a read/write master, once it has
the last of the WAL from the current server, and then you can just
redirect new connections to it, or flip it into place by giving it the
new IP, etc.

If you want to do actual incremental backups as you were thinking
pg_basebackup might do, you could use pgBackRest, which does support
that.

Dave,

* Dave Johansen (davejohansen@gmail.com) wrote:
> So will pg_basebackup keep copying the new data that is added over the
> weekend until I tell it to stop? If so, then on Monday, can I close things
> down, wait for the WAL to finish, swap the roles of the 2 server and then
> restart?

No, pg_basebackup will take a snapshot of the current system,
essentially.  You can set up the new system to connect to the current
system, using recovery.conf, to pull the WAL records and apply them to
the new server.

* Dave Johansen (davejohansen@gmail.com) wrote:
> On Fri, Feb 5, 2016 at 1:54 PM, Stephen Frost <sfrost@snowman.net> wrote:
> > * Dave Johansen (davejohansen@gmail.com) wrote:
> > > So will pg_basebackup keep copying the new data that is added over the
> > > weekend until I tell it to stop? If so, then on Monday, can I close
> > things
> > > down, wait for the WAL to finish, swap the roles of the 2 server and then
> > > restart?
> >
> > No, pg_basebackup will take a snapshot of the current system,
> > essentially.  You can set up the new system to connect to the current
> > system, using recovery.conf, to pull the WAL records and apply them to
> > the new server.
> >
>
> So do I set that up before running pg_basebackup?
>
> Or in other words, pg_basebackup will get all of the existing data and then
> the setup using recovery.conf will get the new data?

Right.  Note that to use pg_basebackup or to have a replica connect to
the existing system, you need to have archive_mode set to archive or
higher (and you should at least do hot_standby, really), and have
max_wal_senders set higher than 0 (I tend to set it to about 3, just
because it uses little in the way of resources, etc).  If you have a
high transaction rate, you might also want to set wal_keep_segments up a
bit.

On Saturday, February 6, 2016, Dave Johansen <davejohansen@gmail.com> wrote:

On Fri, Feb 5, 2016 at 2:09 PM, Stephen Frost <sfrost@snowman.net> wrote:

* Dave Johansen (davejohansen@gmail.com) wrote:
> On Fri, Feb 5, 2016 at 1:54 PM, Stephen Frost <sfrost@snowman.net> wrote:
> > * Dave Johansen (davejohansen@gmail.com) wrote:
> > > So will pg_basebackup keep copying the new data that is added over the
> > > weekend until I tell it to stop? If so, then on Monday, can I close
> > things
> > > down, wait for the WAL to finish, swap the roles of the 2 server and then
> > > restart?
> >
> > No, pg_basebackup will take a snapshot of the current system,
> > essentially.  You can set up the new system to connect to the current
> > system, using recovery.conf, to pull the WAL records and apply them to
> > the new server.
> >
>
> So do I set that up before running pg_basebackup?
>
> Or in other words, pg_basebackup will get all of the existing data and then
> the setup using recovery.conf will get the new data?

Right.  Note that to use pg_basebackup or to have a replica connect to
the existing system, you need to have archive_mode set to archive or
higher (and you should at least do hot_standby, really), and have
max_wal_senders set higher than 0 (I tend to set it to about 3, just
because it uses little in the way of resources, etc).  If you have a
high transaction rate, you might also want to set wal_keep_segments up a
bit.

Just to clarify, do you mean *wal_level* set to archive or higher (i.e. wal_level instead of archive_mode)?

One final question, step #5 in "How to Use" ( https://wiki.postgresql.org/wiki/Streaming_Replication#How_to_Use ) makes it sounds like having wal_keep_segments at a high enough level means that archive_mode being on isn't necessary. Is that a correct understanding of the description there?

Thanks a ton for all the help!

Dave

Hi  Dave, 

If you are using PostgreSQL version greater than 9.1 then you can go for pg_basebackup and create recovery.conf in backup . 

I suggest you to go for wal to wal replication so that the archive overhead will reduce. 

 

Put wal_keep_segment=150. 

Also 

Wal_level = hot_standby 

Not necessary to enable archive_mode and archive_command.

Once the replication comes in sync you can take down time in off peak hours and promote the slave.

If you specify -X f or more likely -X s, that will cause pg_basebackup to include the WAL files that were written after you started the operation. Since you're setting up a replica, use the -X s option as that's for streaming.

On Sat, Feb 6, 2016 at 6:38 PM, John Scalia <jayknowsunix@gmail.com> wrote:

If you specify -X f or more likely -X s, that will cause pg_basebackup to include the WAL files that were written after you started the operation. Since you're setting up a replica, use the -X s option as that's for streaming.

I ran pg_basebackup with -X s and it finished in the middle of the night last night. I would now like to make the switch, but what's the best way to copy over the records that have been inserted since the backup stopped?

On Wed, Feb 17, 2016 at 10:02 AM, Dave Johansen <davejohansen@gmail.com> wrote:

On Sat, Feb 6, 2016 at 6:38 PM, John Scalia <jayknowsunix@gmail.com> wrote:

If you specify -X f or more likely -X s, that will cause pg_basebackup to include the WAL files that were written after you started the operation. Since you're setting up a replica, use the -X s option as that's for streaming.

I ran pg_basebackup with -X s and it finished in the middle of the night last night. I would now like to make the switch, but what's the best way to copy over the records that have been inserted since the backup stopped?

The -Xs option just keeps the WAL files that were written during the backup run so that if you restore it, it's brought back up to a consistent state at the point when the backup itself finished.

If you want to be able to bring up a slave from a backup at any point after that backup was complete, you have to keep all the WAL files that have been written since then. This is what is called Point-In-Time Recovery (PITR). I highly recommend you read up on the docs for how WAL files in postgres work and how to use them with backups and slaves. I think that is a key point your missing in understanding how to get a streaming slave set up and working.

http://www.postgresql.org/docs/9.5/static/continuous-archiving.html

On Wed, Feb 17, 2016 at 8:32 AM, Keith <keith@keithf4.com> wrote:

On Wed, Feb 17, 2016 at 10:02 AM, Dave Johansen <davejohansen@gmail.com> wrote:

On Sat, Feb 6, 2016 at 6:38 PM, John Scalia <jayknowsunix@gmail.com> wrote:

If you specify -X f or more likely -X s, that will cause pg_basebackup to include the WAL files that were written after you started the operation. Since you're setting up a replica, use the -X s option as that's for streaming.

I ran pg_basebackup with -X s and it finished in the middle of the night last night. I would now like to make the switch, but what's the best way to copy over the records that have been inserted since the backup stopped?

The -Xs option just keeps the WAL files that were written during the backup run so that if you restore it, it's brought back up to a consistent state at the point when the backup itself finished.

If you want to be able to bring up a slave from a backup at any point after that backup was complete, you have to keep all the WAL files that have been written since then. This is what is called Point-In-Time Recovery (PITR). I highly recommend you read up on the docs for how WAL files in postgres work and how to use them with backups and slaves. I think that is a key point your missing in understanding how to get a streaming slave set up and working.

http://www.postgresql.org/docs/9.5/static/continuous-archiving.html

Ok, that was a misunderstanding on my part. I had understood that with the -Xs option pg_basebackup would stay online and keep streaming the WAL files until it was turned off. Thanks for the clarification, but on a related note, that would be a really nice feature that would make doing this sort of replication a lot easier.

I have run into another issue now though. I'm trying to start up the server after running pg_basebackup and it keeps timing out. Do I have something setup incorrectly? Or what is causing this issue?

On Wed, Feb 17, 2016 at 11:34 AM, Dave Johansen <davejohansen@gmail.com> wrote:

On Wed, Feb 17, 2016 at 8:32 AM, Keith <keith@keithf4.com> wrote:

On Wed, Feb 17, 2016 at 10:02 AM, Dave Johansen <davejohansen@gmail.com> wrote:

On Sat, Feb 6, 2016 at 6:38 PM, John Scalia <jayknowsunix@gmail.com> wrote:

If you specify -X f or more likely -X s, that will cause pg_basebackup to include the WAL files that were written after you started the operation. Since you're setting up a replica, use the -X s option as that's for streaming.

I ran pg_basebackup with -X s and it finished in the middle of the night last night. I would now like to make the switch, but what's the best way to copy over the records that have been inserted since the backup stopped?

The -Xs option just keeps the WAL files that were written during the backup run so that if you restore it, it's brought back up to a consistent state at the point when the backup itself finished.

If you want to be able to bring up a slave from a backup at any point after that backup was complete, you have to keep all the WAL files that have been written since then. This is what is called Point-In-Time Recovery (PITR). I highly recommend you read up on the docs for how WAL files in postgres work and how to use them with backups and slaves. I think that is a key point your missing in understanding how to get a streaming slave set up and working.

http://www.postgresql.org/docs/9.5/static/continuous-archiving.html

Ok, that was a misunderstanding on my part. I had understood that with the -Xs option pg_basebackup would stay online and keep streaming the WAL files until it was turned off. Thanks for the clarification, but on a related note, that would be a really nice feature that would make doing this sort of replication a lot easier.

That feature does currently exist, it's just not part of pg_basebackup, which is why I recommended that you read up on the documentation for PITR in postgres. 

 

I have run into another issue now though. I'm trying to start up the server after running pg_basebackup and it keeps timing out. Do I have something setup incorrectly? Or what is causing this issue?

It's probably replaying the WAL files to bring itself into a consistent state. If you're using the service options of the OS to start/stop the database it may have a timeout period shorter than the replay takes. I know that is an issue on debian/ubuntu sometimes. Monitor the postgres log files themselves to see where things are at and give it a few minutes to start up and see how it goes. If it's unable to start at all, the log files will tell you that as well. 

On Wed, Feb 17, 2016 at 9:45 AM, Keith <keith@keithf4.com> wrote:

On Wed, Feb 17, 2016 at 11:34 AM, Dave Johansen <davejohansen@gmail.com> wrote:

On Wed, Feb 17, 2016 at 8:32 AM, Keith <keith@keithf4.com> wrote:

On Wed, Feb 17, 2016 at 10:02 AM, Dave Johansen <davejohansen@gmail.com> wrote:

On Sat, Feb 6, 2016 at 6:38 PM, John Scalia <jayknowsunix@gmail.com> wrote:

If you specify -X f or more likely -X s, that will cause pg_basebackup to include the WAL files that were written after you started the operation. Since you're setting up a replica, use the -X s option as that's for streaming.

I ran pg_basebackup with -X s and it finished in the middle of the night last night. I would now like to make the switch, but what's the best way to copy over the records that have been inserted since the backup stopped?

The -Xs option just keeps the WAL files that were written during the backup run so that if you restore it, it's brought back up to a consistent state at the point when the backup itself finished.

If you want to be able to bring up a slave from a backup at any point after that backup was complete, you have to keep all the WAL files that have been written since then. This is what is called Point-In-Time Recovery (PITR). I highly recommend you read up on the docs for how WAL files in postgres work and how to use them with backups and slaves. I think that is a key point your missing in understanding how to get a streaming slave set up and working.

http://www.postgresql.org/docs/9.5/static/continuous-archiving.html

Ok, that was a misunderstanding on my part. I had understood that with the -Xs option pg_basebackup would stay online and keep streaming the WAL files until it was turned off. Thanks for the clarification, but on a related note, that would be a really nice feature that would make doing this sort of replication a lot easier.

That feature does currently exist, it's just not part of pg_basebackup, which is why I recommended that you read up on the documentation for PITR in postgres. 

I was referring to the feature being added to pg_basebackup itself. I'm not familiar at all with the code and what adding a feature like this would take, but it already has support for streaming the WAL logs but just exits when the base backup is complete. I was thinking of something along the lines of --continue or --stayalive that would keep the streaming of the WAL logs going even after the base backup completes.

 

 

I have run into another issue now though. I'm trying to start up the server after running pg_basebackup and it keeps timing out. Do I have something setup incorrectly? Or what is causing this issue?

It's probably replaying the WAL files to bring itself into a consistent state. If you're using the service options of the OS to start/stop the database it may have a timeout period shorter than the replay takes. I know that is an issue on debian/ubuntu sometimes. Monitor the postgres log files themselves to see where things are at and give it a few minutes to start up and see how it goes. If it's unable to start at all, the log files will tell you that as well. 

Yes, the WAL logs are being replayed and it appears that systemd is timing out and stopping the startup process. I adjusted the systemd parameters to not timeout so quickly and it appears to have resolved the issue.

Thanks for all of the help,

Dave

On Wed, Feb 17, 2016 at 12:49 PM, Dave Johansen <davejohansen@gmail.com> wrote:

On Wed, Feb 17, 2016 at 9:45 AM, Keith <keith@keithf4.com> wrote:

On Wed, Feb 17, 2016 at 11:34 AM, Dave Johansen <davejohansen@gmail.com> wrote:

On Wed, Feb 17, 2016 at 8:32 AM, Keith <keith@keithf4.com> wrote:

On Wed, Feb 17, 2016 at 10:02 AM, Dave Johansen <davejohansen@gmail.com> wrote:

On Sat, Feb 6, 2016 at 6:38 PM, John Scalia <jayknowsunix@gmail.com> wrote:

If you specify -X f or more likely -X s, that will cause pg_basebackup to include the WAL files that were written after you started the operation. Since you're setting up a replica, use the -X s option as that's for streaming.

I ran pg_basebackup with -X s and it finished in the middle of the night last night. I would now like to make the switch, but what's the best way to copy over the records that have been inserted since the backup stopped?

The -Xs option just keeps the WAL files that were written during the backup run so that if you restore it, it's brought back up to a consistent state at the point when the backup itself finished.

If you want to be able to bring up a slave from a backup at any point after that backup was complete, you have to keep all the WAL files that have been written since then. This is what is called Point-In-Time Recovery (PITR). I highly recommend you read up on the docs for how WAL files in postgres work and how to use them with backups and slaves. I think that is a key point your missing in understanding how to get a streaming slave set up and working.

http://www.postgresql.org/docs/9.5/static/continuous-archiving.html

Ok, that was a misunderstanding on my part. I had understood that with the -Xs option pg_basebackup would stay online and keep streaming the WAL files until it was turned off. Thanks for the clarification, but on a related note, that would be a really nice feature that would make doing this sort of replication a lot easier.

That feature does currently exist, it's just not part of pg_basebackup, which is why I recommended that you read up on the documentation for PITR in postgres. 

I was referring to the feature being added to pg_basebackup itself. I'm not familiar at all with the code and what adding a feature like this would take, but it already has support for streaming the WAL logs but just exits when the base backup is complete. I was thinking of something along the lines of --continue or --stayalive that would keep the streaming of the WAL logs going even after the base backup completes.

Can almost guarantee you this will never be something pg_basebackup does. 

Look into the archive_command

http://www.postgresql.org/docs/9.5/static/runtime-config-wal.html#GUC-ARCHIVE-COMMAND

or setting up pg_receivexlog

http://www.postgresql.org/docs/9.5/static/app-pgreceivexlog.html

if you need something that continuously keeps a backup of WAL files.