Re: Set new system identifier using pg_resetxlog

On 2014-06-30 19:57:58 +0900, Fujii Masao wrote:
> On Sun, Jun 29, 2014 at 11:18 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> > On 2014-06-29 19:44:21 +0530, Abhijit Menon-Sen wrote:
> >> At 2014-06-27 00:51:02 +0200, petr(at)2ndquadrant(dot)com wrote:
> >> >
> >> > Also based on Alvaro's comment, I replaced the scanf parsing code with
> >> > strtoul(l) function.
> >>
> >> As far as I can tell (from the thread and a quick look at the patch),
> >> your latest version of the patch addresses all the review comments.
> >>
> >> Should I mark this ready for committer now?
> >
> > Well, we haven't really agreed on a way forward yet. Robert disagrees
> > that the feature is independently useful and thinks it might be
> > dangerous for some users. I don't agree with either of those points, but
> > that doesn't absolve the patch from the objection. I think tentatively
> > have been more people in favor, but it's not clear cut imo.
>
> So what's the usecase of this feature? If it's for "normal operation",
> using pg_resetxlog for that is a bit dangerous because it can corrupt
> the database easily.

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.

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.

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.

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.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services