Re: UUIDs in core WAS: 9.4 Proposal: Initdb creates a single table

On 04/26/2014 11:18 AM, Tom Lane wrote:
> It's worth noting in this connection that we've never tried hard to ensure
> that "database identifiers" are actually unique. One potentially serious
> issue is that slave servers will have the same identifier as their master.

Yeah, this is one of those things I've been thinking about. The proble
is that we need a "node" ID, which identifies the PostgreSQL instance,
and a "dataset ID", which identifies the chain of data, especially when
combined with the timeline ID. So a master and replica would have
different node IDs, but the same dataset ID, until the replica is
promoted, at which point its dataset ID + timeline No. would change.
This would allow for relatively easy management of large clusters by
allowing automated identification of databases and their mirrors.

However, there's a fundamental problem with the concept of the dataset
ID in that there's absolutely no way for PostgreSQL to know when it has
a unique dataset. Consider a downtime database file cloning for
example; the two databases would have the same identifier and yet both
be standalones which quickly diverge. So I haven't thought of a good
solution to that.

We could implement a NodeID, though, based on some combination of IP/MAC
address and port though. Not entirely reliable, but better than nothing ...

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com