Message-ID: <a3f8e93f958d137bf95f2f1367f046e28bb2dd45.camel@cybertec.at>
Subject: Re: error: database is not accepting commands to avoid wraparound
 data loss in database ....
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Josef Machytka <josef.machytka@gmail.com>,
	pgsql-admin@lists.postgresql.org
Date: Wed, 20 Mar 2019 15:59:44 +0100
In-Reply-To: 
 <CAGvVEFtZ1L9Lv=UTngA2uke71X78S+q95vPMU-v9ioUo6ygC_w@mail.gmail.com>
References: 
	<CAGvVEFtZ1L9Lv=UTngA2uke71X78S+q95vPMU-v9ioUo6ygC_w@mail.gmail.com>
Organization: Cybertec
Content-Type: text/plain; charset="UTF-8"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Precedence: bulk

On Wed, 2019-03-20 at 12:01 +0100, Josef Machytka wrote:
> I checked solution for wraparound error and since all sources I found
> suggested to do VACUUM under single-user mode I decided to test it on
> second smaller data warehouse database (~16TB of data) which is not so
> crucial for us to have it still running since I knew it will be inaccessible
> during that time. Vacuum is running for 24 hours already on this database
> and I can only wait for it to finish....
> 
> Can I do something else with main data warehouse database than just this
> painfully slow VACUUM? If I would change some settings for freeze would
> it help in way that database would accept commands again so I would be
> able to drop subscription and vacuum it in normal way?

One this happen, all you can do it wait for the manual VACUUM to finish.
Don't interrupt it.
The alternative (losing your data to corruption) is more painful than
this outage.

If you perform database backups with pg_dump, one faster solution with little
data loss would be to restore such a backup.  The resulting database will
not be bloated.

To prevent this from happening, monitor database bloat and tune autovacuum
to be more aggressive if necessary.

If your load is mostly UPDATEs, see ig you can make use of the HOT update
feature.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com