pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedSetting up streaming replication with new server as master?
19+ messages / 5 participants
[nested] [flat]
* Setting up streaming replication with new server as master?
@ 2016-02-05 20:13 Dave Johansen <davejohansen@gmail.com>
2016-02-05 20:27 ` Re: Setting up streaming replication with new server as master? Stephen Frost <sfrost@snowman.net>
0 siblings, 1 reply; 19+ messages in thread
From: Dave Johansen @ 2016-02-05 20:13 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Setting up streaming replication with new server as master?
2016-02-05 20:13 Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
@ 2016-02-05 20:27 ` Stephen Frost <sfrost@snowman.net>
2016-02-05 20:44 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
0 siblings, 1 reply; 19+ messages in thread
From: Stephen Frost @ 2016-02-05 20:27 UTC (permalink / raw)
To: Dave Johansen <davejohansen@gmail.com>; +Cc: pgsql-admin
* 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
Attachments:
signature.asc (application/pgp-signature, 818B) download
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Setting up streaming replication with new server as master?
2016-02-05 20:13 Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-05 20:27 ` Re: Setting up streaming replication with new server as master? Stephen Frost <sfrost@snowman.net>
@ 2016-02-05 20:44 ` Dave Johansen <davejohansen@gmail.com>
2016-02-05 20:54 ` Re: Setting up streaming replication with new server as master? Stephen Frost <sfrost@snowman.net>
0 siblings, 1 reply; 19+ messages in thread
From: Dave Johansen @ 2016-02-05 20:44 UTC (permalink / raw)
To: Stephen Frost <sfrost@snowman.net>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Setting up streaming replication with new server as master?
2016-02-05 20:13 Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-05 20:27 ` Re: Setting up streaming replication with new server as master? Stephen Frost <sfrost@snowman.net>
2016-02-05 20:44 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
@ 2016-02-05 20:54 ` Stephen Frost <sfrost@snowman.net>
2016-02-05 21:02 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
0 siblings, 1 reply; 19+ messages in thread
From: Stephen Frost @ 2016-02-05 20:54 UTC (permalink / raw)
To: Dave Johansen <davejohansen@gmail.com>; +Cc: pgsql-admin
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
Attachments:
signature.asc (application/pgp-signature, 818B) download
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Setting up streaming replication with new server as master?
2016-02-05 20:13 Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-05 20:27 ` Re: Setting up streaming replication with new server as master? Stephen Frost <sfrost@snowman.net>
2016-02-05 20:44 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-05 20:54 ` Re: Setting up streaming replication with new server as master? Stephen Frost <sfrost@snowman.net>
@ 2016-02-05 21:02 ` Dave Johansen <davejohansen@gmail.com>
2016-02-05 21:09 ` Re: Setting up streaming replication with new server as master? Stephen Frost <sfrost@snowman.net>
0 siblings, 1 reply; 19+ messages in thread
From: Dave Johansen @ 2016-02-05 21:02 UTC (permalink / raw)
To: Stephen Frost <sfrost@snowman.net>; +Cc: pgsql-admin
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?
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Setting up streaming replication with new server as master?
2016-02-05 20:13 Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-05 20:27 ` Re: Setting up streaming replication with new server as master? Stephen Frost <sfrost@snowman.net>
2016-02-05 20:44 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-05 20:54 ` Re: Setting up streaming replication with new server as master? Stephen Frost <sfrost@snowman.net>
2016-02-05 21:02 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
@ 2016-02-05 21:09 ` Stephen Frost <sfrost@snowman.net>
2016-02-05 21:19 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
0 siblings, 1 reply; 19+ messages in thread
From: Stephen Frost @ 2016-02-05 21:09 UTC (permalink / raw)
To: Dave Johansen <davejohansen@gmail.com>; +Cc: pgsql-admin
* 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
Attachments:
signature.asc (application/pgp-signature, 818B) download
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Setting up streaming replication with new server as master?
2016-02-05 20:13 Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-05 20:27 ` Re: Setting up streaming replication with new server as master? Stephen Frost <sfrost@snowman.net>
2016-02-05 20:44 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-05 20:54 ` Re: Setting up streaming replication with new server as master? Stephen Frost <sfrost@snowman.net>
2016-02-05 21:02 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-05 21:09 ` Re: Setting up streaming replication with new server as master? Stephen Frost <sfrost@snowman.net>
@ 2016-02-05 21:19 ` Dave Johansen <davejohansen@gmail.com>
2016-02-06 03:29 ` Re: Setting up streaming replication with new server as master? Shreeyansh Dba <shreeyansh2014@gmail.com>
0 siblings, 1 reply; 19+ messages in thread
From: Dave Johansen @ 2016-02-05 21:19 UTC (permalink / raw)
To: Stephen Frost <sfrost@snowman.net>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Setting up streaming replication with new server as master?
2016-02-05 20:13 Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-05 20:27 ` Re: Setting up streaming replication with new server as master? Stephen Frost <sfrost@snowman.net>
2016-02-05 20:44 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-05 20:54 ` Re: Setting up streaming replication with new server as master? Stephen Frost <sfrost@snowman.net>
2016-02-05 21:02 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-05 21:09 ` Re: Setting up streaming replication with new server as master? Stephen Frost <sfrost@snowman.net>
2016-02-05 21:19 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
@ 2016-02-06 03:29 ` Shreeyansh Dba <shreeyansh2014@gmail.com>
2016-02-06 21:55 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
0 siblings, 1 reply; 19+ messages in thread
From: Shreeyansh Dba @ 2016-02-06 03:29 UTC (permalink / raw)
To: Dave Johansen <davejohansen@gmail.com>; +Cc: Stephen Frost <sfrost@snowman.net>; pgsql-admin
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
> <javascript:_e(%7B%7D,'cvml','sfrost@snowman.net');>> wrote:
>
>> * Dave Johansen (davejohansen@gmail.com
>> <javascript:_e(%7B%7D,'cvml','davejohansen@gmail.com');>) wrote:
>> > On Fri, Feb 5, 2016 at 1:54 PM, Stephen Frost <sfrost@snowman.net
>> <javascript:_e(%7B%7D,'cvml','sfrost@snowman.net');>> wrote:
>> > > * Dave Johansen (davejohansen@gmail.com
>> <javascript:_e(%7B%7D,'cvml','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
Www.shreeyansh.com
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Setting up streaming replication with new server as master?
2016-02-05 20:13 Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-05 20:27 ` Re: Setting up streaming replication with new server as master? Stephen Frost <sfrost@snowman.net>
2016-02-05 20:44 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-05 20:54 ` Re: Setting up streaming replication with new server as master? Stephen Frost <sfrost@snowman.net>
2016-02-05 21:02 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-05 21:09 ` Re: Setting up streaming replication with new server as master? Stephen Frost <sfrost@snowman.net>
2016-02-05 21:19 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-06 03:29 ` Re: Setting up streaming replication with new server as master? Shreeyansh Dba <shreeyansh2014@gmail.com>
@ 2016-02-06 21:55 ` Dave Johansen <davejohansen@gmail.com>
2016-02-07 01:38 ` Re: Setting up streaming replication with new server as master? John Scalia <jayknowsunix@gmail.com>
2016-02-07 03:00 ` Re: Setting up streaming replication with new server as master? Stephen Frost <sfrost@snowman.net>
0 siblings, 2 replies; 19+ messages in thread
From: Dave Johansen @ 2016-02-06 21:55 UTC (permalink / raw)
To: Shreeyansh Dba <shreeyansh2014@gmail.com>; +Cc: Stephen Frost <sfrost@snowman.net>; pgsql-admin
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?
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Setting up streaming replication with new server as master?
2016-02-05 20:13 Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-05 20:27 ` Re: Setting up streaming replication with new server as master? Stephen Frost <sfrost@snowman.net>
2016-02-05 20:44 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-05 20:54 ` Re: Setting up streaming replication with new server as master? Stephen Frost <sfrost@snowman.net>
2016-02-05 21:02 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-05 21:09 ` Re: Setting up streaming replication with new server as master? Stephen Frost <sfrost@snowman.net>
2016-02-05 21:19 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-06 03:29 ` Re: Setting up streaming replication with new server as master? Shreeyansh Dba <shreeyansh2014@gmail.com>
2016-02-06 21:55 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
@ 2016-02-07 01:38 ` John Scalia <jayknowsunix@gmail.com>
2016-02-17 15:02 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
1 sibling, 1 reply; 19+ messages in thread
From: John Scalia @ 2016-02-07 01:38 UTC (permalink / raw)
To: Dave Johansen <davejohansen@gmail.com>; +Cc: Shreeyansh Dba <shreeyansh2014@gmail.com>; Stephen Frost <sfrost@snowman.net>; pgsql-admin
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?
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Setting up streaming replication with new server as master?
2016-02-05 20:13 Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-05 20:27 ` Re: Setting up streaming replication with new server as master? Stephen Frost <sfrost@snowman.net>
2016-02-05 20:44 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-05 20:54 ` Re: Setting up streaming replication with new server as master? Stephen Frost <sfrost@snowman.net>
2016-02-05 21:02 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-05 21:09 ` Re: Setting up streaming replication with new server as master? Stephen Frost <sfrost@snowman.net>
2016-02-05 21:19 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-06 03:29 ` Re: Setting up streaming replication with new server as master? Shreeyansh Dba <shreeyansh2014@gmail.com>
2016-02-06 21:55 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-07 01:38 ` Re: Setting up streaming replication with new server as master? John Scalia <jayknowsunix@gmail.com>
@ 2016-02-17 15:02 ` Dave Johansen <davejohansen@gmail.com>
2016-02-17 15:32 ` Re: Setting up streaming replication with new server as master? Keith <keith@keithf4.com>
2016-02-17 15:35 ` Re: Setting up streaming replication with new server as master? John Scalia <jayknowsunix@gmail.com>
0 siblings, 2 replies; 19+ messages in thread
From: Dave Johansen @ 2016-02-17 15:02 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; +Cc: Shreeyansh Dba <shreeyansh2014@gmail.com>; Stephen Frost <sfrost@snowman.net>; pgsql-admin
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?
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Setting up streaming replication with new server as master?
2016-02-05 20:13 Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-05 20:27 ` Re: Setting up streaming replication with new server as master? Stephen Frost <sfrost@snowman.net>
2016-02-05 20:44 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-05 20:54 ` Re: Setting up streaming replication with new server as master? Stephen Frost <sfrost@snowman.net>
2016-02-05 21:02 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-05 21:09 ` Re: Setting up streaming replication with new server as master? Stephen Frost <sfrost@snowman.net>
2016-02-05 21:19 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-06 03:29 ` Re: Setting up streaming replication with new server as master? Shreeyansh Dba <shreeyansh2014@gmail.com>
2016-02-06 21:55 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-07 01:38 ` Re: Setting up streaming replication with new server as master? John Scalia <jayknowsunix@gmail.com>
2016-02-17 15:02 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
@ 2016-02-17 15:32 ` Keith <keith@keithf4.com>
2016-02-17 16:34 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
1 sibling, 1 reply; 19+ messages in thread
From: Keith @ 2016-02-17 15:32 UTC (permalink / raw)
To: Dave Johansen <davejohansen@gmail.com>; +Cc: John Scalia <jayknowsunix@gmail.com>; Shreeyansh Dba <shreeyansh2014@gmail.com>; Stephen Frost <sfrost@snowman.net>; pgsql-admin
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Setting up streaming replication with new server as master?
2016-02-05 20:13 Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-05 20:27 ` Re: Setting up streaming replication with new server as master? Stephen Frost <sfrost@snowman.net>
2016-02-05 20:44 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-05 20:54 ` Re: Setting up streaming replication with new server as master? Stephen Frost <sfrost@snowman.net>
2016-02-05 21:02 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-05 21:09 ` Re: Setting up streaming replication with new server as master? Stephen Frost <sfrost@snowman.net>
2016-02-05 21:19 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-06 03:29 ` Re: Setting up streaming replication with new server as master? Shreeyansh Dba <shreeyansh2014@gmail.com>
2016-02-06 21:55 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-07 01:38 ` Re: Setting up streaming replication with new server as master? John Scalia <jayknowsunix@gmail.com>
2016-02-17 15:02 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-17 15:32 ` Re: Setting up streaming replication with new server as master? Keith <keith@keithf4.com>
@ 2016-02-17 16:34 ` Dave Johansen <davejohansen@gmail.com>
2016-02-17 16:45 ` Re: Setting up streaming replication with new server as master? Keith <keith@keithf4.com>
0 siblings, 1 reply; 19+ messages in thread
From: Dave Johansen @ 2016-02-17 16:34 UTC (permalink / raw)
To: Keith <keith@keithf4.com>; +Cc: John Scalia <jayknowsunix@gmail.com>; Shreeyansh Dba <shreeyansh2014@gmail.com>; Stephen Frost <sfrost@snowman.net>; pgsql-admin
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?
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Setting up streaming replication with new server as master?
2016-02-05 20:13 Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-05 20:27 ` Re: Setting up streaming replication with new server as master? Stephen Frost <sfrost@snowman.net>
2016-02-05 20:44 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-05 20:54 ` Re: Setting up streaming replication with new server as master? Stephen Frost <sfrost@snowman.net>
2016-02-05 21:02 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-05 21:09 ` Re: Setting up streaming replication with new server as master? Stephen Frost <sfrost@snowman.net>
2016-02-05 21:19 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-06 03:29 ` Re: Setting up streaming replication with new server as master? Shreeyansh Dba <shreeyansh2014@gmail.com>
2016-02-06 21:55 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-07 01:38 ` Re: Setting up streaming replication with new server as master? John Scalia <jayknowsunix@gmail.com>
2016-02-17 15:02 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-17 15:32 ` Re: Setting up streaming replication with new server as master? Keith <keith@keithf4.com>
2016-02-17 16:34 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
@ 2016-02-17 16:45 ` Keith <keith@keithf4.com>
2016-02-17 17:49 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
0 siblings, 1 reply; 19+ messages in thread
From: Keith @ 2016-02-17 16:45 UTC (permalink / raw)
To: Dave Johansen <davejohansen@gmail.com>; +Cc: John Scalia <jayknowsunix@gmail.com>; Shreeyansh Dba <shreeyansh2014@gmail.com>; Stephen Frost <sfrost@snowman.net>; pgsql-admin
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.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Setting up streaming replication with new server as master?
2016-02-05 20:13 Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-05 20:27 ` Re: Setting up streaming replication with new server as master? Stephen Frost <sfrost@snowman.net>
2016-02-05 20:44 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-05 20:54 ` Re: Setting up streaming replication with new server as master? Stephen Frost <sfrost@snowman.net>
2016-02-05 21:02 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-05 21:09 ` Re: Setting up streaming replication with new server as master? Stephen Frost <sfrost@snowman.net>
2016-02-05 21:19 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-06 03:29 ` Re: Setting up streaming replication with new server as master? Shreeyansh Dba <shreeyansh2014@gmail.com>
2016-02-06 21:55 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-07 01:38 ` Re: Setting up streaming replication with new server as master? John Scalia <jayknowsunix@gmail.com>
2016-02-17 15:02 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-17 15:32 ` Re: Setting up streaming replication with new server as master? Keith <keith@keithf4.com>
2016-02-17 16:34 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-17 16:45 ` Re: Setting up streaming replication with new server as master? Keith <keith@keithf4.com>
@ 2016-02-17 17:49 ` Dave Johansen <davejohansen@gmail.com>
2016-02-17 18:05 ` Re: Setting up streaming replication with new server as master? Keith <keith@keithf4.com>
0 siblings, 1 reply; 19+ messages in thread
From: Dave Johansen @ 2016-02-17 17:49 UTC (permalink / raw)
To: Keith <keith@keithf4.com>; +Cc: John Scalia <jayknowsunix@gmail.com>; Shreeyansh Dba <shreeyansh2014@gmail.com>; Stephen Frost <sfrost@snowman.net>; pgsql-admin
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Setting up streaming replication with new server as master?
2016-02-05 20:13 Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-05 20:27 ` Re: Setting up streaming replication with new server as master? Stephen Frost <sfrost@snowman.net>
2016-02-05 20:44 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-05 20:54 ` Re: Setting up streaming replication with new server as master? Stephen Frost <sfrost@snowman.net>
2016-02-05 21:02 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-05 21:09 ` Re: Setting up streaming replication with new server as master? Stephen Frost <sfrost@snowman.net>
2016-02-05 21:19 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-06 03:29 ` Re: Setting up streaming replication with new server as master? Shreeyansh Dba <shreeyansh2014@gmail.com>
2016-02-06 21:55 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-07 01:38 ` Re: Setting up streaming replication with new server as master? John Scalia <jayknowsunix@gmail.com>
2016-02-17 15:02 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-17 15:32 ` Re: Setting up streaming replication with new server as master? Keith <keith@keithf4.com>
2016-02-17 16:34 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-17 16:45 ` Re: Setting up streaming replication with new server as master? Keith <keith@keithf4.com>
2016-02-17 17:49 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
@ 2016-02-17 18:05 ` Keith <keith@keithf4.com>
2016-02-17 18:19 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
0 siblings, 1 reply; 19+ messages in thread
From: Keith @ 2016-02-17 18:05 UTC (permalink / raw)
To: Dave Johansen <davejohansen@gmail.com>; +Cc: John Scalia <jayknowsunix@gmail.com>; Shreeyansh Dba <shreeyansh2014@gmail.com>; Stephen Frost <sfrost@snowman.net>; pgsql-admin
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.
>
>>
>>
>>> 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
>
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Setting up streaming replication with new server as master?
2016-02-05 20:13 Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-05 20:27 ` Re: Setting up streaming replication with new server as master? Stephen Frost <sfrost@snowman.net>
2016-02-05 20:44 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-05 20:54 ` Re: Setting up streaming replication with new server as master? Stephen Frost <sfrost@snowman.net>
2016-02-05 21:02 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-05 21:09 ` Re: Setting up streaming replication with new server as master? Stephen Frost <sfrost@snowman.net>
2016-02-05 21:19 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-06 03:29 ` Re: Setting up streaming replication with new server as master? Shreeyansh Dba <shreeyansh2014@gmail.com>
2016-02-06 21:55 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-07 01:38 ` Re: Setting up streaming replication with new server as master? John Scalia <jayknowsunix@gmail.com>
2016-02-17 15:02 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-17 15:32 ` Re: Setting up streaming replication with new server as master? Keith <keith@keithf4.com>
2016-02-17 16:34 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-17 16:45 ` Re: Setting up streaming replication with new server as master? Keith <keith@keithf4.com>
2016-02-17 17:49 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-17 18:05 ` Re: Setting up streaming replication with new server as master? Keith <keith@keithf4.com>
@ 2016-02-17 18:19 ` Dave Johansen <davejohansen@gmail.com>
0 siblings, 0 replies; 19+ messages in thread
From: Dave Johansen @ 2016-02-17 18:19 UTC (permalink / raw)
To: Keith <keith@keithf4.com>; +Cc: John Scalia <jayknowsunix@gmail.com>; Shreeyansh Dba <shreeyansh2014@gmail.com>; Stephen Frost <sfrost@snowman.net>; pgsql-admin
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.
>>>>>
>>>>> 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.
>
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.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Setting up streaming replication with new server as master?
2016-02-05 20:13 Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-05 20:27 ` Re: Setting up streaming replication with new server as master? Stephen Frost <sfrost@snowman.net>
2016-02-05 20:44 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-05 20:54 ` Re: Setting up streaming replication with new server as master? Stephen Frost <sfrost@snowman.net>
2016-02-05 21:02 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-05 21:09 ` Re: Setting up streaming replication with new server as master? Stephen Frost <sfrost@snowman.net>
2016-02-05 21:19 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-06 03:29 ` Re: Setting up streaming replication with new server as master? Shreeyansh Dba <shreeyansh2014@gmail.com>
2016-02-06 21:55 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-07 01:38 ` Re: Setting up streaming replication with new server as master? John Scalia <jayknowsunix@gmail.com>
2016-02-17 15:02 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
@ 2016-02-17 15:35 ` John Scalia <jayknowsunix@gmail.com>
1 sibling, 0 replies; 19+ messages in thread
From: John Scalia @ 2016-02-17 15:35 UTC (permalink / raw)
To: Dave Johansen <davejohansen@gmail.com>; +Cc: Shreeyansh Dba <shreeyansh2014@gmail.com>; Stephen Frost <sfrost@snowman.net>; pgsql-admin
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?
>
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Setting up streaming replication with new server as master?
2016-02-05 20:13 Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-05 20:27 ` Re: Setting up streaming replication with new server as master? Stephen Frost <sfrost@snowman.net>
2016-02-05 20:44 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-05 20:54 ` Re: Setting up streaming replication with new server as master? Stephen Frost <sfrost@snowman.net>
2016-02-05 21:02 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-05 21:09 ` Re: Setting up streaming replication with new server as master? Stephen Frost <sfrost@snowman.net>
2016-02-05 21:19 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-06 03:29 ` Re: Setting up streaming replication with new server as master? Shreeyansh Dba <shreeyansh2014@gmail.com>
2016-02-06 21:55 ` Re: Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
@ 2016-02-07 03:00 ` Stephen Frost <sfrost@snowman.net>
1 sibling, 0 replies; 19+ messages in thread
From: Stephen Frost @ 2016-02-07 03:00 UTC (permalink / raw)
To: Dave Johansen <davejohansen@gmail.com>; +Cc: Shreeyansh Dba <shreeyansh2014@gmail.com>; pgsql-admin
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
Attachments:
signature.asc (application/pgp-signature, 818B) download
^ permalink raw reply [nested|flat] 19+ messages in thread
end of thread, other threads:[~2016-02-17 18:19 UTC | newest]
Thread overview: 19+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-02-05 20:13 Setting up streaming replication with new server as master? Dave Johansen <davejohansen@gmail.com>
2016-02-05 20:27 ` Stephen Frost <sfrost@snowman.net>
2016-02-05 20:44 ` Dave Johansen <davejohansen@gmail.com>
2016-02-05 20:54 ` Stephen Frost <sfrost@snowman.net>
2016-02-05 21:02 ` Dave Johansen <davejohansen@gmail.com>
2016-02-05 21:09 ` Stephen Frost <sfrost@snowman.net>
2016-02-05 21:19 ` Dave Johansen <davejohansen@gmail.com>
2016-02-06 03:29 ` Shreeyansh Dba <shreeyansh2014@gmail.com>
2016-02-06 21:55 ` Dave Johansen <davejohansen@gmail.com>
2016-02-07 01:38 ` John Scalia <jayknowsunix@gmail.com>
2016-02-17 15:02 ` Dave Johansen <davejohansen@gmail.com>
2016-02-17 15:32 ` Keith <keith@keithf4.com>
2016-02-17 16:34 ` Dave Johansen <davejohansen@gmail.com>
2016-02-17 16:45 ` Keith <keith@keithf4.com>
2016-02-17 17:49 ` Dave Johansen <davejohansen@gmail.com>
2016-02-17 18:05 ` Keith <keith@keithf4.com>
2016-02-17 18:19 ` Dave Johansen <davejohansen@gmail.com>
2016-02-17 15:35 ` John Scalia <jayknowsunix@gmail.com>
2016-02-07 03:00 ` Stephen Frost <sfrost@snowman.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox