From: John Scalia <jayknowsunix@gmail.com>
To: Dave Johansen <davejohansen@gmail.com>
Cc: Shreeyansh Dba <shreeyansh2014@gmail.com>
Cc: Stephen Frost <sfrost@snowman.net>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: Setting up streaming replication with new server as master?
Date: Sat, 6 Feb 2016 19:38:14 -0600
Message-ID: <2D1A4542-9754-4ADB-ADAD-0F1D9622F667@gmail.com> (raw)
In-Reply-To: <CAAcYxUeTGoOd0Pnz4ts0XeRSFdju_a57=dnQLJX1tikC1vc2Kg@mail.gmail.com>
References: <CAAcYxUdTYWFdUOhiNzBv290YmeYgAv4iqh4gyiyBW3XgcCmjSw@mail.gmail.com>
<20160205202729.GZ3331@tamriel.snowman.net>
<CAAcYxUcPNa_c_dQd_-2Ez6HmntLkqOmNohag_EbKVDgV372SzA@mail.gmail.com>
<20160205205456.GA3331@tamriel.snowman.net>
<CAAcYxUe7zkKz6DSeqW88mPv4BasJnWwO2APnbU8=S20sSyTDMw@mail.gmail.com>
<20160205210928.GB3331@tamriel.snowman.net>
<CAAcYxUdVPaubLovuB_ZA8yRwgD3sO6bXTspCModDYECA6_mtzw@mail.gmail.com>
<CAGDYbUP_FSweNn=_t6qijJqPG5_88zzHTL_e+yi3mYa6HNbPLg@mail.gmail.com>
<CAAcYxUeTGoOd0Pnz4ts0XeRSFdju_a57=dnQLJX1tikC1vc2Kg@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: jayknowsunix@gmail.com, davejohansen@gmail.com, shreeyansh2014@gmail.com, sfrost@snowman.net
Subject: Re: Setting up streaming replication with new server as master?
In-Reply-To: <2D1A4542-9754-4ADB-ADAD-0F1D9622F667@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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