Обсуждение: [ADMIN] Database Error
Using PostgreSQL v9.3 on RedHat platform. Last week the VM that the database resides on ran out of space...since that time after bringing the service back on-linegetting intermittent connection issues. Today I'm receiving the following. ERROR: database is not accepting commands to avoid wraparound data loss in database "----" HINT: Stop the postmaster and use a standalone backend to vacuum that database. You might also need to commit or roll back old prepared transactions. Any ideas? Thank you in advance, Denise Ferrell -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
On Wed, Oct 25, 2017 at 7:30 AM, Ferrell, Denise D CTR NSWCDD, H11 <denise.ferrell.ctr@navy.mil> wrote:
Using PostgreSQL v9.3 on RedHat platform.
Last week the VM that the database resides on ran out of space...since that time after bringing the service back on-line getting intermittent connection issues.
Today I'm receiving the following.
ERROR: database is not accepting commands to avoid wraparound data loss in database "----"
HINT: Stop the postmaster and use a standalone backend to vacuum that database.
You might also need to commit or roll back old prepared transactions.
Sounds like you'll need to restart the DB in single-user mode and run a VACUUM FREEZE on the whole thing.
Here's a good read on a similar incident: https://blog.sentry.io/2015/07/23/transaction-id-wraparound-in-postgres.html
Don Seiler
www.seiler.us
www.seiler.us
Don't reply off-list, you'll get better help if you keep the list involved.
On Wed, Oct 25, 2017 at 10:46 AM, Ferrell, Denise D CTR NSWCDD, H11 <denise.ferrell.ctr@navy.mil> wrote:
Can you provide info on restarting in single user mode?
https://www.postgresql.org/docs/9.3/static/app-postgres.html, look for the --single parameter. Not sure if there's a way to do it with pg_ctl, I don't see one.
I would assume the following in psql will work - vacuum -all -U <username>
Just run as postgres (superuser) in psql: vacuum freeze analyze verbose;
If you size your maintenance_work_mem appropriately, it should run well. How big is your database?
Don Seiler
www.seiler.us
www.seiler.us
I've ran the vacuum freeze which corrected some issues but now I'm getting the following WARNING: "terminating connection because of crash of another server process "relation "<table_name>" page 1601779 is uninitialized --- fixing" Is there something else that is needed besides the VACUUM FREEZE? Thank you in advance, Denise Ferrell -----Original Message----- From: Ferrell, Denise D CTR NSWCDD, H11 Sent: Wednesday, October 25, 2017 2:00 PM To: 'Don Seiler' Subject: RE: [Non-DoD Source] Re: [ADMIN] Database Error Got it! Is there a way to extent the max number for these? d -----Original Message----- From: Don Seiler [mailto:don@seiler.us] Sent: Wednesday, October 25, 2017 12:32 PM To: Ferrell, Denise D CTR NSWCDD, H11 Subject: Re: [Non-DoD Source] Re: [ADMIN] Database Error Make sure you are using the FREEZE option. That is what resets the transaction IDs (which is the problem you're seeing). On Wed, Oct 25, 2017 at 11:30 AM, Ferrell, Denise D CTR NSWCDD, H11 <denise.ferrell.ctr@navy.mil> wrote: Thank you again for the information. I'm currently in and running a full vacuum.Denise-----Original Message-----From: pgsql-admin-owner@postgresql.org<mailto:pgsql-admin-owner@postgresql.org> [mailto:pgsql-admin-owner@postgresql.org <mailto:pgsql-admin-owner@postgresql.org>] On Behalf Of Don SeilerSent: Wednesday, October 25, 2017 10:42 AMTo: Ferrell,Denise D CTR NSWCDD, H11Cc: pgsql-adminSubject: [Non-DoD Source] Re: [ADMIN] Database ErrorOn Wed, Oct 25, 2017 at7:30 AM, Ferrell, Denise D CTR NSWCDD, H11 <denise.ferrell.ctr@navy.mil> wrote: Using PostgreSQL v9.3 on RedHatplatform. Last week the VM that the database resides on ran out of space...since that time after bringing theservice back on-line getting intermittent connection issues. Today I'm receiving the following. ERROR: database is not accepting commands to avoid wraparound data loss in database "----" HINT: Stop the postmaster anduse a standalone backend to vacuum that database. You might also need to commit or roll back old prepared transactions.Soundslike you'll need to restart the DB in single-user mode and run a VACUUM FREEZE on the whole thing.Here'sa good read on a similar incident: https://blog.sentry.io/2015/07/23/transaction-id-wraparound-in-postgres.html <https://blog.sentry.io/2015/07/23/transaction-id-wraparound-in-postgres.html>--Don Seilerwww.seiler.us -- Don Seiler www.seiler.us
-----Original Message----- From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Ferrell, Denise D CTR NSWCDD,H11 Sent: Friday, October 27, 2017 9:35 AM To: pgsql-admin Subject: Re: [Non-DoD Source] Re: [ADMIN] Database Error Importance: High I've ran the vacuum freeze which corrected some issues but now I'm getting the following WARNING: "terminating connection because of crash of another server process "relation "<table_name>" page 1601779 is uninitialized --- fixing" Is there something else that is needed besides the VACUUM FREEZE? Thank you in advance, Denise Ferrell -----Original Message----- From: Ferrell, Denise D CTR NSWCDD, H11 Sent: Wednesday, October 25, 2017 2:00 PM To: 'Don Seiler' Subject: RE: [Non-DoD Source] Re: [ADMIN] Database Error Got it! Is there a way to extent the max number for these? d -----Original Message----- From: Don Seiler [mailto:don@seiler.us] Sent: Wednesday, October 25, 2017 12:32 PM To: Ferrell, Denise D CTR NSWCDD, H11 Subject: Re: [Non-DoD Source] Re: [ADMIN] Database Error Make sure you are using the FREEZE option. That is what resets the transaction IDs (which is the problem you're seeing). On Wed, Oct 25, 2017 at 11:30 AM, Ferrell, Denise D CTR NSWCDD, H11 <denise.ferrell.ctr@navy.mil> wrote: Thank you again for the information. I'm currently in and running a full vacuum.Denise-----Original Message-----From: pgsql-admin-owner@postgresql.org<mailto:pgsql-admin-owner@postgresql.org> [mailto:pgsql-admin-owner@postgresql.org <mailto:pgsql-admin-owner@postgresql.org>] On Behalf Of Don SeilerSent: Wednesday, October 25, 2017 10:42 AMTo: Ferrell,Denise D CTR NSWCDD, H11Cc: pgsql-adminSubject: [Non-DoD Source] Re: [ADMIN] Database ErrorOn Wed, Oct 25, 2017 at7:30 AM, Ferrell, Denise D CTR NSWCDD, H11 <denise.ferrell.ctr@navy.mil> wrote: Using PostgreSQL v9.3 on RedHatplatform. Last week the VM that the database resides on ran out of space...since that time after bringing theservice back on-line getting intermittent connection issues. Today I'm receiving the following. ERROR: database is not accepting commands to avoid wraparound data loss in database "----" HINT: Stop the postmaster anduse a standalone backend to vacuum that database. You might also need to commit or roll back old prepared transactions.Soundslike you'll need to restart the DB in single-user mode and run a VACUUM FREEZE on the whole thing.Here'sa good read on a similar incident: https://blog.sentry.io/2015/07/23/transaction-id-wraparound-in-postgres.html <https://blog.sentry.io/2015/07/23/transaction-id-wraparound-in-postgres.html>--Don Seilerwww.seiler.us -- Don Seiler www.seiler.us
That's the thing, I'm already running v9.3.9. We are planning to upgrade to the latest in March so hopefully I can
keepit stable until then.
Thanks for the information.
Denise
-----Original Message-----
From: Bhanu Murthy [mailto:bhanu_murthy@yahoo.com]
Sent: Monday, October 30, 2017 12:28 PM
To: Don Seiler
Cc: Ferrell, Denise D CTR NSWCDD, H11; pgsql-admin
Subject: [Non-DoD Source] Re: [ADMIN] Database Error
Perform vacuumdb and see if the error goes away.
More permanent solution is to patch upgrade to 9.3.9 where TXN wrap around issue has been resolved!
Sent from my iPhone
On Oct 25, 2017, at 10:42 AM, Don Seiler <don@seiler.us> wrote:
On Wed, Oct 25, 2017 at 7:30 AM, Ferrell, Denise D CTR NSWCDD, H11 <denise.ferrell.ctr@navy.mil> wrote:
Using PostgreSQL v9.3 on RedHat platform. Last week the VM that the database resides on ran out of
space...sincethat time after bringing the service back on-line getting intermittent connection issues. Today I'm
receivingthe following. ERROR: database is not accepting commands to avoid wraparound data loss in database
"----" HINT: Stop the postmaster and use a standalone backend to vacuum that database. You might also need to
commitor roll back old prepared transactions.
Sounds like you'll need to restart the DB in single-user mode and run a VACUUM FREEZE on the whole thing.
Here's a good read on a similar incident: https://blog.sentry.io/2015/07/23/transaction-id-wraparound-in-postgres.html
-- Don Seilerwww.seiler.us