Running a test database on RAM disk

Question

Is it possible to create and start a Postgresql server as an unprivileged user with data directory owned by the same user? I want to run tests and create a test database for it, the data files being located on a RAM disk.

$ mkdir -p /dev/shm/tests
$ pg_createcluster 9.3 tests -d /dev/shm/tests/pgsql/data
install: cannot change permissions of ‘/etc/postgresql/9.3/tests’: No such file or directory
Error: could not create configuration directory; you might need to run this program with root privileges

If sudo is unavoidable, is there a way to enter admin password only once, so that subsequent test runs would not require it? I mean I would like to stop the test server and its data directory after tests.

Answer 1

pg_createcluster will insist on putting the configuration files in the predefined privileged location. To work around that, just call initdb directly:

/usr/lib/postgresql/9.3/bin/initdb /dev/shm/tests/pgsql/data

Of course you can also configure sudo to not require a password, but that's really a different question with plenty of answers already available.

Answer 2

Ok, it took me a while to get this working, but here it is:

def run(command, action_descr, env=None):
    print '\n%s' % termcolor.colorize(action_descr, termcolor.c.YELLOW)
    print '$ %s' % termcolor.colorize(command, termcolor.c.YELLOW)
    try:
        output = subprocess.check_output(command, env=env, shell=True)
    except subprocess.CalledProcessError as exc:
        print termcolor.colorize('FAIL:\n%s' % exc.output, termcolor.c.RED, True)
        raise
    else:
        print termcolor.colorize('OK', termcolor.c.GREEN, True)
        return output


def create_test_db_template():
    """Create test template database.
    """
    PG_DIR = run('pg_config --bindir', 'Getting the location of Postgresql executables').strip()
    PG_CTL = os.path.join(PG_DIR, 'pg_ctl')

    test_db_directory = settings.get('tests', 'db_directory')

    env = os.environ.copy()
    env['PGDATA'] = test_db_directory
    env['PGPORT'] = settings.get_test_db_port()

    try:
        status = run('%s status' % PG_CTL, 'Checking previous test server status', env)
    except subprocess.CalledProcessError:
        pass
    else:
        print status
        run('%s stop' % PG_CTL, 'Stopping previous instance of test server', env)

    run('rm -rf %s' % test_db_directory, 'Deleting server data directory')
    run('mkdir -p %s' % test_db_directory, 'Creating server data directory')
    run('%s initdb -o "--auth-host=trust --nosync"' % PG_CTL,
        'Initializing test server and data directory', env)

    pg_log_path = os.path.join(test_db_directory, 'logfile')
    try:
        run('%s start -w --log %s' % (PG_CTL, pg_log_path), 'Starting test server', env)
    except subprocess.CalledProcessError:
        print termcolor.colorize(open(pg_log_path).read(), termcolor.c.RED)
        raise

    for db_name, db_queries in INIT_DB_SQL:
        for db_query in db_queries:
            run('psql %s -c "%s"' % (db_name, db_query), 'Creating template database', env)

    test_template_db_url = 'postgresql://127.0.0.1:%s/%s' % (env['PGPORT'], TEST_TEMPLATE_DB_NAME)
    postgres_db_url = 'postgresql://127.0.0.1:%s/postgres' % (env['PGPORT'])
    ...

This has a lot of additional code, but you should get the idea.

The only thing to do manually is to do:

sudo chmod 777 /var/run/postgresql

This solution is without the RAM disk, but it most of the solution to start Postgresql server in user space is here.

To run all this on a RAM disk, make test_db_directory = 'dev/shm/pgdata/'