postgres

# 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
# or dump only ONE database every 24 hours
45 4 * * * root nice -n 19 su - postgres -c "pg_dump searchprice --clean" | gzip -9 > /var/local/backup/postgres/searchprice_db.sql.gz
# dump only ONE table every 24 hours
45 4 * * * root nice -n 19 su - postgres -c "pg_dump -t track_item_click searchprice --clean" | gzip -9 > /var/local/backup/postgres/track_item_click.sql.gz

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

# or vacuum only ONE database every night (full vacuum on Sunday night, lazy vacuum every other night)
45 3 * * 1-6 root nice -n 19 su - postgres -c "vacuumdb searchprice --analyze --quiet"
45 3 * * 0 root nice -n 19 su - postgres -c "vacuumdb searchprice --full --analyze"


# 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 {};"'

# or re-index one databases once a week
0 3 * * 0 root nice -n 19 su - postgres -c 'psql -U postgres  -d searchprice -c "reindex database searchprice;"'
#or re-index some tables once a week
0 3 * * 0 root nice -n 19 su - postgres -c 'psql -U postgres  -d searchprice -c "reindex table property;reindex table item;"'