Akka.Persistence.PostgreSql 1.5.20

Prefix Reserved
dotnet add package Akka.Persistence.PostgreSql --version 1.5.20                
NuGet\Install-Package Akka.Persistence.PostgreSql -Version 1.5.20                
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.
<PackageReference Include="Akka.Persistence.PostgreSql" Version="1.5.20" />                
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add Akka.Persistence.PostgreSql --version 1.5.20                
#r "nuget: Akka.Persistence.PostgreSql, 1.5.20"                
#r directive can be used in F# Interactive and Polyglot Notebooks. Copy this into the interactive tool or source code of the script to reference the package.
// Install Akka.Persistence.PostgreSql as a Cake Addin
#addin nuget:?package=Akka.Persistence.PostgreSql&version=1.5.20

// Install Akka.Persistence.PostgreSql as a Cake Tool
#tool nuget:?package=Akka.Persistence.PostgreSql&version=1.5.20                

Akka.Persistence.PostgreSql

Akka Persistence journal and snapshot store backed by PostgreSql database.

Configuration

Both journal and snapshot store share the same configuration keys (however they resides in separate scopes, so they are definied distinctly for either journal or snapshot store):

Remember that connection string must be provided separately to Journal and Snapshot Store.

akka.persistence{
	journal {
		plugin = "akka.persistence.journal.postgresql"
		postgresql {
			# qualified type name of the PostgreSql persistence journal actor
			class = "Akka.Persistence.PostgreSql.Journal.PostgreSqlJournal, Akka.Persistence.PostgreSql"

			# dispatcher used to drive journal actor
			plugin-dispatcher = "akka.actor.default-dispatcher"

			# connection string used for database access
			connection-string = ""

			# default SQL commands timeout
			connection-timeout = 30s

			# PostgreSql schema name to table corresponding with persistent journal
			schema-name = public

			# PostgreSql table corresponding with persistent journal
			table-name = event_journal

			# should corresponding journal table be initialized automatically
			auto-initialize = off
			
			# timestamp provider used for generation of journal entries timestamps
			timestamp-provider = "Akka.Persistence.Sql.Common.Journal.DefaultTimestampProvider, Akka.Persistence.Sql.Common"
		
			# metadata table
			metadata-table-name = metadata

			# defines column db type used to store payload. Available option: BYTEA (default), JSON, JSONB
			stored-as = BYTEA

			# Setting used to toggle sequential read access when loading large objects
			# from journals and snapshot stores.
			sequential-access = off

			# When turned on, persistence will use `BIGINT` and `GENERATED ALWAYS AS IDENTITY`
			# for journal table schema creation.
			# NOTE: This only affects newly created tables, as such, it should not affect any
			#       existing database.
			#
			# !!!!! WARNING !!!!!
			# To use this feature, you have to have PorsgreSql version 10 or above
			use-bigint-identity-for-ordering-column = off

			# Setting used to change size of the tags column in persistent journal table
			tags-column-size = 2000
		}
	}

	snapshot-store {
		plugin = "akka.persistence.snapshot-store.postgresql"
		postgresql {
			# qualified type name of the PostgreSql persistence journal actor
			class = "Akka.Persistence.PostgreSql.Snapshot.PostgreSqlSnapshotStore, Akka.Persistence.PostgreSql"

			# dispatcher used to drive journal actor
			plugin-dispatcher = ""akka.actor.default-dispatcher""

			# connection string used for database access
			connection-string = ""

			# default SQL commands timeout
			connection-timeout = 30s

			# PostgreSql schema name to table corresponding with persistent journal
			schema-name = public

			# PostgreSql table corresponding with persistent journal
			table-name = snapshot_store

			# should corresponding journal table be initialized automatically
			auto-initialize = off
			
			# defines column db type used to store payload. Available option: BYTEA (default), JSON, JSONB
			stored-as = BYTEA

			# Setting used to toggle sequential read access when loading large objects
			# from journals and snapshot stores.
			sequential-access = off
		}
	}
}

Table Schema

PostgreSql persistence plugin defines a default table schema used for journal, snapshot store and metadate table.

CREATE TABLE {your_journal_table_name} (
	ordering BIGSERIAL NOT NULL PRIMARY KEY,
    persistence_id VARCHAR(255) NOT NULL,
    sequence_nr BIGINT NOT NULL,
    is_deleted BOOLEAN NOT NULL,
    created_at BIGINT NOT NULL,
    manifest VARCHAR(500) NOT NULL,
    payload BYTEA NOT NULL,
    tags VARCHAR(100) NULL,
    serializer_id INTEGER NULL,
    CONSTRAINT {your_journal_table_name}_uq UNIQUE (persistence_id, sequence_nr)
);

CREATE TABLE {your_snapshot_table_name} (
    persistence_id VARCHAR(255) NOT NULL,
    sequence_nr BIGINT NOT NULL,
    created_at BIGINT NOT NULL,
    manifest VARCHAR(500) NOT NULL,
    payload BYTEA NOT NULL,
    serializer_id INTEGER NULL,
    CONSTRAINT {your_snapshot_table_name}_pk PRIMARY KEY (persistence_id, sequence_nr)
);

CREATE TABLE {your_metadata_table_name} (
    persistence_id VARCHAR(255) NOT NULL,
    sequence_nr BIGINT NOT NULL,
    CONSTRAINT {your_metadata_table_name}_pk PRIMARY KEY (persistence_id, sequence_nr)
);

Note that if you turn on the akka.persistence.journal.postgresql.use-bigint-identity-for-ordering-column flag, the journal table schema will be altered to the latest recommended primary key setting.

CREATE TABLE {your_journal_table_name} (
	ordering BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    persistence_id VARCHAR(255) NOT NULL,
    sequence_nr BIGINT NOT NULL,
    is_deleted BOOLEAN NOT NULL,
    created_at BIGINT NOT NULL,
    manifest VARCHAR(500) NOT NULL,
    payload BYTEA NOT NULL,
    tags VARCHAR(100) NULL,
    serializer_id INTEGER NULL,
    CONSTRAINT {your_journal_table_name}_uq UNIQUE (persistence_id, sequence_nr)
);

Since this script is only run once during table generation, we will not provide any migration path for this change, any migration is left as an exercise for the user.

Migration

From 1.1.0 to 1.3.1
ALTER TABLE {your_journal_table_name} ADD COLUMN serializer_id INTEGER NULL;
ALTER TABLE {your_snapshot_table_name} ADD COLUMN serializer_id INTEGER NULL;
From 1.0.6 to 1.1.0
CREATE TABLE {your_metadata_table_name} (
    persistence_id VARCHAR(255) NOT NULL,
    sequence_nr BIGINT NOT NULL,
    CONSTRAINT {your_metadata_table_name}_pk PRIMARY KEY (persistence_id, sequence_nr)
);

ALTER TABLE {your_journal_table_name} DROP CONSTRAINT {your_journal_table_name}_pk;
ALTER TABLE {your_journal_table_name} ADD COLUMN ordering BIGSERIAL NOT NULL PRIMARY KEY;
ALTER TABLE {your_journal_table_name} ADD COLUMN tags VARCHAR(100) NULL;
ALTER TABLE {your_journal_table_name} ADD CONSTRAINT {your_journal_table_name}_uq UNIQUE (persistence_id, sequence_nr);
ALTER TABLE {your_journal_table_name} ADD COLUMN created_at_temp BIGINT NOT NULL;

UPDATE {your_journal_table_name} SET created_at_temp=extract(epoch from create_at);

ALTER TABLE {your_journal_table_name} DROP COLUMN create_at;
ALTER TABLE {your_journal_table_name} DROP COLUMN created_at_ticks;
ALTER TABLE {your_journal_table_name} RENAME COLUMN created_at_temp TO create_at;

Tests

The PostgreSql tests are packaged and run as part of the default "All" build task.

In order to run the tests, you must do the following things:

  1. Download and install PostgreSql from: http://www.postgresql.org/download/
  2. Install PostgreSql with the default settings. The default connection string uses the following credentials:
  3. Username: postgres
  4. Password: postgres
  5. A custom app.config file can be used and needs to be placed in the same folder as the dll

or run postgres in docker

docker run -d --rm --name=akka-postgres-db -p 5432:5432 -l deployer=akkadotnet -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=postgres postgres:9.6
Product Compatible and additional computed target framework versions.
.NET net5.0 was computed.  net5.0-windows was computed.  net6.0 was computed.  net6.0-android was computed.  net6.0-ios was computed.  net6.0-maccatalyst was computed.  net6.0-macos was computed.  net6.0-tvos was computed.  net6.0-windows was computed.  net7.0 was computed.  net7.0-android was computed.  net7.0-ios was computed.  net7.0-maccatalyst was computed.  net7.0-macos was computed.  net7.0-tvos was computed.  net7.0-windows was computed.  net8.0 was computed.  net8.0-android was computed.  net8.0-browser was computed.  net8.0-ios was computed.  net8.0-maccatalyst was computed.  net8.0-macos was computed.  net8.0-tvos was computed.  net8.0-windows was computed. 
.NET Core netcoreapp2.0 was computed.  netcoreapp2.1 was computed.  netcoreapp2.2 was computed.  netcoreapp3.0 was computed.  netcoreapp3.1 was computed. 
.NET Standard netstandard2.0 is compatible.  netstandard2.1 was computed. 
.NET Framework net461 was computed.  net462 was computed.  net463 was computed.  net47 was computed.  net471 was computed.  net472 was computed.  net48 was computed.  net481 was computed. 
MonoAndroid monoandroid was computed. 
MonoMac monomac was computed. 
MonoTouch monotouch was computed. 
Tizen tizen40 was computed.  tizen60 was computed. 
Xamarin.iOS xamarinios was computed. 
Xamarin.Mac xamarinmac was computed. 
Xamarin.TVOS xamarintvos was computed. 
Xamarin.WatchOS xamarinwatchos was computed. 
Compatible target framework(s)
Included target framework(s) (in package)
Learn more about Target Frameworks and .NET Standard.

NuGet packages (1)

Showing the top 1 NuGet packages that depend on Akka.Persistence.PostgreSql:

Package Downloads
Akka.Persistence.PostgreSql.Hosting

Akka.Persistence.PostgreSql Microsoft.Extensions.Hosting support.

GitHub repositories

This package is not used by any popular GitHub repositories.

Version Downloads Last updated
1.5.20 21,361 5/10/2024
1.5.15.1 13,875 1/29/2024
1.5.15 248 1/29/2024
1.5.13 16,458 10/4/2023
1.5.4.1 20,587 4/27/2023
1.5.4 3,151 4/25/2023
1.5.3 434 4/21/2023
1.5.2 4,618 4/6/2023
1.5.1.1 875 3/22/2023
1.5.1 793 3/16/2023
1.5.0 2,688 3/2/2023
1.4.46 12,819 11/30/2022
1.4.45 22,289 10/21/2022
1.4.35 42,288 3/25/2022
1.4.32 6,147 1/18/2022
1.4.31 13,400 12/21/2021
1.4.29 383 12/15/2021
1.4.25 3,003 9/9/2021
1.4.19 6,104 6/16/2021
1.4.17 2,376 3/17/2021
1.3.9 47,765 8/29/2018
1.3.8 5,859 7/8/2018
1.3.1 4,379 9/12/2017
1.1.2.4-beta 1,344 1/12/2017
1.0.5.2-beta 1,146 12/10/2015
1.0.4.1-beta 1,314 8/8/2015
1.0.3-beta 972 6/12/2015
1.0.2.9-beta 1,002 6/3/2015

[Update Akka.NET to v1.5.20](https://github.com/akkadotnet/akka.net/releases/tag/1.5.20)
[Update Akka.Hosting to 1.5.20](https://github.com/akkadotnet/Akka.Hosting/releases/tag/1.5.20)
[Update Npgsql minimum version to v8.0.3](https://github.com/akkadotnet/Akka.Persistence.PostgreSql/pull/261)
PostgreSQL minimum version update**
The minimum version of the Npgsql NuGet package have been upgraded to 8.0.3 due to [this CVE report](https://github.com/advisories/GHSA-x9vc-6hfv-hg8c)