Date: Mon, 14 Apr 2014 11:57:51 -0500 (CDT)
From: Scott Whitney <scott@journyx.com>
Reply-To: Scott Whitney <swhitney@journyx.com>
To: Jim Mercer <jim@reptiles.org>
Cc: pgsql-admin@postgresql.org
Message-ID: 
 <2125585511.76278.1397494671973.JavaMail.zimbra@mail.int.journyx.com>
In-Reply-To: <20140414163956.GA27624@reptiles.org>
References: <20140414163956.GA27624@reptiles.org>
Subject: Re: streaming replication - pgsql 9.2.3
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="----=_Part_76277_1864975868.1397494671971"
Thread-Topic: streaming replication - pgsql 9.2.3
Thread-Index: zDpw85JsC9jRysdWLCz6dKxi+6FFig==
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

------=_Part_76277_1864975868.1397494671971
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

My particular opinion on this sometimes causes "a bit of a stir," let us say, so take this as my opinion and my use case. 

I have: 
1) Production server 
2) "Non-production" (demos/training) used to replicate #1 
3) Offsite "internal production" used to replicate #1 and #2 

So, 
#2 replicates #1 (gigabit back end) 
#3 replicates #2 _and_ #1 (65Mbps back end) 

Each does their own individual pg_dump of each db nightly for DR/backup purposes. 

I do not archive the WAL files and I feel it's more trouble than it is worth. Rather, I set: 

wal_keep_segments = 500 

500 on my production server gives me about 30 hours, give or take. 

I know this because I watch how quickly I fill up a 16MB log over the course 
of several says. 

So...why keep WAL files in a replicated environment? 

I only see them as useful for one of 2 reasons. 

a) In theory you could do something clever and have your replicated server set up to "be behind" 
a certain amount, and you could likely use those WAL files for a PITR at some point in the past 
(in between your current replication time and the latest WAL file). Maybe. Don't know. Never tried. 
This would be useful if you "lost" your production cluster at some point in between those 2 windows, 
and you were fast enough to notice it. Maybe. 

b) If your server stops replicating (out of space, can't connect, fell behind due to bandwidth are 
the 3 scenarios I have run into more than once), and I get to 502 WAL files required, in order to 
get my slave back to good, I then have to take #3 offline, do my pg_startbackup(), rsync, and bring it 
back online in order to get replication functioning again . If I _had_ archived those files, in theory it would 
eventually catch up rather than missing a current WAL file that is needed for replication. 

That's my .02 on the matter. I've been running it this way for about a year, now, and it works great for me. 

----- Original Message -----

> Hi,

> I have set up streaming replication between two servers, using the various
> walkthroughs the googlepedia found for me.

> At this point, it certainly seems that the replication is working, and I am
> able to actually blow away the slave, and have it rebuild, resync and be back
> in action as a read-only source.

> I am confused about the purpose and/or point of the WAL files in the context
> of streaming replication.

> The documentation seems to be quite insistent that the 'archive_command'
> should
> be enabled on the master, and the 'restore_command' should be enabled on the
> slave.

