Thread: WAL replay asking for very old WAL

could it be that you have some other slave trying to replicate from this one
(that was last built) and it has fallen behind and is asking for that old
WAL file?






--
View this message in context: http://postgresql.nabble.com/WAL-replay-asking-for-very-old-WAL-tp5891050p5891066.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.

I think you're correct, I did have another server replication off of this one, but could this cause the server to stay
inrecover mode? 

In the log below it has caught up but doesn't not leave recovery mode even after a restart of the service.

2016-03-07 16:04:52 UTC FATAL: the database system is starting up
2016-03-07 16:04:52 UTC LOG: restored log file "000000010000581D00000082" from archive
2016-03-07 16:04:53 UTC FATAL: the database system is starting up
2016-03-07 16:04:53 UTC FATAL: the database system is starting up
2016-03-07 16:04:54 UTC LOG: restored log file "000000010000581D00000083" from archive
2016-03-07 16:04:55 UTC FATAL: the database system is starting up
2016-03-07 16:04:55 UTC LOG: restored log file "000000010000581D00000084" from archive
2016-03-07 16:04:55 UTC LOG: consistent recovery state reached at 581D/84D5FD40
2016-03-07 16:04:55 UTC LOG: database system is ready to accept read only connections
2016-03-07 16:04:56 UTC LOG: unexpected pageaddr 581B/44000000 in log segment 000000010000581D00000085, offset 0
2016-03-07 16:04:56 UTC LOG: started streaming WAL from primary at 581D/84000000 on timeline 1
2016-03-07 16:04:56 UTC ERROR: cannot execute UNLISTEN during recovery
2016-03-07 16:04:56 UTC STATEMENT: unlisten *
2016-03-07 16:04:56 UTC ERROR: cannot execute UNLISTEN during recovery
2016-03-07 16:04:56 UTC STATEMENT: unlisten *
2016-03-07 16:04:57 UTC ERROR: cannot execute UNLISTEN during recovery
2016-03-07 16:04:57 UTC STATEMENT: unlisten *
2016-03-07 16:04:57 UTC ERROR: cannot execute UNLISTEN during recovery
2016-03-07 16:04:57 UTC STATEMENT: unlisten *
2016-03-07 16:04:57 UTC ERROR: cannot execute UNLISTEN during recovery
2016-03-07 16:04:57 UTC STATEMENT: unlisten *
2016-03-07 16:04:58 UTC ERROR: cannot execute UNLISTEN during recovery
2016-03-07 16:04:58 UTC STATEMENT: unlisten *
2016-03-07 16:04:58 UTC ERROR: cannot execute UNLISTEN during recovery
2016-03-07 16:04:58 UTC STATEMENT: unlisten *
2016-03-07 16:05:03 UTC ERROR: cannot execute UNLISTEN during recovery
2016-03-07 16:05:03 UTC STATEMENT: unlisten *
2016-03-07 16:05:06 UTC ERROR: cannot execute UNLISTEN during recovery
2016-03-07 16:05:06 UTC STATEMENT: unlisten *
2016-03-07 16:05:08 UTC ERROR: cannot execute UNLISTEN during recovery
2016-03-07 16:05:08 UTC STATEMENT: unlisten *
2016-03-07 16:05:08 UTC ERROR: cannot execute UNLISTEN during recovery
2016-03-07 16:05:08 UTC STATEMENT: unlisten *
2016-03-07 16:05:13 UTC ERROR: cannot execute UNLISTEN during recovery
2016-03-07 16:05:13 UTC STATEMENT: unlisten *

Stephen Kuntz | Systems Administrator
Pelmorex Media Inc.
T: 905.829.1159 x1376

-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of MirrorX
Sent: Monday, March 7, 2016 10:48 AM
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] WAL replay asking for very old WAL

could it be that you have some other slave trying to replicate from this one (that was last built) and it has fallen
behindand is asking for that old WAL file? 






