PostgreSQL administration

See Routine PostgreSQL Maintenance Tasks.

Backups

One method of backing up the databases is covered in Backing up postgreSQL databases.

Vacuum

An important maintenance task is periodic vacuuming of the database. This cleans a PostgreSQL database. The easiest way to do this by turning on the auto-vacuum daemon which is disabled by default. To enable, ensure the following configuration entries are set in /var/lib/pgsql/data/postgresql.conf

 stats_start_collector = on
 stats_row_level = on
 autovacuum = on

Manual vacuuming

If you don't want to run autovacuum, then it's probably a good idea to occasionally do it yourself.

vacuumdb -a -z -f

All database, Full vacuum and analyZe (Calculate statistics for use by the optimizer). vacuumdb will also generate internal statistics used by the PostgreSQL query optimizer.

Configuration

Have a look at the following parameters. These are some of the ones that you may want to tweak to improve performance or to speed up VACUUMING operations.

work_mem = 2048                 # min 64, size in KB
# Quantity of RAM PostgreSQL uses for VACUUM, ANALYZE,
# CREATE INDEX, and adding foriegn keys.
maintenance_work_mem = 51200            # min 1024, size in KB

# - Free Space Map -
# NOTICE message suggested increasing these parameters after a
# system wide VACUUM ANALYSE
max_fsm_pages = 300000                  # min max_fsm_relations*16, 6 bytes each
max_fsm_relations = 2000                # min 100, ~70 bytes each

# Defines the on-disk cache size of the transaction log
# for write operations.
checkpoint_segments = 10                # in logfile segments, min 1, 16MB each

# - Planner Cost Constants -
# Tells the query planner the largest possible database
# object that could be expected to be cached. Generally should be set
# to about 2/3 of RAM, on a dedicated server.
effective_cache_size = 100000           # typically 8KB each

# For auto-vacuuming
stats_row_level = on

# AUTOVACUUM PARAMETERS
autovacuum = on                         # enable autovacuum subprocess?

max_connections = 100