Minimal postgres instance for testing

Question

I have some code that interacts with postgres databases via JDBC. However, for testing purposes I just want to quick way to create a new database and connect to it without having to modify my global postgres installation, manage users, etc. How do people generally do this kind of testing?

Answer 1

I'd locate the initdb executable and use it to create a new database instance temporary storage writeable by the current user. Since it's a test instance, use something like initdb --auth=trust --username=postgres -D /path/to/temp/datadir so the new database is set up to accept connections without requiring passwords.

Use pg_ctl to start the server, specifying a port to override the default set in the generated postgresql.conf and avoid conflicts.

Connect to the new database cluster and do whatever work is required. You'll want to connect as the user postgres first and run any required CREATE USER and CREATE DATABASE commands before handing control over to your test code.

Finally, use pg_ctl to stop it and finally delete the data directory.

All you need is initdb and pg_ctl on the PATH, and a helper class to manage the server.

See:

• initdb
• pg_ctl

Answer 2

There are several libraries that make create temporary instances of Postgres for testing easy:

• http://github.com/tk0miya/testing.postgresql
• http://github.com/TJC/Test-postgresql
• http://eradman.com/ephemeralpg

All of these are designed shut down and remove the temporary database automatically. This is how you might use pg_tmp(1) to create a minimal Postgres instance from a Java:

import java.io.*;
import java.sql.*;

public class test_pg
{
    public static void main(String args[])
    {
        try {
            Process p = Runtime.getRuntime().exec("pg_tmp -t");
            BufferedReader input = new BufferedReader
                    (new InputStreamReader(p.getInputStream()));
            String pg_uri = "jdbc:" + input.readLine();
            input.close();
            System.out.println("Using " + pg_uri);
            Connection conn = DriverManager.getConnection(pg_uri);
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("select now()");
            while(rs.next()) {
                System.out.print(rs.getString(1));
            }
        } catch(IOException | SQLException e) {
            e.printStackTrace();
        }
    }
}