Message-ID: <1523858878.2304.5.camel@cybertec.at>
Subject: Re: How to tell if PGSQL 8.4 is in standby mode
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: TSG <support@telium.ca>, pgsql-admin@lists.postgresql.org
In-Reply-To: <002701d3d529$76a8dd70$63fa9850$@telium.ca>
References: <002701d3d529$76a8dd70$63fa9850$@telium.ca>
Organization: Cybertec
Content-Type: text/plain; charset="UTF-8"
Date: Mon, 16 Apr 2018 08:07:58 +0200
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit
Precedence: bulk

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.

I probably don't have to tell you that you shouldn't be using 8.4.

> First of all, pg_controldata says the database is "shut down":

Don't know offhand if that is significant or not ...

> But that contradicts the status command output:
> 
> [root@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@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.

... but that convinces me that the server is running.

> 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?

You might want to look into the PostgreSQL log file if there are messages
that tell you what is going on.

Also, you can use "ps" to see if there is an instance of "pg_standby" running.
If yes, what WAL file is it working on? Is that WAL file present?
You can use "strace" to attach to the "pg_standby" process and see what it
is doing.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com