> On the master, I made my own script which is fairly pedantic:
> - copy the WAL file from $PGDATA/pg_xlog into $MASTER:$ARCHDIR
> - rsync file(s) from $MASTER:$ARCHDIR to $SLAVE:$ARCHDIR
> - remove copy of file in $MASTER:$ARCHDIR
> (i don't have a network share between then, so, i am rsyncing)

> On the slave, I made a similarly pedandic script:
> - copy the file from $SLAVE:$ARCHDIR to $PGDATA/pg_xlog/RECOVERYXLOG

> The script on the master runs with regularity, and i am seeing the WAL files
> show up in $SLAVE:$ARCHDIR

> however, on the slave side, i'm seeing some issues:

> - the filename being handed to the script is sometimes for a non-existent
> file
> - it appears to be the next file in sequence, but that filename hasn't even
> been generated on the master yet.
> - it appears, that if there are multiple files in $SLAVE:$ARCHDIR, they might
> actually overwrite themselves when copied to pg_xlog/RECOVERYXLOG

> these are the logs my script is generating, if i do a stop/start of postgres:

> Apr 14 12:19:20 dt-pgsql-001 archive-slave.sh: notice: rtn=0 - cp
> [/export/pg-archive/00000001000000220000007F]
> /export/postgresql/data/main/[pg_xlog/RECOVERYXLOG]
> Apr 14 12:19:20 dt-pgsql-001 archive-slave.sh: notice: rtn=0 - cp
> [/export/pg-archive/000000010000002200000080]
> /export/postgresql/data/main/[pg_xlog/RECOVERYXLOG]
> Apr 14 12:19:23 dt-pgsql-001 archive-slave.sh: error: no such
> file[/export/pg-archive/000000010000002200000081]
> Apr 14 12:19:23 dt-pgsql-001 archive-slave.sh: error: no such
> file[/export/pg-archive/000000010000002200000081]

> am i doing something wrong?

> please advise.

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

> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin

------=_Part_76277_1864975868.1397494671971
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: quoted-printable

<html><body><div style=3D"font-family: times new roman, new york, times, se=
rif; font-size: 12pt; color: #000000"><div><div><span style=3D"font-size: 1=
2pt;">My particular opinion on this sometimes causes "a bit of a stir," let=
 us say, so take this as my opinion and my use case.</span></div><div><br><=
/div><div>I have:</div><div>1) Production server</div><div>2) "Non-producti=
on" (demos/training) used to replicate #1</div><div>3) Offsite "internal pr=
oduction" used to replicate #1 and #2</div><div><br></div><div>So,</div><di=
v>#2 replicates #1 (gigabit back end)</div><div>#3 replicates #2 _and_ #1 (=
65Mbps back end)</div><div><br>Each does their own individual pg_dump of ea=
ch db nightly for DR/backup purposes.</div><div><br></div><div>I do not arc=
hive the WAL files and I feel it's more trouble than it is worth. Rather, I=
 set:</div><div><br></div><div>wal_keep_segments =3D 500</div><div><br></di=
v><div>500 on my production server gives me about 30 hours, give or take.</=
div><div><br></div><div>I know this because I watch how quickly I fill up a=
 16MB log over the course</div><div>of several says.</div><div><br></div><d=
iv>So...why keep WAL files in a replicated environment?</div><div><br></div=
><div>I only see them as useful for one of 2 reasons.</div><div><br></div><=
div>a) In theory you could do something clever and have your replicated ser=
ver set up to "be behind"</div><div>a certain amount, and you could likely =
use those WAL files for a PITR at some point in the past</div><div>(in betw=
een your current replication time and the latest WAL file). Maybe. Don't kn=
ow. Never tried.</div><div>This would be useful if you "lost" your producti=
on cluster at some point in between those 2 windows,</div><div>and you were=
 fast enough to notice it. Maybe.</div><div><br></div><div>b) If your serve=
r stops replicating (out of space, can't connect, fell behind due to bandwi=
dth are</div><div>the 3 scenarios I have run into more than once), and I ge=
t&nbsp;<span style=3D"font-size: 12pt;">to 502 WAL files required, in order=
 to</span></div><div><span style=3D"font-size: 12pt;">get my slave back to =
good, I then have to take #3 offline, do my pg_startbackup(), rsync, and br=
ing it</span></div><div>back online in order to get replication functioning=
 again<span style=3D"font-size: 12pt;">. If I _had_ archived those files, i=
