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.


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.

| improve this answer | |

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)
        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)
        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()

        status = run('%s status' % PG_CTL, 'Checking previous test server status', env)
    except subprocess.CalledProcessError:
        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')
        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)

    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://' % (env['PGPORT'], TEST_TEMPLATE_DB_NAME)
    postgres_db_url = 'postgresql://' % (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/'

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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