Re: Set new system identifier using pg_resetxlog

On 08/25/2014 10:45 PM, Tom Lane wrote:
> Heikki Linnakangas <hlinnakangas(at)vmware(dot)com> writes:
>> It would not need to have the capability to set the
>> system ID to a particular value, only a randomly assigned one (setting
>> it to a particular value could be added to pg_resetxlog, where other
>> dangerous options are).
>
> I'm less convinced about that. While you can shoot yourself in the foot
> by assigning the same system ID to two installations that share WAL
> archive or something like that, this feels a bit different than the ways
> you can shoot yourself in the foot with pg_resetxlog. If we do what you
> say here then I think we'll be right back to the discussion of how to
> separate the assign-a-sysID option from pg_resetxlog's other, more
> dangerous options.

I don't see the use case for setting system id to a particular value.
Andres listed four use cases upthread:

> a) Mark a database as not being the same. Currently if you promote two
> databases, e.g. to shard out, they'll continue to have same system
> identifier. Which really sucks, especially because timeline ids will
> often increase synchronously.

Yes, this is the legitimate use case a DBA would use this feature for.
Resetting the system ID to a random value suffices.

> b) For data recovery it's sometimes useful to create a new database
> (with the same catalog state) and replay all WAL. For that you need to
> reset the system identifier. I've done so hacking up resetxlog
> before.

This falls squarely in the "dangerous" category, and you'll have to
reset other things than the system ID to make it work. Having the option
in pg_resetxlog is fine for this.

> c) We already allow to set pretty much all aspects of the control file
> via resetxlog - there seems little point of not having the ability to
> change the system identifier.

Ok, but it's not something a regular admin would ever use.

> d) In a logical replication scenario one way to identify individual
> nodes is via the system identifier. If you want to convert a
> basebackup into logical standby one sensible way to do so is to
> create a logical replication slots *before* promoting a physical
> backup to guarantee that slot is able to stream out all changes. If
> the slot names contain the consumer's system identifier you need to
> know the new system identifier beforehand.

I didn't understand this one. But it seems like the obvious solution is
to not use the consumer's system identifier as the slot name. Or rename
it afterwards.

- Heikki