Re: Accessing pg_controldata information from SQL

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