Search Postgresql Archives

Re: Replication failed after stalling

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Joe Van Dyk <joe@xxxxxxxxx> writes:

> I'm running Postgresql 9.3. I have a streaming replication server. Someone was running a long COPY query (8 hours) on the standby which halted replication. The
> replication stopped at 3:30 am. I canceled the long-running query at 9:30 am and replication data started catching up.
>
> The data up until 10 am got restored fine (took until 10:30 am to restore that much). Then I started getting errors like "FATAL:  could not receive data from WAL
> stream: ERROR:  requested WAL segment 00000001000003C300000086 has already been removed".
>
> I'm confused about how pg could restore data from 3:30 am to 10 am, then start complaining about missing WAL files.
>
> What's the best way to avoid this problem? Increase wal_keep_segments?

Yes and/or implement as a hybrid of streaming and WAL shipping.

Quite simply, your wal_keep segments was almost enough to  get you
through that backlog period but as your standby was catching up, it hit
a point  where  there was a gap.

Depending on how much traffic your master sees at various times of the
day, it's unsurprising that during peak loads, your grace-period is a
lot lower than during off-peak times due to variations in how quickly
WAL segments are filled and cycled over. 

HTH

>
> Joe
>

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@xxxxxxxxxxx
p: 312.241.7800


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




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux