PG 8.3, Disk Full, server fails to start

Question

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 1

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.

Answer 2

Drop the logfiles located in /pg_log to clean up something and get started.

VACUUM won't reclaim diskspace, you need VACUUM FULL. You could also drop some indexes or other stuff you don't need now, just to reclaim space.

==edit==

Copy pg_ident.conf to another disk or delete all comments to make just a little bit of space. You could cleanup postgresql.conf as well, after making a copy.

After that, start PostgreSQL in single user mode (using --single) and connect to the database you want to clean up. After cleaning up, stop PostgreSQL and restart in normal mode.