PG 8.3, Disk Full, server fails to start

postgresql postgresql-8.4
Leonard picture Leonard · Feb 3, 2012 · Viewed 13k times · Source

This question concerns Postgresql 8.3. I cannot yet create tags so Ichose version 8.4 as tag.

It's a test server, but I cannot connect to it because after some days of intensive use the disk where Postgres resides, is full. Becaues I the RDBMS cannot start, I cannot do a vacuum.

On rebooting, the system says,

Starting PostgreSQL 8.3 database server: mainThe PostgreSQL server failed to sta
rt. Please check the log output: FATAL: could not write lock file "postmaster.pi
d": No space left on device failed!
 failed!

Linux works alright and has enough free disk space. Postgresql has its own partion mounted on /var/lib/ and has zero free bytes.

I think I should free some space before I can restart the database server, but I don't know which files to delete. The log files are not on the same partition, so it won't help to empty them. Can anybody help me on this?

Answer

Gary picture Gary · Feb 3, 2012

pg_log can be cleared it is for regular logs for admins Don't delete the pg_xlog or pg_clog directories as they are for database internals (treat them as you would the data files in base). Removing them can cause problems restarting even if you have the space.

The process I'd try (after taking a backup) is:

Find a directory under the /var/lib/pgsql tree that is quite big, but not so big that it can't be copied to another filesystem. It doesn't have to be the whole base directory etc, it could be a subdirectory within there.

Copy the contents of that directory (including any subdirectories) to another file system.

Remove the old directory and then create a symbolic link where the old directory was to the new location.

Startup the database and VACUUM FULL (or now you have access, or you can just drop a db if you've got a database you can live without).

Shutdown the database.

Remove the symbolic link, and move the files back onto the var/lib filesystem again.

The fact you can't take a filesystem backup would make me a bit loath to do it in your environment though.

Related questions

Generating a UUID in Postgres for Insert statement?

My question is rather simple. I'm aware of the concept of a UUID and I want to generate one to refer to each 'item' from a 'store' in my DB with. Seems reasonable right? The problem is the following line …

Read More
Using psql to connect to PostgreSQL in SSL mode

I am trying to configure ssl certificate for PostgreSQL server. I have created a certificate file (server.crt) and key (server.key) in data directory and update the parameter SSL to "on" to enable secure connection. I just want only …

Read More
Split comma separated column data into additional columns

I have comma separated data in a column: Column ------- a,b,c,d I want to split the comma separated data into multiple columns to get this output: Column1 Column2 Column3 Column4 ------- ------- ------- ------- a b c …

Read More