Further PostgreSQL operational tasks

/ home / computer / postgresql / postgresql-for-mysql-admins / .

Table of Contents

Accessing the database

• O/S user postgres
• CLI tools
• Creating a user
• Unix-domainsockets
• TCP/IP

User management

A USER is just a ROLE with LOGIN privilege by default. CREATE USER is an alias to CREATE ROLE.

Client authentication: The pg_hba.conf File, see PostgreSQL documentation (Host Based Authentication)

$datadir/pg_hba.conf

Read on:

• systemctl (re)start postgres
• pg_ctl reload
• SELECT pg_reload_conf();
• kill -HUP $(pidof postgresql)

View:

postgres=# select * from pg_hba_file_rules;
 rule_number |              file_name              | line_number | type  |   database    | user_name  |  address  |                 netmask                 |  auth_method  | options | error
-------------+-------------------------------------+-------------+-------+---------------+------------+-----------+-----------------------------------------+---------------+---------+-------
           1 | /etc/postgresql/16/main/pg_hba.conf |         118 | local | {all}         | {postgres} |           |                                         | peer          |         |
           2 | /etc/postgresql/16/main/pg_hba.conf |         121 | local | {all}         | {oli}      |           |                                         | trust         |         |
           3 | /etc/postgresql/16/main/pg_hba.conf |         124 | local | {all}         | {all}      |           |                                         | peer          |         |
           4 | /etc/postgresql/16/main/pg_hba.conf |         126 | host  | {all}         | {all}      | 127.0.0.1 | 255.255.255.255                         | scram-sha-256 |         |
           5 | /etc/postgresql/16/main/pg_hba.conf |         128 | host  | {all}         | {all}      | ::1       | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | scram-sha-256 |         |
           6 | /etc/postgresql/16/main/pg_hba.conf |         131 | local | {replication} | {all}      |           |                                         | peer          |         |
           7 | /etc/postgresql/16/main/pg_hba.conf |         132 | host  | {replication} | {all}      | 127.0.0.1 | 255.255.255.255                         | scram-sha-256 |         |
           8 | /etc/postgresql/16/main/pg_hba.conf |         133 | host  | {replication} | {all}      | ::1       | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | scram-sha-256 |         |
(8 rows)

Create User

• Client Utility (CLI)

$ createuser -e app

$ psql
postgres=# CREATE USER app;

List User

postgres=# \du
postgres=# \du+
postgres=# SELECT * FROM pg_user;

postgres=# SELECT rolname FROM pg_roles WHERE rolsuper = true;
 rolname
----------
 postgres

postgres=# SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication FROM pg_roles;
           rolname           | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication
-----------------------------+----------+------------+---------------+-------------+-------------+----------------
 postgres                    | t        | t          | t             | t           | t           | t
 pg_database_owner           | f        | t          | f             | f           | f           | f
 pg_read_all_data            | f        | t          | f             | f           | f           | f
 pg_write_all_data           | f        | t          | f             | f           | f           | f
 pg_monitor                  | f        | t          | f             | f           | f           | f
 pg_read_all_settings        | f        | t          | f             | f           | f           | f
 pg_read_all_stats           | f        | t          | f             | f           | f           | f
 pg_stat_scan_tables         | f        | t          | f             | f           | f           | f
 pg_read_server_files        | f        | t          | f             | f           | f           | f
 pg_write_server_files       | f        | t          | f             | f           | f           | f
 pg_execute_server_program   | f        | t          | f             | f           | f           | f
 pg_signal_backend           | f        | t          | f             | f           | f           | f
 pg_checkpoint               | f        | t          | f             | f           | f           | f
 pg_use_reserved_connections | f        | t          | f             | f           | f           | f
 pg_create_subscription      | f        | t          | f             | f           | f           | f
 oli                         | f        | t          | f             | f           | t           | f

postgres=# SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication
 FROM pg_roles
 WHERE rolcanlogin = true;
 rolname  | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication
----------+----------+------------+---------------+-------------+-------------+----------------
 postgres | t        | t          | t             | t           | t           | t
 oli      | f        | t          | f             | f           | t           | f

