Обсуждение: How to tell if PGSQL 8.4 is in standby mode
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?
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.
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?
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