Running PostgreSQL in memory only

Question

I want to run a small PostgreSQL database that works only in memory, for each unit test I write. For example:

@Before void setUp() { String port = runPostgresOnRandomPort(); connectTo("postgres://localhost:"+port+"/in_memory_db"); // ... } 

Ideally, I will have one postgres executable checked in a version control that will use unit test.

Something like HSQL , but for postgres. How can i do this?

Can I get this version of Postgres? How can I instruct not to use the drive?

Answer 1

This is not possible in Postgres. It does not offer an in-process / in-memory engine such as HSQLDB or MySQL.

If you want to create a standalone environment, you can put Postgres binaries in SVN (but this is more than just one executable).

You will need to run initdb to set up a test database before you can do anything about it. This can be done from a batch file or using Runtime.exec (). But note that initdb is not that fast. You definitely do not want to run this for every test. However, you can get away from this before your test suite.

However, although this can be done, I recommend having a special Postgres installation where you simply recreate your test database before running the tests.

You can recreate the test database using the template database, which makes it fairly fast ( lot faster than running initdb for each test run)

Answer 2

Or you can create a TABLESPACE in ramfs / tempfs and create all your objects there. I recently pointed to an article on how to do this on Linux .

Warning

This could jeopardize the integrity of your entire database cluster.
Read the added warning in the manual.
Therefore, this is only an option for consumables.

For unit-testing, it should work fine. If you use other databases on the same computer, be sure to use a separate database cluster (which has its own port) to be safe.

Answer 3

(translation of my answer from Using PostgreSQL in memory and its generalization):

You cannot run Pg in-process, in-memory

I cannot figure out how to start the Postgres database in memory for testing. Is it possible?

No, It is Immpossible. PostgreSQL is implemented in C and compiled into platform code. Unlike H2 or Derby, you cannot just load the jar and run it as a database with backup storage.

Unlike SQLite, which is also written in C and compiled into platform code, PostgreSQL cannot be loaded in the process. It requires several processes (one per connection), since it is a multi-processor rather than multi-threaded architecture. The requirement of multiprocessing means that you must run postmaster as a separate process.

Instead: preconfigure the connection

I suggest just writing your tests to expect some hostname / username / password to work, and have a test posting of the CREATE DATABASE database with the drop, and then DROP DATABASE at the end of the run. Get data about connecting to the database from the properties file, create target properties, an environment variable, etc.

It is safe to use an existing instance of PostgreSQL in which you already have the databases you care about until the user you supply for your unit tests is not a superuser, but only a user with CREATEDB . In the worst case scenario, you will create performance problems in other databases. For this reason, I prefer to run a fully isolated PostgreSQL installation for testing.

Instead: launching a PostgreSQL instance for testing

Alternatively, if you are really interested, you can install the test postings of the binaries initdb and postgres , run initdb to create the database, change pg_hba.conf trust , run postgres to run it on a random port, create a user, create a database and run tests . You could even link the PostgreSQL binaries for several architectures in the bank and unpack those for the current architecture into a temporary directory before running the tests.

Personally, I believe that great pain should be avoided; The easiest way to set up a test database. However, with the advent of include_dir support in postgresql.conf it got a little easier; now you can just add one line and then write the generated configuration file for the rest.

Faster testing with PostgreSQL

For more information on how to safely improve PostgreSQL performance for testing purposes, see the detailed answer I wrote earlier: Optimize PostgreSQL for quick testing.

H2 PostgreSQL dialog is not a true substitute

Some people instead use the H2 database in the PostgreSQL dialog mode to run tests. I think this is almost as bad as the Rails people using SQLite for testing and PostgreSQL for production deployments.

H2 supports some PostgreSQL extensions and emulates a PostgreSQL dialect. However, this is just emulation. You will find areas where H2 accepts the query, but PostgreSQL does not, where the behavior is different, etc. . You will also find many places where PostgreSQL supports the execution of something that H2 simply cannot - as a window function, at the time of writing.

If you understand the limitations of this approach and access to the database is simple, H2 might be fine. But in this case, you are probably the best candidate for ORM, which abstracts the database because you still do not use its interesting functions - in which case you no longer need to worry about database compatibility.

Table spaces are not the answer!

Do not use table space to create an in-memory database. Not only is this not necessary, as it will not affect performance in any way, but it is also a great way to disrupt access to any other that you might need in the same PostgreSQL installation. Documentation 9.4 now contains the following warning :

WARNING

Despite the fact that it is located outside the PostgreSQL main data directory, table spaces are an integral part of the database cluster and cannot be considered as a stand-alone set of data files. They depend on the metadata contained in the main data directory and, therefore, cannot be attached to another database cluster or reinforced individually. Similarly, if you lose table space (file deletion, disk failure, etc.), the database cluster may become unreadable or may not start. Placing table space on a temporary file system, such as ramdisk risk, reliability of the entire cluster.

because I noticed that too many people are doing this and are having difficulty.

(If you did this, you can mkdir missing tablespace directory to start PostgreSQL, and then DROP missing databases, tables, etc. It’s better not to do this.)

Answer 4

Now you can run a PostgreSQL instance in memory in JUnit tests through the Embedded PostgreSQL component from OpenTable: https://github.com/opentable/otj-pg-embedded .

By adding a dependency to the otj-pg-embedded library ( https://mvnrepository.com/artifact/com.opentable.components/otj-pg-embedded ), you can start and stop your own instance of PostgreSQL in your @Before and @Afer hooks :

 EmbeddedPostgres pg = EmbeddedPostgres.start(); 

They even offer a JUnit rule to automatically start JUnit and stop the PostgreSQL database server for you:

 @Rule public SingleInstancePostgresRule pg = EmbeddedPostgresRules.singleInstance(); 

Answer 5

You can use TestContainers to deploy the PosgreSQL docker container for tests: http://testcontainers.viewdocs.io/testcontainers-java/usage/database_containers/

TestContainers provide JUnit @ Rule / @ ClassRule : this mode starts the database inside the container before your tests and then flushes it.

Example:

 public class SimplePostgreSQLTest { @Rule public PostgreSQLContainer postgres = new PostgreSQLContainer(); @Test public void testSimple() throws SQLException { HikariConfig hikariConfig = new HikariConfig(); hikariConfig.setJdbcUrl(postgres.getJdbcUrl()); hikariConfig.setUsername(postgres.getUsername()); hikariConfig.setPassword(postgres.getPassword()); HikariDataSource ds = new HikariDataSource(hikariConfig); Statement statement = ds.getConnection().createStatement(); statement.execute("SELECT 1"); ResultSet resultSet = statement.getResultSet(); resultSet.next(); int resultSetInt = resultSet.getInt(1); assertEquals("A basic SELECT query succeeds", 1, resultSetInt); } } 

Answer 6

There is currently a version of PostgreSQL in memory from the Russian search company Yandex: https://github.com/yandex-qatools/postgresql-embedded

It is based on the Flapdoodle OSS embedding process.

Usage example (from github page):

 // starting Postgres final EmbeddedPostgres postgres = new EmbeddedPostgres(V9_6); // predefined data directory // final EmbeddedPostgres postgres = new EmbeddedPostgres(V9_6, "/path/to/predefined/data/directory"); final String url = postgres.start("localhost", 5432, "dbName", "userName", "password"); // connecting to a running Postgres and feeding up the database final Connection conn = DriverManager.getConnection(url); conn.createStatement().execute("CREATE TABLE films (code char(5));"); 

I have been using this for a while. It works well.

UPDATED : This project is no longer actively supported.

 Please be adviced that the main maintainer of this project has successfuly migrated to the use of Test Containers project. This is the best possible alternative nowadays. 

Answer 7

You can also use PostgreSQL configuration settings (for example, detailed in the question and accepted answer here ) to achieve performance without having to use a database in memory.