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
