Postgres embedded server equivalent including extensions for .Net applications
This is a library for running a Postgres server embedded equivalent including extensions targeting Windows, Linux and OSX (including Silicon - M1/M2) available since v3.x or above. This project also handles Postgres extensions very well with a neat way to configure and use it.
Note that until v2.x, this library was only supporting Windows.
By default, this project uses the minimum binaries published by zonkyio/embedded-postgres-binaries. Note that this is a minimal set of binaries which can be quickly downloaded (around 10MB) for use rather than the official downloads which are pegged at around 100MB. A list of all available versions of postgres binaries is here: https://mvnrepository.com/artifact/io.zonky.test.postgres/embedded-postgres-binaries-bom. If you click on a specific version, you can lookup the OS platforms for which packages are published.
Library automatically detects the OS environment and architecture to setup the library for use accordingly.
If you have benefitted from this library and has saved you a bunch of time, please feel free to sponsor my work!
Install the package from Nuget using Install-Package MysticMind.PostgresEmbed
or clone the repository and build it.
// using Postgres 15.3.0 with a using block
using (var server = new MysticMind.PostgresEmbed.PgServer("15.3.0"))
{
// start the server
server.Start();
// using Npgsql to connect the server
string connStr = $"Server=localhost;Port={server.PgPort};User Id=postgres;Password=test;Database=postgres";
var conn = new Npgsql.NpgsqlConnection(connStr);
var cmd =
new Npgsql.NpgsqlCommand(
"CREATE TABLE table1(ID CHAR(256) CONSTRAINT id PRIMARY KEY, Title CHAR)",
conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
// using Postgres 15.3.0 with a using block
using (var server = new MysticMind.PostgresEmbed.PgServer("15.3.0"))
{
// start the server
await server.StartAsync();
// using Npgsql to connect the server
string connStr = $"Server=localhost;Port={server.PgPort};User Id=postgres;Password=test;Database=postgres";
var conn = new Npgsql.NpgsqlConnection(connStr);
var cmd =
new Npgsql.NpgsqlCommand(
"CREATE TABLE table1(ID CHAR(256) CONSTRAINT id PRIMARY KEY, Title CHAR)",
conn);
await conn.OpenAsync();
await cmd.ExecuteNonQueryAsync();
await conn.CloseAsync();
}
// Example of using Postgres 15.3.0 with extension PostGIS 3.3.3
// you can add multiple create extension sql statements to be run
var extensions = new List<PgExtensionConfig>();
extensions.Add(new PgExtensionConfig(
"https://download.osgeo.org/postgis/windows/pg15/postgis-bundle-pg15-3.3.3x64.zip"));
using (var server = new MysticMind.PostgresEmbed.PgServer("15.3.0", pgExtensions: extensions))
{
server.Start();
var connStr = string.Format(ConnStr, server.PgPort, PgUser);
var conn = new Npgsql.NpgsqlConnection(connStr);
var cmd = new Npgsql.NpgsqlCommand("CREATE EXTENSION postgis;CREATE EXTENSION fuzzystrmatch", conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
var serverParams = new Dictionary<string, string>();
// set generic query optimizer to off
serverParams.Add("geqo", "off");
// set timezone as UTC
serverParams.Add("timezone", "UTC");
// switch off synchronous commit
serverParams.Add("synchronous_commit", "off");
// set max connections
serverParams.Add("max_connections", "300");
using (var server = new MysticMind.PostgresEmbed.PgServer("15.3.0", pgServerParams: serverParams))
{
server.Start();
// do operations here
}
Since download and extraction of binaries take time, it would be good strategy to setup and teardown the server for each unit tests class instance.
With xUnit, you will need to create a fixture and wire it as a class fixture. See code below:
// this example demonstrates writing an xUnit class fixture
// implements IDisposable to help with the teardown logic.
public class DatabaseServerFixture : IDisposable
{
private static PgServer _pgServer;
public DatabaseServerFixture()
{
var pgExtensions = new List<PgExtensionConfig>();
pgExtensions.Add(
new PgExtensionConfig(
"https://download.osgeo.org/postgis/windows/pg15/postgis-bundle-pg15-3.3.3x64.zip"));
_pgServer = new PgServer("15.3.0", port: 5432, pgExtensions: pgExtensions);
_pgServer.Start();
var connStr = string.Format(ConnStr, server.PgPort, PgUser);
var conn = new Npgsql.NpgsqlConnection(connStr);
var cmd = new Npgsql.NpgsqlCommand("CREATE EXTENSION postgis", conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
public void Dispose()
{
if (_pgServer != null)
{
_pgServer.Stop();
}
}
}
// wire DatabaseServerFixture fixture as a class fixture
// so that it is created once for the whole class
// and shared across all unit tests within the class
public class my_db_tests : IClassFixture<DatabaseServerFixture>
{
[Fact]
public void your_test()
{
// add your test code
}
}
server.PgPort
to fetch the port used by the embedded serverpostgres
is the default database createdpostgres
is the default user (super user) to be used for connectionDbDir
path while creating the server then it will be used as the working directory else it will use the current directory. You will find a folder named pg_embed
within which the binaries
and instance folders are created.clearWorkingDirOnStart=true
in the constuctor while creating the server. By default this value is false
.clearInstanceDirOnStop=true
in the constuctor while creating the server. By default this value is false
.instanceId
in the constructor. This will be helpful in scenarios where you would want to rerun the same named instance already setup. In this case, if the named directory exists, system will skip the setup process and start the server. Note that clearInstanceDirOnStop
and clearWorkingDirOnStart
should be false
(this is the default as well).instanceId
, system will create a new instance by running the whole setup process for every server start.The following steps are done when you run an embedded server:
[specified db dir]\pg_embed\binaries
and reused on further runs.pgsql
and data
folders. Instance folder is created and removed for each embedded server setup and tear down.pgsql
folderdata
folder calling initdb
in a Process
pg_ctl
.psql
at a set interval) if the server has been started till a defined wait timeout.IDisposable
to call Stop automatically within the context of a using(..){...}
block. If using an unit test setup and teardown at the class level, you will call Start()
and Stop()
appropriately.PgServer
class constructor signatures have changed.create extension <extn_name>;
to install the extension. Library will only download and extract the extension based on the url provided.If you are using Npgsql, when you execute the server, you may sporadically notice the following exception
Npgsql.NpgsqlException : Unable to write data to the transport connection: An existing connection was forcibly closed by the remote host.
Refer https://github.com/npgsql/npgsql/issues/939 to know details. Resolution is to use Pooling=false
in connection string.
fixing permissions on existing directory ./pg_embed/aa60c634-fa20-4fa8-b4fc-a43a3b08aa99/data ... initdb: could not change permissions of directory "./pg_embed/aa60c634-fa20-4fa8-b4fc-a43a3b08aa99/data": Permission denied
All processes run from within the embedded server runs under local account. Postgres expects that the parent folder of the data directory has full access permission for the local account.
The fix is to pass a flag addLocalUserAccessPermission
as true
and the system will attempt to add full access before the InitDb step as below for the case of Windows:
icacls.exe c:\pg_embed\aa60c634-fa20-4fa8-b4fc-a43a3b08aa99 /t /grant:r <user>:(OI)(OC)F
For the case of *nix, all the binaries in bin folder are set to 755
by the library to execute.
If you are seeing failures with initdb
with a large negative number then it could be a dependency library issue for Postgres itself, you would need to install Visual C++ Redistributable Packages for Visual Studio 2013 to make MSVCR120.dll
available for Postgres to use.
Note:
This project uses the minimal Postgres binaries published via zonkyio/embedded-postgres-binaries.
Looked at projects Yandex Embedded PostgresSQL and OpenTable Embedded PostgreSQL Component while brainstorming the implementation.
Note that the above projects had only dealt with Postgres binary and none had options to deal with the Postgres extensions.
MysticMind.PostgresEmbed is licensed under MIT License. Refer to License file for more information.
Copyright © 2023 Babu Annamalai