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

[Mads.Tandrup@xxxxxxxxxxxxxxxxxxxxxx]

Hi

Thanks for your reply. Do you know of any options that I could give pg_dump/psql to avoid creating one big transaction? I'm using the plain text format for pg_dump.

Best regards,

Mads

-----pgsql-general-owner@xxxxxxxxxxxxxx skrev: -----

Til: Albe Laurenz <laurenz.albe@xxxxxxxxxx>
Fra: Jeff Janes
Sendt af: pgsql-general-owner@xxxxxxxxxxxxxx
Dato: 05-06-2013 12:43
Cc: "Mads.Tandrup@xxxxxxxxxxxxxxxxxxxxxx" <Mads.Tandrup@xxxxxxxxxxxxxxxxxxxxxx>, "pgsql-general@xxxxxxxxxxxxxx" <pgsql-general@xxxxxxxxxxxxxx>
Emne: 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.albe@xxxxxxxxxx> wrote:

Jeff Janes wrote:
> On Tue, Jun 4, 2013 at 6:25 AM, <Mads.Tandrup@xxxxxxxxxxxxxxxxxxxxxx> 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

______________________________________________________________________
This email has been scanned by the Symantec Email Security.cloud service.
______________________________________________________________________