Re: Set new system identifier using pg_resetxlog

On 06/18/2014 09:17 PM, Josh Berkus wrote:
> On 06/18/2014 11:03 AM, Andres Freund wrote:
>> Well, all those actually do write to the xlog (to write a new
>> checkpoint, containing the updated control file). Since pg_resetxlog has
>> done all this pretty much since forever renaming it now seems to be a
>> big hassle for users for pretty much no benefit? This isn't a tool the
>> average user should ever touch.
>
> If we're using it to create a unique system ID which can be used to
> orchestrate replication and clustering systems, a lot more people are
> going to be touching it than ever did before -- and not just for BDR.

I think pg_resetxlog is still appropriate: changing the system ID will
reset the WAL. In particular, any archived WAL will become useless.

But yeah, this does change the target audience of pg_resetxlog
significantly. Now that we'll have admins running pg_resetxlog as part
of normal operations, we have to document it carefully. We also have to
design the user interface carefully, to make it more clear that while
resetting the system ID won't eat your data, some of the other settings
will.

The proposed "pg_resetxlog --help" output looks like this:

> pg_resetxlog resets the PostgreSQL transaction log.
>
> Usage:
> pg_resetxlog [OPTION]... DATADIR
>
> Options:
> -e XIDEPOCH set next transaction ID epoch
> -f force update to be done
> -l XLOGFILE force minimum WAL starting location for new transaction log
> -m MXID,MXID set next and oldest multitransaction ID
> -n no update, just show what would be done (for testing)
> -o OID set next OID
> -O OFFSET set next multitransaction offset
> -s [SYSID] set system identifier (or generate one)
> -V, --version output version information, then exit
> -x XID set next transaction ID
> -?, --help show this help, then exit
>
> Report bugs to <pgsql-bugs(at)postgresql(dot)org>.

I don't think that's good enough. The -s option should be displayed more
prominently, and the dangerous options like -l and -x should be more
clearly labeled as such.

I would de-emphasize setting the system ID to a particular value. It
might be useful for disaster recovery, like -x, but in general you
should always reset it to a new unique value. If you make it too easy to
set it to a particular value, someone will try initialize a streaming
standby server using initdb+pg_dump, and changing the system ID to match
the master's.

The user manual for pg_resetxlog says:

> pg_resetxlog clears the write-ahead log (WAL) and optionally resets
> some other control information stored in the pg_control file. This
> function is sometimes needed if these files have become corrupted. It
> should be used only as a last resort, when the server will not start
> due to such corruption.

That's clearly not true for the -s option.

In summary, I think we want this feature in some form, but we'll somehow
need to be make the distinction to the dangerous pg_resetxlog usage. It
might be best, after all, to make this a separate utility,
pg_resetsystemid. 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).

- Heikki