Thread: Accessing pg_controldata information from SQL

Accessing pg_controldata information from SQL

From

"Massa, Harald Armin"

Date:

08 May 2009, 09:14:01

Hello,

is there any way to acess the pg_controldata information via SQL?

(running pg_controldata via shell needs file access to the postgresql data dictionary, which is usually not given)

Harald

--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
no fx, no carrier pigeon
-
LASIK good, steroids bad?

Re: Accessing pg_controldata information from SQL

From

Bruce Momjian

Date:

02 June 2009, 14:19:17

Massa, Harald Armin wrote:
> Hello,
>
> is there any way to acess the pg_controldata information via SQL?
>
> (running pg_controldata via shell needs file access to the postgresql data
> dictionary, which is usually not given)

Some of the settings have read-only variables that appear in SHOW ALL or
the pg_settings tables, or SELECT version().  Is there something
specific you are looking for that doesn't appear there?

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

Re: Accessing pg_controldata information from SQL

From

"Massa, Harald Armin"

Date:

03 June 2009, 10:16:00

Hello Bruce,

I am specifically looking for the "Datenbanksystemidentifikation" , that would be "database system identification" - the rather unique ID of a database installation. Third line in pg_controldata output:

pg_control-Versionsnummer:                833
Katalogversionsnummer:                    200711281
----------------------------------------------------------------------------------------------
Datenbanksystemidentifikation:            5293702538224708457
----------------------------------------------------------------------------------------------
Datenbank-Cluster-Status:                 im Produktionsmodus
pg_control zuletzt geändert:              02.06.2009 10:54:27

Best wishes,

Harald

On Tue, Jun 2, 2009 at 4:19 PM, Bruce Momjian <bruce@momjian.us> wrote:

Massa, Harald Armin wrote:
> Hello,
>
> is there any way to acess the pg_controldata information via SQL?
>
> (running pg_controldata via shell needs file access to the postgresql data
> dictionary, which is usually not given)

Some of the settings have read-only variables that appear in SHOW ALL or
the pg_settings tables, or SELECT version(). Is there something
specific you are looking for that doesn't appear there?

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
no fx, no carrier pigeon
-
LASIK good, steroids bad?

Re: Accessing pg_controldata information from SQL

From

Bruce Momjian

Date:

04 June 2009, 04:01:11

Massa, Harald Armin wrote:
> Hello Bruce,
>
> I am specifically looking for the "Datenbanksystemidentifikation" , that
> would be "database system identification" - the rather unique ID of a
> database installation. Third line in pg_controldata output:
>
>
> pg_control-Versionsnummer:                833
> Katalogversionsnummer:                    200711281
> ----------------------------------------------------------------------------------------------
> Datenbanksystemidentifikation:            5293702538224708457
> ----------------------------------------------------------------------------------------------
> Datenbank-Cluster-Status:                 im Produktionsmodus
> pg_control zuletzt ge?ndert:              02.06.2009 10:54:27
>

Ah, good point.  I don't see any SQL API for access to that value.  The
value is used to make sure the xlogs match the server, and pg_resetxlog
will change that identifier.  The value is generated from the
time-of-day that xlog is initialized at bootstrap time:

    gettimeofday(&tv, NULL);
    sysidentifier = ((uint64) tv.tv_sec) << 32;
    sysidentifier |= (uint32) (tv.tv_sec | tv.tv_usec);

I am afraid the setting isn't as unique as you probably want.

Why do you want the value and can we provide a better value for you?

---------------------------------------------------------------------------


>
> Best wishes,
>
> Harald
>
>
> On Tue, Jun 2, 2009 at 4:19 PM, Bruce Momjian <bruce@momjian.us> wrote:
>
> > Massa, Harald Armin wrote:
> > > Hello,
> > >
> > > is there any way to acess the pg_controldata information via SQL?
> > >
> > > (running pg_controldata via shell needs file access to the postgresql
> > data
> > > dictionary, which is usually not given)
> >
> > Some of the settings have read-only variables that appear in SHOW ALL or
> > the pg_settings tables, or SELECT version().  Is there something
> > specific you are looking for that doesn't appear there?
> >
> > --
> >  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
> >  EnterpriseDB                             http://enterprisedb.com
> >
> >  + If your life is a hard drive, Christ can be your backup. +
> >
>
>
>
> --
> GHUM Harald Massa
> persuadere et programmare
> Harald Armin Massa
> Spielberger Stra?e 49
> 70435 Stuttgart
> 0173/9409607
> no fx, no carrier pigeon
> -
> LASIK good, steroids bad?

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

Re: Accessing pg_controldata information from SQL

From

"Massa, Harald Armin"

Date:

04 June 2009, 06:59:30

Bruce,

> would be "database system identification" - the rather unique ID of a

> database installation. Third line in pg_controldata output:
> ----------------------------------------------------------------------------------------------
> Datenbanksystemidentifikation: 5293702538224708457
> ----------------------------------------------------------------------------------------------
Ah, good point. I don't see any SQL API for access to that value. The
value is used to make sure the xlogs match the server, and pg_resetxlog
will change that identifier. The value is generated from the
time-of-day that xlog is initialized at bootstrap time:

gettimeofday(&tv, NULL);
sysidentifier = ((uint64) tv.tv_sec) << 32;
sysidentifier |= (uint32) (tv.tv_sec | tv.tv_usec);

I am afraid the setting isn't as unique as you probably want.

Why do you want the value and can we provide a better value for you?

I have a central PostgreSQL installation. In addiotion PostgreSQL is installed on laptops. Those laptops are SOMETIMES connected to the central database.
The databases on those laptops are (to some extend) replications of the central PostgreSQL database.

Now I have to keep track of information like "Laptop xxx allready got the changes xxx"; esp. with DML-changes which get replicated.
Challenge is to identify the entity "Laptop"... actually I have one table named "dbidentity", which gets populated with a GUID, and use this to mark the databases. Which has some potential for misstakes... so I am looking for something "hard-coded" into PostgreSQL to really identify two different databases (with rather the same content) via a single marker.

So probably this identifier also gets resetted on pg_resetlxlog, and is not really what I would need :( (on the other hand: that system is running since 5 years on different machines an pg_resetxlog was not used one time :) )

Thanks for caring,

Harald

--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
no fx, no carrier pigeon
-
LASIK good, steroids bad?

Re: Accessing pg_controldata information from SQL

From

Bruce Momjian

Date:

04 June 2009, 15:05:23

Massa, Harald Armin wrote:
> Bruce,
>
> > would be "database system identification" - the rather unique ID of a
>
> > > database installation. Third line in pg_controldata output:
> > >
> > ----------------------------------------------------------------------------------------------
> > > Datenbanksystemidentifikation:            5293702538224708457
> > >
> > ----------------------------------------------------------------------------------------------
> > Ah, good point.  I don't see any SQL API for access to that value.  The
> > value is used to make sure the xlogs match the server, and pg_resetxlog
> > will change that identifier.  The value is generated from the
> > time-of-day that xlog is initialized at bootstrap time:
> >
> >    gettimeofday(&tv, NULL);
> >    sysidentifier = ((uint64) tv.tv_sec) << 32;
> >    sysidentifier |= (uint32) (tv.tv_sec | tv.tv_usec);
> >
> > I am afraid the setting isn't as unique as you probably want.
> >
> > Why do you want the value and can we provide a better value for you?
> >
> > I have a central PostgreSQL installation. In addiotion PostgreSQL is
> installed on laptops. Those laptops are SOMETIMES connected to the central
> database.
> The databases on those laptops are (to some extend) replications of the
> central PostgreSQL database.
>
> Now I have to keep track of information like "Laptop xxx allready got the
> changes xxx"; esp. with DML-changes which get replicated.
> Challenge is to identify the entity "Laptop"... actually I have one table
> named "dbidentity", which gets populated with a GUID, and use this to mark
> the databases. Which has some potential for misstakes... so I am looking for
> something "hard-coded" into PostgreSQL to really identify two different
> databases (with rather the same content) via a single marker.
>
> So probably this identifier also gets resetted on pg_resetlxlog, and is not
> really what I would need :( (on the other hand: that system is running since
> 5 years on different machines an pg_resetxlog was not used one time :) )

I am thinking your best solution is to create a table with a uuid column
and reference that to sync up your data.  That would also allow data
dumps to be restored to another machine with the proper identifier
because the identifier is really a characteristic of the data, not of
the xlog or cluster install state.

The problem with that is the you could restore to another machine and
then two machines would have the same uuid values.  I wonder if you
should be generating a new uuid after every sync to prevent that
problem.  That would fix cases where someone restored their data and
tried to sync up again and got duplicate data.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

Re: Accessing pg_controldata information from SQL

From

"Massa, Harald Armin"

Date:

04 June 2009, 15:15:22

Bruce,

I am thinking your best solution is to create a table with a uuid column
and reference that to sync up your data. That would also allow data
dumps to be restored to another machine with the proper identifier
because the identifier is really a characteristic of the data, not of
the xlog or cluster install state.

The problem with that is the you could restore to another machine and
then two machines would have the same uuid values. I wonder if you
should be generating a new uuid after every sync to prevent that
problem. That would fix cases where someone restored their data and
tried to sync up again and got duplicate data.

Exactly that is my current solution and the "... two machines would have" is the current problem.

Changing the UUID does not fit into the current structure, where I log within the central db "Database UUID 123123 patched up with modification number xxx"; and only select the not-yet-done modifications.

That "two machines with same UUID values" was the reason I hoped for that identifier ...

Thanks again for caring, now I know that my solution was quite okay.

My next idea is something like "select md5(relevant_structure_elements_of_database) and to create the DML by checking the differences...

best wishes,

HArald

--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
no fx, no carrier pigeon
-
LASIK good, steroids bad?

Re: Accessing pg_controldata information from SQL

From

Bruce Momjian

Date:

05 June 2009, 01:33:53

Massa, Harald Armin wrote:
> Bruce,
>
> I am thinking your best solution is to create a table with a uuid column
> > and reference that to sync up your data.  That would also allow data
> > dumps to be restored to another machine with the proper identifier
> > because the identifier is really a characteristic of the data, not of
> > the xlog or cluster install state.
> >
> > The problem with that is the you could restore to another machine and
> > then two machines would have the same uuid values.  I wonder if you
> > should be generating a new uuid after every sync to prevent that
> > problem.  That would fix cases where someone restored their data and
> > tried to sync up again and got duplicate data.
> >
>
> Exactly that is my current solution and the "... two machines would have" is
> the current problem.
>
> Changing the UUID does not fit into the current structure, where I log
> within the central db "Database UUID 123123 patched up with modification
> number xxx"; and only select the not-yet-done modifications.
>
> That "two machines with same UUID values" was the reason I hoped for that
> identifier ...
>
> Thanks again for caring, now I know that my solution was quite okay.
>
> My next idea is something like "select
> md5(relevant_structure_elements_of_database) and to create the DML by
> checking the differences...

I suggest you check the file modification date of PG_VERSION using:

    test=> select pg_stat_file('PG_VERSION');
                                       pg_stat_file
    -----------------------------------------------------------------------------------

     (4,"2009-06-04 21:24:18-04","2009-06-04 21:24:04-04","2009-06-04 21:24:04-04",,f)
    (1 row)

The middle value is the file modification date/time.  There is no reason
anyone would modify that file after initdb, so it would give you an
unchanging value to compare against.  A major upgrade would modify that
file modification date/time, as would a sloppy file system restore.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +