Обсуждение: [GENERAL] database is not accepting commands

We have 1.5 TB database that's shown an error and block all commands.
The error is :
"ERROR:  database is not accepting commands to avoid wraparound data loss in
database "dbname"
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."

I tried to do vacuum in the backend mode. Also I tried to set
zero_damaged_pages = on then do the vacuum again but same error appeared.
The error appeared after two hours of vacuuming where verbose shows passing
tables.

The cause of error could be :
1- a lot of insertion queries for not existing table
or
2- truncating data with 40 GB in a week,
we used to do that but this is the first time we had this error.
also, autovacuume is turned on.

Please , I need help or any suggestion?






--
View this message in context: http://www.postgresql-archive.org/database-is-not-accepting-commands-tp5961831.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 05/16/2017 01:28 AM, reem wrote:
> We have 1.5 TB database that's shown an error and block all commands.
> The error is :
> "ERROR:  database is not accepting commands to avoid wraparound data loss in
> database "dbname"
> 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."
>
> I tried to do vacuum in the backend mode. Also I tried to set
> zero_damaged_pages = on then do the vacuum again but same error appeared.
> The error appeared after two hours of vacuuming where verbose shows passing
> tables.

Postgres version?

So was all the above done in the standalone backend?

>
> The cause of error could be :
> 1- a lot of insertion queries for not existing table
> or
> 2- truncating data with 40 GB in a week,
> we used to do that but this is the first time we had this error.
> also, autovacuume is turned on.
>
> Please , I need help or any suggestion?
>
>
>
>
>
>
> --
> View this message in context: http://www.postgresql-archive.org/database-is-not-accepting-commands-tp5961831.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

The version is 9.3 in ubuntu.

yes i did it in standalone mode by using this command :
//usr/lib/postgresql/9.3/bin/postgres --single -D
/var/lib/postgresql/9.3/main -c
config_file=/etc/postgresql/9.3/main/postgresql.conf dbname/



--
View this message in context:
http://www.postgresql-archive.org/database-is-not-accepting-commands-tp5961831p5962028.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

postgres 9.3 in ubuntu OS.

Yes I did this :
1- service postgresql stop
2- /usr/lib/postgresql/9.3/bin/postgres --single -D
/var/lib/postgresql/9.3/main -c
config_file=/etc/postgresql/9.3/main/postgresql.conf dbname
3-vacuum verbose
4- vacumming processed and it shows tables being vacuumed but then suddenly
stopped with the same error above.

Please note we didn't try to VACUUM FULL because it takes a lot of the
resources .






--
View this message in context:
http://www.postgresql-archive.org/database-is-not-accepting-commands-tp5961831p5962030.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 05/16/2017 10:44 PM, reem wrote:
> postgres 9.3 in ubuntu OS.
>
> Yes I did this :
> 1- service postgresql stop
> 2- /usr/lib/postgresql/9.3/bin/postgres --single -D
> /var/lib/postgresql/9.3/main -c
> config_file=/etc/postgresql/9.3/main/postgresql.conf dbname
> 3-vacuum verbose
> 4- vacumming processed and it shows tables being vacuumed but then suddenly
> stopped with the same error above.
>
> Please note we didn't try to VACUUM FULL because it takes a lot of the
> resources .

Have you looked at the other part of the original error message:

"You might also need to commit or roll back old prepared transactions."

Might want to take a look at what is in pg_prepared_xacts:

https://www.postgresql.org/docs/9.3/static/view-pg-prepared-xacts.html



--
Adrian Klaver
adrian.klaver@aklaver.com

On 05/16/2017 10:44 PM, reem wrote:
> postgres 9.3 in ubuntu OS.
>

Meant to add to previous post.

What is the full version number of Postgres e.g. 9.3.x?


--
Adrian Klaver
adrian.klaver@aklaver.com

It works finally.!

I did vacuum FULL for some tables and Then it just works .!

Maybe as you said this is related to the second part which is commit old
prepared transaction .!




--
View this message in context:
http://www.postgresql-archive.org/database-is-not-accepting-commands-tp5961831p5962744.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Thank you so much because of your reply i did it a gain but for some tables,
and it works now .!





--
View this message in context:
http://www.postgresql-archive.org/database-is-not-accepting-commands-tp5961831p5962745.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.