Table permissions

postgres=# SELECT * FROM information_schema.role_table_grants where grantee NOT IN ('postgres', 'PUBLIC');
 grantor  |      grantee      | table_catalog | table_schema |         table_name         | privilege_type | is_grantable | with_hierarchy
----------+-------------------+---------------+--------------+----------------------------+----------------+--------------+----------------
 postgres | pg_monitor        | postgres      | public       | pg_buffercache             | SELECT         | NO           | YES
 postgres | pg_read_all_stats | postgres      | pg_catalog   | pg_backend_memory_contexts | SELECT         | NO           | YES
 postgres | pg_read_all_stats | postgres      | pg_catalog   | pg_shmem_allocations       | SELECT         | NO           | YES

Ownership

postgres=# SELECT * FROM pg_tables where tableowner NOT IN ('postgres');
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+-----------+------------+------------+------------+----------+-------------+-------------

Schema permissions

postgres=# SELECT r.usename AS grantor,
             e.usename AS grantee,
             nspname,
             privilege_type,
             is_grantable
        FROM pg_namespace
JOIN LATERAL (SELECT *
                FROM aclexplode(nspacl) AS x) a
          ON true
        JOIN pg_user e
          ON a.grantee = e.usesysid
        JOIN pg_user r
          ON a.grantor = r.usesysid;
 grantor  | grantee  |      nspname       | privilege_type | is_grantable
----------+----------+--------------------+----------------+--------------
 postgres | postgres | pg_catalog         | USAGE          | f
 postgres | postgres | pg_catalog         | CREATE         | f
 postgres | postgres | information_schema | USAGE          | f
 postgres | postgres | information_schema | CREATE         | f

Schema-level privileges

postgres=# SELECT*
 FROM information_schema.role_schema_grants
 WHERE grantee = 'username';

Database-level privileges

postgres=# SELECT datname, datacl
 FROM pg_database;

https://stackoverflow.com/questions/40759177/postgresql-show-all-the-privileges-for-a-concrete-user

• DROP USER
• ALTER USER

postgres=# ALTER USER oli WITH PASSWORD 'secret';
ALTER ROLE

• Roles

CREATE USER is an alias to CREATE ROLE.

Resetting PostgreSQL superuser password

$ psql
psql: error: connection to server on socket "/tmp/.s.PGSQL.5434" failed: FATAL:  role "dba" does not exist

$ psql --user=mysql
SQL> CREATE ROLE dba WITH LOGIN SUPERUSER PASSWORD 'secret';
SQL> CREATE USER dba WITH SUPERUSER PASSWORD 'secret';
SQL> CREATE USER dba WITH SUPERUSER;

SQL> CREATE USER dba WITH PASSWORD 'secret';
SQL> ALTER USER dba WITH SUPERUSER;
SQL> GRANT ALL PRIVILEGES ON DATABASE test to dba;

Sources

• https://www.postgresql.org/docs/current/sql-createuser.html
• https://phoenixnap.com/kb/postgres-create-user
• https://www.postgresql.org/docs/current/user-manag.html
• https://www.strongdm.com/blog/postgres-create-user
• https://www.strongdm.com/blog/postgres-list-users

Securing PostgreSQL

Encryption

none in Vanilla!

• In transit (TLS/SSL)
• At rest: none in Vanilla. But in Percona PostgreSQL!

Maintenance tasks

Creating a Cluster, a Database a Schema

• Creating a new Database Cluster: https://www.postgresql.org/docs/current/creating-cluster.html initdb https://www.postgresql.org/docs/current/app-initdb.html
• Create a DATABASE
• Create a SCHEMA

Upgrading PostgreSQL

Caution: Cluster in the meaning of “one single instance”!

• Minor Release Upgrade 18.3 → 18.4
• Major Release Upgrade 18.x → 19.y

This is true since 10.x

Minor Releases

Always compatible (stop → replace binaries → start)

Major Releases

Internal data storage format is subject to change

