Open your crontab in edit mode:

1
# crontab -e

And append the following lines to your crontag configuration.

1
2
3
4
5
6
7
8
9
# Georg@2017-06-20
# Cronjobs zum Reorganisieren der Postgres Datenbanken

# vacuum all databases every night (full vacuum on Sunday night, lazy vacuum every other night)
0 3 * * 0 nice -n 19 su - postgres -c "vacuumdb --all --full --analyze"
0 3 * * 1-6 nice -n 19 su - postgres -c "vacuumdb --all --analyze --quiet"

# re-index all databases once a week
0 4 * * 0 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 {};"'

You’re Done!