PostgreSQL turn off durabilty

Question

I want to make a script that will run postgres in-memory without durability. I read this page: http://www.postgresql.org/docs/9.1/static/non-durability.html But I didn't understand how I can set this parameters in script. Could you please, help me?

Thanks for help!

Answer 1

Most of those parameters, like fsync, can only be set in postgresql.conf. Changes are applied by re-starting PostgreSQL. They apply to the whole database cluster - all the databases in that PostgreSQL install. That's because the databases all share a single postmaster, write-ahead log, and set of shared system tables.

The only parameter listed there that you can set at the SQL level in a script is synchronous_commit. By setting synchronous_commit = 'off' you can say "it's OK to lose this transaction if the database crashes in the next few seconds, just make sure it still applies atomically".

I wrote more on this topic in a previous answer, Optimise PostgreSQL for fast testing.

If you want to set the other params with a script you can do so but you have to do it by opening and modifying postgresql.conf using the script, then re-starting PostgreSQL. Text-processing tools like sed make this kind of job easier.

Answer 2

If you're running a debian based linux distro, you can just do something like:

pg_createcluster -d /dev/shm/mypgcluster 8.4 ramcluster

to create a ram based cluster. Note that you'll have to do:

pg_drop cluster 8.4 ramcluster 

and recreate it on reboot etc.