supabase-postgres.git / summary / log / commit / refs
commit 21338c84583acc6f0d65fc99f014c83206aaa32d
Author: samrose <samuel@supabase.io>
Commit: GitHub <noreply@github.com>
Date: Fri Feb 13 20:49:25 2026 +0000
refactor nixos tests to use our config, build, migrations, init etc (#2038)
* feat: custom module for nixos test of postgres
* fix: include orioledb in vm test
* feat: updating tests to use lib.nix supabase settings
nix/ext/tests/default.nix | 8 +-
nix/ext/tests/http.nix | 388 ++++++++------------------
nix/ext/tests/index_advisor.nix | 322 +++++++--------------
nix/ext/tests/lib.nix | 586 +++++++++++++++++++++++++++++++++++++++
nix/ext/tests/orioledb.nix | 105 +++----
nix/ext/tests/pg_plan_filter.nix | 189 +++++--------
nix/ext/tests/pg_repack.nix | 228 +++++++--------
nix/ext/tests/pg_safeupdate.nix | 142 ----------
nix/ext/tests/pgjwt.nix | 259 ++++++++---------
nix/ext/tests/pgmq.nix | 239 ++++++++--------
nix/ext/tests/pgroonga.nix | 246 ++++++++--------
nix/ext/tests/pgrouting.nix | 320 +++++++++------------
nix/ext/tests/pgsodium.nix | 250 ++++++++---------
nix/ext/tests/plpgsql_check.nix | 248 ++++++++---------
nix/ext/tests/plv8.nix | 162 +++++------
nix/ext/tests/postgis.nix | 247 +++++++++--------
nix/ext/tests/timescaledb.nix | 152 +++++-----
nix/ext/tests/vault.nix | 252 ++++++++---------
18 files changed, 2119 insertions(+), 2224 deletions(-)
diff --git a/nix/ext/tests/default.nix b/nix/ext/tests/default.nix
index e2545471..185eef8b 100644
--- a/nix/ext/tests/default.nix
+++ b/nix/ext/tests/default.nix
@@ -3,7 +3,7 @@ let
testsDir = ./.;
testFiles = builtins.attrNames (builtins.readDir testsDir);
nixFiles = builtins.filter (
- name: builtins.match ".*\\.nix$" name != null && name != "default.nix"
+ name: builtins.match ".*\\.nix$" name != null && name != "default.nix" && name != "lib.nix"
) testFiles;
extTest =
extension_name:
@@ -11,8 +11,8 @@ let
pname = extension_name;
inherit (pkgs) lib;
- support_upgrade = if pname == "pg_repack" then false else true;
- run_pg_regress = if pname == "pg_repack" then false else true;
+ support_upgrade = true;
+ run_pg_regress = true;
installedExtension =
postgresMajorVersion:
@@ -359,12 +359,10 @@ builtins.listToAttrs (
"pg_jsonschema"
"pg_net"
"pg_partman"
- "pg_repack"
"pg_stat_monitor"
"pg_tle"
"pgaudit"
"pgtap"
- "postgis"
"vector"
"wal2json"
"wrappers"
diff --git a/nix/ext/tests/http.nix b/nix/ext/tests/http.nix
index edc2d316..a5f1cce5 100644
--- a/nix/ext/tests/http.nix
+++ b/nix/ext/tests/http.nix
@@ -2,93 +2,32 @@
let
pname = "http";
inherit (pkgs) lib;
- mockServer = ../../tests/http-mock-server.py;
+ system = pkgs.pkgsLinux.stdenv.hostPlatform.system;
+ testLib = import ./lib.nix { inherit self pkgs; };
+
installedExtension =
- postgresMajorVersion:
- self.legacyPackages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}."psql_${postgresMajorVersion}".exts."${
- pname
- }";
+ postgresMajorVersion: self.legacyPackages.${system}."psql_${postgresMajorVersion}".exts."${pname}";
versions = postgresqlMajorVersion: (installedExtension postgresqlMajorVersion).versions;
- postgresqlWithExtension =
- postgresql:
- let
- majorVersion =
- if postgresql.isOrioleDB then "orioledb-17" else lib.versions.major postgresql.version;
- pkg = pkgs.pkgsLinux.buildEnv {
- name = "postgresql-${majorVersion}-${pname}";
- paths = [
- postgresql
- postgresql.lib
- (installedExtension majorVersion)
- ]
- ++ lib.optional (postgresql.isOrioleDB
- ) self.legacyPackages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.psql_orioledb-17.exts.orioledb;
- passthru = {
- inherit (postgresql) version psqlSchema;
- installedExtensions = [ (installedExtension majorVersion) ];
- lib = pkg;
- withPackages = _: pkg;
- withJIT = pkg;
- withoutJIT = pkg;
- };
- nativeBuildInputs = [ pkgs.pkgsLinux.makeWrapper ];
- pathsToLink = [
- "/"
- "/bin"
- "/lib"
- ];
- postBuild = ''
- wrapProgram $out/bin/postgres --set NIX_PGLIBDIR $out/lib
- wrapProgram $out/bin/pg_ctl --set NIX_PGLIBDIR $out/lib
- wrapProgram $out/bin/pg_upgrade --set NIX_PGLIBDIR $out/lib
- '';
- };
- in
- pkg;
- psql_15 =
- postgresqlWithExtension
- self.packages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.postgresql_15;
- psql_17 =
- postgresqlWithExtension
- self.packages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.postgresql_17;
- orioledb_17 =
- postgresqlWithExtension
- self.packages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.postgresql_orioledb-17;
+ orioledbVersions = self.legacyPackages.${system}."psql_orioledb-17".exts."${pname}".versions;
+
+ # Convert versions to major.minor format (e.g., "1.5.0" -> "1.5")
+ # http extension doesn't use semver for its SQL scripts
+ toMajorMinor = map (v: lib.versions.majorMinor v);
+
+ mockServer = ../../tests/http-mock-server.py;
in
pkgs.testers.runNixOSTest {
name = pname;
nodes.server =
- { config, ... }:
+ { ... }:
{
- services.openssh = {
- enable = true;
- };
-
- services.postgresql = {
- enable = true;
- package =
- postgresqlWithExtension
- self.packages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.postgresql_15;
- settings = (installedExtension "15").defaultSettings or { };
- authentication = ''
- local all postgres peer map=postgres
- local all all peer map=root
- '';
- identMap = ''
- root root supabase_admin
- postgres postgres postgres
- '';
- ensureUsers = [
- {
- name = "supabase_admin";
- ensureClauses.superuser = true;
- }
- ];
- initialScript = pkgs.writeText "init-postgres" ''
- CREATE TABLE IF NOT EXISTS test_config (key TEXT PRIMARY KEY, value TEXT);
- '';
- };
+ imports = [
+ (testLib.makeSupabaseTestConfig {
+ majorVersion = "15";
+ })
+ ];
+ # HTTP mock server for pg_regress tests
systemd.services.http-mock-server = {
wantedBy = [ "multi-user.target" ];
before = [ "postgresql.service" ];
@@ -104,10 +43,7 @@ pkgs.testers.runNixOSTest {
PYTHONUNBUFFERED = "1";
};
script = ''
- # Ensure temp directory exists
mkdir -p /tmp
-
- # Start the mock server
exec ${pkgs.pkgsLinux.python3}/bin/python3 ${mockServer}
'';
};
@@ -116,248 +52,150 @@ pkgs.testers.runNixOSTest {
after = [ "http-mock-server.service" ];
};
- specialisation.postgresql17.configuration = {
- services.postgresql = {
- package = lib.mkForce (
- postgresqlWithExtension self.packages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.postgresql_17
- );
- settings = ((installedExtension "17").defaultSettings or { });
- };
-
- systemd.services.postgresql-migrate = {
- serviceConfig = {
- Type = "oneshot";
- RemainAfterExit = true;
- User = "postgres";
- Group = "postgres";
- StateDirectory = "postgresql";
- WorkingDirectory = "${builtins.dirOf config.services.postgresql.dataDir}";
- };
- script =
- let
- oldPostgresql =
- postgresqlWithExtension
- self.packages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.postgresql_15;
- newPostgresql =
- postgresqlWithExtension
- self.packages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.postgresql_17;
- oldDataDir = "${builtins.dirOf config.services.postgresql.dataDir}/${oldPostgresql.psqlSchema}";
- newDataDir = "${builtins.dirOf config.services.postgresql.dataDir}/${newPostgresql.psqlSchema}";
- in
- ''
- if [[ ! -d ${newDataDir} ]]; then
- install -d -m 0700 -o postgres -g postgres "${newDataDir}"
- ${newPostgresql}/bin/initdb -D "${newDataDir}"
- ${newPostgresql}/bin/pg_upgrade --old-datadir "${oldDataDir}" --new-datadir "${newDataDir}" \
- --old-bindir "${oldPostgresql}/bin" --new-bindir "${newPostgresql}/bin"
- else
- echo "${newDataDir} already exists"
- fi
- '';
- };
-
- systemd.services.postgresql = {
- after = [ "postgresql-migrate.service" ];
- requires = [ "postgresql-migrate.service" ];
- };
+ specialisation.postgresql17.configuration = testLib.makeUpgradeSpecialisation {
+ fromMajorVersion = "15";
+ toMajorVersion = "17";
};
- specialisation.orioledb17.configuration = {
- services.postgresql = {
- package = lib.mkForce (
- postgresqlWithExtension
- self.packages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.postgresql_orioledb-17
- );
- settings = lib.mkForce (
- ((installedExtension "17").defaultSettings or { })
- // {
- jit = "off";
- shared_preload_libraries = [
- "orioledb"
- ]
- ++ (lib.toList ((installedExtension "17").defaultSettings.shared_preload_libraries or [ ]));
- default_table_access_method = "orioledb";
- }
- );
- initdbArgs = [
- "--allow-group-access"
- "--locale-provider=icu"
- "--encoding=UTF-8"
- "--icu-locale=en_US.UTF-8"
- ];
- initialScript = lib.mkForce (
- pkgs.writeText "init-postgres-with-orioledb" ''
- CREATE EXTENSION orioledb CASCADE;
- CREATE TABLE IF NOT EXISTS test_config (key TEXT PRIMARY KEY, value TEXT);
- ''
- );
- };
-
- systemd.services.postgresql-migrate = {
- # we don't support migrating from postgresql 17 to orioledb-17 so we just reinit the datadir
- serviceConfig = {
- Type = "oneshot";
- RemainAfterExit = true;
- User = "postgres";
- Group = "postgres";
- StateDirectory = "postgresql";
- WorkingDirectory = "${builtins.dirOf config.services.postgresql.dataDir}";
- };
- script =
- let
- newPostgresql =
- postgresqlWithExtension
- self.packages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.postgresql_orioledb-17;
- in
- ''
- if [[ -z "${newPostgresql.psqlSchema}" ]]; then
- echo "Error: psqlSchema is empty, refusing to rm -rf"
- exit 1
- fi
- rm -rf ${builtins.dirOf config.services.postgresql.dataDir}/${newPostgresql.psqlSchema}
- '';
- };
-
- systemd.services.postgresql = {
- after = [ "postgresql-migrate.service" ];
- requires = [ "postgresql-migrate.service" ];
- };
- };
+ specialisation.orioledb17.configuration = testLib.makeOrioledbSpecialisation { };
};
testScript =
{ nodes, ... }:
let
pg17-configuration = "${nodes.server.system.build.toplevel}/specialisation/postgresql17";
orioledb17-configuration = "${nodes.server.system.build.toplevel}/specialisation/orioledb17";
- # Convert versions to major.minor format (e.g., "1.5.0" -> "1.5")
- toMajorMinor = map (v: lib.versions.majorMinor v);
in
''
from pathlib import Path
versions = {
- "15": [${lib.concatStringsSep ", " (map (s: ''"${s}"'') (toMajorMinor (versions "15")))}],
- "17": [${lib.concatStringsSep ", " (map (s: ''"${s}"'') (toMajorMinor (versions "17")))}],
+ "15": [${lib.concatStringsSep ", " (map (s: ''"${s}"'') (toMajorMinor (versions "15")))}],
+ "17": [${lib.concatStringsSep ", " (map (s: ''"${s}"'') (toMajorMinor (versions "17")))}],
"orioledb-17": [${
- lib.concatStringsSep ", " (map (s: ''"${s}"'') (toMajorMinor (versions "orioledb-17")))
+ lib.concatStringsSep ", " (map (s: ''"${s}"'') (toMajorMinor orioledbVersions))
}],
}
extension_name = "${pname}"
support_upgrade = True
- ext_has_background_worker = ${
- if (installedExtension "15") ? hasBackgroundWorker then "True" else "False"
- }
+ pg17_configuration = "${pg17-configuration}"
+ orioledb17_configuration = "${orioledb17-configuration}"
sql_test_directory = Path("${../../tests}")
- pg_regress_test_name = "${(installedExtension "15").pgRegressTestName or pname}"
- ext_schema = "${(installedExtension "15").defaultSchema or "public"}"
- lib_name = "${(installedExtension "15").libName or pname}"
${builtins.readFile ./lib.py}
start_all()
- server.wait_for_unit("multi-user.target")
- server.wait_for_unit("http-mock-server.service")
- server.wait_for_unit("postgresql.service")
+ # Wait for full Supabase initialization (postgres + init-scripts + migrations)
+ server.wait_for_unit("supabase-db-init.service")
- # Read the HTTP mock port and configure it in PostgreSQL
- # Wait for the port file to be created with retry logic
- server.succeed("""
- for i in {1..30}; do
- if [ -f /tmp/http-mock-port ]; then
- break
- fi
- echo "Waiting for HTTP mock server port file... ($i/30)"
- sleep 1
- done
-
- if [ ! -f /tmp/http-mock-port ]; then
- echo "ERROR: HTTP mock server port file not found after 30 seconds"
- systemctl status http-mock-server.service || true
- journalctl -u http-mock-server.service --no-pager || true
- exit 1
- fi
- """)
+ with subtest("Verify PostgreSQL 15 is our custom build"):
+ pg_version = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT version();\""
+ ).strip()
+ assert "${testLib.expectedVersions."15"}" in pg_version, (
+ f"Expected version ${testLib.expectedVersions."15"}, got: {pg_version}"
+ )
- http_port = server.succeed("cat /tmp/http-mock-port").strip()
- server.succeed(f"""
- sudo -u postgres psql -d postgres -c '
- CREATE TABLE IF NOT EXISTS test_config (key TEXT PRIMARY KEY, value TEXT);
- INSERT INTO test_config (key, value) VALUES ('"'"'http_mock_port'"'"', '"'"'{http_port}'"'"')
- ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value;
- '
- """)
+ postgres_path = server.succeed("readlink -f $(which postgres)").strip()
+ assert "postgresql-and-plugins-${testLib.expectedVersions."15"}" in postgres_path, (
+ f"Expected our custom build (${testLib.expectedVersions."15"}), got: {postgres_path}"
+ )
+
+ with subtest("Verify ansible config loaded"):
+ spl = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SHOW shared_preload_libraries;\""
+ ).strip()
+ for ext in ["pg_stat_statements", "pgaudit", "pgsodium", "pg_cron", "pg_net"]:
+ assert ext in spl, f"Expected {ext} in shared_preload_libraries, got: {spl}"
+
+ session_pl = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SHOW session_preload_libraries;\""
+ ).strip()
+ assert "supautils" in session_pl, (
+ f"Expected supautils in session_preload_libraries, got: {session_pl}"
+ )
- test = PostgresExtensionTest(server, extension_name, versions, sql_test_directory, support_upgrade, ext_schema, lib_name)
- test.create_schema()
+ with subtest("Verify init scripts and migrations ran"):
+ roles = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT rolname FROM pg_roles ORDER BY rolname;\""
+ ).strip()
+ for role in ["anon", "authenticated", "authenticator", "dashboard_user", "pgbouncer", "service_role", "supabase_admin", "supabase_auth_admin", "supabase_storage_admin"]:
+ assert role in roles, f"Expected role {role} to exist, got: {roles}"
+
+ schemas = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT schema_name FROM information_schema.schemata ORDER BY schema_name;\""
+ ).strip()
+ for schema in ["auth", "storage", "extensions"]:
+ assert schema in schemas, f"Expected schema {schema} to exist, got: {schemas}"
+
+ test = PostgresExtensionTest(server, extension_name, versions, sql_test_directory, support_upgrade)
with subtest("Check upgrade path with postgresql 15"):
test.check_upgrade_path("15")
- with subtest("Check pg_regress with postgresql 15 after extension upgrade"):
- test.check_pg_regress(Path("${psql_15}/lib/pgxs/src/test/regress/pg_regress"), "15", pg_regress_test_name)
-
last_version = None
with subtest("Check the install of the last version of the extension"):
last_version = test.check_install_last_version("15")
- if ext_has_background_worker:
- with subtest("Test switch_${pname}_version"):
- test.check_switch_extension_with_background_worker(Path(f"${psql_15}/lib/{lib_name}.so"), "15")
-
- with subtest("Check pg_regress with postgresql 15 after installing the last version"):
- test.check_pg_regress(Path("${psql_15}/lib/pgxs/src/test/regress/pg_regress"), "15", pg_regress_test_name)
-
with subtest("switch to postgresql 17"):
server.succeed(
- "${pg17-configuration}/bin/switch-to-configuration test >&2"
+ f"{pg17_configuration}/bin/switch-to-configuration test >&2"
)
server.wait_for_unit("postgresql.service")
- # Reconfigure the HTTP mock port after switching PostgreSQL version
- server.succeed(f"""
- sudo -u postgres psql -d postgres -c '
- INSERT INTO test_config (key, value) VALUES ('"'"'http_mock_port'"'"', '"'"'{http_port}'"'"')
- ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value;
- '
- """)
- with subtest("Check last version of the extension after postgresql upgrade"):
+ with subtest("Verify PostgreSQL 17 is our custom build"):
+ pg_version = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT version();\""
+ ).strip()
+ assert "${testLib.expectedVersions."17"}" in pg_version, (
+ f"Expected version ${testLib.expectedVersions."17"}, got: {pg_version}"
+ )
+
+ postgres_pid = server.succeed(
+ "head -1 /var/lib/postgresql/data-17/postmaster.pid"
+ ).strip()
+ postgres_path = server.succeed(
+ f"readlink -f /proc/{postgres_pid}/exe"
+ ).strip()
+ assert "postgresql-and-plugins-${testLib.expectedVersions."17"}" in postgres_path, (
+ f"Expected our custom build (${testLib.expectedVersions."17"}), got: {postgres_path}"
+ )
+
+ with subtest("Check last version of the extension after upgrade"):
test.assert_version_matches(last_version)
with subtest("Check upgrade path with postgresql 17"):
test.check_upgrade_path("17")
- with subtest("Check pg_regress with postgresql 17 after extension upgrade"):
- test.check_pg_regress(Path("${psql_17}/lib/pgxs/src/test/regress/pg_regress"), "17", pg_regress_test_name)
-
- with subtest("Check the install of the last version of the extension"):
- test.check_install_last_version("17")
-
- with subtest("Check pg_regress with postgresql 17 after installing the last version"):
- test.check_pg_regress(Path("${psql_17}/lib/pgxs/src/test/regress/pg_regress"), "17", pg_regress_test_name)
-
with subtest("switch to orioledb 17"):
server.succeed(
- "${orioledb17-configuration}/bin/switch-to-configuration test >&2"
+ f"{orioledb17_configuration}/bin/switch-to-configuration test >&2"
)
- server.wait_for_unit("postgresql.service")
- # Reconfigure the HTTP mock port after switching to orioledb
- server.succeed(f"""
- sudo -u postgres psql -d postgres -c '
- INSERT INTO test_config (key, value) VALUES ('"'"'http_mock_port'"'"', '"'"'{http_port}'"'"')
- ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value;
- '
- """)
- installed_extensions=test.run_sql("""SELECT extname FROM pg_extension WHERE extname = 'orioledb';""")
- assert "orioledb" in installed_extensions
- test.create_schema()
+ server.wait_for_unit("supabase-db-init.service")
+
+ with subtest("Verify OrioleDB is running"):
+ installed_extensions = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT extname FROM pg_extension WHERE extname = 'orioledb';\""
+ ).strip()
+ assert "orioledb" in installed_extensions, (
+ f"Expected orioledb extension to be installed, got: {installed_extensions}"
+ )
+
+ dam = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SHOW default_table_access_method;\""
+ ).strip()
+ assert dam == "orioledb", (
+ f"Expected default_table_access_method = orioledb, got: {dam}"
+ )
+
+ with subtest("Verify OrioleDB init scripts and migrations ran"):
+ roles = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT rolname FROM pg_roles ORDER BY rolname;\""
+ ).strip()
+ for role in ["anon", "authenticated", "authenticator", "supabase_admin"]:
+ assert role in roles, f"Expected role {role} to exist, got: {roles}"
with subtest("Check upgrade path with orioledb 17"):
test.check_upgrade_path("orioledb-17")
-
- with subtest("Check pg_regress with orioledb 17 after installing the last version"):
- test.check_pg_regress(Path("${orioledb_17}/lib/pgxs/src/test/regress/pg_regress"), "orioledb-17", pg_regress_test_name)
'';
}
-# We don't use the generic test for this extension because:
-# http is not using semver versioning scheme, so we need to adapt the version checks
-# otherwise the test fails with ERROR: extension "http" has no installation script nor update path for version "1.5.0"
+# http extension doesn't use semver versioning scheme, so we use majorMinor for version checks.
diff --git a/nix/ext/tests/index_advisor.nix b/nix/ext/tests/index_advisor.nix
index b936cb03..56cc62bd 100644
--- a/nix/ext/tests/index_advisor.nix
+++ b/nix/ext/tests/index_advisor.nix
@@ -2,186 +2,31 @@
let
pname = "index_advisor";
inherit (pkgs) lib;
+ system = pkgs.pkgsLinux.stdenv.hostPlatform.system;
+ testLib = import ./lib.nix { inherit self pkgs; };
+
installedExtension =
- postgresMajorVersion:
- self.legacyPackages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}."psql_${postgresMajorVersion}".exts.index_advisor;
+ postgresMajorVersion: self.legacyPackages.${system}."psql_${postgresMajorVersion}".exts."${pname}";
versions = postgresqlMajorVersion: (installedExtension postgresqlMajorVersion).versions;
- postgresqlWithExtension =
- postgresql:
- let
- majorVersion =
- if postgresql.isOrioleDB then "orioledb-17" else lib.versions.major postgresql.version;
- pkg = pkgs.pkgsLinux.buildEnv {
- name = "postgresql-${majorVersion}-${pname}";
- paths = [
- postgresql
- postgresql.lib
- (installedExtension majorVersion)
- ]
- ++ lib.optional (postgresql.isOrioleDB
- ) self.legacyPackages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.psql_orioledb-17.exts.orioledb;
- passthru = {
- inherit (postgresql) version psqlSchema;
- installedExtensions = [ (installedExtension majorVersion) ];
- lib = pkg;
- withPackages = _: pkg;
- withJIT = pkg;
- withoutJIT = pkg;
- };
- nativeBuildInputs = [ pkgs.pkgsLinux.makeWrapper ];
- pathsToLink = [
- "/"
- "/bin"
- "/lib"
- ];
- postBuild = ''
- wrapProgram $out/bin/postgres --set NIX_PGLIBDIR $out/lib
- wrapProgram $out/bin/pg_ctl --set NIX_PGLIBDIR $out/lib
- wrapProgram $out/bin/pg_upgrade --set NIX_PGLIBDIR $out/lib
- '';
- };
- in
- pkg;
- psql_15 =
- postgresqlWithExtension
- self.packages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.postgresql_15;
- psql_17 =
- postgresqlWithExtension
- self.packages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.postgresql_17;
- orioledb_17 =
- postgresqlWithExtension
- self.packages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.postgresql_orioledb-17;
+ orioledbVersions = self.legacyPackages.${system}."psql_orioledb-17".exts."${pname}".versions;
in
pkgs.testers.runNixOSTest {
name = pname;
nodes.server =
- { config, ... }:
+ { ... }:
{
- services.openssh = {
- enable = true;
- };
-
- services.postgresql = {
- enable = true;
- package = psql_15;
- enableTCPIP = true;
- authentication = ''
- local all postgres peer map=postgres
- local all all peer map=root
- '';
- identMap = ''
- root root supabase_admin
- postgres postgres postgres
- '';
- ensureUsers = [
- {
- name = "supabase_admin";
- ensureClauses.superuser = true;
- }
- ];
- settings = (installedExtension "15").defaultSettings or { };
+ imports = [
+ (testLib.makeSupabaseTestConfig {
+ majorVersion = "15";
+ })
+ ];
+
+ specialisation.postgresql17.configuration = testLib.makeUpgradeSpecialisation {
+ fromMajorVersion = "15";
+ toMajorVersion = "17";
};
- networking.firewall.allowedTCPPorts = [ config.services.postgresql.settings.port ];
-
- specialisation.postgresql17.configuration = {
- services.postgresql = {
- package = lib.mkForce psql_17;
- settings = (installedExtension "17").defaultSettings or { };
- };
-
- systemd.services.postgresql-migrate = {
- serviceConfig = {
- Type = "oneshot";
- RemainAfterExit = true;
- User = "postgres";
- Group = "postgres";
- StateDirectory = "postgresql";
- WorkingDirectory = "${builtins.dirOf config.services.postgresql.dataDir}";
- };
- script =
- let
- oldPostgresql = psql_15;
- newPostgresql = psql_17;
- oldDataDir = "${builtins.dirOf config.services.postgresql.dataDir}/${oldPostgresql.psqlSchema}";
- newDataDir = "${builtins.dirOf config.services.postgresql.dataDir}/${newPostgresql.psqlSchema}";
- in
- ''
- if [[ ! -d ${newDataDir} ]]; then
- install -d -m 0700 -o postgres -g postgres "${newDataDir}"
- ${newPostgresql}/bin/initdb -D "${newDataDir}"
- ${newPostgresql}/bin/pg_upgrade --old-datadir "${oldDataDir}" --new-datadir "${newDataDir}" \
- --old-bindir "${oldPostgresql}/bin" --new-bindir "${newPostgresql}/bin"
- else
- echo "${newDataDir} already exists"
- fi
- '';
- };
-
- systemd.services.postgresql = {
- after = [ "postgresql-migrate.service" ];
- requires = [ "postgresql-migrate.service" ];
- };
- };
-
- specialisation.orioledb17.configuration = {
- services.postgresql = {
- package = lib.mkForce (
- postgresqlWithExtension
- self.packages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.postgresql_orioledb-17
- );
- settings = lib.mkForce (
- ((installedExtension "17").defaultSettings or { })
- // {
- jit = "off";
- shared_preload_libraries = [
- "orioledb"
- ]
- ++ (lib.toList ((installedExtension "17").defaultSettings.shared_preload_libraries or [ ]));
- default_table_access_method = "orioledb";
- }
- );
- initdbArgs = [
- "--allow-group-access"
- "--locale-provider=icu"
- "--encoding=UTF-8"
- "--icu-locale=en_US.UTF-8"
- ];
- initialScript = pkgs.writeText "init-postgres-with-orioledb" ''
- CREATE EXTENSION orioledb CASCADE;
- '';
- };
-
- systemd.services.postgresql-migrate = {
- # we don't support migrating from postgresql 17 to orioledb-17 so we just reinit the datadir
- serviceConfig = {
- Type = "oneshot";
- RemainAfterExit = true;
- User = "postgres";
- Group = "postgres";
- StateDirectory = "postgresql";
- WorkingDirectory = "${builtins.dirOf config.services.postgresql.dataDir}";
- };
- script =
- let
- newPostgresql =
- postgresqlWithExtension
- self.packages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.postgresql_orioledb-17;
- in
- ''
- if [[ -z "${newPostgresql.psqlSchema}" ]]; then
- echo "Error: psqlSchema is empty, refusing to rm -rf"
- exit 1
- fi
- rm -rf ${builtins.dirOf config.services.postgresql.dataDir}/${newPostgresql.psqlSchema}
- '';
- };
-
- systemd.services.postgresql = {
- after = [ "postgresql-migrate.service" ];
- requires = [ "postgresql-migrate.service" ];
- };
- };
+ specialisation.orioledb17.configuration = testLib.makeOrioledbSpecialisation { };
};
testScript =
{ nodes, ... }:
@@ -194,82 +39,129 @@ pkgs.testers.runNixOSTest {
versions = {
"15": [${lib.concatStringsSep ", " (map (s: ''"${s}"'') (versions "15"))}],
"17": [${lib.concatStringsSep ", " (map (s: ''"${s}"'') (versions "17"))}],
- "orioledb-17": [${lib.concatStringsSep ", " (map (s: ''"${s}"'') (versions "orioledb-17"))}],
+ "orioledb-17": [${lib.concatStringsSep ", " (map (s: ''"${s}"'') orioledbVersions)}],
}
extension_name = "${pname}"
support_upgrade = True
- ext_has_background_worker = ${
- if (installedExtension "15") ? hasBackgroundWorker then "True" else "False"
- }
+ pg17_configuration = "${pg17-configuration}"
+ orioledb17_configuration = "${orioledb17-configuration}"
sql_test_directory = Path("${../../tests}")
- pg_regress_test_name = "${(installedExtension "15").pgRegressTestName or pname}"
- ext_schema = "${(installedExtension "15").defaultSchema or "public"}"
- lib_name = "${(installedExtension "15").libName or pname}"
${builtins.readFile ./lib.py}
start_all()
- server.wait_for_unit("multi-user.target")
- server.wait_for_unit("postgresql.service")
+ # Wait for full Supabase initialization (postgres + init-scripts + migrations)
+ server.wait_for_unit("supabase-db-init.service")
+
+ with subtest("Verify PostgreSQL 15 is our custom build"):
+ pg_version = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT version();\""
+ ).strip()
+ assert "${testLib.expectedVersions."15"}" in pg_version, (
+ f"Expected version ${testLib.expectedVersions."15"}, got: {pg_version}"
+ )
+
+ postgres_path = server.succeed("readlink -f $(which postgres)").strip()
+ assert "postgresql-and-plugins-${testLib.expectedVersions."15"}" in postgres_path, (
+ f"Expected our custom build (${testLib.expectedVersions."15"}), got: {postgres_path}"
+ )
+
+ with subtest("Verify ansible config loaded"):
+ spl = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SHOW shared_preload_libraries;\""
+ ).strip()
+ for ext in ["pg_stat_statements", "pgaudit", "pgsodium", "pg_cron", "pg_net"]:
+ assert ext in spl, f"Expected {ext} in shared_preload_libraries, got: {spl}"
+
+ session_pl = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SHOW session_preload_libraries;\""
+ ).strip()
+ assert "supautils" in session_pl, (
+ f"Expected supautils in session_preload_libraries, got: {session_pl}"
+ )
+
+ with subtest("Verify init scripts and migrations ran"):
+ roles = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT rolname FROM pg_roles ORDER BY rolname;\""
+ ).strip()
+ for role in ["anon", "authenticated", "authenticator", "dashboard_user", "pgbouncer", "service_role", "supabase_admin", "supabase_auth_admin", "supabase_storage_admin"]:
+ assert role in roles, f"Expected role {role} to exist, got: {roles}"
- test = PostgresExtensionTest(server, extension_name, versions, sql_test_directory, support_upgrade, ext_schema, lib_name)
- test.create_schema()
+ schemas = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT schema_name FROM information_schema.schemata ORDER BY schema_name;\""
+ ).strip()
+ for schema in ["auth", "storage", "extensions"]:
+ assert schema in schemas, f"Expected schema {schema} to exist, got: {schemas}"
+
+ test = PostgresExtensionTest(server, extension_name, versions, sql_test_directory, support_upgrade)
with subtest("Check upgrade path with postgresql 15"):
test.check_upgrade_path("15")
- with subtest("Check pg_regress with postgresql 15 after extension upgrade"):
- test.check_pg_regress(Path("${psql_15}/lib/pgxs/src/test/regress/pg_regress"), "15", pg_regress_test_name)
-
last_version = None
with subtest("Check the install of the last version of the extension"):
last_version = test.check_install_last_version("15")
- if ext_has_background_worker:
- with subtest("Test switch_${pname}_version"):
- test.check_switch_extension_with_background_worker(Path(f"${psql_15}/lib/{lib_name}.so"), "15")
-
- with subtest("Check pg_regress with postgresql 15 after installing the last version"):
- test.check_pg_regress(Path("${psql_15}/lib/pgxs/src/test/regress/pg_regress"), "15", pg_regress_test_name)
-
with subtest("switch to postgresql 17"):
server.succeed(
- "${pg17-configuration}/bin/switch-to-configuration test >&2"
+ f"{pg17_configuration}/bin/switch-to-configuration test >&2"
+ )
+ server.wait_for_unit("postgresql.service")
+
+ with subtest("Verify PostgreSQL 17 is our custom build"):
+ pg_version = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT version();\""
+ ).strip()
+ assert "${testLib.expectedVersions."17"}" in pg_version, (
+ f"Expected version ${testLib.expectedVersions."17"}, got: {pg_version}"
+ )
+
+ postgres_pid = server.succeed(
+ "head -1 /var/lib/postgresql/data-17/postmaster.pid"
+ ).strip()
+ postgres_path = server.succeed(
+ f"readlink -f /proc/{postgres_pid}/exe"
+ ).strip()
+ assert "postgresql-and-plugins-${testLib.expectedVersions."17"}" in postgres_path, (
+ f"Expected our custom build (${testLib.expectedVersions."17"}), got: {postgres_path}"
)
- with subtest("Check last version of the extension after postgresql upgrade"):
+ with subtest("Check last version of the extension after upgrade"):
test.assert_version_matches(last_version)
with subtest("Check upgrade path with postgresql 17"):
test.check_upgrade_path("17")
- with subtest("Check pg_regress with postgresql 17 after extension upgrade"):
- test.check_pg_regress(Path("${psql_17}/lib/pgxs/src/test/regress/pg_regress"), "17", pg_regress_test_name)
-
- with subtest("Check the install of the last version of the extension"):
- test.check_install_last_version("17")
-
- with subtest("Check pg_regress with postgresql 17 after installing the last version"):
- test.check_pg_regress(Path("${psql_17}/lib/pgxs/src/test/regress/pg_regress"), "17", pg_regress_test_name)
-
with subtest("switch to orioledb 17"):
server.succeed(
- "${orioledb17-configuration}/bin/switch-to-configuration test >&2"
+ f"{orioledb17_configuration}/bin/switch-to-configuration test >&2"
+ )
+ server.wait_for_unit("supabase-db-init.service")
+
+ with subtest("Verify OrioleDB is running"):
+ installed_extensions = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT extname FROM pg_extension WHERE extname = 'orioledb';\""
+ ).strip()
+ assert "orioledb" in installed_extensions, (
+ f"Expected orioledb extension to be installed, got: {installed_extensions}"
)
- installed_extensions=test.run_sql("""SELECT extname FROM pg_extension WHERE extname = 'orioledb';""")
- assert "orioledb" in installed_extensions
- test.create_schema()
+
+ dam = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SHOW default_table_access_method;\""
+ ).strip()
+ assert dam == "orioledb", (
+ f"Expected default_table_access_method = orioledb, got: {dam}"
+ )
+
+ with subtest("Verify OrioleDB init scripts and migrations ran"):
+ roles = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT rolname FROM pg_roles ORDER BY rolname;\""
+ ).strip()
+ for role in ["anon", "authenticated", "authenticator", "supabase_admin"]:
+ assert role in roles, f"Expected role {role} to exist, got: {roles}"
with subtest("Check upgrade path with orioledb 17"):
test.check_upgrade_path("orioledb-17")
-
- # NOTE: pg_regress tests are currently disabled for OrioleDB due to compatibility issues
- # The standard pg_regress test framework does not currently work with OrioleDB's
- # specialized storage engine, causing test failures that need investigation.
- #
- # TODO: Re-enable once OrioleDB pg_regress compatibility is resolved
- # with subtest("Check pg_regress with orioledb 17 after installing the last version"):
- # test.check_pg_regress(Path("${orioledb_17}/lib/pgxs/src/test/regress/pg_regress"), "orioledb-17", pg_regress_test_name)
'';
}
diff --git a/nix/ext/tests/lib.nix b/nix/ext/tests/lib.nix
new file mode 100644
index 00000000..a07b838e
--- /dev/null
+++ b/nix/ext/tests/lib.nix
@@ -0,0 +1,586 @@
+{ self, pkgs }:
+let
+ inherit (pkgs) lib;
+ system = pkgs.pkgsLinux.stdenv.hostPlatform.system;
+
+ expectedVersions = {
+ "15" = "15.14";
+ "17" = "17.6";
+ };
+
+ defaultPort = 5432;
+
+ # Simple getkey script that returns a static hex key for testing
+ getkeyScript = pkgs.pkgsLinux.writeShellScript "pgsodium-getkey" ''
+ echo "0000000000000000000000000000000000000000000000000000000000000000"
+ '';
+
+ # Source paths for ansible config files and migrations
+ ansibleConfigDir = builtins.path {
+ path = ../../../ansible/files/postgresql_config;
+ name = "postgresql-config";
+ };
+ extensionCustomScriptsDir = builtins.path {
+ path = ../../../ansible/files/postgresql_extension_custom_scripts;
+ name = "extension-custom-scripts";
+ };
+ migrationsDir = builtins.path {
+ path = ../../../migrations/db;
+ name = "migrations-db";
+ };
+ pgbouncerAuthSchemaSql = builtins.path {
+ path = ../../../ansible/files/pgbouncer_config/pgbouncer_auth_schema.sql;
+ name = "pgbouncer-auth-schema.sql";
+ };
+ statExtensionSql = builtins.path {
+ path = ../../../ansible/files/stat_extension.sql;
+ name = "stat-extension.sql";
+ };
+ postgresqlSchemaSql = builtins.path {
+ path = ../../../nix/tools/postgresql_schema.sql;
+ name = "postgresql-schema.sql";
+ };
+
+ # Process the ansible config files into a derivation with @dataDir@ placeholders
+ processAnsibleConfig =
+ { majorVersion }:
+ pkgs.pkgsLinux.runCommand "processed-postgresql-config-${majorVersion}" { } ''
+ mkdir -p $out/conf.d $out/extension-custom-scripts
+
+ # Copy ansible config files (make writable so we can append/modify later)
+ cp ${ansibleConfigDir}/pg_hba.conf.j2 $out/pg_hba.conf
+ cp ${ansibleConfigDir}/pg_ident.conf.j2 $out/pg_ident.conf
+ chmod u+w $out/pg_hba.conf $out/pg_ident.conf
+
+ # Copy conf.d
+ cp -r ${ansibleConfigDir}/conf.d/* $out/conf.d/ || true
+
+ # Copy read-replica config
+ cp ${ansibleConfigDir}/custom_read_replica.conf $out/read-replica.conf
+
+ # Copy extension custom scripts
+ cp -r ${extensionCustomScriptsDir}/* $out/extension-custom-scripts/
+
+ # Process supautils.conf: substitute extension_custom_scripts_path
+ sed "s|supautils.extension_custom_scripts_path = '/etc/postgresql-custom/extension-custom-scripts'|supautils.extension_custom_scripts_path = '@dataDir@/extension-custom-scripts'|" \
+ ${ansibleConfigDir}/supautils.conf.j2 > $out/supautils.conf
+
+ # Process postgresql.conf with all required substitutions
+ sed \
+ -e "1i\\
+ include = '@dataDir@/supautils.conf'" \
+ -e "\$a\\
+ pgsodium.getkey_script = '${getkeyScript}'" \
+ -e "\$a\\
+ vault.getkey_script = '${getkeyScript}'" \
+ -e "s|data_directory = '/var/lib/postgresql/data'|data_directory = '@dataDir@'|" \
+ -e "s|hba_file = '/etc/postgresql/pg_hba.conf'|hba_file = '@dataDir@/pg_hba.conf'|" \
+ -e "s|ident_file = '/etc/postgresql/pg_ident.conf'|ident_file = '@dataDir@/pg_ident.conf'|" \
+ -e "s|include = '/etc/postgresql/logging.conf'|#&|" \
+ -e "s|include = '/etc/postgresql-custom/read-replica.conf'|include = '@dataDir@/read-replica.conf'|" \
+ -e "\$a\\
+ session_preload_libraries = 'supautils'" \
+ -e "s|include_dir = '/etc/postgresql-custom/conf.d'|include_dir = '@dataDir@/conf.d'|" \
+ -e "\$a\\
+ unix_socket_directories = '/run/postgresql'" \
+ ${ansibleConfigDir}/postgresql.conf.j2 > $out/postgresql.conf
+
+ # Prepend peer auth lines to pg_hba.conf so local socket auth works in test VMs
+ # (tests run as root, psql uses local socket without -h)
+ {
+ echo "local all supabase_admin peer map=supabase_map"
+ echo "local all postgres peer map=supabase_map"
+ cat $out/pg_hba.conf
+ } > $out/pg_hba.conf.tmp
+ mv $out/pg_hba.conf.tmp $out/pg_hba.conf
+
+ # Add ident mappings for root -> supabase_admin and postgres -> supabase_admin
+ echo "supabase_map root supabase_admin" >> $out/pg_ident.conf
+ echo "supabase_map postgres supabase_admin" >> $out/pg_ident.conf
+
+ # Version-specific adjustments (mirroring run-server.sh.in:250-295)
+ ${
+ if majorVersion == "17" || majorVersion == "orioledb-17" then
+ ''
+ # PG 17+: remove timescaledb from shared_preload_libraries
+ sed -i 's/ timescaledb,//g' $out/postgresql.conf
+ # PG 17+: comment out db_user_namespace (removed in PG 17)
+ sed -i 's/db_user_namespace = off/#db_user_namespace = off/g' $out/postgresql.conf
+ # PG 17+: remove timescaledb and plv8 from supautils privileged_extensions
+ sed -i 's/ timescaledb,//g; s/ plv8,//g;' $out/supautils.conf
+ ''
+ else
+ ""
+ }
+ ${
+ if majorVersion == "orioledb-17" then
+ ''
+ # OrioleDB: also remove pgjwt from supautils privileged_extensions
+ sed -i 's/ pgjwt,//g;' $out/supautils.conf
+ # OrioleDB: append orioledb to shared_preload_libraries
+ sed -i "s/\(shared_preload_libraries.*\)'\(.*\)$/\1, orioledb'\2/" $out/postgresql.conf
+ echo "default_table_access_method = 'orioledb'" >> $out/postgresql.conf
+ echo "orioledb.enable_rewind = true" >> $out/postgresql.conf
+ echo "orioledb.rewind_max_time = 1200" >> $out/postgresql.conf
+ echo "orioledb.rewind_max_transactions = 100000" >> $out/postgresql.conf
+ echo "orioledb.rewind_buffers = 1280" >> $out/postgresql.conf
+ ''
+ else
+ ""
+ }
+ '';
+
+ # Create a NixOS module that provides a full Supabase-like PostgreSQL test environment
+ makeSupabaseTestConfig =
+ {
+ majorVersion,
+ postgresPort ? defaultPort,
+ }:
+ let
+ postgresPackage = self.packages.${system}."psql_${majorVersion}/bin";
+ groongaPackage = self.packages.${system}.supabase-groonga;
+ processedConfig = processAnsibleConfig { inherit majorVersion; };
+ dataDir = "/var/lib/postgresql/data";
+ port = toString postgresPort;
+
+ # Runs as root: ensure data directory exists with correct ownership
+ preStartRootScript = pkgs.pkgsLinux.writeShellScript "postgresql-pre-start-root" ''
+ set -euo pipefail
+ DATA_DIR="${dataDir}"
+ if [ ! -d "$DATA_DIR" ]; then
+ mkdir -p -m 0700 "$DATA_DIR"
+ chown postgres:postgres "$DATA_DIR"
+ fi
+ '';
+
+ # Runs as postgres: initdb, config deployment, validation
+ initScript = pkgs.pkgsLinux.writeShellScript "postgresql-init" ''
+ set -euo pipefail
+ DATA_DIR="${dataDir}"
+
+ # Initialize database if it doesn't exist
+ if [ ! -f "$DATA_DIR/PG_VERSION" ]; then
+ echo "Initializing database at $DATA_DIR"
+ ${postgresPackage}/bin/initdb --allow-group-access --data-checksums -U supabase_admin -D "$DATA_DIR"
+ fi
+
+ # Deploy processed config files with @dataDir@ substituted
+ for f in postgresql.conf pg_hba.conf pg_ident.conf supautils.conf read-replica.conf; do
+ sed "s|@dataDir@|$DATA_DIR|g" ${processedConfig}/$f > "$DATA_DIR/$f"
+ done
+
+ # Copy conf.d directory
+ rm -rf "$DATA_DIR/conf.d"
+ cp -r ${processedConfig}/conf.d "$DATA_DIR/conf.d"
+ chmod -R u+w "$DATA_DIR/conf.d"
+
+ # Copy extension-custom-scripts directory
+ rm -rf "$DATA_DIR/extension-custom-scripts"
+ cp -r ${processedConfig}/extension-custom-scripts "$DATA_DIR/extension-custom-scripts"
+ chmod -R u+w "$DATA_DIR/extension-custom-scripts"
+
+ # Validate config
+ echo "Validating PostgreSQL configuration..."
+ ${postgresPackage}/bin/postgres -C shared_preload_libraries -D "$DATA_DIR"
+ '';
+
+ dbInitScript = pkgs.pkgsLinux.writeShellScript "supabase-db-init" ''
+ set -euo pipefail
+
+ # Wait for PostgreSQL to be ready
+ echo "Waiting for PostgreSQL to be ready..."
+ for i in $(seq 1 60); do
+ if ${postgresPackage}/bin/pg_isready -h localhost -p ${port} -q; then
+ echo "PostgreSQL is ready"
+ break
+ fi
+ if [ "$i" -eq 60 ]; then
+ echo "PostgreSQL failed to become ready"
+ exit 1
+ fi
+ sleep 1
+ done
+
+ PSQL="${postgresPackage}/bin/psql"
+
+ # Create postgres role (matching run-server.sh.in)
+ echo "Creating postgres role..."
+ $PSQL -h localhost -p ${port} -U supabase_admin -d postgres -c "CREATE ROLE postgres SUPERUSER LOGIN;" || true
+ $PSQL -h localhost -p ${port} -U supabase_admin -d postgres -c "ALTER DATABASE postgres OWNER TO postgres;" || true
+
+ # Run init-scripts as postgres user (matching run-server.sh.in)
+ for sql in ${migrationsDir}/init-scripts/*.sql; do
+ echo "Running init-script: $sql"
+ $PSQL -v ON_ERROR_STOP=1 -h localhost -p ${port} -U postgres -f "$sql" postgres
+ done
+
+ # Run pgbouncer auth schema
+ echo "Running pgbouncer auth schema..."
+ $PSQL -v ON_ERROR_STOP=1 -h localhost -p ${port} -U postgres -d postgres -f ${pgbouncerAuthSchemaSql}
+
+ # Run stat extension
+ echo "Running stat extension..."
+ $PSQL -v ON_ERROR_STOP=1 -h localhost -p ${port} -U postgres -d postgres -f ${statExtensionSql}
+
+ # Run migrations as supabase_admin (matching run-server.sh.in)
+ for sql in ${migrationsDir}/migrations/*.sql; do
+ echo "Running migration: $sql"
+ $PSQL -v ON_ERROR_STOP=1 -h localhost -p ${port} -U supabase_admin -f "$sql" postgres
+ done
+
+ # Run postgresql schema
+ echo "Running postgresql schema..."
+ $PSQL -v ON_ERROR_STOP=1 -h localhost -p ${port} -U supabase_admin -f ${postgresqlSchemaSql} postgres
+
+ echo "Database initialization complete"
+ '';
+ in
+ { ... }:
+ {
+ # System users
+ users.users.postgres = {
+ isSystemUser = true;
+ group = "postgres";
+ home = "/var/lib/postgresql";
+ createHome = true;
+ };
+ users.groups.postgres = { };
+
+ # Required directories
+ systemd.tmpfiles.rules = [
+ "d /var/lib/postgresql 0750 postgres postgres -"
+ "d /run/postgresql 0755 postgres postgres -"
+ ];
+
+ # Locale
+ i18n.defaultLocale = "en_US.UTF-8";
+
+ # Networking
+ networking.firewall.allowedTCPPorts = [ postgresPort ];
+
+ # Make PostgreSQL available system-wide
+ environment.systemPackages = [ postgresPackage ];
+
+ # PostgreSQL service (custom, bypassing services.postgresql)
+ systemd.services.postgresql = {
+ description = "PostgreSQL Database Server";
+ wantedBy = [ "multi-user.target" ];
+ after = [ "network.target" ];
+
+ serviceConfig = {
+ Type = "notify";
+ User = "postgres";
+ Group = "postgres";
+ ExecStartPre = [
+ ("+" + preStartRootScript)
+ initScript
+ ];
+ ExecStart = "${postgresPackage}/bin/postgres -D ${dataDir}";
+ KillMode = "mixed";
+ KillSignal = "SIGINT";
+ TimeoutStopSec = 90;
+ LimitNOFILE = 16384;
+ };
+
+ environment = {
+ GRN_PLUGINS_DIR = "${groongaPackage}/lib/groonga/plugins";
+ LANG = "en_US.UTF-8";
+ };
+ };
+
+ # Database initialization service (runs init-scripts and migrations)
+ systemd.services.supabase-db-init = {
+ description = "Supabase Database Initialization";
+ wantedBy = [ "multi-user.target" ];
+ after = [ "postgresql.service" ];
+ requires = [ "postgresql.service" ];
+
+ serviceConfig = {
+ Type = "oneshot";
+ RemainAfterExit = true;
+ User = "root";
+ Group = "root";
+ ExecStart = dbInitScript;
+ };
+
+ environment = {
+ LANG = "en_US.UTF-8";
+ };
+ };
+ };
+
+ # Create a specialisation configuration for pg_upgrade from one major version to another
+ makeUpgradeSpecialisation =
+ {
+ fromMajorVersion,
+ toMajorVersion,
+ }:
+ let
+ oldPkg = self.packages.${system}."psql_${fromMajorVersion}/bin";
+ newPkg = self.packages.${system}."psql_${toMajorVersion}/bin";
+ groongaPackage = self.packages.${system}.supabase-groonga;
+ oldDataDir = "/var/lib/postgresql/data";
+ newDataDir = "/var/lib/postgresql/data-${toMajorVersion}";
+ processedNewConfig = processAnsibleConfig { majorVersion = toMajorVersion; };
+
+ migrateScript = pkgs.pkgsLinux.writeShellScript "postgresql-migrate" ''
+ set -euo pipefail
+
+ OLD_DATA="${oldDataDir}"
+ NEW_DATA="${newDataDir}"
+
+ if [ -d "$NEW_DATA" ]; then
+ echo "$NEW_DATA already exists, skipping migration"
+ exit 0
+ fi
+
+ echo "Starting pg_upgrade from ${fromMajorVersion} to ${toMajorVersion}"
+
+ # Create new data directory (runs as postgres user, so ownership is automatic)
+ mkdir -p -m 0700 "$NEW_DATA"
+
+ # Initialize new cluster
+ ${newPkg}/bin/initdb --allow-group-access --data-checksums -U supabase_admin -D "$NEW_DATA"
+
+ # Deploy config files to new data directory
+ for f in postgresql.conf pg_hba.conf pg_ident.conf supautils.conf read-replica.conf; do
+ sed "s|@dataDir@|$NEW_DATA|g" ${processedNewConfig}/$f > "$NEW_DATA/$f"
+ done
+
+ # Copy conf.d and extension-custom-scripts
+ rm -rf "$NEW_DATA/conf.d"
+ cp -r ${processedNewConfig}/conf.d "$NEW_DATA/conf.d"
+ chmod -R u+w "$NEW_DATA/conf.d"
+
+ rm -rf "$NEW_DATA/extension-custom-scripts"
+ cp -r ${processedNewConfig}/extension-custom-scripts "$NEW_DATA/extension-custom-scripts"
+ chmod -R u+w "$NEW_DATA/extension-custom-scripts"
+
+ # Run pg_upgrade
+ # Use --username=supabase_admin because that's the bootstrap superuser
+ # (from initdb -U supabase_admin) and our pg_ident.conf maps OS user
+ # postgres → DB user supabase_admin (not postgres → postgres)
+ cd /var/lib/postgresql
+ ${newPkg}/bin/pg_upgrade \
+ --username supabase_admin \
+ --old-datadir "$OLD_DATA" \
+ --new-datadir "$NEW_DATA" \
+ --old-bindir "${oldPkg}/bin" \
+ --new-bindir "${newPkg}/bin" \
+ --old-options="-c config_file=$OLD_DATA/postgresql.conf" \
+ --new-options="-c config_file=$NEW_DATA/postgresql.conf"
+
+ echo "pg_upgrade completed successfully"
+ '';
+ in
+ {
+ # Migration service runs before postgresql
+ systemd.services.postgresql-migrate = {
+ description = "PostgreSQL Major Version Migration";
+ serviceConfig = {
+ Type = "oneshot";
+ RemainAfterExit = true;
+ User = "postgres";
+ Group = "postgres";
+ ExecStart = migrateScript;
+ };
+ environment = {
+ GRN_PLUGINS_DIR = "${groongaPackage}/lib/groonga/plugins";
+ LANG = "en_US.UTF-8";
+ };
+ };
+
+ # Override postgresql to use new package and new data directory
+ systemd.services.postgresql = {
+ after = [ "postgresql-migrate.service" ];
+ requires = [ "postgresql-migrate.service" ];
+ serviceConfig = {
+ ExecStart = lib.mkForce "${newPkg}/bin/postgres -D ${newDataDir}";
+ };
+ environment = {
+ GRN_PLUGINS_DIR = lib.mkForce "${groongaPackage}/lib/groonga/plugins";
+ };
+ };
+
+ # Disable db-init — data is migrated, not re-initialized
+ systemd.services.supabase-db-init = {
+ wantedBy = lib.mkForce [ ];
+ serviceConfig.ExecStart = lib.mkForce "${pkgs.pkgsLinux.coreutils}/bin/true";
+ };
+
+ # Add new package to system PATH (don't mkForce — that changes /etc and
+ # breaks D-Bus policy during switch-to-configuration)
+ environment.systemPackages = [ newPkg ];
+ };
+ # Create a specialisation for OrioleDB — wipes data and reinitializes from scratch
+ # (no pg_upgrade path from regular PG to OrioleDB), then runs full Supabase init
+ makeOrioledbSpecialisation =
+ {
+ postgresPort ? defaultPort,
+ }:
+ let
+ orioledbPkg = self.packages.${system}."psql_orioledb-17/bin";
+ groongaPackage = self.packages.${system}.supabase-groonga;
+ newDataDir = "/var/lib/postgresql/data-orioledb-17";
+ processedConfig = processAnsibleConfig { majorVersion = "orioledb-17"; };
+ port = toString postgresPort;
+
+ # Wipe existing data — no upgrade path from regular PG to OrioleDB
+ migrateScript = pkgs.pkgsLinux.writeShellScript "postgresql-orioledb-migrate" ''
+ set -euo pipefail
+ NEW_DATA="${newDataDir}"
+ if [ -d "$NEW_DATA" ]; then
+ rm -rf "$NEW_DATA"
+ fi
+ '';
+
+ # Runs as root: ensure data directory exists with correct ownership
+ preStartRootScript = pkgs.pkgsLinux.writeShellScript "postgresql-orioledb-pre-start-root" ''
+ set -euo pipefail
+ DATA_DIR="${newDataDir}"
+ if [ ! -d "$DATA_DIR" ]; then
+ mkdir -p -m 0700 "$DATA_DIR"
+ chown postgres:postgres "$DATA_DIR"
+ fi
+ '';
+
+ # Runs as postgres: initdb with OrioleDB-specific args, config deployment, validation
+ initScript = pkgs.pkgsLinux.writeShellScript "postgresql-orioledb-init" ''
+ set -euo pipefail
+ DATA_DIR="${newDataDir}"
+
+ if [ ! -f "$DATA_DIR/PG_VERSION" ]; then
+ echo "Initializing OrioleDB database at $DATA_DIR"
+ ${orioledbPkg}/bin/initdb \
+ --allow-group-access --data-checksums \
+ --locale-provider=icu --encoding=UTF-8 --icu-locale=en_US.UTF-8 \
+ -U supabase_admin -D "$DATA_DIR"
+ fi
+
+ # Deploy processed config files with @dataDir@ substituted
+ for f in postgresql.conf pg_hba.conf pg_ident.conf supautils.conf read-replica.conf; do
+ sed "s|@dataDir@|$DATA_DIR|g" ${processedConfig}/$f > "$DATA_DIR/$f"
+ done
+
+ # Copy conf.d directory
+ rm -rf "$DATA_DIR/conf.d"
+ cp -r ${processedConfig}/conf.d "$DATA_DIR/conf.d"
+ chmod -R u+w "$DATA_DIR/conf.d"
+
+ # Copy extension-custom-scripts directory
+ rm -rf "$DATA_DIR/extension-custom-scripts"
+ cp -r ${processedConfig}/extension-custom-scripts "$DATA_DIR/extension-custom-scripts"
+ chmod -R u+w "$DATA_DIR/extension-custom-scripts"
+
+ # Validate config
+ echo "Validating PostgreSQL configuration..."
+ ${orioledbPkg}/bin/postgres -C shared_preload_libraries -D "$DATA_DIR"
+ '';
+
+ # Full db init: CREATE EXTENSION orioledb first, then init-scripts + migrations
+ dbInitScript = pkgs.pkgsLinux.writeShellScript "supabase-orioledb-db-init" ''
+ set -euo pipefail
+
+ echo "Waiting for PostgreSQL to be ready..."
+ for i in $(seq 1 60); do
+ if ${orioledbPkg}/bin/pg_isready -h localhost -p ${port} -q; then
+ echo "PostgreSQL is ready"
+ break
+ fi
+ if [ "$i" -eq 60 ]; then
+ echo "PostgreSQL failed to become ready"
+ exit 1
+ fi
+ sleep 1
+ done
+
+ PSQL="${orioledbPkg}/bin/psql"
+
+ # Create orioledb extension first (before init-scripts, so tables use orioledb storage)
+ echo "Creating orioledb extension..."
+ $PSQL -h localhost -p ${port} -U supabase_admin -d postgres -c "CREATE EXTENSION orioledb CASCADE;"
+
+ # Create postgres role (matching run-server.sh.in)
+ echo "Creating postgres role..."
+ $PSQL -h localhost -p ${port} -U supabase_admin -d postgres -c "CREATE ROLE postgres SUPERUSER LOGIN;" || true
+ $PSQL -h localhost -p ${port} -U supabase_admin -d postgres -c "ALTER DATABASE postgres OWNER TO postgres;" || true
+
+ # Run init-scripts as postgres user (matching run-server.sh.in)
+ for sql in ${migrationsDir}/init-scripts/*.sql; do
+ echo "Running init-script: $sql"
+ $PSQL -v ON_ERROR_STOP=1 -h localhost -p ${port} -U postgres -f "$sql" postgres
+ done
+
+ # Run pgbouncer auth schema
+ echo "Running pgbouncer auth schema..."
+ $PSQL -v ON_ERROR_STOP=1 -h localhost -p ${port} -U postgres -d postgres -f ${pgbouncerAuthSchemaSql}
+
+ # Run stat extension
+ echo "Running stat extension..."
+ $PSQL -v ON_ERROR_STOP=1 -h localhost -p ${port} -U postgres -d postgres -f ${statExtensionSql}
+
+ # Run migrations as supabase_admin (matching run-server.sh.in)
+ for sql in ${migrationsDir}/migrations/*.sql; do
+ echo "Running migration: $sql"
+ $PSQL -v ON_ERROR_STOP=1 -h localhost -p ${port} -U supabase_admin -f "$sql" postgres
+ done
+
+ # Run postgresql schema
+ echo "Running postgresql schema..."
+ $PSQL -v ON_ERROR_STOP=1 -h localhost -p ${port} -U supabase_admin -f ${postgresqlSchemaSql} postgres
+
+ echo "OrioleDB database initialization complete"
+ '';
+ in
+ {
+ # Reinit service wipes data for fresh orioledb cluster
+ systemd.services.postgresql-migrate = {
+ description = "PostgreSQL OrioleDB Reinitialization";
+ serviceConfig = {
+ Type = "oneshot";
+ RemainAfterExit = true;
+ User = "postgres";
+ Group = "postgres";
+ ExecStart = migrateScript;
+ };
+ environment = {
+ LANG = "en_US.UTF-8";
+ };
+ };
+
+ # Override postgresql: new package, new data dir, new ExecStartPre for orioledb initdb
+ systemd.services.postgresql = {
+ after = [ "postgresql-migrate.service" ];
+ requires = [ "postgresql-migrate.service" ];
+ serviceConfig = {
+ ExecStartPre = lib.mkForce [
+ ("+" + preStartRootScript)
+ initScript
+ ];
+ ExecStart = lib.mkForce "${orioledbPkg}/bin/postgres -D ${newDataDir}";
+ };
+ environment = {
+ GRN_PLUGINS_DIR = lib.mkForce "${groongaPackage}/lib/groonga/plugins";
+ };
+ };
+
+ # Override db-init with orioledb-aware version (creates orioledb ext + full init)
+ systemd.services.supabase-db-init = {
+ wantedBy = lib.mkForce [ "multi-user.target" ];
+ serviceConfig.ExecStart = lib.mkForce dbInitScript;
+ };
+
+ # Add orioledb package to system PATH
+ environment.systemPackages = [ orioledbPkg ];
+ };
+in
+{
+ inherit
+ processAnsibleConfig
+ makeSupabaseTestConfig
+ makeUpgradeSpecialisation
+ makeOrioledbSpecialisation
+ expectedVersions
+ defaultPort
+ ;
+}
diff --git a/nix/ext/tests/orioledb.nix b/nix/ext/tests/orioledb.nix
index bb1a3125..167e2f0f 100644
--- a/nix/ext/tests/orioledb.nix
+++ b/nix/ext/tests/orioledb.nix
@@ -1,79 +1,60 @@
{ self, pkgs }:
let
pname = "orioledb";
- inherit (pkgs) lib;
- postgresqlWithExtension =
- postgresql:
- let
- majorVersion = lib.versions.major postgresql.version;
- pkg = pkgs.pkgsLinux.buildEnv {
- name = "postgresql-${majorVersion}-${pname}";
- paths = [
- postgresql
- postgresql.lib
- (self.legacyPackages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}."psql_orioledb-17".exts.orioledb)
- ];
- passthru = {
- inherit (postgresql) version psqlSchema;
- installedExtensions = [
- (self.legacyPackages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}."psql_orioledb-17".exts.orioledb)
- ];
- lib = pkg;
- withPackages = _: pkg;
- withJIT = pkg;
- withoutJIT = pkg;
- };
- nativeBuildInputs = [ pkgs.pkgsLinux.makeWrapper ];
- pathsToLink = [
- "/"
- "/bin"
- "/lib"
- ];
- postBuild = ''
- wrapProgram $out/bin/postgres --set NIX_PGLIBDIR $out/lib
- wrapProgram $out/bin/pg_ctl --set NIX_PGLIBDIR $out/lib
- wrapProgram $out/bin/pg_upgrade --set NIX_PGLIBDIR $out/lib
- '';
- };
- in
- pkg;
- psql_orioledb =
- postgresqlWithExtension
- self.packages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.postgresql_orioledb-17;
+ testLib = import ./lib.nix { inherit self pkgs; };
in
pkgs.testers.runNixOSTest {
name = pname;
nodes.server =
{ ... }:
{
- services.openssh = {
- enable = true;
- };
+ imports = [
+ (testLib.makeSupabaseTestConfig {
+ majorVersion = "15";
+ })
+ ];
- services.postgresql = {
- enable = true;
- package = psql_orioledb;
- settings = {
- shared_preload_libraries = "orioledb";
- default_table_access_method = "orioledb";
- };
- initdbArgs = [
- "--allow-group-access"
- "--locale-provider=icu"
- "--encoding=UTF-8"
- "--icu-locale=en_US.UTF-8"
- ];
- initialScript = pkgs.writeText "init-postgres-with-orioledb" ''
- CREATE EXTENSION orioledb CASCADE;
- '';
- };
+ specialisation.orioledb17.configuration = testLib.makeOrioledbSpecialisation { };
};
testScript =
- { ... }:
+ { nodes, ... }:
+ let
+ orioledb17-configuration = "${nodes.server.system.build.toplevel}/specialisation/orioledb17";
+ in
''
+ orioledb17_configuration = "${orioledb17-configuration}"
+
start_all()
- server.wait_for_unit("multi-user.target")
- server.wait_for_unit("postgresql.service")
+ # Wait for full Supabase initialization on PG 15
+ server.wait_for_unit("supabase-db-init.service")
+
+ with subtest("switch to orioledb 17"):
+ server.succeed(
+ f"{orioledb17_configuration}/bin/switch-to-configuration test >&2"
+ )
+ server.wait_for_unit("supabase-db-init.service")
+
+ with subtest("Verify OrioleDB is running"):
+ installed_extensions = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT extname FROM pg_extension WHERE extname = 'orioledb';\""
+ ).strip()
+ assert "orioledb" in installed_extensions, (
+ f"Expected orioledb extension to be installed, got: {installed_extensions}"
+ )
+
+ dam = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SHOW default_table_access_method;\""
+ ).strip()
+ assert dam == "orioledb", (
+ f"Expected default_table_access_method = orioledb, got: {dam}"
+ )
+
+ with subtest("Verify OrioleDB init scripts and migrations ran"):
+ roles = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT rolname FROM pg_roles ORDER BY rolname;\""
+ ).strip()
+ for role in ["anon", "authenticated", "authenticator", "supabase_admin"]:
+ assert role in roles, f"Expected role {role} to exist, got: {roles}"
'';
}
diff --git a/nix/ext/tests/pg_plan_filter.nix b/nix/ext/tests/pg_plan_filter.nix
index 30e1170e..480133cf 100644
--- a/nix/ext/tests/pg_plan_filter.nix
+++ b/nix/ext/tests/pg_plan_filter.nix
@@ -1,142 +1,107 @@
+# plan_filter is a shared-library-only module, not a CREATE EXTENSION type
+# extension. It's loaded via shared_preload_libraries and configured via GUC
+# parameters (plan_filter.statement_cost_limit, plan_filter.limit_select_only).
+# See: https://github.com/pgexperts/pg_plan_filter
{ self, pkgs }:
let
- pname = "plan_filter";
- inherit (pkgs) lib;
- installedExtension =
- postgresMajorVersion:
- self.legacyPackages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}."psql_${postgresMajorVersion}".exts."${
- pname
- }";
- versions = postgresqlMajorVersion: (installedExtension postgresqlMajorVersion).versions;
- postgresqlWithExtension =
- postgresql:
- let
- majorVersion = lib.versions.major postgresql.version;
- pkg = pkgs.pkgsLinux.buildEnv {
- name = "postgresql-${majorVersion}-${pname}";
- paths = [
- postgresql
- postgresql.lib
- (installedExtension majorVersion)
- ];
- passthru = {
- inherit (postgresql) version psqlSchema;
- installedExtensions = [ (installedExtension majorVersion) ];
- lib = pkg;
- withPackages = _: pkg;
- withJIT = pkg;
- withoutJIT = pkg;
- };
- nativeBuildInputs = [ pkgs.pkgsLinux.makeWrapper ];
- pathsToLink = [
- "/"
- "/bin"
- "/lib"
- ];
- postBuild = ''
- wrapProgram $out/bin/postgres --set NIX_PGLIBDIR $out/lib
- wrapProgram $out/bin/pg_ctl --set NIX_PGLIBDIR $out/lib
- wrapProgram $out/bin/pg_upgrade --set NIX_PGLIBDIR $out/lib
- '';
- };
- in
- pkg;
- psql_15 =
- postgresqlWithExtension
- self.packages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.postgresql_15;
- psql_17 =
- postgresqlWithExtension
- self.packages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.postgresql_17;
+ testLib = import ./lib.nix { inherit self pkgs; };
in
pkgs.testers.runNixOSTest {
- name = pname;
+ name = "plan_filter";
nodes.server =
- { config, ... }:
+ { ... }:
{
- services.postgresql = {
- enable = true;
- package = psql_15;
- settings = (installedExtension "15").defaultSettings or { };
- };
-
- specialisation.postgresql17.configuration = {
- services.postgresql = {
- package = lib.mkForce psql_17;
- settings = (installedExtension "15").defaultSettings or { };
- };
-
- systemd.services.postgresql-migrate = {
- serviceConfig = {
- Type = "oneshot";
- RemainAfterExit = true;
- User = "postgres";
- Group = "postgres";
- StateDirectory = "postgresql";
- WorkingDirectory = "${builtins.dirOf config.services.postgresql.dataDir}";
- };
- script =
- let
- oldPostgresql = psql_15;
- newPostgresql = psql_17;
- oldDataDir = "${builtins.dirOf config.services.postgresql.dataDir}/${oldPostgresql.psqlSchema}";
- newDataDir = "${builtins.dirOf config.services.postgresql.dataDir}/${newPostgresql.psqlSchema}";
- in
- ''
- if [[ ! -d ${newDataDir} ]]; then
- install -d -m 0700 -o postgres -g postgres "${newDataDir}"
- ${newPostgresql}/bin/initdb -D "${newDataDir}"
- ${newPostgresql}/bin/pg_upgrade --old-datadir "${oldDataDir}" --new-datadir "${newDataDir}" \
- --old-bindir "${oldPostgresql}/bin" --new-bindir "${newPostgresql}/bin"
- else
- echo "${newDataDir} already exists"
- fi
- '';
- };
+ imports = [
+ (testLib.makeSupabaseTestConfig {
+ majorVersion = "15";
+ })
+ ];
- systemd.services.postgresql = {
- after = [ "postgresql-migrate.service" ];
- requires = [ "postgresql-migrate.service" ];
- };
+ specialisation.postgresql17.configuration = testLib.makeUpgradeSpecialisation {
+ fromMajorVersion = "15";
+ toMajorVersion = "17";
};
+
+ specialisation.orioledb17.configuration = testLib.makeOrioledbSpecialisation { };
};
testScript =
{ nodes, ... }:
let
pg17-configuration = "${nodes.server.system.build.toplevel}/specialisation/postgresql17";
+ orioledb17-configuration = "${nodes.server.system.build.toplevel}/specialisation/orioledb17";
in
''
- from pathlib import Path
- versions = {
- "15": [${lib.concatStringsSep ", " (map (s: ''"${s}"'') (versions "15"))}],
- "17": [${lib.concatStringsSep ", " (map (s: ''"${s}"'') (versions "17"))}],
- }
- extension_name = "${pname}"
- support_upgrade = False
pg17_configuration = "${pg17-configuration}"
- ext_has_background_worker = ${
- if (installedExtension "15") ? hasBackgroundWorker then "True" else "False"
- }
- sql_test_directory = Path("${../../tests}")
- pg_regress_test_name = "${(installedExtension "15").pgRegressTestName or pname}"
+ orioledb17_configuration = "${orioledb17-configuration}"
+
+ def check_plan_filter(server):
+ """Verify plan_filter is loaded and its GUC parameters are functional."""
+ spl = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SHOW shared_preload_libraries;\""
+ ).strip()
+ assert "plan_filter" in spl, (
+ f"Expected plan_filter in shared_preload_libraries, got: {spl}"
+ )
- ${builtins.readFile ./lib.py}
+ # Verify GUC parameter is registered (default: 0 = no filter)
+ cost_limit = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SHOW plan_filter.statement_cost_limit;\""
+ ).strip()
+ assert cost_limit is not None, "plan_filter.statement_cost_limit GUC not available"
+
+ # Verify the parameter can be set
+ server.succeed(
+ "psql -U supabase_admin -d postgres -c \"SET plan_filter.statement_cost_limit = 100000.0;\""
+ )
start_all()
- server.wait_for_unit("multi-user.target")
- server.wait_for_unit("postgresql.service")
+ # Wait for full Supabase initialization (postgres + init-scripts + migrations)
+ server.wait_for_unit("supabase-db-init.service")
- test = PostgresExtensionTest(server, extension_name, versions, sql_test_directory, support_upgrade)
+ with subtest("Verify PostgreSQL 15 is our custom build"):
+ pg_version = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT version();\""
+ ).strip()
+ assert "${testLib.expectedVersions."15"}" in pg_version, (
+ f"Expected version ${testLib.expectedVersions."15"}, got: {pg_version}"
+ )
- with subtest("Check pg_regress with postgresql 15 after extension upgrade"):
- test.check_pg_regress(Path("${psql_15}/lib/pgxs/src/test/regress/pg_regress"), "15", pg_regress_test_name)
+ with subtest("Verify plan_filter is loaded on PostgreSQL 15"):
+ check_plan_filter(server)
with subtest("switch to postgresql 17"):
server.succeed(
f"{pg17_configuration}/bin/switch-to-configuration test >&2"
)
+ server.wait_for_unit("postgresql.service")
+
+ with subtest("Verify PostgreSQL 17 is our custom build"):
+ pg_version = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT version();\""
+ ).strip()
+ assert "${testLib.expectedVersions."17"}" in pg_version, (
+ f"Expected version ${testLib.expectedVersions."17"}, got: {pg_version}"
+ )
+
+ with subtest("Verify plan_filter is loaded on PostgreSQL 17"):
+ check_plan_filter(server)
+
+ with subtest("switch to orioledb 17"):
+ server.succeed(
+ f"{orioledb17_configuration}/bin/switch-to-configuration test >&2"
+ )
+ server.wait_for_unit("supabase-db-init.service")
+
+ with subtest("Verify OrioleDB is running"):
+ installed_extensions = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT extname FROM pg_extension WHERE extname = 'orioledb';\""
+ ).strip()
+ assert "orioledb" in installed_extensions, (
+ f"Expected orioledb extension to be installed, got: {installed_extensions}"
+ )
- with subtest("Check pg_regress with postgresql 17 after extension upgrade"):
- test.check_pg_regress(Path("${psql_17}/lib/pgxs/src/test/regress/pg_regress"), "17", pg_regress_test_name)
+ with subtest("Verify plan_filter is loaded on OrioleDB"):
+ check_plan_filter(server)
'';
}
diff --git a/nix/ext/tests/pg_repack.nix b/nix/ext/tests/pg_repack.nix
index 7ca0da12..832f4687 100644
--- a/nix/ext/tests/pg_repack.nix
+++ b/nix/ext/tests/pg_repack.nix
@@ -2,146 +2,97 @@
let
pname = "pg_repack";
inherit (pkgs) lib;
+ system = pkgs.pkgsLinux.stdenv.hostPlatform.system;
+ testLib = import ./lib.nix { inherit self pkgs; };
+
installedExtension =
- postgresMajorVersion:
- self.legacyPackages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}."psql_${postgresMajorVersion}".exts."${
- pname
- }";
+ postgresMajorVersion: self.legacyPackages.${system}."psql_${postgresMajorVersion}".exts."${pname}";
versions = postgresqlMajorVersion: (installedExtension postgresqlMajorVersion).versions;
- postgresqlWithExtension =
- postgresql:
- let
- majorVersion = lib.versions.major postgresql.version;
- pkg = pkgs.pkgsLinux.buildEnv {
- name = "postgresql-${majorVersion}-${pname}";
- paths = [
- postgresql
- postgresql.lib
- (installedExtension majorVersion)
- ];
- passthru = {
- inherit (postgresql) version psqlSchema;
- installedExtensions = [ (installedExtension majorVersion) ];
- lib = pkg;
- withPackages = _: pkg;
- withJIT = pkg;
- withoutJIT = pkg;
- };
- nativeBuildInputs = [ pkgs.pkgsLinux.makeWrapper ];
- pathsToLink = [
- "/"
- "/bin"
- "/lib"
- ];
- postBuild = ''
- wrapProgram $out/bin/postgres --set NIX_PGLIBDIR $out/lib
- wrapProgram $out/bin/pg_ctl --set NIX_PGLIBDIR $out/lib
- wrapProgram $out/bin/pg_upgrade --set NIX_PGLIBDIR $out/lib
- '';
- };
- in
- pkg;
+ orioledbVersions = self.legacyPackages.${system}."psql_orioledb-17".exts."${pname}".versions;
in
pkgs.testers.runNixOSTest {
name = pname;
nodes.server =
- { config, ... }:
+ { ... }:
{
- services.openssh = {
- enable = true;
- };
+ imports = [
+ (testLib.makeSupabaseTestConfig {
+ majorVersion = "15";
+ })
+ ];
- services.postgresql = {
- enable = true;
- package =
- postgresqlWithExtension
- self.packages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.postgresql_15;
- enableTCPIP = true;
- authentication = ''
- local all postgres peer map=postgres
- local all all peer map=root
- '';
- identMap = ''
- root root supabase_admin
- postgres postgres postgres
- '';
- ensureUsers = [
- {
- name = "supabase_admin";
- ensureClauses.superuser = true;
- }
- ];
+ specialisation.postgresql17.configuration = testLib.makeUpgradeSpecialisation {
+ fromMajorVersion = "15";
+ toMajorVersion = "17";
};
- networking.firewall.allowedTCPPorts = [ config.services.postgresql.settings.port ];
-
- specialisation.postgresql17.configuration = {
- services.postgresql = {
- package = lib.mkForce (
- postgresqlWithExtension self.packages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.postgresql_17
- );
- };
-
- systemd.services.postgresql-migrate = {
- serviceConfig = {
- Type = "oneshot";
- RemainAfterExit = true;
- User = "postgres";
- Group = "postgres";
- StateDirectory = "postgresql";
- WorkingDirectory = "${builtins.dirOf config.services.postgresql.dataDir}";
- };
- script =
- let
- oldPostgresql =
- postgresqlWithExtension
- self.packages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.postgresql_15;
- newPostgresql =
- postgresqlWithExtension
- self.packages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.postgresql_17;
- oldDataDir = "${builtins.dirOf config.services.postgresql.dataDir}/${oldPostgresql.psqlSchema}";
- newDataDir = "${builtins.dirOf config.services.postgresql.dataDir}/${newPostgresql.psqlSchema}";
- in
- ''
- if [[ ! -d ${newDataDir} ]]; then
- install -d -m 0700 -o postgres -g postgres "${newDataDir}"
- ${newPostgresql}/bin/initdb -D "${newDataDir}"
- ${newPostgresql}/bin/pg_upgrade --old-datadir "${oldDataDir}" --new-datadir "${newDataDir}" \
- --old-bindir "${oldPostgresql}/bin" --new-bindir "${newPostgresql}/bin"
- else
- echo "${newDataDir} already exists"
- fi
- '';
- };
-
- systemd.services.postgresql = {
- after = [ "postgresql-migrate.service" ];
- requires = [ "postgresql-migrate.service" ];
- };
- };
+ specialisation.orioledb17.configuration = testLib.makeOrioledbSpecialisation { };
};
testScript =
{ nodes, ... }:
let
pg17-configuration = "${nodes.server.system.build.toplevel}/specialisation/postgresql17";
+ orioledb17-configuration = "${nodes.server.system.build.toplevel}/specialisation/orioledb17";
in
''
from pathlib import Path
versions = {
"15": [${lib.concatStringsSep ", " (map (s: ''"${s}"'') (versions "15"))}],
"17": [${lib.concatStringsSep ", " (map (s: ''"${s}"'') (versions "17"))}],
+ "orioledb-17": [${lib.concatStringsSep ", " (map (s: ''"${s}"'') orioledbVersions)}],
}
extension_name = "${pname}"
support_upgrade = False
pg17_configuration = "${pg17-configuration}"
+ orioledb17_configuration = "${orioledb17-configuration}"
sql_test_directory = Path("${../../tests}")
${builtins.readFile ./lib.py}
start_all()
- server.wait_for_unit("multi-user.target")
- server.wait_for_unit("postgresql.service")
+ # Wait for full Supabase initialization (postgres + init-scripts + migrations)
+ server.wait_for_unit("supabase-db-init.service")
+
+ with subtest("Verify PostgreSQL 15 is our custom build"):
+ pg_version = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT version();\""
+ ).strip()
+ assert "${testLib.expectedVersions."15"}" in pg_version, (
+ f"Expected version ${testLib.expectedVersions."15"}, got: {pg_version}"
+ )
+
+ postgres_path = server.succeed("readlink -f $(which postgres)").strip()
+ assert "postgresql-and-plugins-${testLib.expectedVersions."15"}" in postgres_path, (
+ f"Expected our custom build (${testLib.expectedVersions."15"}), got: {postgres_path}"
+ )
+
+ with subtest("Verify ansible config loaded"):
+ spl = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SHOW shared_preload_libraries;\""
+ ).strip()
+ for ext in ["pg_stat_statements", "pgaudit", "pgsodium", "pg_cron", "pg_net"]:
+ assert ext in spl, f"Expected {ext} in shared_preload_libraries, got: {spl}"
+
+ session_pl = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SHOW session_preload_libraries;\""
+ ).strip()
+ assert "supautils" in session_pl, (
+ f"Expected supautils in session_preload_libraries, got: {session_pl}"
+ )
+
+ with subtest("Verify init scripts and migrations ran"):
+ roles = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT rolname FROM pg_roles ORDER BY rolname;\""
+ ).strip()
+ for role in ["anon", "authenticated", "authenticator", "dashboard_user", "pgbouncer", "service_role", "supabase_admin", "supabase_auth_admin", "supabase_storage_admin"]:
+ assert role in roles, f"Expected role {role} to exist, got: {roles}"
+
+ schemas = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT schema_name FROM information_schema.schemata ORDER BY schema_name;\""
+ ).strip()
+ for schema in ["auth", "storage", "extensions"]:
+ assert schema in schemas, f"Expected schema {schema} to exist, got: {schemas}"
test = PostgresExtensionTest(server, extension_name, versions, sql_test_directory, support_upgrade)
@@ -156,14 +107,67 @@ pkgs.testers.runNixOSTest {
server.succeed(
f"{pg17_configuration}/bin/switch-to-configuration test >&2"
)
+ server.wait_for_unit("postgresql.service")
+
+ with subtest("Verify PostgreSQL 17 is our custom build"):
+ pg_version = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT version();\""
+ ).strip()
+ assert "${testLib.expectedVersions."17"}" in pg_version, (
+ f"Expected version ${testLib.expectedVersions."17"}, got: {pg_version}"
+ )
+
+ # After specialisation switch, 'which postgres' still resolves to PG 15
+ # (both packages in systemPackages; can't mkForce without breaking D-Bus).
+ # The postgres binary is also wrapped (.postgres-wrapped) so pgrep -ox fails.
+ # Instead, get the postmaster PID from the PG 17 data directory's pid file.
+ postgres_pid = server.succeed(
+ "head -1 /var/lib/postgresql/data-17/postmaster.pid"
+ ).strip()
+ postgres_path = server.succeed(
+ f"readlink -f /proc/{postgres_pid}/exe"
+ ).strip()
+ assert "postgresql-and-plugins-${testLib.expectedVersions."17"}" in postgres_path, (
+ f"Expected our custom build (${testLib.expectedVersions."17"}), got: {postgres_path}"
+ )
with subtest("Check last version of the extension after upgrade"):
test.assert_version_matches(last_version)
with subtest("Check upgrade path with postgresql 17"):
test.check_upgrade_path("17")
+
+ with subtest("switch to orioledb 17"):
+ server.succeed(
+ f"{orioledb17_configuration}/bin/switch-to-configuration test >&2"
+ )
+ server.wait_for_unit("supabase-db-init.service")
+
+ with subtest("Verify OrioleDB is running"):
+ installed_extensions = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT extname FROM pg_extension WHERE extname = 'orioledb';\""
+ ).strip()
+ assert "orioledb" in installed_extensions, (
+ f"Expected orioledb extension to be installed, got: {installed_extensions}"
+ )
+
+ dam = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SHOW default_table_access_method;\""
+ ).strip()
+ assert dam == "orioledb", (
+ f"Expected default_table_access_method = orioledb, got: {dam}"
+ )
+
+ with subtest("Verify OrioleDB init scripts and migrations ran"):
+ roles = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT rolname FROM pg_roles ORDER BY rolname;\""
+ ).strip()
+ for role in ["anon", "authenticated", "authenticator", "supabase_admin"]:
+ assert role in roles, f"Expected role {role} to exist, got: {roles}"
+
+ with subtest("Check upgrade path with orioledb 17"):
+ test.check_upgrade_path("orioledb-17")
'';
}
-# We don't use the generic test for this extension because:
-# pg_repack does not support upgrade as the extension doesn't provide the upgrade SQL scripts
-# and fails with ERROR: extension "pg_repack" has no update path from version "1.4.8" to version "1.5.0"
+# pg_repack does not support in-place upgrade as it doesn't provide the upgrade SQL scripts
+# and fails with ERROR: extension "pg_repack" has no update path from version "1.4.8" to version "1.5.0"
diff --git a/nix/ext/tests/pg_safeupdate.nix b/nix/ext/tests/pg_safeupdate.nix
deleted file mode 100644
index 83c2b080..00000000
--- a/nix/ext/tests/pg_safeupdate.nix
+++ /dev/null
@@ -1,142 +0,0 @@
-{ self, pkgs }:
-let
- pname = "safeupdate";
- inherit (pkgs) lib;
- installedExtension =
- postgresMajorVersion:
- self.legacyPackages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}."psql_${postgresMajorVersion}".exts."${
- pname
- }";
- versions = postgresqlMajorVersion: (installedExtension postgresqlMajorVersion).versions;
- postgresqlWithExtension =
- postgresql:
- let
- majorVersion = lib.versions.major postgresql.version;
- pkg = pkgs.pkgsLinux.buildEnv {
- name = "postgresql-${majorVersion}-${pname}";
- paths = [
- postgresql
- postgresql.lib
- (installedExtension majorVersion)
- ];
- passthru = {
- inherit (postgresql) version psqlSchema;
- installedExtensions = [ (installedExtension majorVersion) ];
- lib = pkg;
- withPackages = _: pkg;
- withJIT = pkg;
- withoutJIT = pkg;
- };
- nativeBuildInputs = [ pkgs.pkgsLinux.makeWrapper ];
- pathsToLink = [
- "/"
- "/bin"
- "/lib"
- ];
- postBuild = ''
- wrapProgram $out/bin/postgres --set NIX_PGLIBDIR $out/lib
- wrapProgram $out/bin/pg_ctl --set NIX_PGLIBDIR $out/lib
- wrapProgram $out/bin/pg_upgrade --set NIX_PGLIBDIR $out/lib
- '';
- };
- in
- pkg;
- psql_15 =
- postgresqlWithExtension
- self.packages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.postgresql_15;
- psql_17 =
- postgresqlWithExtension
- self.packages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.postgresql_17;
-in
-pkgs.testers.runNixOSTest {
- name = pname;
- nodes.server =
- { config, ... }:
- {
- services.postgresql = {
- enable = true;
- package = psql_15;
- settings = (installedExtension "15").defaultSettings or { };
- };
-
- specialisation.postgresql17.configuration = {
- services.postgresql = {
- package = lib.mkForce psql_17;
- settings = (installedExtension "15").defaultSettings or { };
- };
-
- systemd.services.postgresql-migrate = {
- serviceConfig = {
- Type = "oneshot";
- RemainAfterExit = true;
- User = "postgres";
- Group = "postgres";
- StateDirectory = "postgresql";
- WorkingDirectory = "${builtins.dirOf config.services.postgresql.dataDir}";
- };
- script =
- let
- oldPostgresql = psql_15;
- newPostgresql = psql_17;
- oldDataDir = "${builtins.dirOf config.services.postgresql.dataDir}/${oldPostgresql.psqlSchema}";
- newDataDir = "${builtins.dirOf config.services.postgresql.dataDir}/${newPostgresql.psqlSchema}";
- in
- ''
- if [[ ! -d ${newDataDir} ]]; then
- install -d -m 0700 -o postgres -g postgres "${newDataDir}"
- ${newPostgresql}/bin/initdb -D "${newDataDir}"
- ${newPostgresql}/bin/pg_upgrade --old-datadir "${oldDataDir}" --new-datadir "${newDataDir}" \
- --old-bindir "${oldPostgresql}/bin" --new-bindir "${newPostgresql}/bin"
- else
- echo "${newDataDir} already exists"
- fi
- '';
- };
-
- systemd.services.postgresql = {
- after = [ "postgresql-migrate.service" ];
- requires = [ "postgresql-migrate.service" ];
- };
- };
- };
- testScript =
- { nodes, ... }:
- let
- pg17-configuration = "${nodes.server.system.build.toplevel}/specialisation/postgresql17";
- in
- ''
- from pathlib import Path
- versions = {
- "15": [${lib.concatStringsSep ", " (map (s: ''"${s}"'') (versions "15"))}],
- "17": [${lib.concatStringsSep ", " (map (s: ''"${s}"'') (versions "17"))}],
- }
- extension_name = "${pname}"
- support_upgrade = False
- pg17_configuration = "${pg17-configuration}"
- ext_has_background_worker = ${
- if (installedExtension "15") ? hasBackgroundWorker then "True" else "False"
- }
- sql_test_directory = Path("${../../tests}")
- pg_regress_test_name = "${(installedExtension "15").pgRegressTestName or pname}"
-
- ${builtins.readFile ./lib.py}
-
- start_all()
-
- server.wait_for_unit("multi-user.target")
- server.wait_for_unit("postgresql.service")
-
- test = PostgresExtensionTest(server, extension_name, versions, sql_test_directory, support_upgrade)
-
- with subtest("Check pg_regress with postgresql 15 after extension upgrade"):
- test.check_pg_regress(Path("${psql_15}/lib/pgxs/src/test/regress/pg_regress"), "15", pg_regress_test_name)
-
- with subtest("switch to postgresql 17"):
- server.succeed(
- f"{pg17_configuration}/bin/switch-to-configuration test >&2"
- )
-
- with subtest("Check pg_regress with postgresql 17 after extension upgrade"):
- test.check_pg_regress(Path("${psql_17}/lib/pgxs/src/test/regress/pg_regress"), "17", pg_regress_test_name)
- '';
-}
diff --git a/nix/ext/tests/pgjwt.nix b/nix/ext/tests/pgjwt.nix
index 39fcd18f..b6bc31eb 100644
--- a/nix/ext/tests/pgjwt.nix
+++ b/nix/ext/tests/pgjwt.nix
@@ -2,199 +2,166 @@
let
pname = "pgjwt";
inherit (pkgs) lib;
+ system = pkgs.pkgsLinux.stdenv.hostPlatform.system;
+ testLib = import ./lib.nix { inherit self pkgs; };
+
installedExtension =
- postgresMajorVersion:
- self.legacyPackages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}."psql_${postgresMajorVersion}".exts."${
- pname
- }";
+ postgresMajorVersion: self.legacyPackages.${system}."psql_${postgresMajorVersion}".exts."${pname}";
versions = postgresqlMajorVersion: (installedExtension postgresqlMajorVersion).versions;
- postgresqlWithExtension =
- postgresql:
- let
- majorVersion = lib.versions.major postgresql.version;
- pkg = pkgs.pkgsLinux.buildEnv {
- name = "postgresql-${majorVersion}-${pname}";
- paths = [
- postgresql
- postgresql.lib
- (installedExtension majorVersion)
- ];
- passthru = {
- inherit (postgresql) version psqlSchema;
- installedExtensions = [ (installedExtension majorVersion) ];
- lib = pkg;
- withPackages = _: pkg;
- withJIT = pkg;
- withoutJIT = pkg;
- };
- nativeBuildInputs = [ pkgs.pkgsLinux.makeWrapper ];
- pathsToLink = [
- "/"
- "/bin"
- "/lib"
- ];
- postBuild = ''
- wrapProgram $out/bin/postgres --set NIX_PGLIBDIR $out/lib
- wrapProgram $out/bin/pg_ctl --set NIX_PGLIBDIR $out/lib
- wrapProgram $out/bin/pg_upgrade --set NIX_PGLIBDIR $out/lib
- '';
- };
- in
- pkg;
- psql_15 =
- postgresqlWithExtension
- self.packages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.postgresql_15;
- psql_17 =
- postgresqlWithExtension
- self.packages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.postgresql_17;
+ orioledbVersions = self.legacyPackages.${system}."psql_orioledb-17".exts."${pname}".versions;
in
pkgs.testers.runNixOSTest {
name = pname;
nodes.server =
- { config, ... }:
+ { ... }:
{
- services.postgresql = {
- enable = true;
- package = psql_15;
- enableTCPIP = true;
- authentication = ''
- local all postgres peer map=postgres
- local all all peer map=root
- '';
- identMap = ''
- root root supabase_admin
- postgres postgres postgres
- '';
- ensureUsers = [
- {
- name = "supabase_admin";
- ensureClauses.superuser = true;
- }
- ];
- # pg_regress test for pgjwt rely on the "extensions" schema to be present
- initialScript = pkgs.writeText "init-postgres-with-schema" ''
- CREATE SCHEMA IF NOT EXISTS extensions;
- CREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA extensions;
- '';
- settings = (installedExtension "15").defaultSettings or { };
+ imports = [
+ (testLib.makeSupabaseTestConfig {
+ majorVersion = "15";
+ })
+ ];
+
+ specialisation.postgresql17.configuration = testLib.makeUpgradeSpecialisation {
+ fromMajorVersion = "15";
+ toMajorVersion = "17";
};
- networking.firewall.allowedTCPPorts = [ config.services.postgresql.settings.port ];
-
- specialisation.postgresql17.configuration = {
- services.postgresql = {
- package = lib.mkForce psql_17;
- settings = (installedExtension "17").defaultSettings or { };
- };
-
- systemd.services.postgresql-migrate = {
- serviceConfig = {
- Type = "oneshot";
- RemainAfterExit = true;
- User = "postgres";
- Group = "postgres";
- StateDirectory = "postgresql";
- WorkingDirectory = "${builtins.dirOf config.services.postgresql.dataDir}";
- };
- script =
- let
- oldPostgresql = psql_15;
- newPostgresql = psql_17;
- oldDataDir = "${builtins.dirOf config.services.postgresql.dataDir}/${oldPostgresql.psqlSchema}";
- newDataDir = "${builtins.dirOf config.services.postgresql.dataDir}/${newPostgresql.psqlSchema}";
- in
- ''
- if [[ ! -d ${newDataDir} ]]; then
- install -d -m 0700 -o postgres -g postgres "${newDataDir}"
- ${newPostgresql}/bin/initdb -D "${newDataDir}"
- ${newPostgresql}/bin/pg_upgrade --old-datadir "${oldDataDir}" --new-datadir "${newDataDir}" \
- --old-bindir "${oldPostgresql}/bin" --new-bindir "${newPostgresql}/bin"
- else
- echo "${newDataDir} already exists"
- fi
- '';
- };
-
- systemd.services.postgresql = {
- after = [ "postgresql-migrate.service" ];
- requires = [ "postgresql-migrate.service" ];
- };
- };
+ specialisation.orioledb17.configuration = testLib.makeOrioledbSpecialisation { };
};
testScript =
{ nodes, ... }:
let
pg17-configuration = "${nodes.server.system.build.toplevel}/specialisation/postgresql17";
+ orioledb17-configuration = "${nodes.server.system.build.toplevel}/specialisation/orioledb17";
in
''
from pathlib import Path
versions = {
"15": [${lib.concatStringsSep ", " (map (s: ''"${s}"'') (versions "15"))}],
"17": [${lib.concatStringsSep ", " (map (s: ''"${s}"'') (versions "17"))}],
+ "orioledb-17": [${lib.concatStringsSep ", " (map (s: ''"${s}"'') orioledbVersions)}],
}
extension_name = "${pname}"
support_upgrade = False
pg17_configuration = "${pg17-configuration}"
- ext_has_background_worker = ${
- if (installedExtension "15") ? hasBackgroundWorker then "True" else "False"
- }
+ orioledb17_configuration = "${orioledb17-configuration}"
sql_test_directory = Path("${../../tests}")
- pg_regress_test_name = "${(installedExtension "15").pgRegressTestName or pname}"
${builtins.readFile ./lib.py}
start_all()
- server.wait_for_unit("multi-user.target")
- server.wait_for_unit("postgresql.service")
+ # Wait for full Supabase initialization (postgres + init-scripts + migrations)
+ server.wait_for_unit("supabase-db-init.service")
- test = PostgresExtensionTest(server, extension_name, versions, sql_test_directory, support_upgrade)
+ with subtest("Verify PostgreSQL 15 is our custom build"):
+ pg_version = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT version();\""
+ ).strip()
+ assert "${testLib.expectedVersions."15"}" in pg_version, (
+ f"Expected version ${testLib.expectedVersions."15"}, got: {pg_version}"
+ )
+
+ postgres_path = server.succeed("readlink -f $(which postgres)").strip()
+ assert "postgresql-and-plugins-${testLib.expectedVersions."15"}" in postgres_path, (
+ f"Expected our custom build (${testLib.expectedVersions."15"}), got: {postgres_path}"
+ )
+
+ with subtest("Verify ansible config loaded"):
+ spl = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SHOW shared_preload_libraries;\""
+ ).strip()
+ for ext in ["pg_stat_statements", "pgaudit", "pgsodium", "pg_cron", "pg_net"]:
+ assert ext in spl, f"Expected {ext} in shared_preload_libraries, got: {spl}"
+
+ session_pl = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SHOW session_preload_libraries;\""
+ ).strip()
+ assert "supautils" in session_pl, (
+ f"Expected supautils in session_preload_libraries, got: {session_pl}"
+ )
+
+ with subtest("Verify init scripts and migrations ran"):
+ roles = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT rolname FROM pg_roles ORDER BY rolname;\""
+ ).strip()
+ for role in ["anon", "authenticated", "authenticator", "dashboard_user", "pgbouncer", "service_role", "supabase_admin", "supabase_auth_admin", "supabase_storage_admin"]:
+ assert role in roles, f"Expected role {role} to exist, got: {roles}"
- if support_upgrade:
- with subtest("Check upgrade path with postgresql 15"):
- test.check_upgrade_path("15")
+ schemas = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT schema_name FROM information_schema.schemata ORDER BY schema_name;\""
+ ).strip()
+ for schema in ["auth", "storage", "extensions"]:
+ assert schema in schemas, f"Expected schema {schema} to exist, got: {schemas}"
- with subtest("Check pg_regress with postgresql 15 after extension upgrade"):
- # We need to uninstall the extension before running pg_regress
- test.drop_extension()
- test.check_pg_regress(Path("${psql_15}/lib/pgxs/src/test/regress/pg_regress"), "15", pg_regress_test_name)
+ test = PostgresExtensionTest(server, extension_name, versions, sql_test_directory, support_upgrade)
+
+ with subtest("Check upgrade path with postgresql 15"):
+ test.check_upgrade_path("15")
last_version = None
with subtest("Check the install of the last version of the extension"):
last_version = test.check_install_last_version("15")
- if ext_has_background_worker:
- with subtest("Test switch_${pname}_version"):
- test.check_switch_extension_with_background_worker(Path("${psql_15}/lib/${pname}.so"), "15")
-
- with subtest("Check pg_regress with postgresql 15 after installing the last version"):
- # We need to uninstall the extension before running pg_regress
- test.drop_extension()
- test.check_pg_regress(Path("${psql_15}/lib/pgxs/src/test/regress/pg_regress"), "15", pg_regress_test_name)
-
with subtest("switch to postgresql 17"):
server.succeed(
f"{pg17_configuration}/bin/switch-to-configuration test >&2"
)
+ server.wait_for_unit("postgresql.service")
+
+ with subtest("Verify PostgreSQL 17 is our custom build"):
+ pg_version = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT version();\""
+ ).strip()
+ assert "${testLib.expectedVersions."17"}" in pg_version, (
+ f"Expected version ${testLib.expectedVersions."17"}, got: {pg_version}"
+ )
+
+ postgres_pid = server.succeed(
+ "head -1 /var/lib/postgresql/data-17/postmaster.pid"
+ ).strip()
+ postgres_path = server.succeed(
+ f"readlink -f /proc/{postgres_pid}/exe"
+ ).strip()
+ assert "postgresql-and-plugins-${testLib.expectedVersions."17"}" in postgres_path, (
+ f"Expected our custom build (${testLib.expectedVersions."17"}), got: {postgres_path}"
+ )
- with subtest("Check last version of the extension after postgresql upgrade"):
+ with subtest("Check last version of the extension after upgrade"):
test.assert_version_matches(last_version)
- if support_upgrade:
- with subtest("Check upgrade path with postgresql 17"):
- test.check_upgrade_path("17")
+ with subtest("Check upgrade path with postgresql 17"):
+ test.check_upgrade_path("17")
- with subtest("Check pg_regress with postgresql 17 after extension upgrade"):
- # We need to uninstall the extension before running pg_regress
- test.drop_extension()
- test.check_pg_regress(Path("${psql_17}/lib/pgxs/src/test/regress/pg_regress"), "17", pg_regress_test_name)
+ with subtest("switch to orioledb 17"):
+ server.succeed(
+ f"{orioledb17_configuration}/bin/switch-to-configuration test >&2"
+ )
+ server.wait_for_unit("supabase-db-init.service")
+
+ with subtest("Verify OrioleDB is running"):
+ installed_extensions = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT extname FROM pg_extension WHERE extname = 'orioledb';\""
+ ).strip()
+ assert "orioledb" in installed_extensions, (
+ f"Expected orioledb extension to be installed, got: {installed_extensions}"
+ )
- with subtest("Check the install of the last version of the extension"):
- test.check_install_last_version("17")
+ dam = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SHOW default_table_access_method;\""
+ ).strip()
+ assert dam == "orioledb", (
+ f"Expected default_table_access_method = orioledb, got: {dam}"
+ )
+
+ with subtest("Verify OrioleDB init scripts and migrations ran"):
+ roles = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT rolname FROM pg_roles ORDER BY rolname;\""
+ ).strip()
+ for role in ["anon", "authenticated", "authenticator", "supabase_admin"]:
+ assert role in roles, f"Expected role {role} to exist, got: {roles}"
- with subtest("Check pg_regress with postgresql 17 after installing the last version"):
- # We need to uninstall the extension before running pg_regress
- test.drop_extension()
- test.check_pg_regress(Path("${psql_17}/lib/pgxs/src/test/regress/pg_regress"), "17", pg_regress_test_name)
+ with subtest("Check upgrade path with orioledb 17"):
+ test.check_upgrade_path("orioledb-17")
'';
}
diff --git a/nix/ext/tests/pgmq.nix b/nix/ext/tests/pgmq.nix
index eac0c350..ed71fe3a 100644
--- a/nix/ext/tests/pgmq.nix
+++ b/nix/ext/tests/pgmq.nix
@@ -2,185 +2,166 @@
let
pname = "pgmq";
inherit (pkgs) lib;
+ system = pkgs.pkgsLinux.stdenv.hostPlatform.system;
+ testLib = import ./lib.nix { inherit self pkgs; };
+
installedExtension =
- postgresMajorVersion:
- self.legacyPackages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}."psql_${postgresMajorVersion}".exts."${
- pname
- }";
+ postgresMajorVersion: self.legacyPackages.${system}."psql_${postgresMajorVersion}".exts."${pname}";
versions = postgresqlMajorVersion: (installedExtension postgresqlMajorVersion).versions;
- postgresqlWithExtension =
- postgresql:
- let
- majorVersion = lib.versions.major postgresql.version;
- pkg = pkgs.pkgsLinux.buildEnv {
- name = "postgresql-${majorVersion}-${pname}";
- paths = [
- postgresql
- postgresql.lib
- (installedExtension majorVersion)
- ];
- passthru = {
- inherit (postgresql) version psqlSchema;
- installedExtensions = [ (installedExtension majorVersion) ];
- lib = pkg;
- withPackages = _: pkg;
- withJIT = pkg;
- withoutJIT = pkg;
- };
- nativeBuildInputs = [ pkgs.pkgsLinux.makeWrapper ];
- pathsToLink = [
- "/"
- "/bin"
- "/lib"
- ];
- postBuild = ''
- wrapProgram $out/bin/postgres --set NIX_PGLIBDIR $out/lib
- wrapProgram $out/bin/pg_ctl --set NIX_PGLIBDIR $out/lib
- wrapProgram $out/bin/pg_upgrade --set NIX_PGLIBDIR $out/lib
- '';
- };
- in
- pkg;
- psql_15 =
- postgresqlWithExtension
- self.packages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.postgresql_15;
- psql_17 =
- postgresqlWithExtension
- self.packages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.postgresql_17;
+ orioledbVersions = self.legacyPackages.${system}."psql_orioledb-17".exts."${pname}".versions;
in
pkgs.testers.runNixOSTest {
- name = "pgmq";
+ name = pname;
nodes.server =
- { config, ... }:
+ { ... }:
{
- services.postgresql = {
- enable = true;
- package = psql_15;
- authentication = ''
- local all postgres peer map=postgres
- local all all peer map=root
- '';
- identMap = ''
- root root supabase_admin
- postgres postgres postgres
- '';
- ensureUsers = [
- {
- name = "supabase_admin";
- ensureClauses.superuser = true;
- }
- ];
- settings = (installedExtension "15").defaultSettings or { };
+ imports = [
+ (testLib.makeSupabaseTestConfig {
+ majorVersion = "15";
+ })
+ ];
+
+ specialisation.postgresql17.configuration = testLib.makeUpgradeSpecialisation {
+ fromMajorVersion = "15";
+ toMajorVersion = "17";
};
- specialisation.postgresql17.configuration = {
- services.postgresql = {
- package = lib.mkForce psql_17;
- };
-
- systemd.services.postgresql-migrate = {
- serviceConfig = {
- Type = "oneshot";
- RemainAfterExit = true;
- User = "postgres";
- Group = "postgres";
- StateDirectory = "postgresql";
- WorkingDirectory = "${builtins.dirOf config.services.postgresql.dataDir}";
- };
- script =
- let
- oldPostgresql = psql_15;
- newPostgresql = psql_17;
- oldDataDir = "${builtins.dirOf config.services.postgresql.dataDir}/${oldPostgresql.psqlSchema}";
- newDataDir = "${builtins.dirOf config.services.postgresql.dataDir}/${newPostgresql.psqlSchema}";
- in
- ''
- if [[ ! -d ${newDataDir} ]]; then
- install -d -m 0700 -o postgres -g postgres "${newDataDir}"
- ${newPostgresql}/bin/initdb -D "${newDataDir}"
- ${newPostgresql}/bin/pg_upgrade --old-datadir "${oldDataDir}" --new-datadir "${newDataDir}" \
- --old-bindir "${oldPostgresql}/bin" --new-bindir "${newPostgresql}/bin"
- else
- echo "${newDataDir} already exists"
- fi
- '';
- };
-
- systemd.services.postgresql = {
- after = [ "postgresql-migrate.service" ];
- requires = [ "postgresql-migrate.service" ];
- };
- };
+ specialisation.orioledb17.configuration = testLib.makeOrioledbSpecialisation { };
};
testScript =
{ nodes, ... }:
let
pg17-configuration = "${nodes.server.system.build.toplevel}/specialisation/postgresql17";
+ orioledb17-configuration = "${nodes.server.system.build.toplevel}/specialisation/orioledb17";
in
''
from pathlib import Path
versions = {
"15": [${lib.concatStringsSep ", " (map (s: ''"${s}"'') (versions "15"))}],
"17": [${lib.concatStringsSep ", " (map (s: ''"${s}"'') (versions "17"))}],
+ "orioledb-17": [${lib.concatStringsSep ", " (map (s: ''"${s}"'') orioledbVersions)}],
}
extension_name = "${pname}"
support_upgrade = True
pg17_configuration = "${pg17-configuration}"
- ext_has_background_worker = ${
- if (installedExtension "15") ? hasBackgroundWorker then "True" else "False"
- }
+ orioledb17_configuration = "${orioledb17-configuration}"
sql_test_directory = Path("${../../tests}")
- pg_regress_test_name = "${(installedExtension "15").pgRegressTestName or pname}"
${builtins.readFile ./lib.py}
start_all()
- server.wait_for_unit("multi-user.target")
- server.wait_for_unit("postgresql.service")
+ # Wait for full Supabase initialization (postgres + init-scripts + migrations)
+ server.wait_for_unit("supabase-db-init.service")
+
+ with subtest("Verify PostgreSQL 15 is our custom build"):
+ pg_version = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT version();\""
+ ).strip()
+ assert "${testLib.expectedVersions."15"}" in pg_version, (
+ f"Expected version ${testLib.expectedVersions."15"}, got: {pg_version}"
+ )
+
+ postgres_path = server.succeed("readlink -f $(which postgres)").strip()
+ assert "postgresql-and-plugins-${testLib.expectedVersions."15"}" in postgres_path, (
+ f"Expected our custom build (${testLib.expectedVersions."15"}), got: {postgres_path}"
+ )
+
+ with subtest("Verify ansible config loaded"):
+ spl = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SHOW shared_preload_libraries;\""
+ ).strip()
+ for ext in ["pg_stat_statements", "pgaudit", "pgsodium", "pg_cron", "pg_net"]:
+ assert ext in spl, f"Expected {ext} in shared_preload_libraries, got: {spl}"
+
+ session_pl = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SHOW session_preload_libraries;\""
+ ).strip()
+ assert "supautils" in session_pl, (
+ f"Expected supautils in session_preload_libraries, got: {session_pl}"
+ )
+
+ with subtest("Verify init scripts and migrations ran"):
+ roles = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT rolname FROM pg_roles ORDER BY rolname;\""
+ ).strip()
+ for role in ["anon", "authenticated", "authenticator", "dashboard_user", "pgbouncer", "service_role", "supabase_admin", "supabase_auth_admin", "supabase_storage_admin"]:
+ assert role in roles, f"Expected role {role} to exist, got: {roles}"
+
+ schemas = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT schema_name FROM information_schema.schemata ORDER BY schema_name;\""
+ ).strip()
+ for schema in ["auth", "storage", "extensions"]:
+ assert schema in schemas, f"Expected schema {schema} to exist, got: {schemas}"
test = PostgresExtensionTest(server, extension_name, versions, sql_test_directory, support_upgrade)
with subtest("Check upgrade path with postgresql 15"):
test.check_upgrade_path("15")
- test.run_sql_file("${../../../ansible/files/postgresql_extension_custom_scripts/pgmq/after-create.sql}")
-
- with subtest("Check pg_regress with postgresql 15 after extension upgrade"):
- test.check_pg_regress(Path("${psql_15}/lib/pgxs/src/test/regress/pg_regress"), "15", pg_regress_test_name)
last_version = None
with subtest("Check the install of the last version of the extension"):
last_version = test.check_install_last_version("15")
- test.run_sql_file("${../../../ansible/files/postgresql_extension_custom_scripts/pgmq/after-create.sql}")
-
- if ext_has_background_worker:
- with subtest("Test switch_${pname}_version"):
- test.check_switch_extension_with_background_worker(Path("${psql_15}/lib/${pname}.so"), "15")
-
- with subtest("Check pg_regress with postgresql 15 after installing the last version"):
- test.check_pg_regress(Path("${psql_15}/lib/pgxs/src/test/regress/pg_regress"), "15", pg_regress_test_name)
with subtest("switch to postgresql 17"):
server.succeed(
f"{pg17_configuration}/bin/switch-to-configuration test >&2"
)
+ server.wait_for_unit("postgresql.service")
+
+ with subtest("Verify PostgreSQL 17 is our custom build"):
+ pg_version = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT version();\""
+ ).strip()
+ assert "${testLib.expectedVersions."17"}" in pg_version, (
+ f"Expected version ${testLib.expectedVersions."17"}, got: {pg_version}"
+ )
+
+ postgres_pid = server.succeed(
+ "head -1 /var/lib/postgresql/data-17/postmaster.pid"
+ ).strip()
+ postgres_path = server.succeed(
+ f"readlink -f /proc/{postgres_pid}/exe"
+ ).strip()
+ assert "postgresql-and-plugins-${testLib.expectedVersions."17"}" in postgres_path, (
+ f"Expected our custom build (${testLib.expectedVersions."17"}), got: {postgres_path}"
+ )
- with subtest("Check last version of the extension after postgresql upgrade"):
+ with subtest("Check last version of the extension after upgrade"):
test.assert_version_matches(last_version)
- test.run_sql_file("${../../../ansible/files/postgresql_extension_custom_scripts/pgmq/after-create.sql}")
with subtest("Check upgrade path with postgresql 17"):
test.check_upgrade_path("17")
- test.run_sql_file("${../../../ansible/files/postgresql_extension_custom_scripts/pgmq/after-create.sql}")
- with subtest("Check pg_regress with postgresql 17 after extension upgrade"):
- test.check_pg_regress(Path("${psql_17}/lib/pgxs/src/test/regress/pg_regress"), "17", pg_regress_test_name)
+ with subtest("switch to orioledb 17"):
+ server.succeed(
+ f"{orioledb17_configuration}/bin/switch-to-configuration test >&2"
+ )
+ server.wait_for_unit("supabase-db-init.service")
+
+ with subtest("Verify OrioleDB is running"):
+ installed_extensions = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT extname FROM pg_extension WHERE extname = 'orioledb';\""
+ ).strip()
+ assert "orioledb" in installed_extensions, (
+ f"Expected orioledb extension to be installed, got: {installed_extensions}"
+ )
- with subtest("Check the install of the last version of the extension"):
- test.check_install_last_version("17")
- test.run_sql_file("${../../../ansible/files/postgresql_extension_custom_scripts/pgmq/after-create.sql}")
+ dam = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SHOW default_table_access_method;\""
+ ).strip()
+ assert dam == "orioledb", (
+ f"Expected default_table_access_method = orioledb, got: {dam}"
+ )
+
+ with subtest("Verify OrioleDB init scripts and migrations ran"):
+ roles = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT rolname FROM pg_roles ORDER BY rolname;\""
+ ).strip()
+ for role in ["anon", "authenticated", "authenticator", "supabase_admin"]:
+ assert role in roles, f"Expected role {role} to exist, got: {roles}"
- with subtest("Check pg_regress with postgresql 17 after installing the last version"):
- test.check_pg_regress(Path("${psql_17}/lib/pgxs/src/test/regress/pg_regress"), "17", pg_regress_test_name)
+ with subtest("Check upgrade path with orioledb 17"):
+ test.check_upgrade_path("orioledb-17")
'';
}
diff --git a/nix/ext/tests/pgroonga.nix b/nix/ext/tests/pgroonga.nix
index ee9c505c..b0c6e38f 100644
--- a/nix/ext/tests/pgroonga.nix
+++ b/nix/ext/tests/pgroonga.nix
@@ -2,190 +2,172 @@
let
pname = "pgroonga";
inherit (pkgs) lib;
+ system = pkgs.pkgsLinux.stdenv.hostPlatform.system;
+ testLib = import ./lib.nix { inherit self pkgs; };
+
installedExtension =
- postgresMajorVersion:
- self.legacyPackages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}."psql_${postgresMajorVersion}".exts."${
- pname
- }";
+ postgresMajorVersion: self.legacyPackages.${system}."psql_${postgresMajorVersion}".exts."${pname}";
versions = postgresqlMajorVersion: (installedExtension postgresqlMajorVersion).versions;
- postgresqlWithExtension =
- postgresql:
- let
- majorVersion = lib.versions.major postgresql.version;
- pkg = pkgs.pkgsLinux.buildEnv {
- name = "postgresql-${majorVersion}-${pname}";
- paths = [
- postgresql
- postgresql.lib
- (installedExtension majorVersion)
- ];
- passthru = {
- inherit (postgresql) version psqlSchema;
- installedExtensions = [ (installedExtension majorVersion) ];
- lib = pkg;
- withPackages = _: pkg;
- withJIT = pkg;
- withoutJIT = pkg;
- };
- nativeBuildInputs = [ pkgs.pkgsLinux.makeWrapper ];
- pathsToLink = [
- "/"
- "/bin"
- "/lib"
- ];
- postBuild = ''
- wrapProgram $out/bin/postgres --set NIX_PGLIBDIR $out/lib
- wrapProgram $out/bin/pg_ctl --set NIX_PGLIBDIR $out/lib
- wrapProgram $out/bin/pg_upgrade --set NIX_PGLIBDIR $out/lib
- '';
- };
- in
- pkg;
- psql_15 =
- postgresqlWithExtension
- self.packages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.postgresql_15;
- psql_17 =
- postgresqlWithExtension
- self.packages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.postgresql_17;
+ orioledbVersions = self.legacyPackages.${system}."psql_orioledb-17".exts."${pname}".versions;
in
pkgs.testers.runNixOSTest {
name = pname;
nodes.server =
- { config, ... }:
+ { ... }:
{
- services.openssh = {
- enable = true;
- };
+ imports = [
+ (testLib.makeSupabaseTestConfig {
+ majorVersion = "15";
+ })
+ ];
- services.postgresql = {
- enable = true;
- package = psql_15;
- enableTCPIP = true;
- authentication = ''
- local all postgres peer map=postgres
- local all all peer map=root
- '';
- identMap = ''
- root root supabase_admin
- postgres postgres postgres
- '';
- ensureUsers = [
- {
- name = "supabase_admin";
- ensureClauses.superuser = true;
- }
- ];
- };
+ # pgroonga needs mecab environment variables
systemd.services.postgresql.environment.MECAB_DICDIR = "${
- self.packages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.mecab-naist-jdic
+ self.packages.${system}.mecab-naist-jdic
}/lib/mecab/dic/naist-jdic";
systemd.services.postgresql.environment.MECAB_CONFIG = "${pkgs.pkgsLinux.mecab}/bin/mecab-config";
- systemd.services.postgresql.environment.GRN_PLUGINS_DIR = "${
- self.packages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.supabase-groonga
- }/lib/groonga/plugins";
-
- specialisation.postgresql17.configuration = {
- services.postgresql = {
- package = lib.mkForce psql_17;
- };
-
- systemd.services.postgresql-migrate = {
- serviceConfig = {
- Type = "oneshot";
- RemainAfterExit = true;
- User = "postgres";
- Group = "postgres";
- StateDirectory = "postgresql";
- WorkingDirectory = "${builtins.dirOf config.services.postgresql.dataDir}";
- };
- script =
- let
- oldPostgresql = psql_15;
- newPostgresql = psql_17;
- oldDataDir = "${builtins.dirOf config.services.postgresql.dataDir}/${oldPostgresql.psqlSchema}";
- newDataDir = "${builtins.dirOf config.services.postgresql.dataDir}/${newPostgresql.psqlSchema}";
- in
- ''
- if [[ ! -d ${newDataDir} ]]; then
- install -d -m 0700 -o postgres -g postgres "${newDataDir}"
- ${newPostgresql}/bin/initdb -D "${newDataDir}"
- ${newPostgresql}/bin/pg_upgrade --old-datadir "${oldDataDir}" --new-datadir "${newDataDir}" \
- --old-bindir "${oldPostgresql}/bin" --new-bindir "${newPostgresql}/bin"
- else
- echo "${newDataDir} already exists"
- fi
- '';
- };
-
- systemd.services.postgresql = {
- after = [ "postgresql-migrate.service" ];
- requires = [ "postgresql-migrate.service" ];
- };
+
+ specialisation.postgresql17.configuration = testLib.makeUpgradeSpecialisation {
+ fromMajorVersion = "15";
+ toMajorVersion = "17";
};
+
+ specialisation.orioledb17.configuration = testLib.makeOrioledbSpecialisation { };
};
testScript =
{ nodes, ... }:
let
pg17-configuration = "${nodes.server.system.build.toplevel}/specialisation/postgresql17";
+ orioledb17-configuration = "${nodes.server.system.build.toplevel}/specialisation/orioledb17";
in
''
from pathlib import Path
versions = {
"15": [${lib.concatStringsSep ", " (map (s: ''"${s}"'') (versions "15"))}],
"17": [${lib.concatStringsSep ", " (map (s: ''"${s}"'') (versions "17"))}],
+ "orioledb-17": [${lib.concatStringsSep ", " (map (s: ''"${s}"'') orioledbVersions)}],
}
extension_name = "${pname}"
+ support_upgrade = True
pg17_configuration = "${pg17-configuration}"
- ext_has_background_worker = ${
- if (installedExtension "15") ? hasBackgroundWorker then "True" else "False"
- }
+ orioledb17_configuration = "${orioledb17-configuration}"
sql_test_directory = Path("${../../tests}")
- pg_regress_test_name = "${(installedExtension "15").pgRegressTestName or pname}"
${builtins.readFile ./lib.py}
start_all()
- server.wait_for_unit("multi-user.target")
- server.wait_for_unit("postgresql.service")
+ # Wait for full Supabase initialization (postgres + init-scripts + migrations)
+ server.wait_for_unit("supabase-db-init.service")
+
+ with subtest("Verify PostgreSQL 15 is our custom build"):
+ pg_version = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT version();\""
+ ).strip()
+ assert "${testLib.expectedVersions."15"}" in pg_version, (
+ f"Expected version ${testLib.expectedVersions."15"}, got: {pg_version}"
+ )
+
+ postgres_path = server.succeed("readlink -f $(which postgres)").strip()
+ assert "postgresql-and-plugins-${testLib.expectedVersions."15"}" in postgres_path, (
+ f"Expected our custom build (${testLib.expectedVersions."15"}), got: {postgres_path}"
+ )
+
+ with subtest("Verify ansible config loaded"):
+ spl = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SHOW shared_preload_libraries;\""
+ ).strip()
+ for ext in ["pg_stat_statements", "pgaudit", "pgsodium", "pg_cron", "pg_net"]:
+ assert ext in spl, f"Expected {ext} in shared_preload_libraries, got: {spl}"
+
+ session_pl = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SHOW session_preload_libraries;\""
+ ).strip()
+ assert "supautils" in session_pl, (
+ f"Expected supautils in session_preload_libraries, got: {session_pl}"
+ )
- test = PostgresExtensionTest(server, extension_name, versions, sql_test_directory)
+ with subtest("Verify init scripts and migrations ran"):
+ roles = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT rolname FROM pg_roles ORDER BY rolname;\""
+ ).strip()
+ for role in ["anon", "authenticated", "authenticator", "dashboard_user", "pgbouncer", "service_role", "supabase_admin", "supabase_auth_admin", "supabase_storage_admin"]:
+ assert role in roles, f"Expected role {role} to exist, got: {roles}"
+
+ schemas = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT schema_name FROM information_schema.schemata ORDER BY schema_name;\""
+ ).strip()
+ for schema in ["auth", "storage", "extensions"]:
+ assert schema in schemas, f"Expected schema {schema} to exist, got: {schemas}"
+
+ test = PostgresExtensionTest(server, extension_name, versions, sql_test_directory, support_upgrade)
with subtest("Check upgrade path with postgresql 15"):
test.check_upgrade_path("15")
- with subtest("Check pg_regress with postgresql 15 after extension upgrade"):
- test.check_pg_regress(Path("${psql_15}/lib/pgxs/src/test/regress/pg_regress"), "15", pg_regress_test_name)
-
last_version = None
with subtest("Check the install of the last version of the extension"):
last_version = test.check_install_last_version("15")
- if ext_has_background_worker:
- with subtest("Test switch_${pname}_version"):
- test.check_switch_extension_with_background_worker(Path("${psql_15}/lib/${pname}.so"), "15")
-
- with subtest("Check pg_regress with postgresql 15 after installing the last version"):
- test.check_pg_regress(Path("${psql_15}/lib/pgxs/src/test/regress/pg_regress"), "15", pg_regress_test_name)
-
with subtest("switch to postgresql 17"):
server.succeed(
f"{pg17_configuration}/bin/switch-to-configuration test >&2"
)
+ server.wait_for_unit("postgresql.service")
+
+ with subtest("Verify PostgreSQL 17 is our custom build"):
+ pg_version = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT version();\""
+ ).strip()
+ assert "${testLib.expectedVersions."17"}" in pg_version, (
+ f"Expected version ${testLib.expectedVersions."17"}, got: {pg_version}"
+ )
+
+ postgres_pid = server.succeed(
+ "head -1 /var/lib/postgresql/data-17/postmaster.pid"
+ ).strip()
+ postgres_path = server.succeed(
+ f"readlink -f /proc/{postgres_pid}/exe"
+ ).strip()
+ assert "postgresql-and-plugins-${testLib.expectedVersions."17"}" in postgres_path, (
+ f"Expected our custom build (${testLib.expectedVersions."17"}), got: {postgres_path}"
+ )
- with subtest("Check last version of the extension after postgresql upgrade"):
+ with subtest("Check last version of the extension after upgrade"):
test.assert_version_matches(last_version)
with subtest("Check upgrade path with postgresql 17"):
test.check_upgrade_path("17")
- with subtest("Check pg_regress with postgresql 17 after extension upgrade"):
- test.check_pg_regress(Path("${psql_17}/lib/pgxs/src/test/regress/pg_regress"), "17", pg_regress_test_name)
+ with subtest("switch to orioledb 17"):
+ server.succeed(
+ f"{orioledb17_configuration}/bin/switch-to-configuration test >&2"
+ )
+ server.wait_for_unit("supabase-db-init.service")
+
+ with subtest("Verify OrioleDB is running"):
+ installed_extensions = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT extname FROM pg_extension WHERE extname = 'orioledb';\""
+ ).strip()
+ assert "orioledb" in installed_extensions, (
+ f"Expected orioledb extension to be installed, got: {installed_extensions}"
+ )
- with subtest("Check the install of the last version of the extension"):
- test.check_install_last_version("17")
+ dam = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SHOW default_table_access_method;\""
+ ).strip()
+ assert dam == "orioledb", (
+ f"Expected default_table_access_method = orioledb, got: {dam}"
+ )
+
+ with subtest("Verify OrioleDB init scripts and migrations ran"):
+ roles = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT rolname FROM pg_roles ORDER BY rolname;\""
+ ).strip()
+ for role in ["anon", "authenticated", "authenticator", "supabase_admin"]:
+ assert role in roles, f"Expected role {role} to exist, got: {roles}"
- with subtest("Check pg_regress with postgresql 17 after installing the last version"):
- test.check_pg_regress(Path("${psql_17}/lib/pgxs/src/test/regress/pg_regress"), "17", pg_regress_test_name)
+ with subtest("Check upgrade path with orioledb 17"):
+ test.check_upgrade_path("orioledb-17")
'';
}
diff --git a/nix/ext/tests/pgrouting.nix b/nix/ext/tests/pgrouting.nix
index 845246d8..f6fee222 100644
--- a/nix/ext/tests/pgrouting.nix
+++ b/nix/ext/tests/pgrouting.nix
@@ -2,167 +2,31 @@
let
pname = "pgrouting";
inherit (pkgs) lib;
+ system = pkgs.pkgsLinux.stdenv.hostPlatform.system;
+ testLib = import ./lib.nix { inherit self pkgs; };
+
installedExtension =
- postgresMajorVersion:
- self.legacyPackages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}."psql_${postgresMajorVersion}".exts."${
- pname
- }";
+ postgresMajorVersion: self.legacyPackages.${system}."psql_${postgresMajorVersion}".exts."${pname}";
versions = postgresqlMajorVersion: (installedExtension postgresqlMajorVersion).versions;
- postgresqlWithExtension =
- postgresql:
- let
- majorVersion = lib.versions.major postgresql.version;
- pkg = pkgs.pkgsLinux.buildEnv {
- name = "postgresql-${majorVersion}-${pname}";
- paths = [
- postgresql
- postgresql.lib
- (installedExtension majorVersion)
- (self.legacyPackages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}."psql_${majorVersion}".exts.postgis
- )
- ]
- ++ lib.optional (postgresql.isOrioleDB) (
- self.legacyPackages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}."psql_orioledb-17".exts.orioledb
- );
- passthru = {
- inherit (postgresql) version psqlSchema;
- installedExtensions = [ (installedExtension majorVersion) ];
- lib = pkg;
- withPackages = _: pkg;
- withJIT = pkg;
- withoutJIT = pkg;
- };
- nativeBuildInputs = [ pkgs.pkgsLinux.makeWrapper ];
- pathsToLink = [
- "/"
- "/bin"
- "/lib"
- ];
- postBuild = ''
- wrapProgram $out/bin/postgres --set NIX_PGLIBDIR $out/lib
- wrapProgram $out/bin/pg_ctl --set NIX_PGLIBDIR $out/lib
- wrapProgram $out/bin/pg_upgrade --set NIX_PGLIBDIR $out/lib
- '';
- };
- in
- pkg;
- pg_regress = pkgs.callPackage ../pg_regress.nix {
- postgresql = self.packages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.postgresql_15;
- };
+ orioledbVersions = self.legacyPackages.${system}."psql_orioledb-17".exts."${pname}".versions;
in
pkgs.testers.runNixOSTest {
name = pname;
nodes.server =
- { config, ... }:
+ { ... }:
{
- services.openssh = {
- enable = true;
- };
- users.users.root.openssh.authorizedKeys.keys = [
- "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAIIo+ulCUfJjnCVgfM4946Ih5Nm8DeZZiayYeABHGPEl7 jfroche"
+ imports = [
+ (testLib.makeSupabaseTestConfig {
+ majorVersion = "15";
+ })
];
- services.postgresql = {
- enable = true;
- package =
- postgresqlWithExtension
- self.packages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.postgresql_15;
- };
-
- specialisation.postgresql17.configuration = {
- services.postgresql = {
- package = lib.mkForce (
- postgresqlWithExtension self.packages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.postgresql_17
- );
- };
-
- systemd.services.postgresql-migrate = {
- serviceConfig = {
- Type = "oneshot";
- RemainAfterExit = true;
- User = "postgres";
- Group = "postgres";
- StateDirectory = "postgresql";
- WorkingDirectory = "${builtins.dirOf config.services.postgresql.dataDir}";
- };
- script =
- let
- oldPostgresql =
- postgresqlWithExtension
- self.packages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.postgresql_15;
- newPostgresql =
- postgresqlWithExtension
- self.packages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.postgresql_17;
- oldDataDir = "${builtins.dirOf config.services.postgresql.dataDir}/${oldPostgresql.psqlSchema}";
- newDataDir = "${builtins.dirOf config.services.postgresql.dataDir}/${newPostgresql.psqlSchema}";
- in
- ''
- if [[ ! -d ${newDataDir} ]]; then
- install -d -m 0700 -o postgres -g postgres "${newDataDir}"
- ${newPostgresql}/bin/initdb -D "${newDataDir}"
- ${newPostgresql}/bin/pg_upgrade --old-datadir "${oldDataDir}" --new-datadir "${newDataDir}" \
- --old-bindir "${oldPostgresql}/bin" --new-bindir "${newPostgresql}/bin"
- else
- echo "${newDataDir} already exists"
- fi
- '';
- };
-
- systemd.services.postgresql = {
- after = [ "postgresql-migrate.service" ];
- requires = [ "postgresql-migrate.service" ];
- };
+ specialisation.postgresql17.configuration = testLib.makeUpgradeSpecialisation {
+ fromMajorVersion = "15";
+ toMajorVersion = "17";
};
- specialisation.orioledb17.configuration = {
- services.postgresql = {
- package = lib.mkForce (
- postgresqlWithExtension
- self.packages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.postgresql_orioledb-17
- );
- settings = {
- shared_preload_libraries = "orioledb";
- default_table_access_method = "orioledb";
- };
- initdbArgs = [
- "--allow-group-access"
- "--locale-provider=icu"
- "--encoding=UTF-8"
- "--icu-locale=en_US.UTF-8"
- ];
- initialScript = pkgs.writeText "init-postgres-with-orioledb" ''
- CREATE EXTENSION orioledb CASCADE;
- '';
- };
-
- systemd.services.postgresql-migrate = {
- # we don't support migrating from postgresql 17 to orioledb-17 so we just reinit the datadir
- serviceConfig = {
- Type = "oneshot";
- RemainAfterExit = true;
- User = "postgres";
- Group = "postgres";
- StateDirectory = "postgresql";
- WorkingDirectory = "${builtins.dirOf config.services.postgresql.dataDir}";
- };
- script =
- let
- newPostgresql =
- postgresqlWithExtension
- self.packages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.postgresql_orioledb-17;
- in
- ''
- set -x
- systemctl cat postgresql.service
- rm -rf ${builtins.dirOf config.services.postgresql.dataDir}/${newPostgresql.psqlSchema}
- '';
- };
-
- systemd.services.postgresql = {
- after = [ "postgresql-migrate.service" ];
- requires = [ "postgresql-migrate.service" ];
- };
- };
+ specialisation.orioledb17.configuration = testLib.makeOrioledbSpecialisation { };
};
testScript =
{ nodes, ... }:
@@ -171,69 +35,139 @@ pkgs.testers.runNixOSTest {
orioledb17-configuration = "${nodes.server.system.build.toplevel}/specialisation/orioledb17";
in
''
+ from pathlib import Path
versions = {
"15": [${lib.concatStringsSep ", " (map (s: ''"${s}"'') (versions "15"))}],
"17": [${lib.concatStringsSep ", " (map (s: ''"${s}"'') (versions "17"))}],
- "orioledb-17": [${lib.concatStringsSep ", " (map (s: ''"${s}"'') (versions "orioledb-17"))}],
+ "orioledb-17": [${lib.concatStringsSep ", " (map (s: ''"${s}"'') orioledbVersions)}],
}
+ extension_name = "${pname}"
+ support_upgrade = True
+ pg17_configuration = "${pg17-configuration}"
+ orioledb17_configuration = "${orioledb17-configuration}"
+ sql_test_directory = Path("${../../tests}")
- def run_sql(query):
- return server.succeed(f"""sudo -u postgres psql -t -A -F\",\" -c \"{query}\" """).strip()
-
- def run_pg_regress(sql_file, pg_version):
- try:
- server.succeed(f"""sudo -u postgres ${pg_regress}/bin/pg_regress --inputdir=${../../tests} --use-existing --dbname=postgres --outputdir=/tmp/regression_output_{pg_version} "{sql_file}" """)
- except:
- server.copy_from_vm(f"/tmp/regression_output_{pg_version}", "")
- raise
-
- def check_upgrade_path(pg_version):
- with subtest("Check ${pname} upgrade path"):
- firstVersion = versions[pg_version][0]
- server.succeed("sudo -u postgres psql -c 'DROP EXTENSION IF EXISTS ${pname};'")
- run_sql(f"""CREATE EXTENSION ${pname} WITH VERSION '{firstVersion}' CASCADE;""")
- installed_version = run_sql(r"""SELECT extversion FROM pg_extension WHERE extname = '${pname}';""")
- assert installed_version == firstVersion, f"Expected ${pname} version {firstVersion}, but found {installed_version}"
- for version in versions[pg_version][1:]:
- run_sql(f"""ALTER EXTENSION ${pname} UPDATE TO '{version}';""")
- installed_version = run_sql(r"""SELECT extversion FROM pg_extension WHERE extname = '${pname}';""")
- assert installed_version == version, f"Expected ${pname} version {version}, but found {installed_version}"
- run_pg_regress("${pname}", pg_version)
+ ${builtins.readFile ./lib.py}
start_all()
- server.wait_for_unit("multi-user.target")
- server.wait_for_unit("postgresql.service")
+ # Wait for full Supabase initialization (postgres + init-scripts + migrations)
+ server.wait_for_unit("supabase-db-init.service")
+
+ with subtest("Verify PostgreSQL 15 is our custom build"):
+ pg_version = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT version();\""
+ ).strip()
+ assert "${testLib.expectedVersions."15"}" in pg_version, (
+ f"Expected version ${testLib.expectedVersions."15"}, got: {pg_version}"
+ )
+
+ postgres_path = server.succeed("readlink -f $(which postgres)").strip()
+ assert "postgresql-and-plugins-${testLib.expectedVersions."15"}" in postgres_path, (
+ f"Expected our custom build (${testLib.expectedVersions."15"}), got: {postgres_path}"
+ )
+
+ with subtest("Verify ansible config loaded"):
+ spl = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SHOW shared_preload_libraries;\""
+ ).strip()
+ for ext in ["pg_stat_statements", "pgaudit", "pgsodium", "pg_cron", "pg_net"]:
+ assert ext in spl, f"Expected {ext} in shared_preload_libraries, got: {spl}"
+
+ session_pl = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SHOW session_preload_libraries;\""
+ ).strip()
+ assert "supautils" in session_pl, (
+ f"Expected supautils in session_preload_libraries, got: {session_pl}"
+ )
+
+ with subtest("Verify init scripts and migrations ran"):
+ roles = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT rolname FROM pg_roles ORDER BY rolname;\""
+ ).strip()
+ for role in ["anon", "authenticated", "authenticator", "dashboard_user", "pgbouncer", "service_role", "supabase_admin", "supabase_auth_admin", "supabase_storage_admin"]:
+ assert role in roles, f"Expected role {role} to exist, got: {roles}"
- check_upgrade_path("15")
+ schemas = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT schema_name FROM information_schema.schemata ORDER BY schema_name;\""
+ ).strip()
+ for schema in ["auth", "storage", "extensions"]:
+ assert schema in schemas, f"Expected schema {schema} to exist, got: {schemas}"
- with subtest("Check ${pname} latest extension version"):
- server.succeed("sudo -u postgres psql -c 'DROP EXTENSION ${pname};'")
- server.succeed("sudo -u postgres psql -c 'CREATE EXTENSION ${pname} CASCADE;'")
- installed_extensions=run_sql(r"""SELECT extname, extversion FROM pg_extension;""")
- latestVersion = versions["15"][-1]
- assert f"${pname},{latestVersion}" in installed_extensions
+ test = PostgresExtensionTest(server, extension_name, versions, sql_test_directory, support_upgrade)
+
+ with subtest("Check upgrade path with postgresql 15"):
+ test.check_upgrade_path("15")
+
+ last_version = None
+ with subtest("Check the install of the last version of the extension"):
+ last_version = test.check_install_last_version("15")
with subtest("switch to postgresql 17"):
server.succeed(
- "${pg17-configuration}/bin/switch-to-configuration test >&2"
+ f"{pg17_configuration}/bin/switch-to-configuration test >&2"
+ )
+ server.wait_for_unit("postgresql.service")
+
+ with subtest("Verify PostgreSQL 17 is our custom build"):
+ pg_version = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT version();\""
+ ).strip()
+ assert "${testLib.expectedVersions."17"}" in pg_version, (
+ f"Expected version ${testLib.expectedVersions."17"}, got: {pg_version}"
)
- with subtest("Check ${pname} latest extension version after upgrade"):
- installed_extensions=run_sql(r"""SELECT extname, extversion FROM pg_extension;""")
- latestVersion = versions["17"][-1]
- assert f"${pname},{latestVersion}" in installed_extensions
+ # After specialisation switch, 'which postgres' still resolves to PG 15
+ # (both packages in systemPackages; can't mkForce without breaking D-Bus).
+ # The postgres binary is also wrapped (.postgres-wrapped) so pgrep -ox fails.
+ # Instead, get the postmaster PID from the PG 17 data directory's pid file.
+ postgres_pid = server.succeed(
+ "head -1 /var/lib/postgresql/data-17/postmaster.pid"
+ ).strip()
+ postgres_path = server.succeed(
+ f"readlink -f /proc/{postgres_pid}/exe"
+ ).strip()
+ assert "postgresql-and-plugins-${testLib.expectedVersions."17"}" in postgres_path, (
+ f"Expected our custom build (${testLib.expectedVersions."17"}), got: {postgres_path}"
+ )
+
+ with subtest("Check last version of the extension after upgrade"):
+ test.assert_version_matches(last_version)
- check_upgrade_path("17")
+ with subtest("Check upgrade path with postgresql 17"):
+ test.check_upgrade_path("17")
with subtest("switch to orioledb 17"):
server.succeed(
- "${orioledb17-configuration}/bin/switch-to-configuration test >&2"
+ f"{orioledb17_configuration}/bin/switch-to-configuration test >&2"
+ )
+ server.wait_for_unit("supabase-db-init.service")
+
+ with subtest("Verify OrioleDB is running"):
+ installed_extensions = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT extname FROM pg_extension WHERE extname = 'orioledb';\""
+ ).strip()
+ assert "orioledb" in installed_extensions, (
+ f"Expected orioledb extension to be installed, got: {installed_extensions}"
)
- installed_extensions=run_sql(r"""SELECT extname FROM pg_extension WHERE extname = 'orioledb';""")
- assert "orioledb" in installed_extensions
- check_upgrade_path("orioledb-17")
+ dam = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SHOW default_table_access_method;\""
+ ).strip()
+ assert dam == "orioledb", (
+ f"Expected default_table_access_method = orioledb, got: {dam}"
+ )
+
+ with subtest("Verify OrioleDB init scripts and migrations ran"):
+ roles = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT rolname FROM pg_roles ORDER BY rolname;\""
+ ).strip()
+ for role in ["anon", "authenticated", "authenticator", "supabase_admin"]:
+ assert role in roles, f"Expected role {role} to exist, got: {roles}"
+
+ with subtest("Check upgrade path with orioledb 17"):
+ test.check_upgrade_path("orioledb-17")
'';
}
-# We don't use the generic test for this extension because: it requires postgis to be installed as well.
+# pgrouting requires postgis; both are included in the psql_*/bin packages via lib.nix.
+# pgrouting supports in-place upgrades (ALTER EXTENSION ... UPDATE TO).
diff --git a/nix/ext/tests/pgsodium.nix b/nix/ext/tests/pgsodium.nix
index 4d296280..8578736d 100644
--- a/nix/ext/tests/pgsodium.nix
+++ b/nix/ext/tests/pgsodium.nix
@@ -1,162 +1,162 @@
+# pgsodium's supautils before-create.sql hook enforces that only the latest
+# version can be installed (e.g. "only pgsodium 3.1.8 is supported"), so
+# upgrade path testing is skipped — older versions cannot be created.
{ self, pkgs }:
let
pname = "pgsodium";
inherit (pkgs) lib;
+ system = pkgs.pkgsLinux.stdenv.hostPlatform.system;
+ testLib = import ./lib.nix { inherit self pkgs; };
+
installedExtension =
- postgresMajorVersion:
- self.legacyPackages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}."psql_${postgresMajorVersion}".exts."${
- pname
- }";
+ postgresMajorVersion: self.legacyPackages.${system}."psql_${postgresMajorVersion}".exts."${pname}";
versions = postgresqlMajorVersion: (installedExtension postgresqlMajorVersion).versions;
- postgresqlWithExtension =
- postgresql:
- let
- majorVersion = lib.versions.major postgresql.version;
- pkg = pkgs.pkgsLinux.buildEnv {
- name = "postgresql-${majorVersion}-${pname}";
- paths = [
- postgresql
- postgresql.lib
- (installedExtension majorVersion)
- (self.legacyPackages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}."psql_${majorVersion}".exts.hypopg
- )
- ];
- passthru = {
- inherit (postgresql) version psqlSchema;
- installedExtensions = [ (installedExtension majorVersion) ];
- lib = pkg;
- withPackages = _: pkg;
- withJIT = pkg;
- withoutJIT = pkg;
- };
- nativeBuildInputs = [ pkgs.pkgsLinux.makeWrapper ];
- pathsToLink = [
- "/"
- "/bin"
- "/lib"
- ];
- postBuild = ''
- wrapProgram $out/bin/postgres --set NIX_PGLIBDIR $out/lib
- wrapProgram $out/bin/pg_ctl --set NIX_PGLIBDIR $out/lib
- wrapProgram $out/bin/pg_upgrade --set NIX_PGLIBDIR $out/lib
- '';
- };
- in
- pkg;
- pgsodiumGetKey = lib.getExe (
- pkgs.writeShellScriptBin "pgsodium-getkey" ''
- echo 0000000000000000000000000000000000000000000000000000000000000000
- ''
- );
+ orioledbVersions = self.legacyPackages.${system}."psql_orioledb-17".exts."${pname}".versions;
in
pkgs.testers.runNixOSTest {
name = pname;
nodes.server =
- { config, ... }:
+ { ... }:
{
- services.postgresql = {
- enable = true;
- package =
- postgresqlWithExtension
- self.packages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.postgresql_15;
- settings = {
- "shared_preload_libraries" = pname;
- "pgsodium.getkey_script" = pgsodiumGetKey;
- };
- };
+ imports = [
+ (testLib.makeSupabaseTestConfig {
+ majorVersion = "15";
+ })
+ ];
- specialisation.postgresql17.configuration = {
- services.postgresql = {
- package = lib.mkForce (
- postgresqlWithExtension self.packages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.postgresql_17
- );
- };
-
- systemd.services.postgresql-migrate = {
- serviceConfig = {
- Type = "oneshot";
- RemainAfterExit = true;
- User = "postgres";
- Group = "postgres";
- StateDirectory = "postgresql";
- WorkingDirectory = "${builtins.dirOf config.services.postgresql.dataDir}";
- };
- script =
- let
- oldPostgresql =
- postgresqlWithExtension
- self.packages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.postgresql_15;
- newPostgresql =
- postgresqlWithExtension
- self.packages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.postgresql_17;
- oldDataDir = "${builtins.dirOf config.services.postgresql.dataDir}/${oldPostgresql.psqlSchema}";
- newDataDir = "${builtins.dirOf config.services.postgresql.dataDir}/${newPostgresql.psqlSchema}";
- in
- ''
- if [[ ! -d ${newDataDir} ]]; then
- install -d -m 0700 -o postgres -g postgres "${newDataDir}"
- ${newPostgresql}/bin/initdb -D "${newDataDir}"
- echo "shared_preload_libraries = '${pname}'" >> "${newDataDir}/postgresql.conf"
- echo "pgsodium.getkey_script = '${pgsodiumGetKey}'" >> "${newDataDir}/postgresql.conf";
- ${newPostgresql}/bin/pg_upgrade --old-datadir "${oldDataDir}" --new-datadir "${newDataDir}" \
- --old-bindir "${oldPostgresql}/bin" --new-bindir "${newPostgresql}/bin"
- else
- echo "${newDataDir} already exists"
- fi
- '';
- };
-
- systemd.services.postgresql = {
- after = [ "postgresql-migrate.service" ];
- requires = [ "postgresql-migrate.service" ];
- };
+ specialisation.postgresql17.configuration = testLib.makeUpgradeSpecialisation {
+ fromMajorVersion = "15";
+ toMajorVersion = "17";
};
+
+ specialisation.orioledb17.configuration = testLib.makeOrioledbSpecialisation { };
};
testScript =
{ nodes, ... }:
let
pg17-configuration = "${nodes.server.system.build.toplevel}/specialisation/postgresql17";
+ orioledb17-configuration = "${nodes.server.system.build.toplevel}/specialisation/orioledb17";
in
''
+ from pathlib import Path
versions = {
"15": [${lib.concatStringsSep ", " (map (s: ''"${s}"'') (versions "15"))}],
"17": [${lib.concatStringsSep ", " (map (s: ''"${s}"'') (versions "17"))}],
+ "orioledb-17": [${lib.concatStringsSep ", " (map (s: ''"${s}"'') orioledbVersions)}],
}
+ extension_name = "${pname}"
+ support_upgrade = False
+ pg17_configuration = "${pg17-configuration}"
+ orioledb17_configuration = "${orioledb17-configuration}"
+ sql_test_directory = Path("${../../tests}")
- def run_sql(query):
- return server.succeed(f"""sudo -u postgres psql -t -A -F\",\" -c \"{query}\" """).strip()
-
- def check_upgrade_path(pg_version):
- with subtest("Check ${pname} upgrade path"):
- firstVersion = versions[pg_version][0]
- server.succeed("sudo -u postgres psql -c 'DROP EXTENSION IF EXISTS ${pname};'")
- run_sql(f"""CREATE EXTENSION ${pname} WITH VERSION '{firstVersion}' CASCADE;""")
- installed_version = run_sql(r"""SELECT extversion FROM pg_extension WHERE extname = '${pname}';""")
- assert installed_version == firstVersion, f"Expected ${pname} version {firstVersion}, but found {installed_version}"
- for version in versions[pg_version][1:]:
- run_sql(f"""ALTER EXTENSION ${pname} UPDATE TO '{version}';""")
- installed_version = run_sql(r"""SELECT extversion FROM pg_extension WHERE extname = '${pname}';""")
- assert installed_version == version, f"Expected ${pname} version {version}, but found {installed_version}"
+ ${builtins.readFile ./lib.py}
start_all()
- server.wait_for_unit("multi-user.target")
- server.wait_for_unit("postgresql.service")
+ # Wait for full Supabase initialization (postgres + init-scripts + migrations)
+ server.wait_for_unit("supabase-db-init.service")
+
+ with subtest("Verify PostgreSQL 15 is our custom build"):
+ pg_version = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT version();\""
+ ).strip()
+ assert "${testLib.expectedVersions."15"}" in pg_version, (
+ f"Expected version ${testLib.expectedVersions."15"}, got: {pg_version}"
+ )
+
+ postgres_path = server.succeed("readlink -f $(which postgres)").strip()
+ assert "postgresql-and-plugins-${testLib.expectedVersions."15"}" in postgres_path, (
+ f"Expected our custom build (${testLib.expectedVersions."15"}), got: {postgres_path}"
+ )
+
+ with subtest("Verify ansible config loaded"):
+ spl = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SHOW shared_preload_libraries;\""
+ ).strip()
+ for ext in ["pg_stat_statements", "pgaudit", "pgsodium", "pg_cron", "pg_net"]:
+ assert ext in spl, f"Expected {ext} in shared_preload_libraries, got: {spl}"
+
+ session_pl = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SHOW session_preload_libraries;\""
+ ).strip()
+ assert "supautils" in session_pl, (
+ f"Expected supautils in session_preload_libraries, got: {session_pl}"
+ )
+
+ with subtest("Verify init scripts and migrations ran"):
+ roles = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT rolname FROM pg_roles ORDER BY rolname;\""
+ ).strip()
+ for role in ["anon", "authenticated", "authenticator", "dashboard_user", "pgbouncer", "service_role", "supabase_admin", "supabase_auth_admin", "supabase_storage_admin"]:
+ assert role in roles, f"Expected role {role} to exist, got: {roles}"
- check_upgrade_path("15")
+ schemas = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT schema_name FROM information_schema.schemata ORDER BY schema_name;\""
+ ).strip()
+ for schema in ["auth", "storage", "extensions"]:
+ assert schema in schemas, f"Expected schema {schema} to exist, got: {schemas}"
- with subtest("Check ${pname} latest extension version"):
- server.succeed("sudo -u postgres psql -c 'DROP EXTENSION ${pname};'")
- server.succeed("sudo -u postgres psql -c 'CREATE EXTENSION ${pname} CASCADE;'")
- installed_extensions=run_sql(r"""SELECT extname, extversion FROM pg_extension;""")
- latestVersion = versions["15"][-1]
- assert f"${pname},{latestVersion}" in installed_extensions
+ test = PostgresExtensionTest(server, extension_name, versions, sql_test_directory, support_upgrade)
+
+ last_version = None
+ with subtest("Check the install of the last version of the extension"):
+ last_version = test.check_install_last_version("15")
with subtest("switch to postgresql 17"):
server.succeed(
- "${pg17-configuration}/bin/switch-to-configuration test >&2"
+ f"{pg17_configuration}/bin/switch-to-configuration test >&2"
+ )
+ server.wait_for_unit("postgresql.service")
+
+ with subtest("Verify PostgreSQL 17 is our custom build"):
+ pg_version = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT version();\""
+ ).strip()
+ assert "${testLib.expectedVersions."17"}" in pg_version, (
+ f"Expected version ${testLib.expectedVersions."17"}, got: {pg_version}"
+ )
+
+ postgres_pid = server.succeed(
+ "head -1 /var/lib/postgresql/data-17/postmaster.pid"
+ ).strip()
+ postgres_path = server.succeed(
+ f"readlink -f /proc/{postgres_pid}/exe"
+ ).strip()
+ assert "postgresql-and-plugins-${testLib.expectedVersions."17"}" in postgres_path, (
+ f"Expected our custom build (${testLib.expectedVersions."17"}), got: {postgres_path}"
+ )
+
+ with subtest("Check last version of the extension after upgrade"):
+ test.assert_version_matches(last_version)
+
+ with subtest("switch to orioledb 17"):
+ server.succeed(
+ f"{orioledb17_configuration}/bin/switch-to-configuration test >&2"
+ )
+ server.wait_for_unit("supabase-db-init.service")
+
+ with subtest("Verify OrioleDB is running"):
+ installed_extensions = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT extname FROM pg_extension WHERE extname = 'orioledb';\""
+ ).strip()
+ assert "orioledb" in installed_extensions, (
+ f"Expected orioledb extension to be installed, got: {installed_extensions}"
)
- check_upgrade_path("17")
+ dam = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SHOW default_table_access_method;\""
+ ).strip()
+ assert dam == "orioledb", (
+ f"Expected default_table_access_method = orioledb, got: {dam}"
+ )
+
+ with subtest("Verify OrioleDB init scripts and migrations ran"):
+ roles = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT rolname FROM pg_roles ORDER BY rolname;\""
+ ).strip()
+ for role in ["anon", "authenticated", "authenticator", "supabase_admin"]:
+ assert role in roles, f"Expected role {role} to exist, got: {roles}"
+
'';
}
diff --git a/nix/ext/tests/plpgsql_check.nix b/nix/ext/tests/plpgsql_check.nix
index e4b381fe..5ebc83f5 100644
--- a/nix/ext/tests/plpgsql_check.nix
+++ b/nix/ext/tests/plpgsql_check.nix
@@ -1,183 +1,163 @@
+# plpgsql_check's shared library is loaded at server start and registers GUC
+# parameters. Installing older versions fails with "attempt to redefine
+# parameter" since the already-loaded .so has already registered them, so
+# upgrade path testing is skipped.
{ self, pkgs }:
let
pname = "plpgsql_check";
inherit (pkgs) lib;
+ system = pkgs.pkgsLinux.stdenv.hostPlatform.system;
+ testLib = import ./lib.nix { inherit self pkgs; };
+
installedExtension =
- postgresMajorVersion:
- self.legacyPackages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}."psql_${postgresMajorVersion}".exts."${
- pname
- }";
+ postgresMajorVersion: self.legacyPackages.${system}."psql_${postgresMajorVersion}".exts."${pname}";
versions = postgresqlMajorVersion: (installedExtension postgresqlMajorVersion).versions;
- postgresqlWithExtension =
- postgresql:
- let
- majorVersion = lib.versions.major postgresql.version;
- pkg = pkgs.pkgsLinux.buildEnv {
- name = "postgresql-${majorVersion}-${pname}";
- paths = [
- postgresql
- postgresql.lib
- (installedExtension majorVersion)
- ];
- passthru = {
- inherit (postgresql) version psqlSchema;
- installedExtensions = [ (installedExtension majorVersion) ];
- lib = pkg;
- withPackages = _: pkg;
- withJIT = pkg;
- withoutJIT = pkg;
- };
- nativeBuildInputs = [ pkgs.pkgsLinux.makeWrapper ];
- pathsToLink = [
- "/"
- "/bin"
- "/lib"
- ];
- postBuild = ''
- wrapProgram $out/bin/postgres --set NIX_PGLIBDIR $out/lib
- wrapProgram $out/bin/pg_ctl --set NIX_PGLIBDIR $out/lib
- wrapProgram $out/bin/pg_upgrade --set NIX_PGLIBDIR $out/lib
- '';
- };
- in
- pkg;
- psql_15 =
- postgresqlWithExtension
- self.packages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.postgresql_15;
- psql_17 =
- postgresqlWithExtension
- self.packages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.postgresql_17;
+ orioledbVersions = self.legacyPackages.${system}."psql_orioledb-17".exts."${pname}".versions;
in
pkgs.testers.runNixOSTest {
name = pname;
nodes.server =
- { config, ... }:
+ { ... }:
{
- services.openssh = {
- enable = true;
+ imports = [
+ (testLib.makeSupabaseTestConfig {
+ majorVersion = "15";
+ })
+ ];
+
+ specialisation.postgresql17.configuration = testLib.makeUpgradeSpecialisation {
+ fromMajorVersion = "15";
+ toMajorVersion = "17";
};
- services.postgresql = {
- enable = true;
- package = psql_15;
- enableTCPIP = true;
- authentication = ''
- local all postgres peer map=postgres
- local all all peer map=root
- '';
- identMap = ''
- root root supabase_admin
- postgres postgres postgres
- '';
- ensureUsers = [
- {
- name = "supabase_admin";
- ensureClauses.superuser = true;
- }
- ];
- settings = (installedExtension "15").defaultSettings or { };
- };
-
- networking.firewall.allowedTCPPorts = [ config.services.postgresql.settings.port ];
-
- specialisation.postgresql17.configuration = {
- services.postgresql = {
- package = lib.mkForce psql_17;
- };
-
- systemd.services.postgresql-migrate = {
- serviceConfig = {
- Type = "oneshot";
- RemainAfterExit = true;
- User = "postgres";
- Group = "postgres";
- StateDirectory = "postgresql";
- WorkingDirectory = "${builtins.dirOf config.services.postgresql.dataDir}";
- };
- script =
- let
- oldPostgresql = psql_15;
- newPostgresql = psql_17;
- oldDataDir = "${builtins.dirOf config.services.postgresql.dataDir}/${oldPostgresql.psqlSchema}";
- newDataDir = "${builtins.dirOf config.services.postgresql.dataDir}/${newPostgresql.psqlSchema}";
- in
- ''
- if [[ ! -d ${newDataDir} ]]; then
- install -d -m 0700 -o postgres -g postgres "${newDataDir}"
- ${newPostgresql}/bin/initdb -D "${newDataDir}"
- ${newPostgresql}/bin/pg_upgrade --old-datadir "${oldDataDir}" --new-datadir "${newDataDir}" \
- --old-bindir "${oldPostgresql}/bin" --new-bindir "${newPostgresql}/bin"
- else
- echo "${newDataDir} already exists"
- fi
- '';
- };
-
- systemd.services.postgresql = {
- after = [ "postgresql-migrate.service" ];
- requires = [ "postgresql-migrate.service" ];
- };
- };
+ specialisation.orioledb17.configuration = testLib.makeOrioledbSpecialisation { };
};
testScript =
{ nodes, ... }:
let
pg17-configuration = "${nodes.server.system.build.toplevel}/specialisation/postgresql17";
+ orioledb17-configuration = "${nodes.server.system.build.toplevel}/specialisation/orioledb17";
in
''
from pathlib import Path
versions = {
"15": [${lib.concatStringsSep ", " (map (s: ''"${s}"'') (versions "15"))}],
"17": [${lib.concatStringsSep ", " (map (s: ''"${s}"'') (versions "17"))}],
+ "orioledb-17": [${lib.concatStringsSep ", " (map (s: ''"${s}"'') orioledbVersions)}],
}
extension_name = "${pname}"
- support_upgrade = True
+ support_upgrade = False
pg17_configuration = "${pg17-configuration}"
- ext_has_background_worker = ${
- if (installedExtension "15") ? hasBackgroundWorker then "True" else "False"
- }
+ orioledb17_configuration = "${orioledb17-configuration}"
sql_test_directory = Path("${../../tests}")
- pg_regress_test_name = "${(installedExtension "15").pgRegressTestName or pname}"
${builtins.readFile ./lib.py}
start_all()
- server.wait_for_unit("multi-user.target")
- server.wait_for_unit("postgresql.service")
+ # Wait for full Supabase initialization (postgres + init-scripts + migrations)
+ server.wait_for_unit("supabase-db-init.service")
- test = PostgresExtensionTest(server, extension_name, versions, sql_test_directory, support_upgrade)
+ with subtest("Verify PostgreSQL 15 is our custom build"):
+ pg_version = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT version();\""
+ ).strip()
+ assert "${testLib.expectedVersions."15"}" in pg_version, (
+ f"Expected version ${testLib.expectedVersions."15"}, got: {pg_version}"
+ )
+
+ postgres_path = server.succeed("readlink -f $(which postgres)").strip()
+ assert "postgresql-and-plugins-${testLib.expectedVersions."15"}" in postgres_path, (
+ f"Expected our custom build (${testLib.expectedVersions."15"}), got: {postgres_path}"
+ )
+
+ with subtest("Verify ansible config loaded"):
+ spl = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SHOW shared_preload_libraries;\""
+ ).strip()
+ for ext in ["pg_stat_statements", "pgaudit", "pgsodium", "pg_cron", "pg_net"]:
+ assert ext in spl, f"Expected {ext} in shared_preload_libraries, got: {spl}"
+
+ session_pl = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SHOW session_preload_libraries;\""
+ ).strip()
+ assert "supautils" in session_pl, (
+ f"Expected supautils in session_preload_libraries, got: {session_pl}"
+ )
- if ext_has_background_worker:
- with subtest("Test switch_${pname}_version"):
- test.check_switch_extension_with_background_worker(Path("${psql_15}/lib/${pname}.so"), "15")
+ with subtest("Verify init scripts and migrations ran"):
+ roles = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT rolname FROM pg_roles ORDER BY rolname;\""
+ ).strip()
+ for role in ["anon", "authenticated", "authenticator", "dashboard_user", "pgbouncer", "service_role", "supabase_admin", "supabase_auth_admin", "supabase_storage_admin"]:
+ assert role in roles, f"Expected role {role} to exist, got: {roles}"
- with subtest("Check pg_regress with postgresql 15 after installing the last version"):
- test.check_pg_regress(Path("${psql_15}/lib/pgxs/src/test/regress/pg_regress"), "15", pg_regress_test_name)
+ schemas = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT schema_name FROM information_schema.schemata ORDER BY schema_name;\""
+ ).strip()
+ for schema in ["auth", "storage", "extensions"]:
+ assert schema in schemas, f"Expected schema {schema} to exist, got: {schemas}"
+
+ test = PostgresExtensionTest(server, extension_name, versions, sql_test_directory, support_upgrade)
+
+ last_version = None
+ with subtest("Check the install of the last version of the extension"):
+ last_version = test.check_install_last_version("15")
with subtest("switch to postgresql 17"):
server.succeed(
f"{pg17_configuration}/bin/switch-to-configuration test >&2"
)
+ server.wait_for_unit("postgresql.service")
+
+ with subtest("Verify PostgreSQL 17 is our custom build"):
+ pg_version = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT version();\""
+ ).strip()
+ assert "${testLib.expectedVersions."17"}" in pg_version, (
+ f"Expected version ${testLib.expectedVersions."17"}, got: {pg_version}"
+ )
- if ext_has_background_worker:
- with subtest("Test switch_${pname}_version"):
- test.check_switch_extension_with_background_worker(Path("${psql_17}/lib/${pname}.so"), "17")
-
- with subtest("Check upgrade path with postgresql 17"):
- test.check_upgrade_path("17")
+ postgres_pid = server.succeed(
+ "head -1 /var/lib/postgresql/data-17/postmaster.pid"
+ ).strip()
+ postgres_path = server.succeed(
+ f"readlink -f /proc/{postgres_pid}/exe"
+ ).strip()
+ assert "postgresql-and-plugins-${testLib.expectedVersions."17"}" in postgres_path, (
+ f"Expected our custom build (${testLib.expectedVersions."17"}), got: {postgres_path}"
+ )
- last_version = versions["17"][-1]
- with subtest("Check last version of the extension after postgresql upgrade"):
+ with subtest("Check last version of the extension after upgrade"):
test.assert_version_matches(last_version)
- with subtest("Check pg_regress with postgresql 17 after extension upgrade"):
- test.check_pg_regress(Path("${psql_17}/lib/pgxs/src/test/regress/pg_regress"), "17", pg_regress_test_name)
+ with subtest("switch to orioledb 17"):
+ server.succeed(
+ f"{orioledb17_configuration}/bin/switch-to-configuration test >&2"
+ )
+ server.wait_for_unit("supabase-db-init.service")
+
+ with subtest("Verify OrioleDB is running"):
+ installed_extensions = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT extname FROM pg_extension WHERE extname = 'orioledb';\""
+ ).strip()
+ assert "orioledb" in installed_extensions, (
+ f"Expected orioledb extension to be installed, got: {installed_extensions}"
+ )
- with subtest("Check the install of the last version of the extension"):
- test.check_install_last_version("17")
+ dam = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SHOW default_table_access_method;\""
+ ).strip()
+ assert dam == "orioledb", (
+ f"Expected default_table_access_method = orioledb, got: {dam}"
+ )
+
+ with subtest("Verify OrioleDB init scripts and migrations ran"):
+ roles = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT rolname FROM pg_roles ORDER BY rolname;\""
+ ).strip()
+ for role in ["anon", "authenticated", "authenticator", "supabase_admin"]:
+ assert role in roles, f"Expected role {role} to exist, got: {roles}"
- with subtest("Check pg_regress with postgresql 17 after installing the last version"):
- test.check_pg_regress(Path("${psql_17}/lib/pgxs/src/test/regress/pg_regress"), "17", pg_regress_test_name)
'';
}
diff --git a/nix/ext/tests/plv8.nix b/nix/ext/tests/plv8.nix
index 381cbcae..937b8f85 100644
--- a/nix/ext/tests/plv8.nix
+++ b/nix/ext/tests/plv8.nix
@@ -1,120 +1,88 @@
-# we don't use the default nixos test because we don't support plv8 with pg 17
+# plv8 only supports PostgreSQL 15 (not PG 17 or OrioleDB)
{ self, pkgs }:
let
pname = "plv8";
inherit (pkgs) lib;
+ system = pkgs.pkgsLinux.stdenv.hostPlatform.system;
+ testLib = import ./lib.nix { inherit self pkgs; };
+
installedExtension =
- postgresMajorVersion:
- self.legacyPackages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}."psql_${postgresMajorVersion}".exts."${
- pname
- }";
+ postgresMajorVersion: self.legacyPackages.${system}."psql_${postgresMajorVersion}".exts."${pname}";
versions = postgresqlMajorVersion: (installedExtension postgresqlMajorVersion).versions;
- postgresqlWithExtension =
- postgresql:
- let
- majorVersion = lib.versions.major postgresql.version;
- pkg = pkgs.pkgsLinux.buildEnv {
- name = "postgresql-${majorVersion}-${pname}";
- paths = [
- postgresql
- postgresql.lib
- (installedExtension majorVersion)
- ];
- passthru = {
- inherit (postgresql) version psqlSchema;
- installedExtensions = [ (installedExtension majorVersion) ];
- lib = pkg;
- withPackages = _: pkg;
- withJIT = pkg;
- withoutJIT = pkg;
- };
- nativeBuildInputs = [ pkgs.pkgsLinux.makeWrapper ];
- pathsToLink = [
- "/"
- "/bin"
- "/lib"
- ];
- postBuild = ''
- wrapProgram $out/bin/postgres --set NIX_PGLIBDIR $out/lib
- wrapProgram $out/bin/pg_ctl --set NIX_PGLIBDIR $out/lib
- wrapProgram $out/bin/pg_upgrade --set NIX_PGLIBDIR $out/lib
- '';
- };
- in
- pkg;
- psql_15 =
- postgresqlWithExtension
- self.packages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.postgresql_15;
in
pkgs.testers.runNixOSTest {
name = pname;
nodes.server =
{ ... }:
{
- services.openssh = {
- enable = true;
- };
-
- services.postgresql = {
- enable = true;
- package =
- postgresqlWithExtension
- self.packages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.postgresql_15;
- authentication = ''
- local all postgres peer map=postgres
- local all all peer map=root
- '';
- identMap = ''
- root root supabase_admin
- postgres postgres postgres
- '';
- ensureUsers = [
- {
- name = "supabase_admin";
- ensureClauses.superuser = true;
- }
- ];
- };
+ imports = [
+ (testLib.makeSupabaseTestConfig {
+ majorVersion = "15";
+ })
+ ];
};
- testScript =
- { ... }:
- ''
- from pathlib import Path
- versions = {
- "15": [${lib.concatStringsSep ", " (map (s: ''"${s}"'') (versions "15"))}],
- }
- extension_name = "${pname}"
- support_upgrade = False
- ext_has_background_worker = ${
- if (installedExtension "15") ? hasBackgroundWorker then "True" else "False"
- }
- sql_test_directory = Path("${../../tests}")
- pg_regress_test_name = "${(installedExtension "15").pgRegressTestName or pname}"
+ testScript = ''
+ from pathlib import Path
+ versions = {
+ "15": [${lib.concatStringsSep ", " (map (s: ''"${s}"'') (versions "15"))}],
+ }
+ extension_name = "${pname}"
+ support_upgrade = False
+ sql_test_directory = Path("${../../tests}")
+
+ ${builtins.readFile ./lib.py}
+
+ start_all()
+
+ # Wait for full Supabase initialization (postgres + init-scripts + migrations)
+ server.wait_for_unit("supabase-db-init.service")
- ${builtins.readFile ./lib.py}
+ with subtest("Verify PostgreSQL 15 is our custom build"):
+ pg_version = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT version();\""
+ ).strip()
+ assert "${testLib.expectedVersions."15"}" in pg_version, (
+ f"Expected version ${testLib.expectedVersions."15"}, got: {pg_version}"
+ )
- start_all()
+ postgres_path = server.succeed("readlink -f $(which postgres)").strip()
+ assert "postgresql-and-plugins-${testLib.expectedVersions."15"}" in postgres_path, (
+ f"Expected our custom build (${testLib.expectedVersions."15"}), got: {postgres_path}"
+ )
- server.wait_for_unit("multi-user.target")
- server.wait_for_unit("postgresql.service")
+ with subtest("Verify ansible config loaded"):
+ spl = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SHOW shared_preload_libraries;\""
+ ).strip()
+ for ext in ["pg_stat_statements", "pgaudit", "pgsodium", "pg_cron", "pg_net"]:
+ assert ext in spl, f"Expected {ext} in shared_preload_libraries, got: {spl}"
- test = PostgresExtensionTest(server, extension_name, versions, sql_test_directory, support_upgrade)
+ session_pl = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SHOW session_preload_libraries;\""
+ ).strip()
+ assert "supautils" in session_pl, (
+ f"Expected supautils in session_preload_libraries, got: {session_pl}"
+ )
- with subtest("Check upgrade path with postgresql 15"):
- test.check_upgrade_path("15")
+ with subtest("Verify init scripts and migrations ran"):
+ roles = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT rolname FROM pg_roles ORDER BY rolname;\""
+ ).strip()
+ for role in ["anon", "authenticated", "authenticator", "dashboard_user", "pgbouncer", "service_role", "supabase_admin", "supabase_auth_admin", "supabase_storage_admin"]:
+ assert role in roles, f"Expected role {role} to exist, got: {roles}"
- with subtest("Check pg_regress with postgresql 15 after extension upgrade"):
- test.check_pg_regress(Path("${psql_15}/lib/pgxs/src/test/regress/pg_regress"), "15", pg_regress_test_name)
+ schemas = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT schema_name FROM information_schema.schemata ORDER BY schema_name;\""
+ ).strip()
+ for schema in ["auth", "storage", "extensions"]:
+ assert schema in schemas, f"Expected schema {schema} to exist, got: {schemas}"
- last_version = None
- with subtest("Check the install of the last version of the extension"):
- last_version = test.check_install_last_version("15")
+ test = PostgresExtensionTest(server, extension_name, versions, sql_test_directory, support_upgrade)
- if ext_has_background_worker:
- with subtest("Test switch_${pname}_version"):
- test.check_switch_extension_with_background_worker(Path("${psql_15}/lib/${pname}.so"), "15")
+ with subtest("Check upgrade path with postgresql 15"):
+ test.check_upgrade_path("15")
- with subtest("Check pg_regress with postgresql 15 after installing the last version"):
- test.check_pg_regress(Path("${psql_15}/lib/pgxs/src/test/regress/pg_regress"), "15", pg_regress_test_name)
- '';
+ with subtest("Check the install of the last version of the extension"):
+ test.check_install_last_version("15")
+ '';
}
diff --git a/nix/ext/tests/postgis.nix b/nix/ext/tests/postgis.nix
index d7b8ccb3..f524270a 100644
--- a/nix/ext/tests/postgis.nix
+++ b/nix/ext/tests/postgis.nix
@@ -2,157 +2,166 @@
let
pname = "postgis";
inherit (pkgs) lib;
+ system = pkgs.pkgsLinux.stdenv.hostPlatform.system;
+ testLib = import ./lib.nix { inherit self pkgs; };
+
installedExtension =
- postgresMajorVersion:
- self.legacyPackages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}."psql_${postgresMajorVersion}".exts."${
- pname
- }";
+ postgresMajorVersion: self.legacyPackages.${system}."psql_${postgresMajorVersion}".exts."${pname}";
versions = postgresqlMajorVersion: (installedExtension postgresqlMajorVersion).versions;
- postgresqlWithExtension =
- postgresql:
- let
- majorVersion = lib.versions.major postgresql.version;
- pkg = pkgs.pkgsLinux.buildEnv {
- name = "postgresql-${majorVersion}-${pname}";
- paths = [
- postgresql
- postgresql.lib
- (installedExtension majorVersion)
- ];
- passthru = {
- inherit (postgresql) version psqlSchema;
- installedExtensions = [ (installedExtension majorVersion) ];
- lib = pkg;
- withPackages = _: pkg;
- withJIT = pkg;
- withoutJIT = pkg;
- };
- nativeBuildInputs = [ pkgs.pkgsLinux.makeWrapper ];
- pathsToLink = [
- "/"
- "/bin"
- "/lib"
- ];
- postBuild = ''
- wrapProgram $out/bin/postgres --set NIX_PGLIBDIR $out/lib
- wrapProgram $out/bin/pg_ctl --set NIX_PGLIBDIR $out/lib
- wrapProgram $out/bin/pg_upgrade --set NIX_PGLIBDIR $out/lib
- '';
- };
- in
- pkg;
+ orioledbVersions = self.legacyPackages.${system}."psql_orioledb-17".exts."${pname}".versions;
in
pkgs.testers.runNixOSTest {
name = pname;
nodes.server =
- { config, ... }:
+ { ... }:
{
- services.openssh = {
- enable = true;
- };
+ imports = [
+ (testLib.makeSupabaseTestConfig {
+ majorVersion = "15";
+ })
+ ];
- services.postgresql = {
- enable = true;
- package =
- postgresqlWithExtension
- self.packages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.postgresql_15;
+ specialisation.postgresql17.configuration = testLib.makeUpgradeSpecialisation {
+ fromMajorVersion = "15";
+ toMajorVersion = "17";
};
- specialisation.postgresql17.configuration = {
- services.postgresql = {
- package = lib.mkForce (
- postgresqlWithExtension self.packages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.postgresql_17
- );
- };
-
- systemd.services.postgresql-migrate = {
- serviceConfig = {
- Type = "oneshot";
- RemainAfterExit = true;
- User = "postgres";
- Group = "postgres";
- StateDirectory = "postgresql";
- WorkingDirectory = "${builtins.dirOf config.services.postgresql.dataDir}";
- };
- script =
- let
- oldPostgresql =
- postgresqlWithExtension
- self.packages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.postgresql_15;
- newPostgresql =
- postgresqlWithExtension
- self.packages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.postgresql_17;
- oldDataDir = "${builtins.dirOf config.services.postgresql.dataDir}/${oldPostgresql.psqlSchema}";
- newDataDir = "${builtins.dirOf config.services.postgresql.dataDir}/${newPostgresql.psqlSchema}";
- in
- ''
- if [[ ! -d ${newDataDir} ]]; then
- install -d -m 0700 -o postgres -g postgres "${newDataDir}"
- ${newPostgresql}/bin/initdb -D "${newDataDir}"
- ${newPostgresql}/bin/pg_upgrade --old-datadir "${oldDataDir}" --new-datadir "${newDataDir}" \
- --old-bindir "${oldPostgresql}/bin" --new-bindir "${newPostgresql}/bin"
- else
- echo "${newDataDir} already exists"
- fi
- '';
- };
-
- systemd.services.postgresql = {
- after = [ "postgresql-migrate.service" ];
- requires = [ "postgresql-migrate.service" ];
- };
- };
+ specialisation.orioledb17.configuration = testLib.makeOrioledbSpecialisation { };
};
testScript =
{ nodes, ... }:
let
pg17-configuration = "${nodes.server.system.build.toplevel}/specialisation/postgresql17";
+ orioledb17-configuration = "${nodes.server.system.build.toplevel}/specialisation/orioledb17";
in
''
+ from pathlib import Path
versions = {
"15": [${lib.concatStringsSep ", " (map (s: ''"${s}"'') (versions "15"))}],
"17": [${lib.concatStringsSep ", " (map (s: ''"${s}"'') (versions "17"))}],
+ "orioledb-17": [${lib.concatStringsSep ", " (map (s: ''"${s}"'') orioledbVersions)}],
}
+ extension_name = "${pname}"
+ support_upgrade = True
+ pg17_configuration = "${pg17-configuration}"
+ orioledb17_configuration = "${orioledb17-configuration}"
+ sql_test_directory = Path("${../../tests}")
- def run_sql(query):
- return server.succeed(f"""sudo -u postgres psql -t -A -F\",\" -c \"{query}\" """).strip()
-
- def check_upgrade_path(pg_version):
- with subtest("Check ${pname} upgrade path"):
- firstVersion = versions[pg_version][0]
- server.succeed("sudo -u postgres psql -c 'DROP EXTENSION IF EXISTS ${pname};'")
- run_sql(f"""CREATE EXTENSION ${pname} WITH VERSION '{firstVersion}' CASCADE;""")
- installed_version = run_sql(r"""SELECT extversion FROM pg_extension WHERE extname = '${pname}';""")
- assert installed_version == firstVersion, f"Expected ${pname} version {firstVersion}, but found {installed_version}"
- for version in versions[pg_version][1:]:
- run_sql(f"""ALTER EXTENSION ${pname} UPDATE TO '{version}';""")
- installed_version = run_sql(r"""SELECT extversion FROM pg_extension WHERE extname = '${pname}';""")
- assert installed_version == version, f"Expected ${pname} version {version}, but found {installed_version}"
+ ${builtins.readFile ./lib.py}
start_all()
- server.wait_for_unit("multi-user.target")
- server.wait_for_unit("postgresql.service")
+ # Wait for full Supabase initialization (postgres + init-scripts + migrations)
+ server.wait_for_unit("supabase-db-init.service")
+
+ with subtest("Verify PostgreSQL 15 is our custom build"):
+ pg_version = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT version();\""
+ ).strip()
+ assert "${testLib.expectedVersions."15"}" in pg_version, (
+ f"Expected version ${testLib.expectedVersions."15"}, got: {pg_version}"
+ )
+
+ postgres_path = server.succeed("readlink -f $(which postgres)").strip()
+ assert "postgresql-and-plugins-${testLib.expectedVersions."15"}" in postgres_path, (
+ f"Expected our custom build (${testLib.expectedVersions."15"}), got: {postgres_path}"
+ )
+
+ with subtest("Verify ansible config loaded"):
+ spl = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SHOW shared_preload_libraries;\""
+ ).strip()
+ for ext in ["pg_stat_statements", "pgaudit", "pgsodium", "pg_cron", "pg_net"]:
+ assert ext in spl, f"Expected {ext} in shared_preload_libraries, got: {spl}"
+
+ session_pl = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SHOW session_preload_libraries;\""
+ ).strip()
+ assert "supautils" in session_pl, (
+ f"Expected supautils in session_preload_libraries, got: {session_pl}"
+ )
+
+ with subtest("Verify init scripts and migrations ran"):
+ roles = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT rolname FROM pg_roles ORDER BY rolname;\""
+ ).strip()
+ for role in ["anon", "authenticated", "authenticator", "dashboard_user", "pgbouncer", "service_role", "supabase_admin", "supabase_auth_admin", "supabase_storage_admin"]:
+ assert role in roles, f"Expected role {role} to exist, got: {roles}"
+
+ schemas = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT schema_name FROM information_schema.schemata ORDER BY schema_name;\""
+ ).strip()
+ for schema in ["auth", "storage", "extensions"]:
+ assert schema in schemas, f"Expected schema {schema} to exist, got: {schemas}"
- check_upgrade_path("15")
+ test = PostgresExtensionTest(server, extension_name, versions, sql_test_directory, support_upgrade)
- with subtest("Check ${pname} latest extension version"):
- server.succeed("sudo -u postgres psql -c 'DROP EXTENSION ${pname};'")
- server.succeed("sudo -u postgres psql -c 'CREATE EXTENSION ${pname} CASCADE;'")
- installed_extensions=run_sql(r"""SELECT extname, extversion FROM pg_extension where extname = '${pname}';""")
- latestVersion = versions["15"][-1]
- assert f"${pname},{latestVersion}" in installed_extensions, f"Expected ${pname} version {latestVersion}, but found {installed_extensions}"
+ with subtest("Check upgrade path with postgresql 15"):
+ test.check_upgrade_path("15")
+
+ last_version = None
+ with subtest("Check the install of the last version of the extension"):
+ last_version = test.check_install_last_version("15")
with subtest("switch to postgresql 17"):
server.succeed(
- "${pg17-configuration}/bin/switch-to-configuration test >&2"
+ f"{pg17_configuration}/bin/switch-to-configuration test >&2"
+ )
+ server.wait_for_unit("postgresql.service")
+
+ with subtest("Verify PostgreSQL 17 is our custom build"):
+ pg_version = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT version();\""
+ ).strip()
+ assert "${testLib.expectedVersions."17"}" in pg_version, (
+ f"Expected version ${testLib.expectedVersions."17"}, got: {pg_version}"
+ )
+
+ postgres_pid = server.succeed(
+ "head -1 /var/lib/postgresql/data-17/postmaster.pid"
+ ).strip()
+ postgres_path = server.succeed(
+ f"readlink -f /proc/{postgres_pid}/exe"
+ ).strip()
+ assert "postgresql-and-plugins-${testLib.expectedVersions."17"}" in postgres_path, (
+ f"Expected our custom build (${testLib.expectedVersions."17"}), got: {postgres_path}"
+ )
+
+ with subtest("Check last version of the extension after upgrade"):
+ test.assert_version_matches(last_version)
+
+ with subtest("Check upgrade path with postgresql 17"):
+ test.check_upgrade_path("17")
+
+ with subtest("switch to orioledb 17"):
+ server.succeed(
+ f"{orioledb17_configuration}/bin/switch-to-configuration test >&2"
+ )
+ server.wait_for_unit("supabase-db-init.service")
+
+ with subtest("Verify OrioleDB is running"):
+ installed_extensions = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT extname FROM pg_extension WHERE extname = 'orioledb';\""
+ ).strip()
+ assert "orioledb" in installed_extensions, (
+ f"Expected orioledb extension to be installed, got: {installed_extensions}"
+ )
+
+ dam = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SHOW default_table_access_method;\""
+ ).strip()
+ assert dam == "orioledb", (
+ f"Expected default_table_access_method = orioledb, got: {dam}"
)
- with subtest("Check ${pname} latest extension version after upgrade"):
- installed_extensions=run_sql(r"""SELECT extname, extversion FROM pg_extension;""")
- latestVersion = versions["17"][-1]
- assert f"${pname},{latestVersion}" in installed_extensions
+ with subtest("Verify OrioleDB init scripts and migrations ran"):
+ roles = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT rolname FROM pg_roles ORDER BY rolname;\""
+ ).strip()
+ for role in ["anon", "authenticated", "authenticator", "supabase_admin"]:
+ assert role in roles, f"Expected role {role} to exist, got: {roles}"
- check_upgrade_path("17")
+ with subtest("Check upgrade path with orioledb 17"):
+ test.check_upgrade_path("orioledb-17")
'';
}
diff --git a/nix/ext/tests/timescaledb.nix b/nix/ext/tests/timescaledb.nix
index 285ad39c..4478d75b 100644
--- a/nix/ext/tests/timescaledb.nix
+++ b/nix/ext/tests/timescaledb.nix
@@ -1,102 +1,88 @@
+# timescaledb only supports PostgreSQL 15 (not PG 17 or OrioleDB)
{ self, pkgs }:
let
pname = "timescaledb";
inherit (pkgs) lib;
+ system = pkgs.pkgsLinux.stdenv.hostPlatform.system;
+ testLib = import ./lib.nix { inherit self pkgs; };
+
installedExtension =
- postgresMajorVersion:
- self.legacyPackages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}."psql_${postgresMajorVersion}".exts."${
- pname
- }";
+ postgresMajorVersion: self.legacyPackages.${system}."psql_${postgresMajorVersion}".exts."${pname}";
versions = (installedExtension "15").versions;
- postgresqlWithExtension =
- postgresql:
- let
- majorVersion = lib.versions.major postgresql.version;
- pkg = pkgs.pkgsLinux.buildEnv {
- name = "postgresql-${majorVersion}-${pname}";
- paths = [
- postgresql
- postgresql.lib
- (installedExtension majorVersion)
- ];
- passthru = {
- inherit (postgresql) version psqlSchema;
- installedExtensions = [ (installedExtension majorVersion) ];
- lib = pkg;
- withPackages = _: pkg;
- withJIT = pkg;
- withoutJIT = pkg;
- };
- nativeBuildInputs = [ pkgs.pkgsLinux.makeWrapper ];
- pathsToLink = [
- "/"
- "/bin"
- "/lib"
- ];
- postBuild = ''
- wrapProgram $out/bin/postgres --set NIX_PGLIBDIR $out/lib
- wrapProgram $out/bin/pg_ctl --set NIX_PGLIBDIR $out/lib
- wrapProgram $out/bin/pg_upgrade --set NIX_PGLIBDIR $out/lib
- '';
- };
- in
- pkg;
- psql_15 =
- postgresqlWithExtension
- self.packages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.postgresql_15;
in
pkgs.testers.runNixOSTest {
- name = "timescaledb";
+ name = pname;
nodes.server =
{ ... }:
{
- services.postgresql = {
- enable = true;
- package = psql_15;
- authentication = ''
- local all postgres peer map=postgres
- local all all peer map=root
- '';
- identMap = ''
- root root supabase_admin
- postgres postgres postgres
- '';
- ensureUsers = [
- {
- name = "supabase_admin";
- ensureClauses.superuser = true;
- }
- { name = "service_role"; }
- ];
-
- settings = {
- shared_preload_libraries = "timescaledb";
- };
- };
+ imports = [
+ (testLib.makeSupabaseTestConfig {
+ majorVersion = "15";
+ })
+ ];
};
- testScript =
- { ... }:
- ''
- ${builtins.readFile ./lib.py}
+ testScript = ''
+ from pathlib import Path
+ versions = {
+ "15": [${lib.concatStringsSep ", " (map (s: ''"${s}"'') versions)}],
+ }
+ extension_name = "${pname}"
+ support_upgrade = True
+ sql_test_directory = Path("${../../tests}")
+
+ ${builtins.readFile ./lib.py}
+
+ start_all()
+
+ # Wait for full Supabase initialization (postgres + init-scripts + migrations)
+ server.wait_for_unit("supabase-db-init.service")
+
+ with subtest("Verify PostgreSQL 15 is our custom build"):
+ pg_version = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT version();\""
+ ).strip()
+ assert "${testLib.expectedVersions."15"}" in pg_version, (
+ f"Expected version ${testLib.expectedVersions."15"}, got: {pg_version}"
+ )
+
+ postgres_path = server.succeed("readlink -f $(which postgres)").strip()
+ assert "postgresql-and-plugins-${testLib.expectedVersions."15"}" in postgres_path, (
+ f"Expected our custom build (${testLib.expectedVersions."15"}), got: {postgres_path}"
+ )
+
+ with subtest("Verify ansible config loaded"):
+ spl = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SHOW shared_preload_libraries;\""
+ ).strip()
+ for ext in ["pg_stat_statements", "pgaudit", "pgsodium", "pg_cron", "pg_net"]:
+ assert ext in spl, f"Expected {ext} in shared_preload_libraries, got: {spl}"
- start_all()
+ session_pl = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SHOW session_preload_libraries;\""
+ ).strip()
+ assert "supautils" in session_pl, (
+ f"Expected supautils in session_preload_libraries, got: {session_pl}"
+ )
- server.wait_for_unit("multi-user.target")
- server.wait_for_unit("postgresql.service")
+ with subtest("Verify init scripts and migrations ran"):
+ roles = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT rolname FROM pg_roles ORDER BY rolname;\""
+ ).strip()
+ for role in ["anon", "authenticated", "authenticator", "dashboard_user", "pgbouncer", "service_role", "supabase_admin", "supabase_auth_admin", "supabase_storage_admin"]:
+ assert role in roles, f"Expected role {role} to exist, got: {roles}"
- versions = {
- "15": [${lib.concatStringsSep ", " (map (s: ''"${s}"'') versions)}],
- }
- extension_name = "${pname}"
- support_upgrade = True
- sql_test_directory = Path("${../../tests}")
+ schemas = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT schema_name FROM information_schema.schemata ORDER BY schema_name;\""
+ ).strip()
+ for schema in ["auth", "storage", "extensions"]:
+ assert schema in schemas, f"Expected schema {schema} to exist, got: {schemas}"
- test = PostgresExtensionTest(server, extension_name, versions, sql_test_directory, support_upgrade, "public", "timescaledb-loader")
+ test = PostgresExtensionTest(server, extension_name, versions, sql_test_directory, support_upgrade)
- with subtest("Check upgrade path with postgresql 15"):
- test.check_upgrade_path("15")
+ with subtest("Check upgrade path with postgresql 15"):
+ test.check_upgrade_path("15")
- with subtest("Test switch_${pname}_version"):
- test.check_switch_extension_with_background_worker(Path("${psql_15}/lib/timescaledb-loader.so"), "15")
- '';
+ with subtest("Check the install of the last version of the extension"):
+ test.check_install_last_version("15")
+ '';
}
diff --git a/nix/ext/tests/vault.nix b/nix/ext/tests/vault.nix
index 28ad6b62..d25ad0ea 100644
--- a/nix/ext/tests/vault.nix
+++ b/nix/ext/tests/vault.nix
@@ -2,200 +2,166 @@
let
pname = "supabase_vault";
inherit (pkgs) lib;
+ system = pkgs.pkgsLinux.stdenv.hostPlatform.system;
+ testLib = import ./lib.nix { inherit self pkgs; };
+
installedExtension =
- postgresMajorVersion:
- self.legacyPackages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}."psql_${postgresMajorVersion}".exts."${
- pname
- }";
+ postgresMajorVersion: self.legacyPackages.${system}."psql_${postgresMajorVersion}".exts."${pname}";
versions = postgresqlMajorVersion: (installedExtension postgresqlMajorVersion).versions;
- postgresqlWithExtension =
- postgresql:
- let
- majorVersion = lib.versions.major postgresql.version;
- pkg = pkgs.pkgsLinux.buildEnv {
- name = "postgresql-${majorVersion}-${pname}";
- paths = [
- postgresql
- postgresql.lib
- (installedExtension majorVersion)
- (self.legacyPackages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}."psql_${majorVersion}".exts.pgsodium
- ) # dependency
- ];
- passthru = {
- inherit (postgresql) version psqlSchema;
- installedExtensions = [ (installedExtension majorVersion) ];
- lib = pkg;
- withPackages = _: pkg;
- withJIT = pkg;
- withoutJIT = pkg;
- };
- nativeBuildInputs = [ pkgs.pkgsLinux.makeWrapper ];
- pathsToLink = [
- "/"
- "/bin"
- "/lib"
- ];
- postBuild = ''
- wrapProgram $out/bin/postgres --set NIX_PGLIBDIR $out/lib
- wrapProgram $out/bin/pg_ctl --set NIX_PGLIBDIR $out/lib
- wrapProgram $out/bin/pg_upgrade --set NIX_PGLIBDIR $out/lib
- '';
- };
- in
- pkg;
- vaultGetKey = lib.getExe (
- pkgs.writeShellScriptBin "vault-getkey" ''
- echo 0000000000000000000000000000000000000000000000000000000000000000
- ''
- );
- psql_15 =
- postgresqlWithExtension
- self.packages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.postgresql_15;
- psql_17 =
- postgresqlWithExtension
- self.packages.${pkgs.pkgsLinux.stdenv.hostPlatform.system}.postgresql_17;
+ orioledbVersions = self.legacyPackages.${system}."psql_orioledb-17".exts."${pname}".versions;
in
pkgs.testers.runNixOSTest {
name = pname;
nodes.server =
- { config, ... }:
+ { ... }:
{
- services.postgresql = {
- enable = true;
- package = psql_15;
- authentication = ''
- local all postgres peer map=postgres
- local all all peer map=root
- '';
- identMap = ''
- root root supabase_admin
- postgres postgres postgres
- '';
- initialScript = pkgs.writeText "vault-init.sql" ''
- CREATE SCHEMA vault;
- '';
- ensureUsers = [
- {
- name = "supabase_admin";
- ensureClauses.superuser = true;
- }
- { name = "service_role"; }
- ];
- settings = {
- "shared_preload_libraries" = "${pname},pgsodium";
- "pgsodium.getkey_script" = vaultGetKey;
- "search_path" = "\"$user\", public, auth, extensions";
- "vault.getkey_script" = vaultGetKey;
- };
+ imports = [
+ (testLib.makeSupabaseTestConfig {
+ majorVersion = "15";
+ })
+ ];
+
+ specialisation.postgresql17.configuration = testLib.makeUpgradeSpecialisation {
+ fromMajorVersion = "15";
+ toMajorVersion = "17";
};
- specialisation.postgresql17.configuration = {
- services.postgresql = {
- package = lib.mkForce psql_17;
- };
-
- systemd.services.postgresql-migrate = {
- serviceConfig = {
- Type = "oneshot";
- RemainAfterExit = true;
- User = "postgres";
- Group = "postgres";
- StateDirectory = "postgresql";
- WorkingDirectory = "${builtins.dirOf config.services.postgresql.dataDir}";
- };
- script =
- let
- oldPostgresql = psql_15;
- newPostgresql = psql_17;
- oldDataDir = "${builtins.dirOf config.services.postgresql.dataDir}/${oldPostgresql.psqlSchema}";
- newDataDir = "${builtins.dirOf config.services.postgresql.dataDir}/${newPostgresql.psqlSchema}";
- in
- ''
- if [[ ! -d ${newDataDir} ]]; then
- install -d -m 0700 -o postgres -g postgres "${newDataDir}"
- ${newPostgresql}/bin/initdb -D "${newDataDir}"
- echo "shared_preload_libraries = '${pname},pgsodium'" >> "${newDataDir}/postgresql.conf"
- echo "vault.getkey_script = '${vaultGetKey}'" >> "${newDataDir}/postgresql.conf";
- echo "pgsodium.getkey_script = '${vaultGetKey}'" >> "${newDataDir}/postgresql.conf";
- ${newPostgresql}/bin/pg_upgrade --old-datadir "${oldDataDir}" --new-datadir "${newDataDir}" \
- --old-bindir "${oldPostgresql}/bin" --new-bindir "${newPostgresql}/bin"
- else
- echo "${newDataDir} already exists"
- fi
- '';
- };
-
- systemd.services.postgresql = {
- after = [ "postgresql-migrate.service" ];
- requires = [ "postgresql-migrate.service" ];
- };
- };
+ specialisation.orioledb17.configuration = testLib.makeOrioledbSpecialisation { };
};
testScript =
{ nodes, ... }:
let
pg17-configuration = "${nodes.server.system.build.toplevel}/specialisation/postgresql17";
+ orioledb17-configuration = "${nodes.server.system.build.toplevel}/specialisation/orioledb17";
in
''
from pathlib import Path
versions = {
"15": [${lib.concatStringsSep ", " (map (s: ''"${s}"'') (versions "15"))}],
"17": [${lib.concatStringsSep ", " (map (s: ''"${s}"'') (versions "17"))}],
+ "orioledb-17": [${lib.concatStringsSep ", " (map (s: ''"${s}"'') orioledbVersions)}],
}
extension_name = "${pname}"
support_upgrade = True
pg17_configuration = "${pg17-configuration}"
- ext_has_background_worker = ${
- if (installedExtension "15") ? hasBackgroundWorker then "True" else "False"
- }
+ orioledb17_configuration = "${orioledb17-configuration}"
sql_test_directory = Path("${../../tests}")
- pg_regress_test_name = "${(installedExtension "15").pgRegressTestName or pname}"
${builtins.readFile ./lib.py}
start_all()
- server.wait_for_unit("multi-user.target")
- server.wait_for_unit("postgresql.service")
+ # Wait for full Supabase initialization (postgres + init-scripts + migrations)
+ server.wait_for_unit("supabase-db-init.service")
- test = PostgresExtensionTest(server, extension_name, versions, sql_test_directory, support_upgrade)
+ with subtest("Verify PostgreSQL 15 is our custom build"):
+ pg_version = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT version();\""
+ ).strip()
+ assert "${testLib.expectedVersions."15"}" in pg_version, (
+ f"Expected version ${testLib.expectedVersions."15"}, got: {pg_version}"
+ )
+
+ postgres_path = server.succeed("readlink -f $(which postgres)").strip()
+ assert "postgresql-and-plugins-${testLib.expectedVersions."15"}" in postgres_path, (
+ f"Expected our custom build (${testLib.expectedVersions."15"}), got: {postgres_path}"
+ )
+
+ with subtest("Verify ansible config loaded"):
+ spl = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SHOW shared_preload_libraries;\""
+ ).strip()
+ for ext in ["pg_stat_statements", "pgaudit", "pgsodium", "pg_cron", "pg_net"]:
+ assert ext in spl, f"Expected {ext} in shared_preload_libraries, got: {spl}"
+
+ session_pl = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SHOW session_preload_libraries;\""
+ ).strip()
+ assert "supautils" in session_pl, (
+ f"Expected supautils in session_preload_libraries, got: {session_pl}"
+ )
+
+ with subtest("Verify init scripts and migrations ran"):
+ roles = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT rolname FROM pg_roles ORDER BY rolname;\""
+ ).strip()
+ for role in ["anon", "authenticated", "authenticator", "dashboard_user", "pgbouncer", "service_role", "supabase_admin", "supabase_auth_admin", "supabase_storage_admin"]:
+ assert role in roles, f"Expected role {role} to exist, got: {roles}"
+ schemas = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT schema_name FROM information_schema.schemata ORDER BY schema_name;\""
+ ).strip()
+ for schema in ["auth", "storage", "extensions"]:
+ assert schema in schemas, f"Expected schema {schema} to exist, got: {schemas}"
+
+ test = PostgresExtensionTest(server, extension_name, versions, sql_test_directory, support_upgrade)
with subtest("Check upgrade path with postgresql 15"):
test.check_upgrade_path("15")
- with subtest("Check pg_regress with postgresql 15 after extension upgrade"):
- test.run_sql_file("${../../../ansible/files/postgresql_extension_custom_scripts/supabase_vault/after-create.sql}")
- test.check_pg_regress(Path("${psql_15}/lib/pgxs/src/test/regress/pg_regress"), "15", pg_regress_test_name)
-
last_version = None
with subtest("Check the install of the last version of the extension"):
last_version = test.check_install_last_version("15")
- with subtest("Check pg_regress with postgresql 15 after installing the last version"):
- test.run_sql_file("${../../../ansible/files/postgresql_extension_custom_scripts/supabase_vault/after-create.sql}")
- test.check_pg_regress(Path("${psql_15}/lib/pgxs/src/test/regress/pg_regress"), "15", pg_regress_test_name)
-
with subtest("switch to postgresql 17"):
server.succeed(
f"{pg17_configuration}/bin/switch-to-configuration test >&2"
)
+ server.wait_for_unit("postgresql.service")
+
+ with subtest("Verify PostgreSQL 17 is our custom build"):
+ pg_version = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT version();\""
+ ).strip()
+ assert "${testLib.expectedVersions."17"}" in pg_version, (
+ f"Expected version ${testLib.expectedVersions."17"}, got: {pg_version}"
+ )
+
+ postgres_pid = server.succeed(
+ "head -1 /var/lib/postgresql/data-17/postmaster.pid"
+ ).strip()
+ postgres_path = server.succeed(
+ f"readlink -f /proc/{postgres_pid}/exe"
+ ).strip()
+ assert "postgresql-and-plugins-${testLib.expectedVersions."17"}" in postgres_path, (
+ f"Expected our custom build (${testLib.expectedVersions."17"}), got: {postgres_path}"
+ )
- with subtest("Check last version of the extension after postgresql upgrade"):
+ with subtest("Check last version of the extension after upgrade"):
test.assert_version_matches(last_version)
with subtest("Check upgrade path with postgresql 17"):
test.check_upgrade_path("17")
- with subtest("Check pg_regress with postgresql 17 after extension upgrade"):
- test.run_sql_file("${../../../ansible/files/postgresql_extension_custom_scripts/supabase_vault/after-create.sql}")
- test.check_pg_regress(Path("${psql_17}/lib/pgxs/src/test/regress/pg_regress"), "17", pg_regress_test_name)
+ with subtest("switch to orioledb 17"):
+ server.succeed(
+ f"{orioledb17_configuration}/bin/switch-to-configuration test >&2"
+ )
+ server.wait_for_unit("supabase-db-init.service")
+
+ with subtest("Verify OrioleDB is running"):
+ installed_extensions = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT extname FROM pg_extension WHERE extname = 'orioledb';\""
+ ).strip()
+ assert "orioledb" in installed_extensions, (
+ f"Expected orioledb extension to be installed, got: {installed_extensions}"
+ )
- with subtest("Check the install of the last version of the extension"):
- test.check_install_last_version("17")
+ dam = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SHOW default_table_access_method;\""
+ ).strip()
+ assert dam == "orioledb", (
+ f"Expected default_table_access_method = orioledb, got: {dam}"
+ )
+
+ with subtest("Verify OrioleDB init scripts and migrations ran"):
+ roles = server.succeed(
+ "psql -U supabase_admin -d postgres -t -A -c \"SELECT rolname FROM pg_roles ORDER BY rolname;\""
+ ).strip()
+ for role in ["anon", "authenticated", "authenticator", "supabase_admin"]:
+ assert role in roles, f"Expected role {role} to exist, got: {roles}"
- with subtest("Check pg_regress with postgresql 17 after installing the last version"):
- test.run_sql_file("${../../../ansible/files/postgresql_extension_custom_scripts/supabase_vault/after-create.sql}")
- test.check_pg_regress(Path("${psql_17}/lib/pgxs/src/test/regress/pg_regress"), "17", pg_regress_test_name)
+ with subtest("Check upgrade path with orioledb 17"):
+ test.check_upgrade_path("orioledb-17")
'';
}
[parent: f183a83f0bd0]