From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Josef Machytka <josef.machytka@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: error: database is not accepting commands to avoid wraparound data loss in database ....
Date: Wed, 20 Mar 2019 15:59:44 +0100
Message-ID: <a3f8e93f958d137bf95f2f1367f046e28bb2dd45.camel@cybertec.at> (raw)
In-Reply-To: <CAGvVEFtZ1L9Lv=UTngA2uke71X78S+q95vPMU-v9ioUo6ygC_w@mail.gmail.com>
References: <CAGvVEFtZ1L9Lv=UTngA2uke71X78S+q95vPMU-v9ioUo6ygC_w@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: laurenz.albe@cybertec.at, josef.machytka@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: error: database is not accepting commands to avoid wraparound data loss in database ....
In-Reply-To: <a3f8e93f958d137bf95f2f1367f046e28bb2dd45.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox