Thread: Tables(s) that feed pg_controldata
In v9.2 (yes, I know it's EOL; there's nothing I can do about it), what tables do I query to get these values, and can I also get them from the streamed replication host? Database cluster state Latest checkpoint location Time of latest checkpoint [postgres@fpslbxhaprl05 ~]$ pg_controldata pg_control version number: 922 Catalog version number: 201204301 Database system identifier: 6114483497489611387 Database cluster state: in production pg_control last modified: Sat 08 Dec 2018 11:08:56 AM EST Latest checkpoint location: C50/87A8F300 Prior checkpoint location: C50/85506608 Latest checkpoint's REDO location: C50/85B86620 Latest checkpoint's TimeLineID: 1 Latest checkpoint's full_page_writes: on Latest checkpoint's NextXID: 0/1965571123 Latest checkpoint's NextOID: 158912675 Latest checkpoint's NextMultiXactId: 18962475 Latest checkpoint's NextMultiOffset: 38526531 Latest checkpoint's oldestXID: 1769771528 Latest checkpoint's oldestXID's DB: 16384 Latest checkpoint's oldestActiveXID: 1965571123 Time of latest checkpoint: Sat 08 Dec 2018 10:56:56 AM EST Minimum recovery ending location: 0/0 Backup start location: 0/0 Backup end location: 0/0 End-of-backup record required: no Current wal_level setting: hot_standby Current max_connections setting: 250 Current max_prepared_xacts setting: 0 Current max_locks_per_xact setting: 320 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 -- Angular momentum makes the world go 'round.
On 12/09/2018 01:25 AM, Ron wrote:
>
> In v9.2 (yes, I know it's EOL; there's nothing I can do about it), what tables
> do I query to get these values, and can I also get them from the streamed
> replication host?
>
> - Database cluster state
> - Latest checkpoint location
> - Time of latest checkpoint
The pg_control file is written directly by PostgreSQL, it does not derive from
any tables.
From 9.6 there are a bunch of functions which will report values contained in
pg_controldata:
https://www.postgresql.org/docs/current/functions-info.html#FUNCTIONS-CONTROLDATA
but you're out of luck for 9.2. The only option to query the desired values via
SQL would be to write an extension which reads pg_controldata
(possibly as a backport of the above-mentioned functions).
Regards
Ian Barwick
--
Ian Barwick http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
On 12/09/2018 07:51 PM, Ian Barwick wrote: > On 12/09/2018 01:25 AM, Ron wrote: > > > > In v9.2 (yes, I know it's EOL; there's nothing I can do about it), what > tables > > do I query to get these values, and can I also get them from the streamed > > replication host? > > > > - Database cluster state > > - Latest checkpoint location > > - Time of latest checkpoint > > The pg_control file is written directly by PostgreSQL, it does not derive > from > any tables. > > From 9.6 there are a bunch of functions which will report values contained in > pg_controldata: > > https://www.postgresql.org/docs/current/functions-info.html#FUNCTIONS-CONTROLDATA > > but you're out of luck for 9.2. The only option to query the desired > values via > SQL would be to write an extension which reads pg_controldata > (possibly as a backport of the above-mentioned functions). Thanks. grep(1) and cut(1) make an adequate work-around. -- Angular momentum makes the world go 'round.
On Sun, Dec 09, 2018 at 08:10:57PM -0600, Ron wrote: > On 12/09/2018 07:51 PM, Ian Barwick wrote: >> but you're out of luck for 9.2. The only option to query the desired >> values via >> SQL would be to write an extension which reads pg_controldata >> (possibly as a backport of the above-mentioned functions). > > Thanks. grep(1) and cut(1) make an adequate work-around. Perhaps. Even with that, writing an extension for 9.2 would require copy-pasting a lot of code from pg_controldata.c and taking as well the code which has been introduced for the various control functions. Dirty, still doable. Adding the control functions has also refactored the code so as getting control file data is easy for backend code (see get_controlfile in controldata_utils.c). (9.2 is EOL'd for one year now, you may want to upgrade.) -- Michael
Attachment
On 12/09/2018 08:50 PM, Michael Paquier wrote: [snip] > (9.2 is EOL'd for one year now, you may want to upgrade.) You assume that the DBA controls the data; he doesn't. It's the customer's data, and they control the OS, RDBMS and application versions. We just migrated the big databases off ancient physical servers, RHEL5 and Pg 8.4 only because the PCI auditors wouldn't pass it without the upgrade. There are still some Windows 2003 / SQL 2005 servers running production. That's the Pg 8.0 era... -- Angular momentum makes the world go 'round.
On 12/10/2018 11:50 AM, Michael Paquier wrote: > On Sun, Dec 09, 2018 at 08:10:57PM -0600, Ron wrote: >> On 12/09/2018 07:51 PM, Ian Barwick wrote: >>> but you're out of luck for 9.2. The only option to query the desired >>> values via >>> SQL would be to write an extension which reads pg_controldata >>> (possibly as a backport of the above-mentioned functions). >> >> Thanks. grep(1) and cut(1) make an adequate work-around. > > Perhaps. Even with that, writing an extension for 9.2 would require > copy-pasting a lot of code from pg_controldata.c and taking as well the > code which has been introduced for the various control functions. > Dirty, still doable. I've got some code which does just that to read pg_control as far back as 9.3, and it is indeed not pretty ;). Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Ian Barwick <ian.barwick@2ndquadrant.com> writes:
> On 12/10/2018 11:50 AM, Michael Paquier wrote:
>> Perhaps. Even with that, writing an extension for 9.2 would require
>> copy-pasting a lot of code from pg_controldata.c and taking as well the
>> code which has been introduced for the various control functions.
>> Dirty, still doable.
> I've got some code which does just that to read pg_control as far
> back as 9.3, and it is indeed not pretty ;).
If I had to do this pre-9.6, I'd write a function in an untrusted PL
that invoked pg_controldata and parsed its output. Ugly, but short
and easy to port across versions.
regards, tom lane