current community

your communities

more stack exchange communities

Stack Exchange Inbox Reputation and Badges
tour help
stack overflow careers
Sign up ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free.
up vote 0 down vote favorite
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.

share|improve this question

1 Answer 1

up vote 2 down vote

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.

share|improve this answer
    
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 0: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
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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