This article addresses dealing with storage_full specifically as it pertains to logging in AWS RDS with a postgres instance. There are other issues that may lead to the storage_full state so please be aware this only addresses when logs are the culprit.
Dealing with Storage Full State (STORAGE_FULL)
Once in the storage_full state, you cannot do basic operations such as restarting the instance or connecting to the instance. To remedy this, you can either:
- add more storage space to the instance
- increase the allocated storage. (You can increase the storage to allow access to the instance to perform additional troubleshooting)
Database is full in provisioned storage after a few hours
It seems like at least a few people have dealt with this issue with the database rapidly filling up in a short amount of time.
Where are my logs stored?
Logs by default are stored in /rds/dbdata/log/error. This means all logging is stored in this folder and is not exclusive to errors. This property can be found in parameter groups with the field “log_directory” as the name.
What’s taking up my storage?
When using something like pandas to_sql(), the default log_statement (all) property will print everything. If you have a large automated INSERT, this will then log every insert statement. While text might initially not sound like it would take up a lot of space, 200 million INSERTs could end up taking about 40GB, as shown below. In this case, the logs ended up taking up around 400GB of data, locking the postgres instance.
2019-03-21 21:59:59 UTC:10.206.116.237(37226):pgdb@postgres:[112563]:LOG: statement: INSERT INTO table_name (id, date, app_id, name, address) VALUES (123456, '2019-03-21', 12452, 'Harrison', '123 Green Lane')
Clearing up Space
In the parameter groups, log_statement can be changed such that it doesn’t overwhelm your logs with unnecessary INSERT statements. In this case, I changed it to ddl instead of all, which only prints CREATE, ALTER and DROP statements. Make sure this is what you want, as you do lose a lot of useful logging with this switch as highlighted by the article below.
Verify which value in your log_statement to change your log_statement to. From Postgresql’s website:
Controls which SQL statements are logged. Valid values are none (off), ddl, mod, and all (all statements). ddl logs all data definition statements, such as CREATE, ALTER, and DROP statements. mod logs all ddl statements, plus data-modifying statements such as INSERT, UPDATE, DELETE, TRUNCATE, and COPY FROM. PREPARE, EXECUTE, and EXPLAIN ANALYZE statements are also logged if their contained command is of an appropriate type.
Reference Links
- https://www.postgresql.org/docs/9.5/runtime-config-logging.html
- https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithParamGroups.html
- https://aws.amazon.com/premiumsupport/knowledge-center/rds-out-of-storage/
- https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_LogAccess.Concepts.PostgreSQL.html
