Re: Set new system identifier using pg_resetxlog - Mailing list pgsql-hackers

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@postgresql.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