How to tell if PGSQL 8.4 is in standby mode

From: "TSG" <support(at)telium(dot)ca>
To: <pgsql-admin(at)lists(dot)postgresql(dot)org>
Subject: How to tell if PGSQL 8.4 is in standby mode
Date: 2018-04-16 02:20:11
Message-ID: 002701d3d529$76a8dd70$63fa9850$@telium.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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(at)node2 pgsql]# pg_controldata data

pg_control version number: 843

Catalog version number: 200904091

Database system identifier: 6514583873281163231

Database cluster state: shut down

pg_control last modified: Sun 15 Apr 2018 05:39:25 PM EDT

Latest checkpoint location: 7/56000020

Prior checkpoint location: 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 of latest checkpoint: Sun 15 Apr 2018 05:39:24 PM EDT

Minimum recovery ending location: 0/0

Maximum data alignment: 8

Database block size: 8192

Blocks per segment of large relation: 131072

WAL block size: 8192

Bytes per WAL segment: 16777216

Maximum length of identifiers: 64

Maximum columns in an index: 32

Maximum size of a TOAST chunk: 1996

Date/time type storage: 64-bit integers

Float4 argument passing: by value

Float8 argument passing: by value

But that contradicts the status command output:

[root(at)node2 pgsql]# sudo -u postgres pg_ctl status -D /var/lib/pgsql/data

pg_ctl: server is running (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(at)node2 pgsql]# sudo -u postgres pg_ctl start -D /var/lib/pgsql/data

pg_ctl: another server might be running; trying to start server anyway

[2018-04-15 23:06:11 GMT] FATAL: lock file "postmaster.pid" already exists

[2018-04-15 23:06:11 GMT] HINT: Is another postmaster (PID 35852) running
in data directory "/var/lib/pgsql/data"?

pg_ctl: could not start server

Examine the log output.

And I can see that my recovery.conf is still in place:

[root(at)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?

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message David G. Johnston 2018-04-16 03:19:20 Re: pgadmin4 - centos7 - "The application server could not be contacted"
Previous Message Ron 2018-04-16 01:16:04 Re: Password encryption