PostgreSQL - Revision history

Philipwilk: Add subsection in upgrade instructions noting that checksumming is now enabled by default from v>18

2026-03-15T16:52:47Z

Add subsection in upgrade instructions noting that checksumming is now enabled by default from v>18

← Older revision		Revision as of 16:52, 15 March 2026
Line 351:		Line 351:

	[https://nixos.org/manual/nixos/stable/#module-postgresql NixOS manual] also contains useful information about this kind of upgrades.		[https://nixos.org/manual/nixos/stable/#module-postgresql NixOS manual] also contains useful information about this kind of upgrades.

			=== Upgrading versions <18 to >=18 ===
			Starting with v18, <code>initdb</code> defaults to enabling data checksums. This will prevent you from being able to upgrade from previous versions that did not have checksumming enabled, as <code>pg_upgrade</code> requires matching cluster checksum settings<ref>https://www.postgresql.org/docs/release/18.0/</ref>.

			To maintain compatibility with a previous database, you may disable checksumming when creating the new database by using the appropriate option, <code>initdb --no-data-checksums ...</code>.

			Alternatively, you can enable checksumming on the previous database with relatively little effort using <code>pg_checksums --pgdata={OLD_DATA_DIR} --enable --progress</code>. This must be done using the binary from the current postgres version, not from the version you wish to upgrade to.

	== See also ==		== See also ==

Patryk27 at 21:03, 2 February 2026

2026-02-02T21:03:22Z

@@ Line 338: / Line 338: @@
 Let the service successfully start once, and then stop it. Upon completion, proceed with the following command, substituting the numbers 15 and 16 with the respective versions you previously used and the more recent one:
-<syntaxhighlight>sudo -u postgres pg_upgrade -b "$(nix build --no-link --print-out-paths nixpkgs#postgresql_15.out)/bin" -B /run/current-system/sw/bin -d /var/lib/postgresql/15 -D /var/lib/postgresql/16</syntaxhighlight>The [https://nixos.org/manual/nixos/stable/#module-postgresql NixOS manual] also has more detailed information about major upgrades.
+<syntaxhighlight>sudo -u postgres pg_upgrade -b "$(nix build --no-link --print-out-paths nixpkgs#postgresql_15.out)/bin" -B /run/current-system/sw/bin -d /var/lib/postgresql/15 -D /var/lib/postgresql/16</syntaxhighlight>
 == See also ==

Deckori: /* Getting started */ Added a note mentioning the pg_config executable

2026-01-08T18:25:15Z

Getting started: Added a note mentioning the pg_config executable

@@ Line 25: / Line 25: @@
 * [https://search.nixos.org/options?query=services.postgresql Available NixOS Postgresql service options]
 It's also possible to setup PostgreSQL with [[Nix Darwin]] similar to how you would on NixOS, see the [https://daiderd.com/nix-darwin/manual/index.html#opt-services.postgresql.enable options].
 === Verify  setup ===

Mikilio: Added documentation on how to configure password protected users securely. Made possible by https://github.com/NixOS/nixpkgs/pull/467286

2025-12-23T15:46:53Z

Added documentation on how to configure password protected users securely. Made possible by https://github.com/NixOS/nixpkgs/pull/467286

← Older revision		Revision as of 15:46, 23 December 2025
Line 190:		Line 190:
	</syntaxhighlight>[https://search.nixos.org/options?show=services.prometheus.exporters.postgres.dataSourceName&from=0&size=50&sort=relevance&type=packages&query=services.prometheus.exporters.postgres See all available options for services.prometheus.exporters.postgres]		</syntaxhighlight>[https://search.nixos.org/options?show=services.prometheus.exporters.postgres.dataSourceName&from=0&size=50&sort=relevance&type=packages&query=services.prometheus.exporters.postgres See all available options for services.prometheus.exporters.postgres]

	== ~~TLS~~ ==		== Remote Access ==

			=== TLS ===
	To turn TLS on in recent versions of postgres it's pretty easy. Their [https://www.postgresql.org/docs/current/ssl-tcp.html docs] are pretty good.		To turn TLS on in recent versions of postgres it's pretty easy. Their [https://www.postgresql.org/docs/current/ssl-tcp.html docs] are pretty good.

Line 205:		Line 206:
	Then in your nix configuration:		Then in your nix configuration:

	<syntaxhighlight lang="nix">		<syntaxhighlight lang="nix">services.postgresql = {
	services.postgresql = {
	enable = true;		enable = true;
	package = pkgs.postgresql_16;		package = pkgs.postgresql_16;
Line 220:		Line 220:
	host sameuser all ::1/128 scram-sha-256		host sameuser all ::1/128 scram-sha-256
	'';		'';
	};		};</syntaxhighlight>
	</syntaxhighlight>

	the `sameuser` mentioned in the authentication section requires the database name be the same as the username, which you may not want, you can change that to `all` to allow an authenticated user the ability to connect to any database.		the `sameuser` mentioned in the authentication section requires the database name be the same as the username, which you may not want, you can change that to `all` to allow an authenticated user the ability to connect to any database.
Line 227:		Line 226:
	`scram-sha-256` is the require a password option, but you can authenticate a variety of different ways, see the official docs for other options as part of pg_hba.conf.		`scram-sha-256` is the require a password option, but you can authenticate a variety of different ways, see the official docs for other options as part of pg_hba.conf.

	~~user~~ creation ~~and permissions are best described in~~ the ~~PG manual under `CREATE ROLE`~~ and ~~`GRANT`~~		=== User creation ===
	for example:		NixOS can declaratively create or manage Postgres users with passwords.
	<syntaxhighlight lang="~~sql~~">
	~~CREATE USER tootieapp WITH~~ PASSWORD '~~BIGLONGRANDOMSTRINGHERE~~';		Postgres allows specifying the password as plaintext, MD5 hash or SCRAM-SHA-256 hash. For declarative configuration, the recommended format is SCRAM-SHA-256 and a hash can be obtained from a valid password for example using the following script:<syntaxhighlight lang="python3" line="1" start="1">
	~~GRANT ALL PRIVILEGES ON DATABASE tootieapp TO tootieapp;~~		#!/usr/bin/env nix-shell
	~~</syntaxhighlight>~~		#! nix-shell -i python3 -p "python3.withPackages (ps: [ps.scramp])"
			import scramp, base64

			PASSWORD = 'secure_password123!'

			m = scramp.ScramMechanism()
			salt, stored_key, server_key, iteration_count = m.make_auth_info(PASSWORD)
			print(f"SCRAM-SHA-256${iteration_count}:{base64.b64encode(salt).decode()}${base64.b64encode(stored_key).decode()}:{base64.b64encode(server_key).decode()}")
			</syntaxhighlight>Using this hash, we can then extend the above configuration as follows:<syntaxhighlight lang="nix">services.postgresql = {
			enable = true;
			package = pkgs.postgresql_16;
			enableTCPIP = true;
			ensureDatabases = [ "tootieapp" ];
			settings = {
			ssl = true;
			};
			authentication = pkgs.lib.mkOverride 10 ''
			#type database DBuser auth-method
			local all all trust
			host sameuser all 127.0.0.1/32 scram-sha-256
			host sameuser all ::1/128 scram-sha-256
			'';
			ensureUsers = [
			{
			name = "tootieapp";
			ensureDBOwnership = true;
			ensureClauses = {
			login = true;
			password = "SCRAM-SHA-256$4096:lB4tguN+gvNVSqk0zGRPHQ==$zh48o1bb9tuRjvGQHh/CeobEyUI4u91rp0K9who8m3I=:mHxc6obGad8/g65+V3C84UQGHIK41Gfx32+xXSZiOss=";
			};
			}
			];
			};</syntaxhighlight>For more details on how to create hashes from valid passwords also refer to https://gist.github.com/jkatz/e0a1f52f66fa03b732945f6eb94d9c21

	== Debugging with <code>psql</code> ==		== Debugging with <code>psql</code> ==

MichaelStapelberg: /* Set the Postgresql versions */

2025-05-24T12:32:52Z

Set the Postgresql versions

← Older revision		Revision as of 12:32, 24 May 2025
Line 126:		Line 126:
	</syntaxhighlight>		</syntaxhighlight>

	Note that changing the package version does not trigger any automatic migrations of your existing databases — follow ~~https://nixos.org/manual/nixos/stable/~~#~~module-services-postgres-upgrading~~ to migrate existing databases.		Note that changing the package version does not trigger any automatic migrations of your existing databases — follow [[#Major upgrades]] to migrate existing databases.

	=== Security ===		=== Security ===

MichaelStapelberg: Reference the PostgreSQL upgrading procedure in the NixOS manual

2025-05-24T12:31:04Z

Reference the PostgreSQL upgrading procedure in the NixOS manual

← Older revision		Revision as of 12:31, 24 May 2025
Line 114:		Line 114:
	=== Set the Postgresql versions ===		=== Set the Postgresql versions ===

	By default, NixOS uses whatever Postgres version shipped as default for your [https://search.nixos.org/options?show=system.stateVersion system.stateVersion]. To use a different or more recent version, you can set ~~it manually~~:		By default, NixOS uses whatever Postgres version shipped as default for your [https://search.nixos.org/options?show=system.stateVersion system.stateVersion].

			To use a different or more recent version, you can manually set one of the [https://search.nixos.org/packages?query=postgresql_ available Nixpkgs Postgresql versions]:

	<syntaxhighlight lang="nix">		<syntaxhighlight lang="nix">
Line 124:		Line 126:
	</syntaxhighlight>		</syntaxhighlight>

	Note that changing the package version does not trigger any automatic migrations of your existing databases~~: if you update Postgres you should be ready to migrate any existing databases manually.~~		Note that changing the package version does not trigger any automatic migrations of your existing databases — follow https://nixos.org/manual/nixos/stable/#module-services-postgres-upgrading to migrate existing databases.

	* [https://~~search.~~nixos.org/~~packages?query=postgresql_ Available Nixpkgs Postgresql versions]~~

	=== Security ===		=== Security ===

Marcusramberg: Add link to nixos manual for postgres upgrades

2025-03-30T13:46:48Z

Add link to nixos manual for postgres upgrades

← Older revision		Revision as of 13:46, 30 March 2025
Line 298:		Line 298:
	Let the service successfully start once, and then stop it. Upon completion, proceed with the following command, substituting the numbers 15 and 16 with the respective versions you previously used and the more recent one:		Let the service successfully start once, and then stop it. Upon completion, proceed with the following command, substituting the numbers 15 and 16 with the respective versions you previously used and the more recent one:

	<syntaxhighlight>		<syntaxhighlight>sudo -u postgres pg_upgrade -b "$(nix build --no-link --print-out-paths nixpkgs#postgresql_15.out)/bin" -B /run/current-system/sw/bin -d /var/lib/postgresql/15 -D /var/lib/postgresql/16</syntaxhighlight>The [https://nixos.org/manual/nixos/stable/#module-postgresql NixOS manual] also has more detailed information about major upgrades.
	sudo -u postgres pg_upgrade -b "$(nix build --no-link --print-out-paths nixpkgs#postgresql_15.out)/bin" -B /run/current-system/sw/bin -d /var/lib/postgresql/15 -D /var/lib/postgresql/16
	</syntaxhighlight>

	== See also ==		== See also ==

Benjaminedwardwebb: Edits the current example of adjusting `services.postgresql.authentication` to allow IP/TCP connections.If you (like me) aren't familiar with configuring authentication rules for a postgres instance and opt to blindly copy-and-paste the examples here, doing that for the IP/TCP example won't work because it removes the auth rule that allows the postgres user to connect via the peer authentication mode, which the systemd service will use. This

2024-11-13T23:01:48Z

Edits the current example of adjusting `services.postgresql.authentication` to allow IP/TCP connections.If you (like me) aren't familiar with configuring authentication rules for a postgres instance and opt to blindly copy-and-paste the examples here, doing that for the IP/TCP example won't work because it removes the auth rule that allows the postgres user to connect via the peer authentication mode, which the systemd service will use. This

← Older revision		Revision as of 23:01, 13 November 2024
Line 93:		Line 93:
	# port = 5432;		# port = 5432;
	authentication = pkgs.lib.mkOverride 10 ''		authentication = pkgs.lib.mkOverride 10 ''
	~~#...~~
	#type database DBuser origin-address auth-method		#type database DBuser origin-address auth-method
			local all all trust
			# ... other auth rules ...

	# ipv4		# ipv4
	host all all 127.0.0.1/32 trust		host all all 127.0.0.1/32 trust
	# ipv6		# ipv6
	host all all ::1/128 trust		host all all ::1/128 trust
	'';		'';
	initialScript = pkgs.writeText "backend-initScript" ''		initialScript = pkgs.writeText "backend-initScript" ''

Dander: add a section about monitoring

2024-10-22T21:04:29Z

add a section about monitoring

← Older revision		Revision as of 21:04, 22 October 2024
Line 178:		Line 178:
	With "sameuser" Postgres will allow DB user access only to databases of the same name. E.g. DB user "mydatabase" will get access to database "mydatabase" and nothing else. The part <code>map=superuser_map</code> is optional.		With "sameuser" Postgres will allow DB user access only to databases of the same name. E.g. DB user "mydatabase" will get access to database "mydatabase" and nothing else. The part <code>map=superuser_map</code> is optional.
	One exception is the DB user "postgres", which by default is a superuser/admin with access to everything.		One exception is the DB user "postgres", which by default is a superuser/admin with access to everything.

			=== Monitoring ===
			A [[Prometheus]] exporter is available to export metrics to Prometheus-compatible storage.<syntaxhighlight lang="nix">
			services.prometheus.exporters.postgres = {
			enable = true;
			listenAddress = "0.0.0.0";
			port = 9187;
			};
			</syntaxhighlight>[https://search.nixos.org/options?show=services.prometheus.exporters.postgres.dataSourceName&from=0&size=50&sort=relevance&type=packages&query=services.prometheus.exporters.postgres See all available options for services.prometheus.exporters.postgres]

	== TLS ==		== TLS ==

Dander: Fix typo

2024-10-22T20:50:03Z

Fix typo

← Older revision		Revision as of 20:50, 22 October 2024
Line 257:		Line 257:
	<syntaxhighlight lang="nix">		<syntaxhighlight lang="nix">
	root$ psql -U postgres		root$ psql -U postgres
	psql: error: connection to server on socket "/run/postgresql/.s.PGSQL.5432" failed: FATAL: Peer authentication failed for user "~~postgress~~"		psql: error: connection to server on socket "/run/postgresql/.s.PGSQL.5432" failed: FATAL: Peer authentication failed for user "postgres"
	</syntaxhighlight>		</syntaxhighlight>