How to tell if PGSQL 8.4 is in standby mode

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

How to tell if PGSQL 8.4 is in standby mode

Telium Technical Support

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

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@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.

 

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?

 

Reply | Threaded
Open this post in threaded view
|

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

Shreeyansh dba
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.




On Mon, Apr 16, 2018 at 7:50 AM, TSG <[hidden email]> 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

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@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.

 

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?

 


Reply | Threaded
Open this post in threaded view
|

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

Laurenz Albe
In reply to this post by Telium Technical Support
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

Reply | Threaded
Open this post in threaded view
|

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

Mark Kirkwood-2
In reply to this post by Telium Technical Support
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

Reply | Threaded
Open this post in threaded view
|

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

Ron-2
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.

Reply | Threaded
Open this post in threaded view
|

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

Mark Kirkwood-2


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


Previous Thread Next Thread