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

I Highlight Some point in your given Database are as bellow

Your database cluster is in shutdown state. Please see in Red.
It looks like there is a breakage in archive files replaying.(Prior
checkpoint location:7/55000020).
Need to verify the connectivity between Master & Replica looks like
archives are not getting applied and DB is in shutdown state.
Please verify Primary/Standby logs to dig more into it.

<http://www.shreeyansh.com>

On Mon, Apr 16, 2018 at 7:50 AM, TSG <support(at)telium(dot)ca> 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(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?
>
>
>