Moving PostgreSQL Database to RAM

If you are software developer, sometimes you need to test your program faster. Normally the bottleneck of your program are in the database (writing to disk). You can increase the performance of your development/testing, by moving the database to the RAM if you have enough free RAM (>3GB). In this article, I will guide you to move your PostgreSQL database to RAM. First of all, you'll need to stop and disable the PostgreSQL, so the port won't conflict:

sudo systemctl stop postgresql
sudo systemctl disable postgresql

As we already know, /tmp folder on Linux mostly use tmpfs, that is a RAM file system. So if we create the database on the /tmp directory, it's on the RAM. What you'll need to do is create a script containing something like this:

#!/usr/bin/env bash
sudo killall postgres
# init directories
src=/tmp/data
sudo rm -rf $src
mkdir -p $src
sudo chown postgres:postgres $src
sudo su - postgres <<EOF
initdb --locale en_CA.UTF-8 -E UTF8 -D '/tmp/data'
sed -i -- 's/max_connections = 100/max_connections = 1024/' /tmp/data/postgresql.conf
sed -i -- 's/#logging_collector = off/logging_collector = on/' /tmp/data/postgresql.conf
sed -i -- "s/#log_directory = 'pg_log'/log_directory = '\/tmp'/" /tmp/data/postgresql.conf
sed -i -- "s/#log_file_mode = 0600/log_file_mode = 0644/" /tmp/data/postgresql.conf
sed -i -- "s/#log_min_duration_statement = -1/log_min_duration_statement = 0/" /tmp/data/postgresql.conf
sed -i -- "s/#log_error_verbosity = default/log_error_verbosity = verbose/" /tmp/data/postgresql.conf
sed -i -- "s/#log_statement = 'none'/log_statement = 'all'/" /tmp/data/postgresql.conf
# sed -i -- "s///" /tmp/data/postgresql.conf
postgres -D /tmp/data &
echo sleep 2 seconds..
sleep 2
createuser xxx
createdb xxx
psql -c 'GRANT ALL PRIVILEGES ON DATABASE xxx TO xxx;'
echo you can restore database now..
EOF

This script will erase all your database and create a new empty database on the RAM, that you can restore into later. This script will also create a log file that shows all queries that could help on the softwade development process. Lastly, to measure your PostgreSQL's data directory size in MB you can type this command:

$ sudo du -s -B 1M /tmp/data/
336     /tmp/data/

That's it, as a measurement, to restore a 137 MB database (about 300k records) in my PC normally it took about 17 seconds, when the database moved to RAM, it only took 5 seconds, so yes, it's a huge improvement.