Re: pg_resetwal --next-transaction-id may cause database failed to restart.

On 2020-Jun-22, movead(dot)li(at)highgo(dot)ca wrote:

> hello hackers,
>
> When I try to use pg_resetwal tool to skip some transaction ID, I get a problem that is
> the tool can accept all transaction id I offered with '-x' option, however, the database
> may failed to restart because of can not read file under $PGDATA/pg_xact. For
> example, the 'NextXID' in a database is 1000, if you offer '-x 32769' then the database
> failed to restart.

Yeah, the normal workaround is to create the necessary file manually in
order to let the system start after such an operation; they are
sometimes necessary to enable testing weird cases with wraparound and
such. So a total rejection to work for these cases would be unhelpful
precisely for the scenario that those switches were intended to serve.

Maybe a better answer is to have a new switch in postmaster that creates
any needed files (incl. producing associated WAL etc); so you'd run
pg_resetwal -x some-value
postmaster --create-special-stuff
then start your server and off you go.

Now maybe this is too much complication for a mechanism that really
isn't for general consumption anyway. I mean, if you're using
pg_resetwal, you're already playing with fire.

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services