1

I can successfully create a ramdisk for a PostgreSQL database.

This command is successful:

psql --host=localhost --command "CREATE TABLESPACE $db_tblspace LOCATION '$dbPath';"

where dbPath is a directory on the ramdisk.
Here:

createdb $dbName --tablespace=$db_tblspace --host=localhost --no-password --echo

I get this error:

CREATE DATABASE pgdb TABLESPACE pgdb_tblspace;
createdb: database creation failed: ERROR:  could not write to file "pg_tblspc/24597/PG_9.2_201204301/24598/12027": No space left on device

I cannot understand this error. The ramdisk has just been created, it is empty and its size is 50 MB. The newly created database is also empty. Why the message "No space left on device"?

Thank you.

Pietro
  • 12,086
  • 26
  • 100
  • 193

1 Answers1

3

Yikes, please don't do this.

A tablespace is not something that can just go away and have the rest of the database stay happy. You'll lose your entire database if you lose the tablespace. Tablespaces on ramdisks are extremely unsafe.

For data where durability is not required please use UNLOGGED or TEMPORARY tables.

If your entire database is throwaway, instead of creating a tablespace on the ramdisk you should initdb an entire new DB on the ramdisk if you need to. You might find this post useful.

Community
  • 1
  • 1
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • You convinced me, especially considering that only certain tables should go on the ramdisk. If I use UNLOGGED or TEMPORARY tables, can I be sure they will not touch the SSD? I want to prevent using it for temporary data that would be overwritten many times a second. – Pietro May 16 '13 at 17:21
  • 1
    @Pietro So your main concern is SSD wear? If so, you have to think not only about the tables themselves but about the write-ahead logs. `unlogged` or `temporary` tables will mostly avoid WAL, so that helps. If using unlogged/temp tables you'll still get disk writes, but not "many times a second" because nothing ever forces them to flush to disk. The OS will buffer them in RAM and only write them out when memory pressure forces it to, especially if you set `fsync=off` in `postgresql.conf`. All these options let PostgreSQL throw your data away (data loss) when it or the server crashes. – Craig Ringer May 17 '13 at 00:59
  • Craig, am I wrong or the only way I can create a table with my requirements limited to this same table is: `CREATE TEMPORARY TABLE table_name ( ... )`? Everything else seems to be related to the database as a whole, an not table specific. – Pietro May 20 '13 at 10:22
  • 1
    @Pietro Whoops, looks like *you still forgot to mention your PostgreSQL version*. If you're on 9.1 or above you can use `UNLOGGED` tables, which like temp tables aren't WAL logged, but unlike temp tables they're visible across all transactions. – Craig Ringer May 20 '13 at 11:19
  • Craig, I am using version 9.2, so I can use UNLOGGED tables. But my previous question is still there: "Everything else seems to be related to the database as a whole, an not table specific". – Pietro May 20 '13 at 11:28
  • @Pietro Not sure what you mean by "everything else". Do you mean the answer I linked to from this one? If so, yes, most of that is db-wide. You only have quite limited options for making a couple of tables write-unsafe while keeping the rest of the DB safe. Tablespaces on temporary storage are *not* a safe option, and `unlogged` tables are better anyway, since you avoid all the WAL writes that'd normally go into the default tablespace not your temp tablespace with your original approach. – Craig Ringer May 20 '13 at 11:39
  • Craig, reading this [page](http://www.postgresql.org/docs/9.2/static/manage-ag-tablespaces.html), I think tablespaces are what I am looking for: "For example, an index which is very heavily used can be placed on a very fast, highly available disk, such as an expensive solid state device. At the same time a table storing archived data which is rarely used or not performance critical could be stored on a less expensive, slower disk system." - This is just what I need, with the transformation: SSD/slow disk --> ramdisk/SSD. – Pietro May 20 '13 at 14:02
  • 1
    @Pietro Try it ... with a *separate database* you initdb'd for the purpose and that contains no data you care about. You'll find out why it's not what you need when you lose all data in the entire database after the tablespace goes away. The docs need much stronger warnings about this; I'll post a patch. – Craig Ringer May 20 '13 at 23:40