Re: Hot Standby Not So Hot Anymore

[Ian Harding <harding.ian@xxxxxxxxx>]

On Mon, Nov 5, 2012 at 8:15 PM, Lonni J Friedman <netllama@xxxxxxxxx> wrote:

On Mon, Nov 5, 2012 at 8:13 PM, Ian Harding <harding.ian@xxxxxxxxx> wrote:
>
>
>
> On Mon, Nov 5, 2012 at 7:57 PM, Lonni J Friedman <netllama@xxxxxxxxx> wrote:
>>
>> On Mon, Nov 5, 2012 at 7:49 PM, Ian Harding <harding.ian@xxxxxxxxx> wrote:
>> >
>> >
>> > On Mon, Nov 5, 2012 at 7:46 PM, Lonni J Friedman <netllama@xxxxxxxxx>
>> > wrote:
>> >>
>> >> On Mon, Nov 5, 2012 at 7:40 PM, Ian Harding <harding.ian@xxxxxxxxx>
>> >> wrote:
>> >> > I had a 9.0.8 hot standby setup, one master, two slaves, working
>> >> > great.
>> >> > Then, I tried to re-initialize by making a base backup, the way I've
>> >> > done it
>> >> > many times before, but for some reason I can't get the standby to
>> >> > accept
>> >> > connections.  I copied the postgresql.conf and recorvery.conf out of
>> >> > the
>> >> > way, cleaned the data directory and extracted the backup files, then
>> >> > replaced the conf files.  Everything works perfectly, but I keep
>> >> > getting
>> >> > :FATAL:  the database system is starting up
>> >> >
>> >> > I know I'm an idiot and that I did something wrong but I can't find
>> >> > it.
>> >> >
>> >> > [root@db03 data]# grep standby postgresql.conf
>> >> > wal_level = hot_standby            # minimal, archive, or hot_standby
>> >> > hot_standby = on        # "on" allows queries during recovery
>> >> > max_standby_archive_delay = -1    # max delay before canceling
>> >> > queries
>> >> > max_standby_streaming_delay = -1    # max delay before canceling
>> >> > queries
>> >> >
>> >> > [root@db03 data]# grep standby recovery.conf
>> >> > # Specifies whether to start the server as a standby. In streaming
>> >> > replication,
>> >> > standby_mode          = 'on'
>> >> > # Specifies a connection string which is used for the standby server
>> >> > to
>> >> > connect
>> >> >
>> >> > [root@db03 data]# ps aux | grep stream
>> >> > postgres 62127  0.1  0.0 34554356 3724 ?       Ss   16:22   0:16
>> >> > postgres:
>> >> > wal receiver process   streaming BA7/12B87818
>> >> >
>> >> >
>> >> > [root@db03 data]# ps aux | grep startup
>> >> > postgres 62122  0.5  0.9 34545900 1223708 ?    Ss   16:22   1:03
>> >> > postgres:
>> >> > startup process   recovering 0000000100000BA700000012
>> >> >
>> >> > Any suggestions appreciated!!!
>> >>
>> >> What's your log have to say?
>> >
>> >
>> > It says everything is happy as normal...
>> >
>> > 2012-11-05 16:22:38.744 PST -  :LOG:  database system was shut down in
>> > recovery at 2012-11-05 16:22:33 PST
>> > 2012-11-05 16:22:38.745 PST -  :LOG:  entering standby mode
>> > 2012-11-05 16:22:38.746 PST -  :LOG:  redo starts at BA5/F96F86A8
>> > 2012-11-05 16:22:38.762 PST - postgres :FATAL:  the database system is
>> > starting up
>> > 2012-11-05 16:22:39.764 PST - postgres :FATAL:  the database system is
>> > starting up
>> > 2012-11-05 16:22:40.766 PST - postgres :FATAL:  the database system is
>> > starting up
>> > 2012-11-05 16:22:41.200 PST -  :LOG:  invalid record length at
>> > BA6/6DCBA48
>> > 2012-11-05 16:22:41.206 PST -  :LOG:  streaming replication successfully
>> > connected to primary
>> >
>> >
>> > This is after I stopped and restarted... after that just more failed
>> > connection attempts.
>>
>> Are you sure that its not still recovering?  In other words, when you
>> look at the 'ps' output do the 'wal receiver process' and 'recovering'
>> entries change?
>
>
> Yeah, it's still recovering.  In normal operation it keeps right on
> recovering, it's just that it used to accept read-only connections...

Is the 2nd slave also experiencing this problem?  If not, is it
further ahead than the bad one?

Everything looks fine (forgot to cc the list...)

[root@db0 ~]# ps aux | grep stream
postgres 45267  0.0  0.0 34546456 3036 ?       Ss   16:22   0:14 postgres: wal sender process postgres 192.168.4.4(55925) streaming BA7/5FC1BFD8
postgres 54247  0.0  0.0 34546456 3036 ?       Ss   18:15   0:07 postgres: wal sender process postgres 192.168.4.3(57482) streaming BA7/5FC1BFD8