Re: postgres standby won't start - Mailing list pgsql-general

Thanks Adrian.

My primary is doing  fine. Only the standby. I am noticing it after a while..my Bad!!
Primary setting
#------------------------------------------------------------------------------
# REPLICATION
#------------------------------------------------------------------------------

max_wal_senders = 5
wal_sender_delay = 1s
wal_keep_segments = 512
vacuum_defer_cleanup_age = 20
hot_standby = on

Since I am unable to bring standby up, would the pg_basebackup help me out here for a complete sync? Thanks again.



Thanks
Kumar Ramalingam
X6015288


-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
Sent: Monday, October 05, 2015 2:18 PM
To: Ramalingam, Sankarakumar; pgsql-general@postgresql.org
Subject: Re: [GENERAL] 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
needsto fetch them for streaming replication. Each segment is normally 16 megabytes. If a standby server connected to
thesending server falls behind by more than wal_keep_segments segments, the sending server might remove a WAL segment
stillneeded by the standby, in which case the replication connection will be terminated. Downstream connections will
alsoeventually fail as a result. (However, the standby server can recover by fetching the segment from archive, if WAL
archivingis in use.) 

     This sets only the minimum number of segments retained in pg_xlog; the system might need to retain more segments
forWAL archival or to recover from a checkpoint. If wal_keep_segments is zero (the default), the system doesn't keep
anyextra segments for standby purposes, so the number of old WAL segments available to standby servers is a function of
thelocation 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.klaver@aklaver.com
The information contained in this e-mail and in any attachments is intended only for the person or entity to which it
isaddressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other
useof, or taking of any action in reliance upon, this information by persons or entities other than the intended
recipientis prohibited. This message has been scanned for known computer viruses.