pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to tell if PGSQL 8.4 is in standby mode
6+ messages / 5 participants
[nested] [flat]
* How to tell if PGSQL 8.4 is in standby mode
@ 2018-04-16 02:20 TSG <support@telium.ca>
2018-04-16 05:03 ` Re: How to tell if PGSQL 8.4 is in standby mode Shreeyansh Dba <shreeyansh2014@gmail.com>
2018-04-16 06:07 ` Re: How to tell if PGSQL 8.4 is in standby mode Laurenz Albe <laurenz.albe@cybertec.at>
2018-04-16 08:18 ` Re: How to tell if PGSQL 8.4 is in standby mode Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
0 siblings, 3 replies; 6+ messages in thread
From: TSG @ 2018-04-16 02:20 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to tell if PGSQL 8.4 is in standby mode
2018-04-16 02:20 How to tell if PGSQL 8.4 is in standby mode TSG <support@telium.ca>
@ 2018-04-16 05:03 ` Shreeyansh Dba <shreeyansh2014@gmail.com>
2 siblings, 0 replies; 6+ messages in thread
From: Shreeyansh Dba @ 2018-04-16 05:03 UTC (permalink / raw)
To: TSG <support@telium.ca>; +Cc: pgsql-admin@lists.postgresql.org
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@telium.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@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?
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to tell if PGSQL 8.4 is in standby mode
2018-04-16 02:20 How to tell if PGSQL 8.4 is in standby mode TSG <support@telium.ca>
@ 2018-04-16 06:07 ` Laurenz Albe <laurenz.albe@cybertec.at>
2 siblings, 0 replies; 6+ messages in thread
From: Laurenz Albe @ 2018-04-16 06:07 UTC (permalink / raw)
To: TSG <support@telium.ca>; pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to tell if PGSQL 8.4 is in standby mode
2018-04-16 02:20 How to tell if PGSQL 8.4 is in standby mode TSG <support@telium.ca>
@ 2018-04-16 08:18 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2018-04-16 12:53 ` Re: How to tell if PGSQL 8.4 is in standby mode Ron <ronljohnsonjr@gmail.com>
2 siblings, 1 reply; 6+ messages in thread
From: Mark Kirkwood @ 2018-04-16 08:18 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to tell if PGSQL 8.4 is in standby mode
2018-04-16 02:20 How to tell if PGSQL 8.4 is in standby mode TSG <support@telium.ca>
2018-04-16 08:18 ` Re: How to tell if PGSQL 8.4 is in standby mode Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
@ 2018-04-16 12:53 ` Ron <ronljohnsonjr@gmail.com>
2018-04-16 21:22 ` Re: How to tell if PGSQL 8.4 is in standby mode Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
0 siblings, 1 reply; 6+ messages in thread
From: Ron @ 2018-04-16 12:53 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to tell if PGSQL 8.4 is in standby mode
2018-04-16 02:20 How to tell if PGSQL 8.4 is in standby mode TSG <support@telium.ca>
2018-04-16 08:18 ` Re: How to tell if PGSQL 8.4 is in standby mode Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2018-04-16 12:53 ` Re: How to tell if PGSQL 8.4 is in standby mode Ron <ronljohnsonjr@gmail.com>
@ 2018-04-16 21:22 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
0 siblings, 0 replies; 6+ messages in thread
From: Mark Kirkwood @ 2018-04-16 21:22 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2018-04-16 21:22 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-04-16 02:20 How to tell if PGSQL 8.4 is in standby mode TSG <support@telium.ca>
2018-04-16 05:03 ` Shreeyansh Dba <shreeyansh2014@gmail.com>
2018-04-16 06:07 ` Laurenz Albe <laurenz.albe@cybertec.at>
2018-04-16 08:18 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2018-04-16 12:53 ` Ron <ronljohnsonjr@gmail.com>
2018-04-16 21:22 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox