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. – Craig Ringer Sep 15 '14 at 1:18

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.

| improve this answer | |
  • 3
    ... which will probably run out of disk space due to the need to write WAL for the table move. – Craig Ringer Sep 15 '14 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. – AKWF Sep 15 '14 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. – AKWF Sep 15 '14 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 – Soni Harriz Sep 15 '14 at 8:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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