n theory it would</span></div><div><span style=3D"font-size: 12pt;"> eventu=
ally catch up rather than missing&nbsp;</span><span style=3D"font-size: 12p=
t;">a current WAL file that is needed for replication.</span></div><div><br=
></div><div>That's my .02 on the matter. I've been running it this way for =
about a year, now, and it works great for me.</div><div><br></div></div><di=
v><br></div><hr id=3D"zwchr"><blockquote style=3D"border-left:2px solid #10=
10FF;margin-left:5px;padding-left:5px;color:#000;font-weight:normal;font-st=
yle:normal;text-decoration:none;font-family:Helvetica,Arial,sans-serif;font=
-size:12pt;">Hi,<br><div><br></div>I have set up streaming replication betw=
een two servers, using the various<br>walkthroughs the googlepedia found fo=
r me.<br><div><br></div>At this point, it certainly seems that the replicat=
ion is working, and I am<br>able to actually blow away the slave, and have =
it rebuild, resync and be back<br>in action as a read-only source.<br><div>=
<br></div>I am confused about the purpose and/or point of the WAL files in =
the context<br>of streaming replication.<br><div><br></div>The documentatio=
n seems to be quite insistent that the 'archive_command' should<br>be enabl=
ed on the master, and the 'restore_command' should be enabled on the<br>sla=
ve.<br><div><br></div>On the master, I made my own script which is fairly p=
edantic:<br>- copy the WAL file from $PGDATA/pg_xlog into $MASTER:$ARCHDIR<=
br>- rsync file(s) from $MASTER:$ARCHDIR to $SLAVE:$ARCHDIR<br>- remove cop=
y of file in $MASTER:$ARCHDIR<br>(i don't have a network share between then=
, so, i am rsyncing)<br><div><br></div>On the slave, I made a similarly ped=
andic script:<br>- copy the file from $SLAVE:$ARCHDIR to $PGDATA/pg_xlog/RE=
COVERYXLOG<br><div><br></div>The script on the master runs with regularity,=
 and i am seeing the WAL files<br>show up in $SLAVE:$ARCHDIR<br><div><br></=
div>however, on the slave side, i'm seeing some issues:<br><div><br></div>-=
 the filename being handed to the script is sometimes for a non-existent fi=
le<br>- it appears to be the next file in sequence, but that filename hasn'=
t even<br>been generated on the master yet.<br>- it appears, that if there =
are multiple files in $SLAVE:$ARCHDIR, they might<br>actually overwrite the=
mselves when copied to pg_xlog/RECOVERYXLOG<br><div><br></div>these are the=
 logs my script is generating, if i do a stop/start of postgres:<br><div><b=
r></div>Apr 14 12:19:20 dt-pgsql-001 archive-slave.sh: notice: rtn=3D0 - cp=
 [/export/pg-archive/00000001000000220000007F] /export/postgresql/data/main=
/[pg_xlog/RECOVERYXLOG]<br>Apr 14 12:19:20 dt-pgsql-001 archive-slave.sh: n=
otice: rtn=3D0 - cp [/export/pg-archive/000000010000002200000080] /export/p=
ostgresql/data/main/[pg_xlog/RECOVERYXLOG]<br>Apr 14 12:19:23 dt-pgsql-001 =
archive-slave.sh: error: no such file[/export/pg-archive/000000010000002200=
000081]<br>Apr 14 12:19:23 dt-pgsql-001 archive-slave.sh: error: no such fi=
le[/export/pg-archive/000000010000002200000081]<br><div><br></div><br>am i =
doing something wrong?<br><div><br></div>please advise.<br><div><br></div><=
br>-- <br>Jim Mercer &nbsp; &nbsp; Reptilian Research &nbsp; &nbsp; &nbsp;j=
im@reptiles.org &nbsp; &nbsp;+1 416 410-5633<br>"He who dies with the most =
toys is nonetheless dead"<br><div><br></div><br>-- <br>Sent via pgsql-admin=
 mailing list (pgsql-admin@postgresql.org)<br>To make changes to your subsc=
ription:<br>http://www.postgresql.org/mailpref/pgsql-admin<br></blockquote>=
<div><br></div></div></body></html>
------=_Part_76277_1864975868.1397494671971--