Re: Accessing pg_controldata information from SQL

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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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