• traditional: dump (pg_dump, pg_dump_all with binaries of the newer version!) → restore (slow)
• pg_upgrade (faster, with relinking?)
• mysql_upgrade vs. pg_upgrade only needed for Major upgrades! See details for -relink etc. Quasi “inplace” is possible nowadays.
• Logical replication (built-in logical replication, pglogical, Slony, Londiste, Bucardo, …)

Problems

• WARNING: database "postgres" has a collation version mismatch
  → Reindex
  See also collation
• 13 → 14: Password hash has changed!
  → New password

Sources

• https://www.shinguz.ch/computer/postgresql/interesting-postgresql-blog-articles/#upgrade
• https://www.postgresql.org/docs/current/upgrading.html
• https://www.postgresql.org/docs/current/pgupgrade.html
• https://www.crunchydata.com/blog/examining-postgres-upgrades-with-pg_upgrade
• https://www.reddit.com/r/PostgreSQL/comments/130g94y/major_version_upgrades_with_pg_upgrade_in_place/
• https://www.dbi-services.com/blog/postgresql-18-swap-mode-for-pg_upgrade/
• https://www.percona.com/blog/fast-upgrade-of-legacy-postgresql-with-minimum-downtime-using-pg_upgrade/
• https://www.cybertec-postgresql.com/en/operating-system-upgrade-corrupts-indexes/
• https://wiki.archlinux.org/title/PostgreSQL#Upgrading_PostgreSQL
• https://decatec.de/linux/postgresql-upgrade-auf-neue-version-durchfuehren/
• https://www.postgresql.eu/events/pgconfeu2024/sessions/session/5797/slides/573/pg_upgrades_2023.pdf

Defragmentation: VACUUM / OPTIMIZE

• VACUUM See also here
  • monitoring/maintenance/ (Autovacuum daemon)
• Autovacuum

SQL> OPTIMIZE TABLE test;   -- MariaDB/MySQL style
SQL> VACUUM test;   -- PostgreSQL style

Monitoring Vacuum: pg_stat_progress_vacuum

Optimizer statistics: ANALYZE

SQL> ANALYZE TABLE test;   -- MariaDB/MySQL style
SQL> ANALYZE test;   -- PostgreSQL style

Monitoring ANALYZE: pg_stat_progress_analyze

Table checksum

History

• 9.3 introduced, default off, decide at initdb
• 12 pg_checksum offline
• 14 .. 17 enable
• 18 default on
• 19 enable online

SQL> SHOW data_checksums;
 data_checksums 
----------------
 on

$ pg_checksums --enable --pgdata=/home/dba/database/postgres-18/data
pg_checksums: error: cluster must be shut down

$ pg_checksums --enable --pgdata=/home/dba/database/postgres-18/data
pg_checksums: error: data checksums are already enabled in cluster

SQL> SELECT datname, checksum_failures, checksum_last_failure FROM pg_stat_database;
  datname  | checksum_failures | checksum_last_failure 
-----------+-------------------+-----------------------
           |                 0 | 
 postgres  |                 0 | 
 test      |                 0 | 
 template1 |                 0 | 
 template0 |                 0 | 

Caution: During upgrade with pg_upgrade data_checksum must be the same on both, old and new instance!

Sources

• Christophe Pettus, PGX, 2026-06-04: All Your GUCs in a Row: data_checksums

Table check and repair

• CHECK TABLE
• CHECKSUM TABLE
• REPAIR TABLE

Error Reporting and Logging

Equivalents to:

• Error Log
• General Query Log
• Slow Query Log
• MariaaDB SQL Error Log and
• MySQL Audit Log

Location of the Logs

Caution: pg_lsclusters is part of Debian/Ubuntu and NOT Vanilla PostgreSQL

$ pg_lsclusters
Ver Cluster Port Status                Owner    Data directory              Log file
16  main    5432 online                postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16-main.log

SQL> show data_directory;
           data_directory            
-------------------------------------
 /home/dba/database/postgres-18/data

postgres=# select name, setting from pg_settings where name like ’log_dir%’ or name = ‘data_directory’; name | setting
—————-+————————————- data_directory | /home/dba/database/postgres-18/data log_directory | log

