PostgreSQL: Difference between revisions

(2 intermediate revisions by 2 users not shown)

Line 1:

~~{{expansion}}~~

[https://www.postgresql.org/ PostgreSQL] also known as Postgres, is a free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance.

This article extends the documentation in the [https://nixos.org/manual/nixos/stable/#module-postgresql NixOS manual].

=== Getting started ===

Line 19:

Line 21:

</syntaxhighlight>

This will setup Postgresql with a default DB superuser/admin "postgres", a database "mydatabase" and let every DB user have access to it without a password through a "local" Unix socket "/var/~~lib~~/postgresql" (TCP/IP is disabled by default because it's less performant and less secure).

This will setup Postgresql with a default DB superuser/admin "postgres", a database "mydatabase" and let every DB user have access to it without a password through a "local" Unix socket "/var/run/postgresql" (TCP/IP is disabled by default because it's less performant and less secure).

* [https://search.nixos.org/options?query=services.postgresql Available NixOS Postgresql service options]

Line 278:

Line 280:

outline=# ALTER DATABASE outline REFRESH COLLATION VERSION;

</syntaxhighlight>

== Major upgrades ==

If you're using NixOS' modules for PostgreSQL and find yourself in a boot/switch after a major bump of it, you'll need to upgrade your cluster.

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:

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 ==

Line 285:

Line 298:

[[Category:Applications]]

[[Category:Database]]

[[Category:NixOS Manual]]