Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WZpDc-0004GT-7y for pgsql-admin@arkaria.postgresql.org; Mon, 14 Apr 2014 22:19:12 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WZpDb-0000Q3-Lc for pgsql-admin@arkaria.postgresql.org; Mon, 14 Apr 2014 22:19:11 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WZpDa-0000Od-8N for pgsql-admin@postgresql.org; Mon, 14 Apr 2014 22:19:10 +0000 Received: from mail-ie0-f178.google.com ([209.85.223.178]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WZpDX-0002RB-2f for pgsql-admin@postgresql.org; Mon, 14 Apr 2014 22:19:09 +0000 Received: by mail-ie0-f178.google.com with SMTP id lx4so8817924iec.9 for ; Mon, 14 Apr 2014 15:19:06 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:date:from:to:cc:message-id:in-reply-to :references:subject:mime-version:content-type; bh=eU7ERkxmUKtF6DzLagK02yBte9ZoItabGCTPiFwvAMk=; b=Z4bmoKt/3/xgftG/2o8qeUbAj1xSsFVHeyGOHGcpXWyiiLadTvZSAL+Blz4EJ4I28h YzzpqZJpsfKSTdfca0iWJ2fgp8PzvAxnS/lY6+unBVvproumk8iVU90gwFu8IPBczCJ+ YH9KlFcbjNOeiwl59G6GJBPEqMzEb5qAJR2kLXo1fsiYqleVISBj7RdzRsjkDk55mgt1 k0lpy4Glad8hPz1t9OoPUZQR3si8P/2jJj8WxNxY0WQ6fbWwGIcc8bH/QmJDH9S8Ra67 H57ahpx2x7GjO0d5z6qYG0HZjJBpauelB/eTapeW61KzBPII4920FGmMCbCGniz6+9Gq /wUg== X-Gm-Message-State: ALoCoQn9JponXh28OnYKm0JTpTr7hXJNEhavYX/ETivJmLcrV9AC8CqLOKmPp1dsisHpsRxjfNobDnT41oIAI6CtEl8KynIzBUffZxYIZ0bxRfSSvWRXvNU= X-Received: by 10.50.28.101 with SMTP id a5mr19208114igh.46.1397513945892; Mon, 14 Apr 2014 15:19:05 -0700 (PDT) Received: from palos ([50.76.140.65]) by mx.google.com with ESMTPSA id p4sm32695395igy.7.2014.04.14.15.19.04 for (version=TLSv1.2 cipher=ECDHE-RSA-RC4-SHA bits=128/128); Mon, 14 Apr 2014 15:19:05 -0700 (PDT) Date: Mon, 14 Apr 2014 16:19:19 -0600 From: Jason Mathis To: Jim Mercer Cc: Scott Whitney , pgsql-admin@postgresql.org, Scott Whitney Message-ID: In-Reply-To: <20140414215143.GA38476@reptiles.org> References: <20140414163956.GA27624@reptiles.org> <2125585511.76278.1397494671973.JavaMail.zimbra@mail.int.journyx.com> <20140414215143.GA38476@reptiles.org> Subject: Re: streaming replication - pgsql 9.2.3 X-Mailer: Airmail (237) MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="534c5ee7_5bd062c2_226e" X-Pg-Spam-Score: -2.6 (--) 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 --534c5ee7_5bd062c2_226e Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Yep, as long as you are ok with losing the data in between the =E2=80=9Cdum= p all=E2=80=9D then it sounds good. For example:=C2=A0 9pm =3D pg_dumpall=C2=A0 during the day you modified and added a bunch of data.=C2=A0 Then the next day 8pm =3D you accidentally drop the database.=C2=A0 Your recovery is last night=E2=80=99s backup and everything that happened d= uring the day is gone.=C2=A0 Also you may want to check out the keep_segments.=C2=A0 http://www.postgresql.org/docs/9.2/static/warm-standby.html "If you use streaming replication without file-based continuous archiving, = you have to set=C2=A0wal_keep_segments=C2=A0in the master to a value high e= nough to ensure that old WAL segments are not recycled too early, while the= standby might still need them to catch up. If the standby falls behind too= much, it needs to be reinitialized from a new base backup. If you set up a= WAL archive that's accessible from the standby,=C2=A0wal_keep_segments=C2= =A0is not required as the standby can always use the archive to catch up." -jason=C2=A0 On April 14, 2014 at 3:51:50 PM, Jim Mercer (jim@reptiles.org) wrote: On Mon, Apr 14, 2014 at 03:14:46PM -0600, Jason Mathis wrote: =20 > Just keep in mind that while wal archiving and replication sound very sim= ilar they are intended for two different things.?? =20 > =20 > Wal Archiving =3D backup/DR/PITR/standby =20 > Replication =3D High availability/reporting?? =20 it would be nice if the documentation reflected this. =20 the current information i derived, basically says to do both. =20 i'm looking to revise my settings to: =20 - nullify/NOP the archive_command and restore_command =20 - use the slave to do a pg_dumpall as the backup =20 the standby node serves as a read-only slave, which allows for some =20 performance gains. =20 the standby node could be promoted to master, should the master have issues= . =20 the backup would allow for a recovery from a catestrophic/cascading error = =20 (ie. drop all tables). =20 does this make sense? =20 -- =20 Jim Mercer Reptilian Research jim@reptiles.org +1 416 410-5633 =20 "He who dies with the most toys is nonetheless dead" =20 --=20 =20 This transmission contains confidential and privileged information intended= =20 solely for the party identified above. If you receive this message in=20 error, you must not use it or convey it to others. Please destroy it=20 immediately and contact the sender at (303) 386-3955 or by return e-mail to= =20 the sender. --534c5ee7_5bd062c2_226e Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline =
Yep, as long as you are ok with losing the data in= between the =E2=80=9Cdump all=E2=80=9D then it sounds good.

For example: =

<= /div>
9pm = =3D pg_dumpall 

during the day you modified and added a bunch of data. <= /div>

Then th= e next day 8pm =3D you accidentally drop the database. 

Your recovery is l= ast night=E2=80=99s backup and everything that happened during the day is g= one. 


Also you may want to check out the keep_segments. 

"If you use s= treaming replication without file-based continuous archiving, you have to s= et wal_keep_segments&n= bsp;in the master to a value high enough to ensure that old WAL segments ar= e not recycled too early, while the standby might still need them to catch = up. If the standby falls behind too much, it needs to be reinitialized from= a new base backup. If you set up a WAL archive that's accessible from the = standby, wal_keep_segments is not required as the standby can always use the archive to catc= h up."

-jason 



On April 14, 2014 = at 3:51:50 PM, Jim Mercer (jim@reptiles= .org) wrote:

On Mon, Apr 14, 2014 at 03:14:46PM -0600, Jason Mathis wr= ote:
> Just keep in mind that while wal archiving and replication sound v= ery similar they are intended for two different things.??
> =20
> Wal Archiving =3D backup/DR/PITR/standby
> Replication =3D High availability/reporting??

it would be nice if the documentation reflected this.

the current information i derived, basically says to do both.

i'm looking to revise my settings to:
- nullify/NOP the archive_command and restore_command
- use the slave to do a pg_dumpall as the backup

the standby node serves as a read-only slave, which allows for some
performance gains.

the standby node could be promoted to master, should the master have is= sues.

the backup would allow for a recovery from a catestrophic/cascading err= or
(ie. drop all tables).

does this make sense?

-- =20
Jim Mercer Reptilian Research jim@reptiles.org +1 416 410-5= 633
"He who dies with the most toys is nonetheless dead"

This transmission contains confidential = and privileged information intended solely for the party identified above. If you receive = this message in error, you must not use it or convey it to others. Please destroy it immediately and contact the se= nder at (303) 386-3955 or by return e-mail to the sender.

--534c5ee7_5bd062c2_226e--