Commands for PostgreSQL Vacuuming Activity

[gaurav_bhandari]

Hi Team,

I need to do vacuuming of PostgreSQL server.

I have gone through the link :-

https://community.apigee.com/questions/14952/postgres-disk-usage-is-85-consumed-please-let-know.html

https://www.postgresql.org/docs/9.3/static/app-vacuumdb.html

My requirement is to vacuum both the facts table and the aggregate tables. Below is the command that I have created but wanted confirmation if it is correct.

/opt/apigee4/share/pgsql/bin/vacuumdb -v -z -a -e -U $userid -d $database > /opt/apigee4/var/log/postgresql/vacuum-$(date).log;

Can someone please suggest if it is correct? Also, can someone tell me the $database value for facts table and aggregate table.

Also, this is the first time that we will do vacuuming, hence first I will use -f parameter for full vacuuming and then create a CRON JOB which will run once in a week and do half vacuuming.

/opt/apigee4/share/pgsql/bin/vacuumdb -v -z -a -f -e -U $userid -d $database > /opt/apigee4/var/log/postgresql/vacuum-$(date).log;

Please suggest your approaches and any other things which I am doing wrong.

Thanks and Regards,

Gaurav Bhandari

[gaurav_bhandari]

Hi Team,

Please respond

[gaurav_bhandari]

Hi team,

Please respond

[#4]

Here is an example of setting up vacuum and routine maintenance on the postgres db. Adjust the hours and times according to your needs.

# run purge on example org, prod environment at 11:00pm, Fridays, keeping only the last 30 days of data
0 23 * * 5 /opt/apigee4/bin/pg_data_purge.sh example prod 30
# run purge on example org, test environment at 11:30pm, Fridays, keeping only he last 30 days of data
30 23 * * 5 /opt/apigee4/bin/pg_data_purge.sh example test 30
# clean the database apigee at midnight fridays
0 0 * * 5 /opt/apigee4/share/pgsql/bin/vacuumdb apigee -U apigee
# run a full vacuum, on the first of the month at 1am
0 1 1 * * /opt/apigee4/share/pgsql/bin/vacuumdb apigee -U apigee —full
# run a backup at midnight on Sundays
0 0 * * 0 /opt/apigee4/bin/backup.sh -i /opt

Please note that if a full vacuum takes place, that table/database is locked during the time of the operation which means the tables will be offline until the activity is complete. If your goal is to free up disk space, use 'VACUUM FULL' on the database.

In our cloud we have enabled autovacuum. This can be done via postgresql.conf changes. We suggest the following settings if you enable autovacuum:

autovacuum = on
autovacuum_freeze_max_age = 500000000
autovacuum_max_workers = 4
autovacuum_naptime = '2m'
autovacuum_vacuum_cost_delay = 0
maintenance_work_mem = '1GB'
vacuum_freeze_min_age = 10000000

For these changes to take affect, we will need to restart the database server.

Now if you are planning on purging analytics data, you might want to implement a check first to see if autovacuum is running so that the purge does not take place at the same time.

A check for something like this can be done with the following query to see when the table was last vacuumed:
SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze FROM pg_stat_user_tables;