PostgreSQL: Difference between revisions
imported>Malteneuss m Fix typo |
imported>Malteneuss Add verification with psql section |
||
Line 22: | Line 22: | ||
* [https://search.nixos.org/options?query=services.postgresql Available NixOS Postgresql service options] | * [https://search.nixos.org/options?query=services.postgresql Available NixOS Postgresql service options] | ||
=== Verify setup === | |||
You can use <code>psql</code> that comes with Postgres in the terminal to verify that the DB setup is as expected: | |||
<syntaxhighlight lang="bash"> | |||
$ sudo -u postgres psql | |||
psql | |||
Type "help" for help. | |||
postgres=# | |||
</syntaxhighlight> | |||
We have to switch to a system user like "postgres" with <code>sudo -u postgres</code, because by default <code>psql</code> logs you into the DB user of the same name as the current Linux/system user. By default, NixOS creates a system and DB user names "postgres". | |||
So the line <code>postgres=# </code> shows that we are now logged-in as DB user "postgres". | |||
Inside <code>psql</code> here the most frequent commands are: | |||
List all databases running on this Postgres instance with <code>\l</code>: | |||
<syntaxhighlight lang="bash"> | |||
postgres=# \l | |||
List of databases | |||
Name | Owner | Encoding | Collate | Ctype | Access privileges | |||
------------+----------+----------+-------------+-------------+------------------------ | |||
mydatabase | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres + | |||
| | | | | postgres=CTc/postgres + | |||
| | | | | rustnixos=CTc/postgres | |||
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | |||
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | |||
| | | | | postgres=CTc/postgres | |||
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | |||
| | | | | postgres=CTc/postgres | |||
(4 rows) | |||
</syntaxhighlight> | |||
List all DB users (also called "roles" in Postgres) with <code>\du</code>: | |||
<syntaxhighlight lang="bash"> | |||
postgres=# \du | |||
List of roles | |||
Role name | Attributes | Member of | |||
-----------+------------------------------------------------------------+----------- | |||
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} | |||
</syntaxhighlight> | |||
List all authentication rules (called an "pg_hba.conf" file in Postgres ) with <code>table pg_hba_file_rules;</code>: | |||
<syntaxhighlight lang="bash"> | |||
postgres=# table pg_hba_file_rules; | |||
line_number | type | database | user_name | address | netmask | auth_method | options | error | |||
-------------+-------+----------+-----------+---------+---------+-------------+---------+------- | |||
1 | local | {all} | {all} | | | peer | | | |||
(1 row) | |||
</syntaxhighlight> | |||
* [https://www.postgresql.org/docs/current/auth-pg-hba-conf.html Official Postgres authentication pg_hba.conf documentation] | |||
=== Allow TCP/IP connections === | === Allow TCP/IP connections === | ||
Line 65: | Line 121: | ||
* [https://search.nixos.org/packages?query=postgresql_ Available Nixpkgs Postgresql versions] | * [https://search.nixos.org/packages?query=postgresql_ Available Nixpkgs Postgresql versions] | ||
== | == Debugging with <code>psql</code> == | ||
To debug the SQL statements futher, one can use '''systemctl cat postgresql''' and see the '''ExecStartPost=/nix/store/rnv1v95bbf2lsy9ncwg7jdwj2s71sqra-unit-script/bin/postgresql-post-start''' line. Then open it with `cat` on the shell and see the '''psql''' command. | To debug the SQL statements futher, one can use '''systemctl cat postgresql''' and see the '''ExecStartPost=/nix/store/rnv1v95bbf2lsy9ncwg7jdwj2s71sqra-unit-script/bin/postgresql-post-start''' line. Then open it with `cat` on the shell and see the '''psql''' command. | ||
Line 122: | Line 138: | ||
^ | ^ | ||
</pre> | </pre> | ||
== FAQ == | |||
=== Connection rejected with "Role does not exist" === | |||
<syntaxhighlight lang="nix"> | |||
$ psql | |||
psql: error: connection to server on socket "/run/postgresql/.s.PGSQL.5432" failed: FATAL: role "root" does not exist | |||
</syntaxhighlight> | |||
You are trying to login as a system user ("root" in this example) that has no DB user of the same name. Try <code>psql -U postgres</code> or <code>sudo -u postgres psql</code> to log in as a different DB user. | |||
== See also == | == See also == |