I have run out of space, because I am new to Postgres and I have just not been monitoring it properly.

Now I cannot even vacuum the tables:

ERROR:  could not extend file "base/20851/11787": No space left on device

I have deleted a whole bunch of data. I realize that only vacuum full will bring back space though.

I have another logical volume with lots of space on it. Can I create some kind of a tablespace over there? If so, is there a specific kind of space to tell the database to do it's "work" over there during the vacuum process? Or do I need to physically move tables to that tablespace? How do I do that?

I apologize for the rapid fire questions. I have found myself in a bit of a spot.

This is PostgreSQL 9.3.

  • Depends on just how full it is. You need space to write WAL records in order to create a tablespace. There are some nasty catch-22 situations around out-of-disk-space issues. Commented Sep 15, 2014 at 1:18

1 Answer 1


Here's what You can do to move your data to the new tablespace :

  1. Create the new tablespace pointing to the new location.

    postgres=# create TABLESPACE movespace LOCATION '/home/postgres_tbsp';
  2. Alter table or database to move to the new tablespace.

    postgres=# ALTER DATABASE slonmaster SET tablespace movespace;

This process could last long as it moves all the data in the table or database to a new location.

  • 3
    ... which will probably run out of disk space due to the need to write WAL for the table move. Commented Sep 15, 2014 at 1:18
  • I freed up about 3GB by moving older WAL files. That at least let me set the temp_tablespace over to the new tablespace. Now I'm "vacuum full"-ing smaller to successively larger tables to give me the space I need. Commented Sep 15, 2014 at 1:30
  • From the behavior I'm seeing, it looks like "vacuum full" rebuilds the file on disk in a tmp directory, then rewrites the whole table to a new file and deletes the old file. Commented Sep 15, 2014 at 1:31
  • 1
    @AKWF , yes, the vacuum full rebuild the table You're vacuuming, so it needs free space as big as the table you're vacuuming Commented Sep 15, 2014 at 8:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.