Re: Streaming replication with sync slave, but disconnects due to missing WAL segments - Mailing list pgsql-general

<font face="Default Sans Serif,Verdana,Arial,Helvetica,sans-serif" size="2"><div>Hi</div><div><br /></div><div>Thanks
foryour reply. Do you know of any options that I could give pg_dump/psql to avoid creating one big transaction? I'm
usingthe plain text format for pg_dump.</div><div><br /></div><div>Best regards,</div><div>Mads</div><br /><br /><font
color="#990099">-----pgsql-general-owner@postgresql.orgskrev: -----</font><div style="padding-left:5px;"><div
style="padding-right:0px;padding-left:5px;border-left:solidblack 2px;">Til: Albe Laurenz
<laurenz.albe@wien.gv.at><br/>Fra: Jeff Janes <br />Sendt af: pgsql-general-owner@postgresql.org<br />Dato:
05-06-201312:43<br />Cc: "Mads.Tandrup@schneider-electric.com" <Mads.Tandrup@schneider-electric.com>,
"pgsql-general@postgresql.org"<pgsql-general@postgresql.org><br />Emne: Re: [GENERAL] Streaming replication with
syncslave, but disconnects due to missing WAL segments<br /><br /><div dir="ltr">On Wed, Jun 5, 2013 at 1:30 AM, Albe
Laurenz<span dir="ltr"><<a href="mailto:laurenz.albe@wien.gv.at"
target="_blank">laurenz.albe@wien.gv.at</a>></span>wrote:<br /><div class="gmail_extra"><div
class="gmail_quote"><blockquoteclass="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc
solid;padding-left:1ex"><divclass="im">Jeff Janes wrote:<br /> > On Tue, Jun 4, 2013 at 6:25 AM, <<a
href="mailto:Mads.Tandrup@schneider-electric.com">Mads.Tandrup@schneider-electric.com</a>>wrote:<br /></div><div
class="im">>>I have a question about sync streaming replication.<br /> >><br /> >> I have 2
postgresql9.1 servers set up with streaming replication. On the<br /> >> master node the slave is configured as a
synchronousstandby. I've verified<br /> >> that pg_stat_replication shows sync_state = sync for the slave
node.<br/> >><br /> >> It all seems to work fine. But I have noticed that sometimes when I restore<br />
>>backups created by pg_dump. The slave node will disconnect with the message<br /> >> in the postgresql
log:<br/> ><br /> > You cannot use pg_dump to set up a database for receiving streaming replication.<br />
><br/> > For that it must be a physical copy, not a logical copy which is what pg_dump generates.<br /><br
/></div>Ithink you misunderstood - he restores a dump on<br /> the *primary* and that makes replication fall over<br />
becauseit cannot catch up.<br /></blockquote><div><br /></div><div style="">Ah, I see.  And the synchronization point
doesn'thelp, because it only occurs at commit-time and if the dump is being loaded in a single transaction, then the
transactioncould last for several checkpoints.  By the time it tries to synchronize, it is already too late.</div><div
style=""><br/></div><div style="">Cheers,</div><div style=""><br /></div><div style="">Jeff</div></div></div></div><br
clear="both"/> ______________________________________________________________________<br /> This email has been scanned
bythe Symantec Email Security.cloud service.<br />
______________________________________________________________________<br/></div></div><div></div></font>