Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1aSEJ5-0007Wl-BH for pgsql-admin@arkaria.postgresql.org; Sun, 07 Feb 2016 01:38:31 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1aSEJ3-0000EN-9b for pgsql-admin@arkaria.postgresql.org; Sun, 07 Feb 2016 01:38:29 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1aSEJ1-0000E1-Io for pgsql-admin@postgresql.org; Sun, 07 Feb 2016 01:38:27 +0000 Received: from mail-ob0-x236.google.com ([2607:f8b0:4003:c01::236]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84) (envelope-from ) id 1aSEIt-0008HG-Ec for pgsql-admin@postgresql.org; Sun, 07 Feb 2016 01:38:26 +0000 Received: by mail-ob0-x236.google.com with SMTP id wb13so120519630obb.1 for ; Sat, 06 Feb 2016 17:38:18 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=content-type:mime-version:subject:from:in-reply-to:date:cc :content-transfer-encoding:message-id:references:to; bh=9xMxiLVfkuE3PnfNTscJJBWMLu2O4Uvknsuxw6iVaHU=; b=SnuhqVVhI9QgEh0Q4H8/Qx/DN6ISkmf8k8l8h5hTorEjGw+69X2sHLBllBQ8pf9DeI SYHGdg0Wt9m94Hljvpe4lT8dA1o+DWxcmEbHYguI823VUhCAFHzWs7QGuvy3oIOUUreD kmAIyAxjEovVnVXMVSwGfSKFCemBvBO+GEAs+du455IpJdM0PGyxk1n3cKbEvjq/8nMb qe4n2LAmplB+gh0wcYWWWeTP80V/562KgmpyX8C1sYxjU7xlJrKhxuTHaZU1T1ZTNsXJ vFWERWmmRcJzz3VypuZG3LVeZXBExsqxNgcmrNDcqsGHpkbJX4AlrUDa0CSfdok7QE1g BJpw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:content-type:mime-version:subject:from :in-reply-to:date:cc:content-transfer-encoding:message-id:references :to; bh=9xMxiLVfkuE3PnfNTscJJBWMLu2O4Uvknsuxw6iVaHU=; b=fCil7cQgpV5OFtEaPZ6NoddfszbUPS6QSdYrizu4xF89FQOeE5NnceiMCTX3nNTX12 wrRrh+BlRYhLuWj2GbWbc7zebjyz/Zv+akG1wHUvcLEasqnHhN5TYvhGEBiU1irnVRZO 8eOMK/ZBGBMP69HLDmhticzHAMPTud/ApsApg840Wm8TZno2Yyv6a/lAyjcVqF/A262W 20O/19bfR/cReEGZ0HWUuQNXHmsGkfEPAtg77pMj1JcnQPQSMXgiukRaOY/NBcmv8v6Z d2/gnqlju5/wu7zhssw1eRhiesSDkde/hXXqyG2I7gtCIU7A2gEoIPHB6imzrEjEXd9B TN/Q== X-Gm-Message-State: AG10YOT14sAOYjBw/qc1i3fWigSP/4/ehd79xZuBoErl8hYnYKFKkMZKrtcw5aP44CSucw== X-Received: by 10.182.63.11 with SMTP id c11mr216126obs.59.1454809095890; Sat, 06 Feb 2016 17:38:15 -0800 (PST) Received: from ?IPv6:2605:6000:f686:8500:217f:86fb:f944:b3d9? ([2605:6000:f686:8500:217f:86fb:f944:b3d9]) by smtp.gmail.com with ESMTPSA id y9sm13122841obg.4.2016.02.06.17.38.15 (version=TLSv1/SSLv3 cipher=OTHER); Sat, 06 Feb 2016 17:38:15 -0800 (PST) Content-Type: multipart/alternative; boundary=Apple-Mail-3CB06929-E7B6-4AF2-B646-4B0C8C4DA55E Mime-Version: 1.0 (1.0) Subject: Re: Setting up streaming replication with new server as master? From: John Scalia X-Mailer: iPad Mail (13D15) In-Reply-To: Date: Sat, 6 Feb 2016 19:38:14 -0600 Cc: Shreeyansh Dba , Stephen Frost , "pgsql-admin@postgresql.org" Content-Transfer-Encoding: 7bit Message-Id: <2D1A4542-9754-4ADB-ADAD-0F1D9622F667@gmail.com> References: <20160205202729.GZ3331@tamriel.snowman.net> <20160205205456.GA3331@tamriel.snowman.net> <20160205210928.GB3331@tamriel.snowman.net> To: Dave Johansen X-Pg-Spam-Score: -1.7 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org --Apple-Mail-3CB06929-E7B6-4AF2-B646-4B0C8C4DA55E Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: quoted-printable If you specify -X f or more likely -X s, that will cause pg_basebackup to in= clude 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 wrote: >=20 >> On Fri, Feb 5, 2016 at 8:29 PM, Shreeyansh Dba = wrote: >>=20 >>=20 >>> On Saturday, February 6, 2016, Dave Johansen wr= ote: >>>> On Fri, Feb 5, 2016 at 2:09 PM, Stephen Frost wrot= e: >>>> * Dave Johansen (davejohansen@gmail.com) wrote: >>>> > On Fri, Feb 5, 2016 at 1:54 PM, Stephen Frost wr= ote: >>>> > > * Dave Johansen (davejohansen@gmail.com) wrote: >>>> > > > So will pg_basebackup keep copying the new data that is added ove= r the >>>> > > > weekend until I tell it to stop? If so, then on Monday, can I clo= se >>>> > > things >>>> > > > down, wait for the WAL to finish, swap the roles of the 2 server a= nd 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 curre= nt >>>> > > 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 an= d then >>>> > the setup using recovery.conf will get the new data? >>>>=20 >>>> 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. >>>=20 >>> Just to clarify, do you mean *wal_level* set to archive or higher (i.e. w= al_level instead of archive_mode)? >>>=20 >>> One final question, step #5 in "How to Use" ( https://wiki.postgresql.or= g/wiki/Streaming_Replication#How_to_Use ) makes it sounds like having wal_ke= ep_segments at a high enough level means that archive_mode being on isn't ne= cessary. Is that a correct understanding of the description there? >>>=20 >>> Thanks a ton for all the help! >>> Dave >>=20 >>=20 >>=20 >> Hi Dave,=20 >>=20 >> If you are using PostgreSQL version greater than 9.1 then you can go for p= g_basebackup and create recovery.conf in backup .=20 >=20 > I'm using 9.2 on RHEL 7.2, so it sounds like that's a good option. > =20 >> I suggest you to go for wal to wal replication so that the archive overhe= ad will reduce.=20 >> =20 >> Put wal_keep_segment=3D150.=20 >> Also=20 >> Wal_level =3D hot_standby=20 >>=20 >> Not necessary to enable archive_mode and archive_command. >=20 > Ok, I'll go with that and thanks for the clarification. > =20 >> Once the replication comes in sync you can take down time in off peak hou= rs and promote the slave. >=20 > Is there a recommended way to migrate the data that was added after the us= e of pg_basebackup happened and before the processing was taken offline? --Apple-Mail-3CB06929-E7B6-4AF2-B646-4B0C8C4DA55E Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable
If you specify -X f or more likely -X s= , that will cause pg_basebackup to include the WAL files that were written a= fter 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 J= ohansen <davejohansen@gmail.com= > wrote:

On Fri, Feb 5, 2016 a= t 8:29 PM, Shreeyansh Dba <shreeyansh2014@gmail.com> wr= ote:


On Saturday, February 6, 2016, Dave Johansen <davejohansen@gmail.com> wr= ote:
On Fri, Feb 5, 2016 at 2:09 PM, Stephen Fros= t <sfrost@snowman.net> wrote:
* Dave Johansen (davejo= hansen@gmail.com) wrote:
> On Fri, Feb 5, 2016 at 1:54 PM, Stephen Frost <sfros= t@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 ser= ver 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 the= m 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 t= hen
> the setup using recovery.conf will get the new data?

Right.  Note that to use pg_basebackup or to have a replica conn= ect 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/Str= eaming_Replication#How_to_Use ) makes it sounds like having wal_keep_seg= ments at a high enough level means that archive_mode being on isn't necessar= y. Is that a correct understanding of the description there?

Thanks a= ton for all the help!
Dave



Hi  Dave, 

If you are using PostgreSQL v= ersion greater than 9.1 then you can go for pg_basebackup and create recover= y.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 wa= l to wal replication so that the archive overhead will reduce. 
 
Put wal_keep_segment=3D150. 
Also 
Wal_level =3D hot_standby 

Not necess= ary to enable archive_mode and archive_command.

<= /div>
Ok, I'll go with that and thanks for the clarification.
<= div> 
Once the repli= cation comes in sync you can take down time in off peak hours and promote th= e slave.

Is there a recommended w= ay to migrate the data that was added after the use of pg_basebackup happene= d and before the processing was taken offline?
= --Apple-Mail-3CB06929-E7B6-4AF2-B646-4B0C8C4DA55E--