--
View this message in context: http://postgresql.nabble.com/WAL-replay-asking-for-very-old-WAL-tp5891050p5891066.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

sorry i don't follow.
the server will stay in recovery mode as long as a recovery.conf file exists
and the host is set up to receive xlogs via streaming from the master. this
seems to be the case according to the logs, if i am not wrong.

what it is you want to happen? the server to stop applying records and come
online in r/w mode?
in this case you could promote the server. or if you want just to apply some
wal files and then come online then i think you have misconfigured your
recovery.conf. i would suggest you paste here what your recovery.conf
contains and what the objective is



Best,



--
View this message in context: http://postgresql.nabble.com/WAL-replay-asking-for-very-old-WAL-tp5891050p5891074.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.

Sorry for the confusion, I am by no means a postgresql guru.

In my other slave servers I don't have the message:

2016-03-07 16:23:36 UTC ERROR: cannot execute UNLISTEN during recovery
2016-03-07 16:23:36 UTC STATEMENT: unlisten *

In the logs once they are caught up to the master. It also doesn't seem to switch to standby mode properly and stream
fromthe master. When I restart, it replays the WAL files since the last restart from the archive. This data should have
beenstreamed from the master, no? 

standby_mode = 'on'
primary_conninfo = 'user=<replicationuser> password=<mypassword> host=masterserver port=5432 sslmode=prefer
sslcompression=1krbsrvname=postgres' 
restore_command = 'copy "\\\\archivefileserver\\BACKUP\\DatabaseBackups\\wal_archive\\%f" "%p"'


Stephen Kuntz | Systems Administrator
Pelmorex Media Inc.
T: 905.829.1159 x1376

-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of MirrorX
Sent: Monday, March 7, 2016 11:13 AM
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] WAL replay asking for very old WAL

sorry i don't follow.
the server will stay in recovery mode as long as a recovery.conf file exists and the host is set up to receive xlogs
viastreaming from the master. this seems to be the case according to the logs, if i am not wrong. 

what it is you want to happen? the server to stop applying records and come online in r/w mode?
in this case you could promote the server. or if you want just to apply some wal files and then come online then i
thinkyou have misconfigured your recovery.conf. i would suggest you paste here what your recovery.conf contains and
whatthe objective is 



Best,



--
View this message in context: http://postgresql.nabble.com/WAL-replay-asking-for-very-old-WAL-tp5891050p5891074.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

this message ->
--
2016-03-07 16:04:56 UTC LOG: started streaming WAL from primary at
581D/84000000 on timeline 1
--

that the server has connected to the master via streaming replication so it
should get the new xlogs without restarting. you can check that this is
happening if you check the pg_last_xlog_receive_location() and
pg_last_xlog_replay_location().

if the only problem is that the database is not online in read-only mode,
then maybe the only thing missing is to enable the 'hot_standby = on' in the
postgresql.conf






--
View this message in context: http://postgresql.nabble.com/WAL-replay-asking-for-very-old-WAL-tp5891050p5891083.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.

I guess the only issue I have existed between the keyboard and chair. Thanks for your time and patients.

Stephen Kuntz | Systems Administrator
Pelmorex Media Inc.
T: 905.829.1159 x1376

-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of MirrorX
Sent: Monday, March 7, 2016 12:01 PM
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] WAL replay asking for very old WAL

this message ->
--
2016-03-07 16:04:56 UTC LOG: started streaming WAL from primary at
581D/84000000 on timeline 1
--

that the server has connected to the master via streaming replication so it should get the new xlogs without
restarting.you can check that this is happening if you check the pg_last_xlog_receive_location() and
pg_last_xlog_replay_location().

if the only problem is that the database is not online in read-only mode, then maybe the only thing missing is to
enablethe 'hot_standby = on' in the postgresql.conf 






--
View this message in context: http://postgresql.nabble.com/WAL-replay-asking-for-very-old-WAL-tp5891050p5891083.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin