Accessing pg_controldata information from SQL

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

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?

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

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?

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

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?

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