Re: Hot Standby Not So Hot Anymore

On Mon, Nov 5, 2012 at 8:15 PM, Lonni J Friedman <netllama(at)gmail(dot)com> wrote:

> On Mon, Nov 5, 2012 at 8:13 PM, Ian Harding <harding(dot)ian(at)gmail(dot)com> wrote:
> >
> >
> >
> > On Mon, Nov 5, 2012 at 7:57 PM, Lonni J Friedman <netllama(at)gmail(dot)com>
> wrote:
> >>
> >> On Mon, Nov 5, 2012 at 7:49 PM, Ian Harding <harding(dot)ian(at)gmail(dot)com>
> wrote:
> >> >
> >> >
> >> > On Mon, Nov 5, 2012 at 7:46 PM, Lonni J Friedman <netllama(at)gmail(dot)com>
> >> > wrote:
> >> >>
> >> >> On Mon, Nov 5, 2012 at 7:40 PM, Ian Harding <harding(dot)ian(at)gmail(dot)com>
> >> >> 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(at)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(at)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(at)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(at)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(at)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