Re: Renaming of pg_xlog and pg_clog - Mailing list pgsql-hackers

On Thu, Oct 20, 2016 at 12:12 PM, Stephen Frost <sfrost@snowman.net> wrote:
> That said, I'd also like to see a --force or similar option or mechanism
> put in place to reduce the risk of users trashing their system because
> they think pg_resetwal is "safe." ("It's just gonna reset things to make
> the database start again, should be fine.").

You know we already have that, right?

> pg_destroydb almost seems like a better choice, though I suppose
> 'pg_clearwal' would be more acceptable.  Doesn't have quite the same
> impact though.
>
> Not sure on the best answer here, but it's definitely foot-gun that some
> users have ended up using on themselves with depressing regularity.

Just to provide some perspective from the other side of this, I
handled a severity-1 escalation a few weeks ago where a user basically
called up and explained their situation and I told them based on all
of the facts and circumstances presented that running pg_resetxlog was
going to be the best way forward and they said that was pretty much
what they were expecting to hear, and did so.  I think there's far too
much anti-pg_resetxlog bias on this list.  The situation where you
need to use it is not common in general, but it's pretty common in
support cases that reach me.  I don't think I'd be exaggerating if I
said that 25% of the customer escalations I handle personally require
the use pg_resetxlog.  Of course, that's because by the time the
ticket gets to me, it's typically the case that all of the easy, neat
solutions have already been ruled out.

My experience is that users who are faced with this situation
typically understand that they are in a bad situation and when I
explain to them --- in a clear and direct way but without the sort of
hysterical exaggeration sometimes seen on this mailing list --- what
the consequences are, they are not surprised by those consequences and
they are willing to accept them because they know that the
alternatives are worse.  For example, consider a customer with a
mostly read-only 20TB database.  If that user runs pg_resetxlog, they
can be back up in 5 minutes and it is likely (though of course not
certain) that corruption will be minimal.  If they restore from
backup, they will be down for many more hours.  After pg_resetxlog,
they can restore from backup on another system or do the
dump-and-restore which I invariably recommend while they are up.  For
many customers, this is a good trade-off.  The decision, because of
the risks associated with it, is usually passed up from the DBA I'm
working with to some business leader.  If that business leader feels
that benefits of being up immediately rather than many hours later are
sufficient to justify the risk of a possibly-corrupted database,
running pg_resetxlog is a perfectly reasonable decision.

I have not yet had one DBA or business leader call back and say "hey,
that thing where we ran pg_resetxlog?  you should have discouraged us
more, because that was a disaster".  Now maybe those disasters have
happened and I am just not aware of them, but I think we should all
take a deep breath and remind ourselves that the database exists in
service to the business and not the other way around.  My job -- when
doing support -- is to tell you what your options are and what
consequences they may have, good and bad -- not to tell you how to run
your company.  Telling users that pg_resetxlog will "destroy your
database" is over-the-top hyperbole.  It's a sharp tool with risks and
benefits and it deserves to be presented exactly that way.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company