Re: Accessing pg_controldata information from SQL - Mailing list pgsql-general

From Bruce Momjian
Subject Re: Accessing pg_controldata information from SQL
Date
Msg-id 200906050133.n551XgA09699@momjian.us
Whole thread Raw
In response to Re: Accessing pg_controldata information from SQL  ("Massa, Harald Armin" <chef@ghum.de>)
List pgsql-general
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. +

pgsql-general by date:

Previous
From: Niklas Johansson
Date:
Subject: Re: limit table to one row
Next
From: S Arvind
Date:
Subject: max execution time of query