Обсуждение: [ADMIN] Database Error

Поиск
Список
Период
Сортировка

[ADMIN] Database Error

От
"Ferrell, Denise D CTR NSWCDD, H11"
Дата:
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

Re: [ADMIN] Database Error

От
Don Seiler
Дата:
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.


--
Don Seiler
www.seiler.us

Re: [Non-DoD Source] Re: [ADMIN] Database Error

От
Don Seiler
Дата:
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

Re: [Non-DoD Source] Re: [ADMIN] Database Error

От
"Ferrell, Denise D CTR NSWCDD, H11"
Дата:
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

Re: [Non-DoD Source] Re: [ADMIN] Database Error

От
"Ferrell, Denise D CTR NSWCDD, H11"
Дата:

-----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

Re: [Non-DoD Source] Re: [ADMIN] Database Error

От
"Ferrell, Denise D CTR NSWCDD, H11"
Дата:
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