Error Log

https://www.postgresql.org/docs/current/runtime-config-logging.html

• Contents

2025-07-01 15:15:06.409 CEST [23470] LOG:  starting PostgreSQL 16.9 (Ubuntu 16.9-0ubuntu0.24.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.3.0-6ubuntu2~24.04) 13.3.0, 64-bit
2025-07-01 15:15:06.409 CEST [23470] LOG:  listening on IPv6 address "::1", port 5432
2025-07-01 15:15:06.409 CEST [23470] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2025-07-01 15:15:06.413 CEST [23470] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2025-07-01 15:15:06.445 CEST [23473] LOG:  database system was shut down at 2025-07-01 15:14:54 CEST
2025-07-01 15:15:06.498 CEST [23470] LOG:  database system is ready to accept connections
...
2025-07-01 15:15:18.004 CEST [23470] LOG:  received fast shutdown request
2025-07-01 15:15:18.009 CEST [23470] LOG:  aborting any active transactions
2025-07-01 15:15:18.011 CEST [23470] LOG:  background worker "logical replication launcher" (PID 23476) exited with exit code 1
2025-07-01 15:15:18.011 CEST [23471] LOG:  shutting down
2025-07-01 15:15:18.015 CEST [23471] LOG:  checkpoint starting: shutdown immediate
2025-07-01 15:15:18.077 CEST [23471] LOG:  checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.005 s, sync=0.002 s, total=0.067 s; sync files=2, longest=0.001 s, average=0.001 s; distance=0 kB, estimate=0 kB; lsn=4/8A753888, redo lsn=4/8A753888
2025-07-01 15:15:18.081 CEST [23470] LOG:  database system is shut down

• Configuration variables

postgres=# SHOW data_directory;
       data_directory
-----------------------------
 /var/lib/postgresql/16/main

and

postgres=# SELECT name, setting FROM pg_settings WHERE name LIKE 'log\_%' OR name = 'logging_collector';
               name                |            setting
-----------------------------------+--------------------------------
 log_autovacuum_min_duration       | 600000
 log_checkpoints                   | on
 log_connections                   | off
 log_destination                   | stderr
 log_directory                     | log
 log_disconnections                | off
 log_duration                      | off
 log_error_verbosity               | default
 log_executor_stats                | off
 log_file_mode                     | 0600
 log_filename                      | postgresql-%Y-%m-%d_%H%M%S.log
 log_hostname                      | off
 log_line_prefix                   | %m [%p] %q%u@%d
 log_lock_waits                    | off
 log_min_duration_sample           | -1
 log_min_duration_statement        | -1
 log_min_error_statement           | error
 log_min_messages                  | warning
 log_parameter_max_length          | -1
 log_parameter_max_length_on_error | 0
 log_parser_stats                  | off
 log_planner_stats                 | off
 log_recovery_conflict_waits       | off
 log_replication_commands          | off
 log_rotation_age                  | 1440
 log_rotation_size                 | 10240
 log_startup_progress_interval     | 10000
 log_statement                     | none
 log_statement_sample_rate         | 1
 log_statement_stats               | off
 log_temp_files                    | -1
 log_timezone                      | Europe/Zurich
 log_transaction_sample_rate       | 0
 log_truncate_on_rotation          | off
 logging_collector                 | off
(35 rows)

log_statement: none,ddl, mod,all

log_hostname

SELECT set_config(’log_statement’, ‘all’, false);

ALTER SYSTEM SET log_statement = ‘all’; ALTER SYSTEM SET log_hostname = true; SELECT pg_reload_conf();

ALTER SYSTEM RESET log_statement;

ALTER DATABASE current_database() SET log_statement = ‘all’;

SELECT name, setting, context, source, boot_val, reset_val, sourcefile, pending_restart from pg_settings WHERE NAME LIKE ’log%’ limit 20;

Sources

• https://www.postgresql.org/docs/current/runtime-config-logging.html
• https://www.atlassian.com/data/admin/how-to-log-queries-in-postgresql