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

Поиск
Список
Период
Сортировка

Setting up streaming replication with new server as master?

От
Dave Johansen
Дата:
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?
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)
Do I need to manually copy the files with rsync?

What's the recommended way to handle this?

Thanks,
Dave

Re: Setting up streaming replication with new server as master?

От
Stephen Frost
Дата:
* 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.

Thanks!

Stephen

Вложения

Re: Setting up streaming replication with new server as master?

От
Dave Johansen
Дата:


On Fri, Feb 5, 2016 at 1:27 PM, Stephen Frost <sfrost@snowman.net> wrote:
* 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.

I'm not really worried about support incremental backups at the moment, but just want to be able to start the copy of data now (it will probably take a good chunk of the weekend) and have it be ready to do a quick change on Monday.

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?

Thanks,
Dave

Re: Setting up streaming replication with new server as master?

От
Stephen Frost
Дата:
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.

Thanks!

Stephen

Вложения

Re: Setting up streaming replication with new server as master?

От
Dave Johansen
Дата:
On Fri, Feb 5, 2016 at 1:54 PM, Stephen Frost <sfrost@snowman.net> wrote:
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.

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?

Re: Setting up streaming replication with new server as master?

От
Stephen Frost
Дата:
* 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.

Thanks!

Stephen

Вложения

Re: Setting up streaming replication with new server as master?

От
Dave Johansen
Дата:
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

Re: Setting up streaming replication with new server as master?

От
Shreeyansh Dba
Дата:


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. 



Thanks and Regards 

Ajinkya Bangale 
Database Administrator 
Shreeyansh Technologies 



Re: Setting up streaming replication with new server as master?

От
Dave Johansen
Дата:
On Fri, Feb 5, 2016 at 8:29 PM, Shreeyansh Dba <shreeyansh2014@gmail.com> wrote:


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'm using 9.2 on RHEL 7.2, so it sounds like that's a good option.
 
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.

Ok, I'll go with that and thanks for the clarification.
 
Once the replication comes in sync you can take down time in off peak hours and promote the slave.

Is there a recommended way to migrate the data that was added after the use of pg_basebackup happened and before the processing was taken offline?

Re: Setting up streaming replication with new server as master?

От
John Scalia
Дата:
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.

--
Jay

Sent from my iPad

On Feb 6, 2016, at 3:55 PM, Dave Johansen <davejohansen@gmail.com> wrote:

On Fri, Feb 5, 2016 at 8:29 PM, Shreeyansh Dba <shreeyansh2014@gmail.com> wrote:


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'm using 9.2 on RHEL 7.2, so it sounds like that's a good option.
 
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.

Ok, I'll go with that and thanks for the clarification.
 
Once the replication comes in sync you can take down time in off peak hours and promote the slave.

Is there a recommended way to migrate the data that was added after the use of pg_basebackup happened and before the processing was taken offline?

Re: Setting up streaming replication with new server as master?

От
Stephen Frost
Дата:
Dave,

* Dave Johansen (davejohansen@gmail.com) wrote:
> On Fri, Feb 5, 2016 at 8:29 PM, Shreeyansh Dba <shreeyansh2014@gmail.com>
> wrote:
> > On Saturday, February 6, 2016, Dave Johansen <davejohansen@gmail.com>
> > wrote:
> >> Just to clarify, do you mean *wal_level* set to archive or higher (i.e.
> >> wal_level instead of archive_mode)?

Yes, sorry about the confusion.

> >> 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?

Yes.

> > If you are using PostgreSQL version greater than 9.1 then you can go for
> > pg_basebackup and create recovery.conf in backup .
>
> I'm using 9.2 on RHEL 7.2, so it sounds like that's a good option.

Right, that should work.

> > 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.
>
> Ok, I'll go with that and thanks for the clarification.

Yeah, note that 150 WAL segments will be like 2.5G, so make sure you
have enough disk space on your WAL volume for them.

> > Once the replication comes in sync you can take down time in off peak
> > hours and promote the slave.
>
> Is there a recommended way to migrate the data that was added after the use
> of pg_basebackup happened and before the processing was taken offline?

If you set up the new server as a streaming replica then the data which
is added after the pg_basebackup will be streamed automatically to the
new server, all the way up til the current server is taken offline.

Thanks!

Stephen

Вложения

Re: Setting up streaming replication with new server as master?

От
Dave Johansen
Дата:
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?

Re: Setting up streaming replication with new server as master?

От
Keith
Дата:


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.


Re: Setting up streaming replication with new server as master?

От
John Scalia
Дата:
Hopefully, you set keep_wal_segments high enough that nothing has "expired" from the master, if this is the case and if you set up the slave so it can see those WAL segments, then your job is done. Just start the slave up and it will process those transactions from the WAL segments that weren't part of the pg_basebackup.

If, however, some of those WAL segments have disappeared off the master server, then you get to perform this exercise all over again, i.e., a new pg_basebackup.
--
Jay

On Wed, Feb 17, 2016 at 9: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?

Re: Setting up streaming replication with new server as master?

От
Dave Johansen
Дата:
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.


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?

Re: Setting up streaming replication with new server as master?

От
Keith
Дата:


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.


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. 

Re: Setting up streaming replication with new server as master?

От
Dave Johansen
Дата:
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.


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

Re: Setting up streaming replication with new server as master?

От
Keith
Дата:


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.


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


or setting up pg_receivexlog


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

 
 
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

Re: Setting up streaming replication with new server as master?

От
Dave Johansen
Дата:
On Wed, Feb 17, 2016 at 11:05 AM, Keith <keith@keithf4.com> wrote:


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.


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


or setting up pg_receivexlog


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

I agree that continuous archiving can be used to enable replication on a live database and is the best solution going with current versions of Postgres. However, adding this feature would simplify scenarios where someone is bringing replication online with a live database but doesn't want to run continous archiving long term (my case).

Basically, this feature would mean that enabling replication on an existing live database could be completed solely with pg_basebackup and without having to mess with the configuration after the fact.