Re: Streaming replication with sync slave, but disconnects due to missing WAL segments

On Wed, Jun 5, 2013 at 1:30 AM, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>wrote:

> Jeff Janes wrote:
> > On Tue, Jun 4, 2013 at 6:25 AM, <Mads(dot)Tandrup(at)schneider-electric(dot)com>
> wrote:
> >> I have a question about sync streaming replication.
> >>
> >> I have 2 postgresql 9.1 servers set up with streaming replication. On
> the
> >> master node the slave is configured as a synchronous standby. I've
> verified
> >> that pg_stat_replication shows sync_state = sync for the slave node.
> >>
> >> It all seems to work fine. But I have noticed that sometimes when I
> restore
> >> backups created by pg_dump. The slave node will disconnect with the
> message
> >> in the postgresql log:
> >
> > You cannot use pg_dump to set up a database for receiving streaming
> replication.
> >
> > For that it must be a physical copy, not a logical copy which is what
> pg_dump generates.
>
> I think you misunderstood - he restores a dump on
> the *primary* and that makes replication fall over
> because it cannot catch up.
>

Ah, I see. And the synchronization point doesn't help, because it only
occurs at commit-time and if the dump is being loaded in a single
transaction, then the transaction could last for several checkpoints. By
the time it tries to synchronize, it is already too late.

Cheers,

Jeff