Postgres on Engineyard space at 100%, pg_xlog filled up with files

I have a database that is growing rapidly. VERY rapidly. My /db directory is at 100% now, and there are a bunch of new files in pg_xlog.

My background is in Oracle and MySQL. I do not know what is going on with this database. I know that I need to hire a Postgres DBA quickly.

Is there anything in the immediate term that I can do to fix my issue? I notice that deleting data does nothing to mitigate my space issue.

Output from pg_settings is:

```
archive_command /bin/true configuration file
archive_mode on configuration file
archive_timeout 1min configuration file
checkpoint_segments 100 configuration file
checkpoint_timeout 5min configuration file
client_encoding UTF8 session
DateStyle ISO, MDY configuration file
default_statistics_target 100 configuration file
default_text_search_config pg_catalog.english configuration file
effective_cache_size 1358MB configuration file
hot_standby on configuration file
hot_standby_feedback on configuration file
lc_messages C configuration file
lc_monetary C configuration file
lc_numeric C configuration file
lc_time C configuration file
listen_addresses * configuration file
log_destination csvlog configuration file
log_line_prefix %m: proc=%p,user=%u,db=%d,host=%r configuration file
log_min_duration_stat

You can revise the checkpoint_segments setting in your postgresql.conf and issue SELECT pg_reload_conf(); as the postgres (super)user (in any database) to make that change live. What the change to that setting does is reduce the number of WAL segments that remain resident in your pg_xlog directory. If you have a lot of data churn in your db you will want to keep the checkpoint_segments setting at 32 minimum (see http://www.postgresql.org/docs/current/static/runtime-config-wal.html#GUC-CHECKPOINT-SEGMENTS for more details).

Some general tips:

• Ensure your autovacuum settings are pretty aggressive.

• Move the pg_xlog directory to a different volume than the $PGDATA directory. I have measured performance increases from 10% - 30% from that change alone.

• Set up a monitoring tool to keep an eye on table/index/database/server size increases. Eg. Nagios, New Relic,

StackExchange Database Administrators Q#53378, answer score: 7