Skip to content

Instantly share code, notes, and snippets.

@mmrwoods
Created January 20, 2012 13:47
Show Gist options
  • Save mmrwoods/1647442 to your computer and use it in GitHub Desktop.
Save mmrwoods/1647442 to your computer and use it in GitHub Desktop.
Postgres maintenance crontab file
# dump all databases once every 24 hours
45 4 * * * root nice -n 19 su - postgres -c "pg_dumpall --clean" | gzip -9 > /var/local/backup/postgres/postgres_all.sql.gz
# vacuum all databases every night (full vacuum on Sunday night, lazy vacuum every other night)
45 3 * * 0 root nice -n 19 su - postgres -c "vacuumdb --all --full --analyze"
45 3 * * 1-6 root nice -n 19 su - postgres -c "vacuumdb --all --analyze --quiet"
# re-index all databases once a week
0 3 * * 0 root nice -n 19 su - postgres -c 'psql -t -c "select datname from pg_database order by datname;" | xargs -n 1 -I"{}" -- psql -U postgres {} -c "reindex database {};"'
@bish0polis
Copy link

Ohai. Look for a .pgpass reference if you're okay encapsulating a password in the clear into a file. there's got to be a better way, so look more for something better .

@patsevanton
Copy link

HI mmrwoods
Why are you run reindex (3.00 a.m. sunday) before vacuum full (3.45 a.m. sunday) ?
I think need run reindex after vacuum full.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment