0

In postgresql version 9.2.1, the database didn't accept any commands to avoid wraparouond dataloss.The following error occured in the pg_log,

ERROR: database is not accepting commands to avoid wraparound data loss in database "XXX" 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 executed vacuum full for the database XXX in standalone backend mode.After that i tried to restart the pgsql, now pgsql server is rejecting connnections. while executing the pg_isready command, the host is rejecting connections.

Is there anything i have to do after completing the vacuum process? what are the possible reasons for the postgres server is failed to start ? Thanks in advance.

Eddie
  • 91
  • 2
  • 10
  • Did you vacuum full every database? You might have to do the template0 and 1 dbs as well as all the others. – Scott Marlowe Oct 23 '17 at 17:22
  • 1
    Also you should really upgrade to the latest 9.2. You're missing years and years of bug fixes not to mention 9.2 is no longer supported. – Scott Marlowe Oct 23 '17 at 17:23
  • Why i have to vacuum every database ? The error itself mentioned the specific database where the wraparound issue is occuring @ScottMarlowe. – Eddie Oct 24 '17 at 05:22
  • Thanks for the upgrade advice. I will have it as my last card. @ScottMarlowe – Eddie Oct 24 '17 at 05:25
  • Once you have fixed the wraparound problem, you should **at least** upgrade to the latest 9.2 version which is 9.2.23 –  Oct 24 '17 at 06:42
  • Ya sure. Thanks @a_horse_with_no_name. – Eddie Oct 24 '17 at 06:44

1 Answers1

0

In single user mode, run

SELECT datname, datfrozenxid FROM pg_database;

to see which databases need to be vacuumed (those with the smallest values).

Run VACUUM (FREEZE) in these databases, not VACUUM (FULL).

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • There is no **datminmxid** attribute in pg_database for the version 9.2.1. And i have to vacuum (freeze) the one with smallest **datfrozenxid**, right ? – Eddie Oct 24 '17 at 06:12
  • Right. And upgrading to a later PostgreSQL won't help. You must get that `VACUUM (FREEZE)` done *now*; upgrade later. – Laurenz Albe Oct 24 '17 at 06:39
  • Thanks. do vacuum freeze or full cause any problems to the data in the database ? Data remains intact, right ? – Eddie Oct 24 '17 at 06:42
  • `VACUUM` won't destroy data. You need `VACUUM (FREEZE)` against transaction ID wraparound; `VACUUM (FULL)` won't help. You data will only be destroyed if you *don't* run `VACUUM (FREEZE)`. – Laurenz Albe Oct 24 '17 at 06:46
  • In case, if the vacuum freeze process also get failed. Is there any way other than upgrading the postgresql version ? – Eddie Oct 24 '17 at 07:37
  • Upgrading won't help. You have to run `VACUUM (FREEZE)`. There is no alternative. – Laurenz Albe Oct 24 '17 at 07:55
  • While opening in single user mode, it just froze with message, **Warning: Database with OID XXXX must be vacuumed within 1000000 transactions. Hint:To avoid a database shutdown, execute a database-wide vacuum in the database. You might also need to commit or rollback old prepared transaction.** I couldn't proceed the vacuum freeze step. – Eddie Oct 24 '17 at 15:01
  • PostgreSQL doesn't "just freeze". Did you use `--single` as first argument when starting? Did you try to enter `VACUUM`? If you can't get it to work, get professional support. You're running the risk of losing your data. – Laurenz Albe Oct 24 '17 at 15:22
  • i used (**postgres.exe --single -D "./../data" **) command to enter into the single user mode from the location **pgsql/bin** – Eddie Oct 25 '17 at 06:01
  • It's working. Actually, it was my mistake. After entering the single user mode command, it took sometime to enter into single user mode. Now my **vacuum freeze** process is running. Thank you for your responses. – Eddie Oct 25 '17 at 11:10
  • I'll keep my fingers crossed that everything works well for you! – Laurenz Albe Oct 25 '17 at 14:49