[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-admin
Subject: Re: [ADMIN] streaming replication - pgsql 9.2.3
From: Jason Mathis <jmathis () redzonesoftware ! com>
Date: 2014-04-14 22:19:19
Message-ID: etPan.534c5ee7.515f007c.226e () palos
[Download RAW message or body]
Yep, as long as you are ok with losing the data in between the "dump all" then it \
sounds good.
For example:
9pm = pg_dumpall
during the day you modified and added a bunch of data.
Then the next day 8pm = you accidentally drop the database.
Your recovery is last night's backup and everything that happened during the day is \
gone.
Also you may want to check out the keep_segments.
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 wal_keep_segments in the master to a value high enough 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, \
wal_keep_segments is not required as the standby can always use the archive to catch \
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 wrote:
> Just keep in mind that while wal archiving and replication sound very similar they \
> are intended for two different things.??
> Wal Archiving = backup/DR/PITR/standby
> Replication = 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 issues.
the backup would allow for a recovery from a catestrophic/cascading error
(ie. drop all tables).
does this make sense?
--
Jim Mercer Reptilian Research jim@reptiles.org +1 416 410-5633
"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 sender at (303) 386-3955 or by return e-mail to
the sender.
[Attachment #3 (text/html)]
<html><head><style>body{font-family:Helvetica,Arial;font-size:13px}</style></head><body \
style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: \
after-white-space;"><div id="bloop_customfont" \
style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: \
0px; line-height: auto;">Yep, as long as you are ok with losing the data in between \
the "dump all" then it sounds good.</div><div id="bloop_customfont" \
style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: \
0px; line-height: auto;"><br></div><div id="bloop_customfont" \
style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: \
0px; line-height: auto;">For example: </div><div id="bloop_customfont" \
style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: \
0px; line-height: auto;"><br></div><div id="bloop_customfont" \
style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: \
0px; line-height: auto;">9pm = pg_dumpall </div><div id="bloop_customfont" \
style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: \
0px; line-height: auto;"><br></div><div id="bloop_customfont" \
style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: \
0px; line-height: auto;">during the day you modified and added a bunch of \
data. </div><div id="bloop_customfont" \
style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: \
0px; line-height: auto;"><br></div><div id="bloop_customfont" \
style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: \
0px; line-height: auto;">Then the next day 8pm = you accidentally drop the \
database. </div><div id="bloop_customfont" \
style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: \
0px; line-height: auto;"><br></div><div id="bloop_customfont" \
style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: \
0px; line-height: auto;">Your recovery is last night's backup and everything that \
happened during the day is gone. </div><div id="bloop_customfont" \
style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: \
0px; line-height: auto;"><br></div><div id="bloop_customfont" \
style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: \
0px; line-height: auto;"><br></div><div id="bloop_customfont" \
style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: \
0px; line-height: auto;">Also you may want to check out the \
keep_segments. </div><div id="bloop_customfont" \
style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: \
0px; line-height: auto;"><br></div><div id="bloop_customfont" \
style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: \
0px; line-height: auto;"><a \
href="http://www.postgresql.org/docs/9.2/static/warm-standby.html">http://www.postgresql.org/docs/9.2/static/warm-standby.html</a></div><div \
id="bloop_customfont" style="font-family:Helvetica,Arial;font-size:13px; color: \
rgba(0,0,0,1.0); margin: 0px; line-height: auto;"><i><span style="font-family: \
verdana, sans-serif; font-size: 12px; line-height: 18.239999771118164px;">"If you use \
streaming replication without file-based continuous archiving, you have to \
set </span><tt class="VARNAME" style="font-size: 12px; line-height: \
18.239999771118164px;">wal_keep_segments</tt><span style="font-family: verdana, \
sans-serif; font-size: 12px; line-height: 18.239999771118164px;"> in the master \
to a value high enough 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, </span><tt class="VARNAME" \
style="font-size: 12px; line-height: \
18.239999771118164px;">wal_keep_segments</tt><span style="font-family: verdana, \
sans-serif; font-size: 12px; line-height: 18.239999771118164px;"> is not \
required as the standby can always use the archive to catch up."</span></i></div><div \
id="bloop_customfont" style="font-family:Helvetica,Arial;font-size:13px; color: \
rgba(0,0,0,1.0); margin: 0px; line-height: auto;"><br></div><div \
id="bloop_customfont" style="font-family:Helvetica,Arial;font-size:13px; color: \
rgba(0,0,0,1.0); margin: 0px; line-height: auto;">-jason </div><div \
id="bloop_customfont" style="font-family:Helvetica,Arial;font-size:13px; color: \
rgba(0,0,0,1.0); margin: 0px; line-height: auto;"><br></div><div \
id="bloop_customfont" style="font-family:Helvetica,Arial;font-size:13px; color: \
rgba(0,0,0,1.0); margin: 0px; line-height: auto;"><br></div> <br><p \
style="color:#000;">On April 14, 2014 at 3:51:50 PM, Jim Mercer (<a \
href="mailto:jim@reptiles.org">jim@reptiles.org</a>) wrote:</p> <blockquote \
type="cite" class="clean_bq"><span><div><div></div><div>On Mon, Apr 14, 2014 at \
03:14:46PM -0600, Jason Mathis wrote: <br>> Just keep in mind that while wal \
archiving and replication sound very similar they are intended for two different \
things.?? <br>>
<br>> Wal Archiving = backup/DR/PITR/standby
<br>> Replication = High availability/reporting??
<br>
<br>it would be nice if the documentation reflected this.
<br>
<br>the current information i derived, basically says to do both.
<br>
<br>i'm looking to revise my settings to:
<br>- nullify/NOP the archive_command and restore_command
<br>- use the slave to do a pg_dumpall as the backup
<br>
<br>the standby node serves as a read-only slave, which allows for some
<br>performance gains.
<br>
<br>the standby node could be promoted to master, should the master have issues.
<br>
<br>the backup would allow for a recovery from a catestrophic/cascading error
<br>(ie. drop all tables).
<br>
<br>does this make sense?
<br>
<br>--
<br>Jim Mercer Reptilian Research jim@reptiles.org +1 416 410-5633
<br>"He who dies with the most toys is nonetheless dead"
<br></div></div></span></blockquote></body></html>
<br>
<p><font face="Arial" size="2">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 sender at (303) 386-3955 or by return e-mail to the \
sender.</font></p>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic