Re: psql 8 warm standby strong start, weak finish

On 04/30/2015 06:31 AM, Charlton Galvarino wrote:
> Hello, psql family.
>
> psql (8.4.7)
>
> I am having mixed results for a warm standby. My warm standby appears to work for a couple of days and then refuses to process any more WAL files.
>
> I will see lovely entries like this in standby.log:
> removing "/var/lib/pgsql/archives/00000001000010E5000000E9"
> ...
> Trigger file : /tmp/pgsql.trigger
> Waiting for WAL file : 00000001000010E5000000EF
> WAL file path : /var/lib/pgsql/archives/00000001000010E5000000EF
> Restoring to : pg_xlog/RECOVERYXLOG
> Sleep interval : 2 seconds
> Max wait interval : 0 forever
> Command for restore : cp "/var/lib/pgsql/archives/00000001000010E5000000EF" "pg_xlog/RECOVERYXLOG"
> Keep archive history : 00000001000010E5000000EA and later
>
> And then /var/lib/pgsql/archives/ will continue to grow because it won't process any more WAL files.
>
> Here's the suspicious part. The 00000001000010E5000000EF WAL in question above is the last file to be listed in standby.log, and it is a different size from all the rest. Is there any chance that my end rsync-ed it over from the master to the standby before the master had fully written it out, so the standby sees it as incomplete and gets stuck? Or is it normal to see a WAL that isn't 16MB? My rsync deletes the sent files from the master, so I have nothing to check it against.

So what is your archive_command?

Where is the listing below from?:
>
> -rw------- 1 postgres postgres 16777216 Apr 29 05:07 00000001000010E5000000EA
> -rw------- 1 postgres postgres 16777216 Apr 29 05:11 00000001000010E5000000EB
> -rw------- 1 postgres postgres 16777216 Apr 29 05:15 00000001000010E5000000EC
> -rw------- 1 postgres postgres 16777216 Apr 29 05:15 00000001000010E5000000ED
> -rw------- 1 postgres postgres 16777216 Apr 29 05:15 00000001000010E5000000EE
> -rw------- 1 postgres postgres 12337152 Apr 29 05:20 00000001000010E5000000EF
> -rw------- 1 postgres postgres 16777216 Apr 29 05:25 00000001000010E5000000F0
> -rw------- 1 postgres postgres 16777216 Apr 29 05:30 00000001000010E5000000F1
>
> And on the master, everything looks good in the logs:
>
> `pg_xlog/00000001000010E5000000EC' -> `/var/lib/pgsql/archives/00000001000010E5000000EC'
> `pg_xlog/00000001000010E5000000ED' -> `/var/lib/pgsql/archives/00000001000010E5000000ED'
> `pg_xlog/00000001000010E5000000EE' -> `/var/lib/pgsql/archives/00000001000010E5000000EE'
> `pg_xlog/00000001000010E5000000EF' -> `/var/lib/pgsql/archives/00000001000010E5000000EF'
> `pg_xlog/00000001000010E5000000F0' -> `/var/lib/pgsql/archives/00000001000010E5000000F0'
> `pg_xlog/00000001000010E5000000F1' -> `/var/lib/pgsql/archives/00000001000010E5000000F1'
>
> I have read around and don't see that my nightly vacuum and pg_dump of the master should have any negative affects on my standby. But if you disagree, please let me know.
>
> TIA.
>
>
>
>
> Charlton Galvarino
> 1 (803) 233-6205 : voice
> 1 (803) 223-9579 : fax
> charlton(at)2creek(dot)com
>
> P.O. Box 50960
> Columbia, SC 29250
>
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com