1

Attempting to vacuum as postgresql database, but it stops after vacuuming about 50 records, with this command/warning:

 backend> vacuum FULL;
 WARNING:  database "postgres" must be vacuumed within 988308 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "postgres".

backend>

// again

WARNING:  database "postgres" must be vacuumed within 988242 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "postgres".

How can this be invoked so that it doesn't stop so often? Ty :^)

kmiklas
  • 13,085
  • 22
  • 67
  • 103
  • 1
    You might be able to run `VACUUM FREEZE` instead of using `full`, possibly just a regular `VACUUM` might also work. Also see: https://www.postgresql.org/docs/11/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND another thing to do is to setup auto vacuum so it handles vacuuming on a schedule of some sort. – jmunsch Jun 29 '19 at 01:00
  • I'm also having this problem of the vacuum command for every 50 records, I'm running on standalone mondo by posgresql 8.1, did you manage to solve this? – Isaque Neves Jun 04 '22 at 13:44

1 Answers1

3

VACUUM (FULL) is quite different from VACUUM, and is not what is needed in this situation. VACUUM (FULL) is not simply a better VACUUM.

You have somehow maneuvered yourself into a bad spot. If you don't succeed in running a normal VACUUM on the postgres database within a. million transactions, your database will suffer data loss.

You have to connect to the postgres database and then run

VACUUM

There will probably be obstacles that you have to remove first. This could be

  • a long running transaction (gone when you start in single user mode)

  • an orphaned prepared transactions

  • an orphaned replication slot

See my blog for more.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • I'm also having this problem of the vacuum command for every 50 records, I'm running on standalone mode posgresql 8.1, did you manage to solve this? – Isaque Neves Jun 04 '22 at 13:44
  • ERROR: database is not accepting commands to avoid wraparound data loss in database "mydb" HINT: Stop the postmaster and use a standalone backend to VACUUM in "mydb" I'm getting this error if I try to connect to the database: – Isaque Neves Jun 04 '22 at 13:46
  • @IsaqueNeves Then go ahead and do that. – Laurenz Albe Jun 04 '22 at 20:18