0

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

# -----------------------------
# CHANGE THESE DEPENDING ON SYSTEM
# VALUES BASED ON 2G RAM
# -----------------------------

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

# -----------------------------
# OPTIONAL DEBUG/LOGGING PARAMS
# -----------------------------

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

Edit

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? – Daniel Vérité Aug 7 '15 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. – Daniel Vérité Aug 7 '15 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. – Daniel Vérité Aug 7 '15 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. – a_horse_with_no_name Aug 7 '15 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. – Chris Hinshaw Aug 7 '15 at 14:21
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 – Chris Hinshaw Aug 11 '15 at 23:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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