I am trying to run a vacuum full analyze on a postgres 8.4 machine where it appears that autovacuum has not been able to keep up. The database is very large and under heavy load.

The problem started with postgres denying updates because it is out of transactions. My only option was to take our business offline and do a manual vacuum full.

The issue is that it is taking a really long time and I have been looking for a dependable way to speed it up. I don't want to arbitrarily make changes to the configuration files while the database is in an unknown state.

Here is the postgres.conf configuration for the server.

listen_addresses = '*'
port = 5476
max_connections = 1000

### logging
log_destination = 'syslog'

constraint_exclusion = partition

### work_mem for vacuuming
maintenance_work_mem = 128MB        # min 1MB
                                    # DEFAULT 16MB

# -----------------------------
# -----------------------------

### RAM * 1/4
shared_buffers = 512MB              # min 128kB or max_connections*16kB
                                  # DEFAULT: 24MB
#### very import. all work happens here, needs to be as big as possible

temp_buffers = 64MB
#### session-local buffers used to access temporary tables on a per-session basis

### not more than RAM / connections
work_mem = 20MB                   # min 64kB
                                  # DEFAULT: 1MB
#### used for sorting in memory

### raise this if log complains or to reduce checkpoints
wal_buffers = 3MB                 # min 32kB
                                  # DEFAULT: 64kB
checkpoint_segments = 16          # in logfile segments, min 1, 16MB each
                                  # DEFAULT: 3
#### stores changes to shared buffer. higher value = fewer checkpoints/flushes

### RAM * 2/3
effective_cache_size = 1365MB
                                  # DEFAULT: 128MB
#### size of largest possible object

### multiplied defaults by 0.2
cpu_tuple_cost = 0.002                  # (same)
cpu_index_tuple_cost = 0.001            # (same)
cpu_operator_cost = 0.0005              # (same)
#### do this. based on older slower hardware.

### milliseconds vacuum will delay when its cost reaches threshold
vacuum_cost_delay = 20            # default = 0

# -----------------------------
# -----------------------------

### uncomment the following 6 lines to enable query logging
#silent_mode = on
#log_min_duration_statement = 0
#log_duration = on
#log_statement = 'none'

Any ideas on what I can do to get this server back up asap would be greatly appreciated.


Here is the exact error message we are receiving, sorry about that.

ERROR:  database is not accepting commands to avoid wraparound data loss in database "live"
  • 1
    Leave out the FULL option. Also share the error message: is it database is not accepting commands to avoid wraparound data loss... or something else? Aug 7, 2015 at 13:31
  • 3
    See wiki.postgresql.org/wiki/VACUUM_FULL for 8.4 it lists the reasons why VACUUM FULL is not what you need now. Aug 7, 2015 at 13:33
  • 1
    You may look at VACUUM FULL as an ill-chosen name for a command that should be called COMPACT. VACUUM and COMPACT are different operations and you need the 1st one, not the 2nd. Aug 7, 2015 at 14:08
  • 1
    Any reason you are running such an outdated (and unmaintained) version? There were tons of improvements to (auto)vacuum in the current release.
    – user1822
    Aug 7, 2015 at 14:17
  • 1
    Great question, I inherited this about 2 months ago. That was my exact question. It is a beast that is 5 databases all using slony replication. We are in talks with a consulting company to help us migrate to a newer version and use native replication. Aug 7, 2015 at 14:21

1 Answer 1


This particular error comes with a hint mentioned in the doc, in Routine vacuuming (8.4):

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

It's a critical situation which normally should not occur if autovacuum can do its job. When you're back online, you should try to find out why this could have happened (wrong vacuum settings?).

In the meantime, what must be done is simply what the HINT tells: VACUUM without additional parameter. As mentioned in the comments , VACUUM FULL is a different command, even though it's not obvious by its name. It rewrites entirely the tables, is much slower and not desirable in this situation.

More details at https://wiki.postgresql.org/wiki/VACUUM_FULL

  • Thanks again Daniel, emergency averted. We are looking into migrating to 9.x postgres very soon Aug 11, 2015 at 23:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.