Re: How to tell if PGSQL 8.4 is in standby mode

Hi, (see below)


On 16/04/18 14:20, TSG wrote:
>
> I have a PostgreSQL 8.4 database that I'm am trying to start in 
> standby mode (I am shipping my own logs from the active DB). I 
> /think/ my standby DB is in standby/recovery mode but I'm not sure how 
> to tell.
>
> First of all, pg_controldata says the database is "shut down":
>
> [root@node2 pgsql]# pg_controldata data
>
> pg_control version number: 843
>
> Catalog version number: 200904091
>
> Databasesystem identifier: 6514583873281163231
>
> Databasecluster state:               shut down
>
> pg_control last modified:             Sun 15Apr 201805:39:25PM EDT
>
> Latest checkpointlocation: 7/56000020
>
> Prior checkpointlocation: 7/55000020
>
> Latest checkpoint's REDO location:    7/56000020
>
> Latest checkpoint's TimeLineID: 116
>
> Latest checkpoint's NextXID:          0/40171
>
> Latest checkpoint's NextOID: 20826
>
> Latest checkpoint's NextMultiXactId:  1
>
> Latest checkpoint's NextMultiOffset: 0
>
> Time oflatest checkpoint: Sun 15Apr 201805:39:24PM EDT
>
> Minimum recovery ending location: 0/0
>
> Maximum data alignment: 8
>
> Databaseblock size: 8192
>
> Blocks per segment oflarge relation: 131072
>
> WAL block size: 8192
>
> Bytes per WAL segment: 16777216
>
> Maximum length ofidentifiers: 64
>
> Maximum columns inan index: 32
>
> Maximum size ofa TOAST chunk: 1996
>
> Date/time type storage: 64-bit integers
>
> Float4 argument passing: byvalue
>
> Float8 argument passing: byvalue
>
> But that contradicts the status command output:
>
> [root@node2 pgsql]# sudo -u postgres pg_ctl status -D /var/lib/pgsql/data
>
> pg_ctl: server isrunning (PID: 35852)
>
> /usr/bin/postgres "-D""/var/lib/pgsql/data"
>
> And when I try to start the database it says it's already running:
>
> [root@node2 pgsql]# sudo -u postgres pg_ctl start-D /var/lib/pgsql/data
>
> pg_ctl: another server might be running; trying tostartserver anyway
>
> [2018-04-1523:06:11GMT] FATAL:  lock file"postmaster.pid"already exists
>
> [2018-04-1523:06:11GMT] HINT: Isanother postmaster (PID 35852) running 
> indata directory "/var/lib/pgsql/data"?
>
> pg_ctl: could notstartserver
>
> Examine the log output.
>
> And I can see that my recovery.conf is still in place:
>
> [root@node2 pgsql]# cat data/recovery.conf
>
> restore_command='pg_standby -r 10 -t /var/run/myapp.trigger 
> /var/lib/pgsql/data/myapp-archive "%f" "%p"'
>
> And finally, I see an every growing list of files in the archive 
> directory.
>
> So, is there a way to tell if the wal (archive) files are being 
> applied to the database?
>

8.4? Seriously, there are so many reasons to be using a later version! 
Among them are easier ways to checking if your standby is actually still 
applying wal! Now, dragging back memories from many years ago I note 
that pg_standby writes its own log file, so you should be able to see 
if/when wal files are being applied in there (usually written in the 
data directory not /etc).

But. Come on - use a later version - this replication stuff is much 
better these days

regards
Mark