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

• Shridhar Khanal, Stormatics, 2026-07-23: The Right Way to Give a Third-Party DBA Access to Your PostgreSQL Database

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

Vacuum / Optimize / Defragment

• Nikolay Sivko, coroot, 2026-07-21: Let’s break autovacuum in Postgres: reproducing failures to make it observable

Analyze

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, …)
• Check also libc collcation changes!

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
• Semab Tariq, Stormatics, 2026-06-16: How To Run Logical Replication On A Production PostgreSQL Cluster

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

$ vacuumlo

Routine Vacuuming: https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND

Routine Database Maintenance Tasks https://www.postgresql.org/docs/current/maintenance.html

https://www.depesz.com/2023/02/06/when-to-use-vacuum-full/

vacuum full is completely separate operation and has nothing to do with a regular vacuum, other than sharing the command name.

vacuum full completely locks the table, and rebuilds it on disk from scratch. It is prohibitive in a production setting without dedicated special maintenance times. Extensions can help with it, but it still a major operation.

vacuum analyze is just a routine procedure that should be run quite often on each of your tables to clean up dead tuples

and rebuild stats.

They are just completely different things. One should be at the minimum ran once a day, the other you shouldn’t be running more than once every few months. If you are, you dont know what you’re doing and you have other problems you trying to cover up with a vacuum full.

I would honestly look at you funny if you suggested one over the other, not sure what sort of tests you’re running

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
• MariaDB SQL Error Log and
• MySQL Audit Log

See also Slow Query Log! https://www.crunchydata.com/blog/postgres-logging-for-performance-optimization

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