Обсуждение: How to tell if PGSQL 8.4 is in standby mode

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

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

On 04/16/2018 03:18 AM, Mark Kirkwood wrote:
> 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.
[snip]
>
> 8.4? Seriously, there are so many reasons to be using a later version!

If the customer says "don't upgrade because we don't want to disturb a 
running system", then... you don't upgrade.  It's as simple as that.


-- 
Angular momentum makes the world go 'round.

On 17/04/18 00:53, Ron wrote:
> On 04/16/2018 03:18 AM, Mark Kirkwood wrote:
>>
> [snip]
>>
>> 8.4? Seriously, there are so many reasons to be using a later version!
>
> If the customer says "don't upgrade because we don't want to disturb a 
> running system", then... you don't upgrade.  It's as simple as that.
>
>

Well, left to themselves - all customers will say that. It is up to us 
IT guys to explain to them the benefits of keeping current and the risks 
of not doing so. E,g in this case:
- really hard to tell if standby is up to date
- ...and will actually come up if needed
- hard to get help in the advent of problems (not many people are 
current with 8,.4 replication for instance)

regards
Mark