只在内存中运行 PostgreSQL

Running PostgreSQL in memory only

提问by Chi-Lan

I want to run a small PostgreSQL database which runs in memory only, for each unit test I write. For instance:

我想为我编写的每个单元测试运行一个仅在内存中运行的小型 PostgreSQL 数据库。例如：

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

Ideally I'll have a single postgres executable checked into the version control, which the unit test will use.

理想情况下，我会将单个 postgres 可执行文件签入版本控制，单元测试将使用该可执行文件。

Something like HSQL, but for postgres. How can I do that?

类似的东西HSQL，但对于postgres。我怎样才能做到这一点？

Were can I get such a Postgres version? How can I instruct it not to use the disk?

我能得到这样的 Postgres 版本吗？如何指示它不使用磁盘？

采纳答案by a_horse_with_no_name

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

这对于 Postgres 是不可能的。它不提供像 HSQLDB 或 MySQL 那样的进程内/内存引擎。

If you want to create a self-contained environment you canput the Postgres binaries into SVN (but it's more than just a single executable).

如果您想创建一个独立的环境，您可以将 Postgres 二进制文件放入 SVN（但它不仅仅是一个可执行文件）。

You will need to run initdbto setup your test database before you can do anything with this. This can be done from a batch file or by using Runtime.exec(). But note that initdb is not something that is fast. You will definitely not want to run that for each test. You might get away running this before your test-suite though.

您需要先运行initdb来设置您的测试数据库，然后才能对此进行任何操作。这可以通过批处理文件或使用 Runtime.exec() 来完成。但请注意，initdb 并不是很快。您绝对不想为每个测试运行它。不过，您可能会在测试套件之前运行它。

However while this can be done, I'd recommend to have a dedicated Postgres installation where you simply recreate your test database before running your tests.

然而，虽然可以做到这一点，但我建议您安装一个专用的 Postgres，您只需在运行测试之前重新创建测试数据库即可。

You can re-create the test-database by using a template database which makes creating it quite fast (a lotfaster than running initdb for each test run)

通过使用使创建它相当快（一个模板数据库您可以重新创建测试数据库很多快于每个测试运行运行initdb）

回答by Craig Ringer

(Moving my answer from Using in-memory PostgreSQLand generalizing it):

（从使用内存中的 PostgreSQL 中移动我的答案并对其进行概括）：

You can't run Pg in-process, in-memory

你不能在进程内、内存中运行 Pg

I can't figure out how to run in-memory Postgres database for testing. Is it possible?

我不知道如何运行内存 Postgres 数据库进行测试。是否可以？

No, it is not possible. PostgreSQL is implemented in C and compiled to platform code. Unlike H2 or Derby you can't just load the jarand fire it up as a throwaway in-memory DB.

不，这是不可能的。PostgreSQL 用 C 实现并编译为平台代码。与 H2 或 Derby 不同，您不能只是加载jar并将其作为一次性内存数据库启动。

Unlike SQLite, which is also written in C and compiled to platform code, PostgreSQL can't be loaded in-process either. It requires multiple processes (one per connection) because it's a multiprocessing, not a multithreading, architecture. The multiprocessing requirement means you mustlaunch the postmaster as a standalone process.

与同样用 C 编写并编译为平台代码的 SQLite 不同，PostgreSQL 也不能在进程内加载。它需要多个进程（每个连接一个），因为它是一种多处理架构，而不是多线程架构。多处理要求意味着您必须将 postmaster 作为独立进程启动。

Instead: preconfigure a connection

相反：预先配置一个连接

I suggest simply writing your tests to expect a particular hostname/username/password to work, and having the test harness CREATE DATABASEa throwaway database, then DROP DATABASEat the end of the run. Get the database connection details from a properties file, build target properties, environment variable, etc.

我建议简单地编写测试以期望特定的主机名/用户名/密码可以工作，并让测试CREATE DATABASE使用一次性数据库，然后DROP DATABASE在运行结束时。从属性文件中获取数据库连接详细信息、构建目标属性、环境变量等。

It's safe to use an existing PostgreSQL instance you already have databases you care about in, so long as the user you supply to your unit tests is nota superuser, only a user with CREATEDBrights. At worst you'll create performance issues in the other databases. I prefer to run a completely isolated PostgreSQL install for testing for that reason.

只要您提供给单元测试的用户不是超级用户，而是具有CREATEDB权限的用户，就可以安全地使用现有的 PostgreSQL 实例，您已经拥有您关心的数据库。最坏的情况是您会在其他数据库中产生性能问题。出于这个原因，我更喜欢运行一个完全隔离的 PostgreSQL 安装来进行测试。

Instead: Launch a throwaway PostgreSQL instance for testing

相反：启动一个一次性的 PostgreSQL 实例进行测试

Alternately, if you're reallykeen you could have your test harness locate the initdband postgresbinaries, run initdbto create a database, modify pg_hba.confto trust, run postgresto start it on a random port, create a user, create a DB, and run the tests. You could even bundle the PostgreSQL binaries for multiple architectures in a jar and unpack the ones for the current architecture to a temporary directory before running the tests.

或者，如果您真的很热衷，您可以让您的测试工具找到initdb和postgres二进制文件，运行initdb以创建数据库，修改pg_hba.conf为trust，运行postgres以在随机端口上启动它，创建用户，创建数据库并运行测试。您甚至可以将多个架构的 PostgreSQL 二进制文件捆绑在一个 jar 中，并在运行测试之前将当前架构的二进制文件解压到一个临时目录中。

Personally I think that's a major pain that should be avoided; it's way easier to just have a test DB configured. However, it's become a little easier with the advent of include_dirsupport in postgresql.conf; now you can just append one line, then write a generated config file for all the rest.

我个人认为这是应该避免的主要痛苦。只配置一个测试数据库会更容易。但是，随着include_dir支持的出现，它变得容易了一些postgresql.conf；现在你可以只添加一行，然后为所有其他内容编写一个生成的配置文件。

Faster testing with PostgreSQL

使用 PostgreSQL 进行更快的测试

For more information about how to safelyimprove the performance of PostgreSQL for testing purposes, see a detailed answer I wrote on this topic earlier: Optimise PostgreSQL for fast testing

有关如何安全地提高 PostgreSQL 性能以进行测试的更多信息，请参阅我之前针对此主题写的详细答案：Optimize PostgreSQL for fast testing

H2's PostgreSQL dialect is not a true substitute

H2 的 PostgreSQL 方言不是真正的替代品

Some people instead use the H2 database in PostgreSQL dialect mode to run tests. I think that's almost as bad as the Rails people using SQLite for testing and PostgreSQL for production deployment.

有些人转而使用 PostgreSQL 方言模式下的 H2 数据库来运行测试。我认为这几乎与使用 SQLite 进行测试和使用 PostgreSQL 进行生产部署的 Rails 人员一样糟糕。

H2 supports some PostgreSQL extensions and emulates the PostgreSQL dialect. However, it's just that - an emulation. You'll find areas where H2 accepts a query but PostgreSQL doesn't, where behaviour differs, etc. You'll also find plenty of places where PostgreSQL supports doing something that H2 just can't - like window functions, at the time of writing.

H2 支持一些 PostgreSQL 扩展并模拟 PostgreSQL 方言。但是，仅此而已 - 仿真。您会发现 H2 接受查询但 PostgreSQL 不接受的区域、行为不同的区域等。在撰写本文时，您还会发现 PostgreSQL 支持做 H2 不能做的事情的很多地方 - 比如窗口函数。

If you understand the limitations of this approach and your database access is simple, H2 might be OK. But in that case you're probably a better candidate for an ORM that abstracts the database because you're not using its interesting features anyway - and in that case, you don't have to care about database compatibility as much anymore.

如果您了解这种方法的局限性并且您的数据库访问很简单，那么 H2 可能没问题。但在这种情况下，您可能更适合抽象数据库的 ORM，因为无论如何您都不会使用其有趣的功能 - 在这种情况下，您不必再关心数据库兼容性了。

Tablespaces are not the answer!

表空间不是答案！

Do notuse a tablespace to create an "in-memory" database. Not only is it unnecessary as it won't help performance significantly anyway, but it's also a great way to disrupt access to any other you might care about in the same PostgreSQL install. The 9.4 documentation now contains the following warning:

千万不能使用表空间来创建一个“内存”数据库。它不仅是不必要的，因为它无论如何都不会显着提高性能，而且它还是一种很好的方式来中断对您在同一个 PostgreSQL 安装中可能关心的任何其他人的访问。9.4 文档现在包含以下警告：

WARNING

Even though located outside the main PostgreSQL data directory, tablespaces are an integral part of the database cluster and cannot be treated as an autonomous collection of data files. They are dependent on metadata contained in the main data directory, and therefore cannot be attached to a different database cluster or backed up individually. Similarly, if you lose a tablespace (file deletion, disk failure, etc), the database cluster might become unreadable or unable to start. Placing a tablespace on a temporary file system like a ramdisk risks the reliability of the entire cluster.

警告

即使位于主 PostgreSQL 数据目录之外，表空间也是数据库集群的组成部分，不能被视为数据文件的自治集合。它们依赖于包含在主数据目录中的元数据，因此不能附加到不同的数据库集群或单独备份。同样，如果丢失表空间（文件删除、磁盘故障等），数据库集群可能变得不可读或无法启动。将表空间放在临时文件系统（如 ramdisk）上会危及整个集群的可靠性。

because I noticed too many people were doing this and running into trouble.

因为我注意到有太多人这样做并遇到了麻烦。

(If you've done this you can mkdirthe missing tablespace directory to get PostgreSQL to start again, then DROPthe missing databases, tables etc. It's better to just not do it.)

（如果你已经这样做了，你可以mkdir通过丢失的表空间目录让 PostgreSQL 重新启动，然后DROP丢失数据库、表等。最好不要这样做。）

回答by Erwin Brandstetter

Or you could create a TABLESPACEin a ramfs / tempfs and create all your objects there.
I recently was pointed to an article about doing exactly that on Linux.

或者您可以在 ramfs / tempfs 中创建一个TABLESPACE 并在那里创建所有对象。
我最近看到一篇关于在 Linux 上做这件事的文章。

Warning

警告

This can endanger the integrity of your whole database cluster.
Read the added warning in the manual.
So this is only an option for expendable data.

这会危及整个数据库集群的完整性。
阅读手册中添加的警告。
所以这只是消耗性数据的一个选项。

For unit-testingit should work just fine. If you are running other databases on the same machine, be sure to use a separate database cluster (which has its own port) to be safe.

对于单元测试，它应该可以正常工作。如果您在同一台机器上运行其他数据库，请务必使用单独的数据库集群（有自己的端口）以确保安全。

回答by Rubms

Now it is possible to run an in-memory instance of PostgreSQL in your JUnit tests via the Embedded PostgreSQL Component from OpenTable: https://github.com/opentable/otj-pg-embedded.

现在可以通过 OpenTable 的嵌入式 PostgreSQL 组件在 JUnit 测试中运行 PostgreSQL 的内存实例：https: //github.com/opentable/otj-pg-embedded。

By adding the 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:

通过向 otj-pg-embedded 库 ( https://mvnrepository.com/artifact/com.opentable.components/otj-pg-embedded)添加依赖项，您可以在 @Before 和@Afer 钩子：

EmbeddedPostgres pg = EmbeddedPostgres.start();

They even offer a JUnit rule to automatically have JUnit starting and stopping your PostgreSQL database server for you:

他们甚至提供了一个 JUnit 规则来自动让 JUnit 为你启动和停止你的 PostgreSQL 数据库服务器：

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

回答by Andrejs

You could use TestContainersto spin up a PosgreSQL docker container for tests: http://testcontainers.viewdocs.io/testcontainers-java/usage/database_containers/

您可以使用TestContainers来启动 PosgreSQL docker 容器进行测试：http://testcontainers.viewdocs.io/testcontainers-java/usage/database_containers/

TestContainers provide a JUnit @Rule/@ClassRule: this mode starts a database inside a container before your tests and tears it down afterwards.

TestContainers 提供了一个JUnit @Rule/@ClassRule：这种模式在测试之前在容器内启动一个数据库，然后将其拆除。

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);
    }
}

回答by akvyalkov

There is now an in-memory version of PostgreSQL from Russian Search company named Yandex: https://github.com/yandex-qatools/postgresql-embedded

现在有来自俄罗斯搜索公司 Yandex 的 PostgreSQL 内存版本：https: //github.com/yandex-qatools/postgresql-embedded

It's based on Flapdoodle OSS's embed process.

它基于 Flapdoodle OSS 的嵌入过程。

Example of using (from github page):

使用示例（来自 github 页面）：

// 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'm using it some time. It works well.

我正在使用它一段时间。它运作良好。

UPDATED: this project is not being actively maintained anymore

更新：这个项目不再被积极维护

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.

回答by Dan

You can also use PostgreSQL configuration settings (such as those detailed in the question and accepted answer here) to achieve performance without necessarily resorting to an in-memory database.

您还可以使用 PostgreSQL 配置设置（例如问题中详述的设置和此处接受的答案）来实现性能，而不必求助于内存数据库。