Re: postgres standby won't start

On 10/05/2015 10:53 AM, Ramalingam, Sankarakumar wrote:
> We have a standby set up between two sites in two different locations.
> The replication was going on well and suddenly it stopped due to error
>
> 2015-09-08 16:07:51 EDT LOG: streaming replication successfully
> connected to primary
>
> 2015-09-08 16:07:51 EDT FATAL: could not receive data from WAL stream:
> FATAL: requested WAL segment 0000000C0000035E000000F0 has already been
> removed

Best guess is you have wal_keep_segments set to low:

http://www.postgresql.org/docs/9.4/interactive/runtime-config-replication.html

wal_keep_segments (integer)

Specifies the minimum number of past log file segments kept in the
pg_xlog directory, in case a standby server needs to fetch them for
streaming replication. Each segment is normally 16 megabytes. If a
standby server connected to the sending server falls behind by more than
wal_keep_segments segments, the sending server might remove a WAL
segment still needed by the standby, in which case the replication
connection will be terminated. Downstream connections will also
eventually fail as a result. (However, the standby server can recover by
fetching the segment from archive, if WAL archiving is in use.)

This sets only the minimum number of segments retained in pg_xlog;
the system might need to retain more segments for WAL archival or to
recover from a checkpoint. If wal_keep_segments is zero (the default),
the system doesn't keep any extra segments for standby purposes, so the
number of old WAL segments available to standby servers is a function of
the location of the previous checkpoint and status of WAL archiving.
This parameter can only be set in the postgresql.conf file or on the
server command line.

>
> I am unable to start the DB as well.

Which one the primary, the standby or both?

>
> Should I restore a fresh copy from production on to this standby to make
> things in order? If yes, how to go about it. I am quite new to Postgres.
> Any help/suggestions will be greatly appreciated.

Depends did you have WAL archiving set up, where you could pull the
missing WAL file(s) from?

If not you will need to rebuild. Take a look at pg_basebackup:

http://www.postgresql.org/docs/9.4/interactive/app-pgbasebackup.html

>
> Thanks
>
> Kumar Ramalingam
>
> Global Database Administration
>
> Elavon, Atlanta , GA
>
> 678 731 5288
>
> The information contained in this e-mail and in any attachments is
> intended only for the person or entity to which it is addressed and may
> contain confidential and/or privileged material. Any review,
> retransmission, dissemination or other use of, or taking of any action
> in reliance upon, this information by persons or entities other than the
> intended recipient is prohibited. This message has been scanned for
> known computer viruses.
>

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