PostgreSQL

Contents

PostgreSQL
pgadmin4
pgLoader
Abbreviations
SQL
pgModeler
pspg - PostgreSQL pager
1. Extensions
  1. postgresql-17-pg-checksums

About

PostgreSQL is a powerful, open source object-relational database system with over 30 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance.

PostgreSQL’s community pronounces PostgreSQL as /ˈpoʊstɡrɛs ˌkjuː ˈɛl/. The original name of PostgreSQL is Postgres therefore, sometimes PostgreSQL is referred as Postgres.

Installation

3rd-Party Repo

Debian's version will most likely not be the most recent. E.g.
- current stable Debian Codename is Buster and offers PostgreSQL 11,
- current stable PostgreSQL 12 and 13 is already in beta 2.
Many additional tools that are not or not yet available in Debian.
- pgadmin4, …

postgresql.org: Install Repo in Debian

Import the repository signing key

   1 ### OLD WAY
   2 # wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc \
   3 #        |sudo apt-key add -
   4 ### NEW WAY
   5 TMPFILE="$(mktemp)"
   6 wget --quiet -O - \
   7         https://www.postgresql.org/media/keys/ACCC4CF8.asc \
   8         |sudo sh -c 'gpg --dearmor \
   9                 > '"$TMPFILE"
  10 [ -f "$TMPFILE" ] && \
  11         sudo mv "$TMPFILE" \
  12                 /usr/share/keyrings/postgresql-keyring.gpg
  13 chmod 0644 /usr/share/keyrings/postgresql-keyring.gpg
  14 ### ALTERNATIVE
  15 TMPFILE="$(mktemp)"
  16 wget --quiet -O - \
  17         https://www.postgresql.org/media/keys/ACCC4CF8.asc \
  18         |sudo sh -c 'gpg --dearmor \
  19                 > '"$TMPFILE"
  20 [ -f "$TMPFILE" ] && \
  21         sudo mv "$TMPFILE" \
  22                 /etc/apt/keyrings/postgresql-keyring.gpg
  23 chmod 0644 /etc/apt/keyrings/postgresql-keyring.gpg

Create the repository configuration file

   1 ### CURRENT STABLE VERSION
   2 sudo sh -c 'echo "deb [signed-by=/usr/share/keyrings/postgresql-keyring.gpg]" \
   3         "http://apt.postgresql.org/pub/repos/apt" \
   4         "$(lsb_release -cs)-pgdg main" \
   5         > /etc/apt/sources.list.d/pgdg.list'
   6 ### THERE ARE ADDITIONAL VERSION SPECIFIC COMPONENTS TO 'main' e.g. '14'
   7 sudo sh -c 'echo "deb [signed-by=/usr/share/keyrings/postgresql-keyring.gpg]" \
   8         "http://apt.postgresql.org/pub/repos/apt" \
   9         "$(lsb_release -cs)-pgdg main 14 15" \
  10         > /etc/apt/sources.list.d/pgdg.list'
  11 sudo sh -c 'echo "deb [signed-by=/etc/apt/keyrings/postgresql-keyring.gpg]" \
  12         "http://apt.postgresql.org/pub/repos/apt" \
  13         "$(lsb_release -cs)-pgdg main" \
  14         > /etc/apt/sources.list.d/pgdg.list'

Add apt pinning as necessary

   1 cat <<-EOF > /etc/apt/preferences.d/pgdg
   2 Package:        *
   3 Pin:            origin apt.postgresql.org
   4 Pin-Priority:   130
   5 EOF

Update the package lists

   1 apt update

Install the PostgreSQL Global Development Group (PGDG) keyring

   1 apt install pgdg-keyring

Then continue as in #Distribution

Distribution

Install the latest version of PostgreSQL.

   1 apt install postgresql

If you want a specific version, use 'postgresql-11' or similar instead of 'postgresql':

   1 apt install postgresql-11

Configure

Performance tuning

Links

Wiki Tuning Your PostgreSQL Server

General advice

You will probably not kill the problem with hardware.
- Big dataset can only be managed by grey matter.
There is no silver bullet
- Measure, analyze, take counter measures
Queries are the cause of load. :-D
- Optimize your queries
- Use small datasets
- Return multiple results a from a single query, to reduce the amount of queries and thereby avoiding reading the dataset multiple times.
Enable pg_stat_statements

pg_stat_statements

Posgresql Docs current - ALTER SYSTEM

The overhead should be not to much.

This command configures the parameter in /var/lib/postgresql/13/main/postgresql.auto.conf

   1 ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';

Then restart postgresql

   1 systemctl restart postgresql

Check the extentsion

   1 CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
   2 SELECT calls, query FROM pg_stat_statements LIMIT 1;

The following queries have been extracted from an enlightening talk held by
Hans Schönig (IT, CYBERTEC PostgreSQL International GmbH) on Chemnitzer Linux Tage 2022

Magic query to identify expensive queries :-D

   1 SELECT substring(query, 1, 50) AS short_query,
   2        round(total_exec_time::numeric,2) AS total_time,
   3        calls,
   4        round(mean_exec_time::numeric, 2) AS mean_time,
   5        round((100 * total_exec_time / sum(total_exec_time::numeric)
   6                OVER ())::numeric, 2) AS percentage_overall
   7 FROM   pg_stat_statements
   8 ORDER  BY total_exec_time DESC
   9 LIMIT 20;

Identify expensive sequential scans that happen often to eliminate sequential scans by using indexes.

   1 SELECT  schemaname, relname, seq_scan, seq_tup_read,
   2         idx_scan, seq_tup_read / seq_scan AS avg
   3 FROM    pg_stat_user_tables
   4 WHERE   seq_scan > 0
   5 ORDER BY seq_tup_read DESC;

To reduce the size of the execution plans you may
disable parallelism during the gathering

   1 SET max_parallel_workers_per_gather TO 0;

Manage

Any administrative command should be issued by user postgres (for simplicity). This user is authenticated locally using the peer method, retrieving the username from the operating system.

   1 su - postgres

Create user

   1 createuser -e --pwprompt matrix-synapse
   2 Geben Sie das Passwort der neuen Rolle ein:
   3 Geben Sie es noch einmal ein:
   4 SELECT pg_catalog.set_config('search_path', '', false)
   5 CREATE ROLE "matrix-synapse"
   6         PASSWORD 'hashed_password'
   7         NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;

Create database

Create database (with a specific collation)

   1 createdb -e --encoding=UTF8 \
   2         --lc-collate=C --lc-ctype=C \
   3         -T template0 --owner=matrix-synapse \
   4         matrix-synapse
   5 
   6 SELECT pg_catalog.set_config('search_path', '', false);
   7 CREATE DATABASE "matrix-synapse" OWNER "matrix-synapse" ENCODING 'UTF8' TEMPLATE template0 LC_COLLATE 'C' LC_CTYPE 'C';

The collation of a database maybe changed by

dumping the old DB,
creating a new DB with the correct collation
restoring the dump to the new DB
and moving the DBs to their correct names.

List users

   1 # su - postgres
   2 $ psql
   3 psql (12.3 (Debian 12.3-1.pgdg100+1))
   4 Geben Sie »help« für Hilfe ein.
   5 
   6 ### ALTERNATIVE1
   7 postgres=# \du
   8

Change password

   1 # su - postgres
   2 $ psql
   3 psql (12.3 (Debian 12.3-1.pgdg100+1))
   4 Geben Sie »help« für Hilfe ein.
   5 
   6 ### ALTERNATIVE1
   7 postgres=# \password user_name
   8 Neues Passwort eingeben:
   9 Geben Sie es noch einmal ein:
  10 postgres=# \q
  11 
  12 ### ALTERNATIVE2
  13 postgres=# ALTER USER user_name WITH PASSWORD 'new_password';
  14 postgres=# \q
  15

Grant privileges to user

Rename database

Move databases around

   1 ALTER DATABASE "matrix-synapse"
   2 RENAME TO "matrix-synapse_old";
   3 
   4 ALTER DATABASE "matrix-synapse1"
   5 RENAME TO "matrix-synapse";

Upgrade

Please also see the docs on the system
/usr/share/doc/postgresql-14/README.Debian.gz

Install the package of the new major release.

Now you'll have to version of postgres running on different ports (wasting memory). As you can see in
ss -tlnp| column -t| grep -e State -e post

   1 State   Recv-Q  Send-Q  Local           Address:Port  Peer
   2 LISTEN  0       224     127.0.0.1:5432  0.0.0.0:*     users:(("postgres",pid=9722,fd=4))
   3 LISTEN  0       244     127.0.0.1:5434  0.0.0.0:*     users:(("postgres",pid=10443,fd=6))
   4 LISTEN  0       224     [::1]:5432      [::]:*        users:(("postgres",pid=9722,fd=3))
   5 LISTEN  0       244     [::1]:5434      [::]:*        users:(("postgres",pid=10443,fd=5))

If we try to upgrade, the process aborts because a cluster main in version 13 already exists.

   1 postgres@backup1:~$ pg_upgradecluster 11 main
   2 Error: target cluster 13/main already exists

We need to drop this cluster to perform a upgrade. So we quickly check, if the databases of the higher version are empty.

   1 root@backup1 ~ # su - postgres
   2 postgres@backup1:~$ LANG=C psql -p 5434
   3 psql (13.1 (Debian 13.1-1.pgdg100+1))
   4 Type "help" for help.
   5 
   6 postgres=# \l
   7                                   List of databases
   8    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
   9 -----------+----------+----------+-------------+-------------+-----------------------
  10  postgres  | postgres | UTF8     | de_DE.UTF-8 | de_DE.UTF-8 | 
  11  template0 | postgres | UTF8     | de_DE.UTF-8 | de_DE.UTF-8 | =c/postgres          +
  12            |          |          |             |             | postgres=CTc/postgres
  13  template1 | postgres | UTF8     | de_DE.UTF-8 | de_DE.UTF-8 | =c/postgres          +
  14            |          |          |             |             | postgres=CTc/postgres
  15 (3 rows)
  16 
  17 postgres=# \c postgres 
  18 You are now connected to database "postgres" as user "postgres".
  19 postgres=# \d
  20 Did not find any relations.
  21 postgres=# \q
  22 postgres@backup1:~$

They are empty and we can safely drop the cluster in version 13.

Prior to the the upgrade process all applications that access databases on the old system need to be shut down.

The upgrade process

   1 ### AS ROOT - STOP THE CLUSTER
   2 systemctl stop postgresql@13-main.service
   3 ### AS USER POSTGRES - DROP THE CLUSTER
   4 pg_dropcluster 13 main
   5 ### AS ROOT - RELOAD SYSTEMD
   6 systemctl daemon-reload
   7 ### AS USER POSTGRES - UPGRADE THE CLUSTER
   8 postgres@backup1:~$ pg_upgradecluster 11 main                                                                                                                                                                                                                                                        
   9 Stopping old cluster...                                                                                                                                                                                                                                                                              
  10 Warning: stopping the cluster using pg_ctlcluster will mark the systemd unit as failed. Consider using systemctl:                                                                                                                                                                                    
  11   sudo systemctl stop postgresql@11-main                                                                                                                                                                                                                                                             
  12 Restarting old cluster with restricted connections...                                                                                                                                                                                                                                                
  13 Notice: extra pg_ctl/postgres options given, bypassing systemctl for start operation                                                                                                                                                                                                                 
  14 Creating new PostgreSQL cluster 13/main ...                                                                                                                                                                                                                                                          
  15 /usr/lib/postgresql/13/bin/initdb -D /var/lib/postgresql/13/main --auth-local peer --auth-host md5 --encoding UTF8 --lc-collate de_DE.UTF-8 --lc-ctype de_DE.UTF-8                                                                                                                                   
  16 Die Dateien, die zu diesem Datenbanksystem gehören, werden dem Benutzer                                                                                                                                                                                                                              
  17 »postgres« gehören. Diesem Benutzer muss auch der Serverprozess gehören.                                                                                                                                                                                                                             
  18                                                                                                                                                                                                                                                                                                      
  19 Der Datenbankcluster wird mit der Locale »de_DE.UTF-8« initialisiert werden.                                                                                                                                                                                                                         
  20 Die Standardtextsuchekonfiguration wird auf »german« gesetzt.                                                                                                                                                                                                                                        
  21                                                                                                                                                                                                                                                                                                      
  22 Datenseitenprüfsummen sind ausgeschaltet.                                                                                                                                                                                                                                                            
  23 
  24 berichtige Zugriffsrechte des bestehenden Verzeichnisses /var/lib/postgresql/13/main ... ok
  25 erzeuge Unterverzeichnisse ... ok
  26 wähle Implementierung von dynamischem Shared Memory ... posix
  27 wähle Vorgabewert für max_connections ... 100
  28 wähle Vorgabewert für shared_buffers ... 128MB
  29 wähle Vorgabewert für Zeitzone ... Europe/Berlin
  30 erzeuge Konfigurationsdateien ... ok
  31 führe Bootstrap-Skript aus ... ok
  32 führe Post-Bootstrap-Initialisierung durch ... ok
  33 synchronisiere Daten auf Festplatte ... ok
  34 
  35 Erfolg. Sie können den Datenbankserver jetzt mit
  36 
  37     pg_ctlcluster 13 main start
  38 
  39 starten.
  40 
  41 Warning: systemd does not know about the new cluster yet. Operations like "service postgresql start" will not handle it. To fix, run:
  42   sudo systemctl daemon-reload
  43 Ver Cluster Port Status Owner    Data directory              Log file
  44 13  main    5434 down   postgres /var/lib/postgresql/13/main /var/log/postgresql/postgresql-13-main.log
  45 Starting new cluster...
  46 Notice: extra pg_ctl/postgres options given, bypassing systemctl for start operation
  47 Roles, databases, schemas, ACLs...
  48  set_config
  49 ------------
  50 
  51 (1 Zeile)
  52 
  53  set_config
  54 ------------
  55 
  56 (1 Zeile)
  57 
  58  set_config
  59 ------------
  60 
  61 (1 Zeile)
  62 
  63  set_config
  64 ------------
  65 
  66 (1 Zeile)
  67 
  68 Fixing hardcoded library paths for stored procedures...
  69 Upgrading database template1...
  70 Analyzing database template1...
  71 Fixing hardcoded library paths for stored procedures...
  72 Upgrading database bareos...
  73 Analyzing database bareos...
  74 Fixing hardcoded library paths for stored procedures...
  75 Upgrading database postgres...
  76 Analyzing database postgres...
  77 Copying old configuration files...
  78 Copying old start.conf...
  79 Copying old pg_ctl.conf...
  80 Stopping target cluster...
  81   sudo systemctl stop postgresql@13-main
  82 Stopping old cluster...
  83   sudo systemctl stop postgresql@11-main
  84 Disabling automatic startup of old cluster...
  85 Configuring old cluster to use a different port (5434)...
  86 Starting target cluster on port 5432...
  87 Warning: the cluster will not be running as a systemd service. Consider using systemctl:
  88   sudo systemctl start postgresql@13-main
  89 
  90 Success. Please check that the upgraded cluster works. If it does,
  91 you can remove the old cluster with
  92     pg_dropcluster 11 main
  93 
  94 Ver Cluster Port Status Owner    Data directory              Log file
  95 11  main    5434 down   postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log
  96 Ver Cluster Port Status Owner    Data directory              Log file
  97 13  main    5432 online postgres /var/lib/postgresql/13/main /var/log/postgresql/postgresql-13-main.log
  98 postgres@backup1:~

Test everything and get rid of the old relicts.

Hint: Be careful before removing the old postgresql binaries.

Make sure nothing else depends (in some indirect way) on the old binaries. Debian does not maintain all versions in its repositories (e.g. only 11 13 15).

For example: The backend akonadi-backend-postgresql only depends on postgresql which may be satisfied after an upgrade, since both versions 14 and 15 are concurrently installed. But akonadi-server won't start up without the old binaries, because it hasn't been upgraded and the old binaries are necessary during an upgrade!

Drop old clusters

After an upgrade the old versions still reside on disk pg_lsclusters

   1 Ver Cluster Port Status                Owner    Data directory              Log file
   2 10  main    5433 down,binaries_missing postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log
   3 11  main    5434 down                  postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log
   4 13  main    5432 online                postgres /var/lib/postgresql/13/main /var/log/postgresql/postgresql-13-main.log

Drop the old clusters

   1 # pg_dropcluster 
   2 # Usage: /usr/bin/pg_dropcluster [--stop] <version> <cluster>
   3 pg_dropcluster 10 main
   4 pg_dropcluster 11 main

Check activity on database

Make sure to connect to the right database instance (version) with
pg_lsclusters

Show activity

   1 SELECT * FROM pg_stat_activity WHERE datname='DATABASE_NAME';

Drop database

   1 sudo su - postgres
   2 dropdb DATABASE_NAME

Backup

Postgres Docs - pg_dump — extract a PostgreSQL database into a script file or other archive file

Create directory for the logical backup

   1 DIR_BACKUP="/var/backups/postgres"
   2 install -o postgres -g postgres -m 750 -d "$DIR_BACKUP"

Dump the database

   1 su - postgres
   2 psql -l
   3 pg_dump -v matrix-synapse \
   4         |pigz -c > "$DIR_BACKUP/matrix-synapse_$(date +%F_%H:%M).sql.gz"

Restore

Postgres Docs - pg_restore — restore a PostgreSQL database from an archive file created by pg_dump

#Create database first

Restore the database

   1 su - postgres
   2 psql -l
   3 ### WITH TEXT FORMAT
   4 pigz -dc < "$DIR_BACKUP/matrix-synapse_2022-10-19_01:52.sql.gz" \
   5         |psql -v -d matrix-synapse1
   6 ### WITH NON-TEXT FORMAT
   7 pigz -dc < "$DIR_BACKUP/matrix-synapse_2022-10-19_01:52.sql.gz" \
   8         |pg_restore -v -d matrix-synapse1

Fix out of sync sequences

Sequences and IDs of tables may get out of sync.

   1 An exception occurred while executing a query: SQLSTATE[23505]: Unique violation: 7 ERROR:  duplicate key value violates unique constraint "calendars_pkey"
   2 DETAIL:  Key (id)=(63) already exists.

The following commands can resolve the issue. Please adjust to your needs.

   1 nextclouddb=# SELECT coalesce(max(id)+1, 1) FROM calendars;
   2  coalesce 
   3 ----------
   4        87
   5 (1 row)
   6 
   7 nextclouddb=# SELECT * FROM calendars_id_seq;
   8  last_value | log_cnt | is_called 
   9 ------------+---------+-----------
  10          63 |      32 | t
  11 (1 row)
  12 
  13 nextclouddb=# SELECT setval(pg_get_serial_sequence('calendars', 'id'), coalesce(max(id)+1, 1), false) FROM calendars;
  14  setval 
  15 --------
  16      87
  17 (1 row)

pgadmin4

About

PostgreSQL management tool

pgAdmin is an open source administration and management tool for the PostgreSQL database. It includes a graphical administration interface, an SQL query tool, a procedural code debugger and much more. The tool is designed to answer the needs of developers, DBAs and system administrators alike.

Bug REL-4_21

REL-4_21 has a bug

   1 commit 370094274c8cfe98c4744e99b00236263a4c4db5
   2 Author: Aditya Toshniwal <aditya.toshniwal@enterprisedb.com>
   3 Date:   Mon May 4 15:00:45 2020 +0100
   4 
   5     Fixed post-login redirect location when running in server mode under a non-default root. Fixes #5473
   6

REL-4_22 already contains the the fix. In the meantime…

post-login-redirect-fix.patch

   1 18c18,19
   2 < from flask_security.utils import config_value, get_post_logout_redirect
   3 ---
   4 > from flask_security.utils import config_value, get_post_logout_redirect, \
   5 >     get_post_login_redirect
   6 63c64
   7 <         return flask.redirect('/')
   8 ---
   9 >         return flask.redirect(get_post_login_redirect())

Apply the patch

   1 patch /usr/share/pgadmin4/web/pgadmin/authenticate/__init__.py \
   2          < post-login-redirect-fix.patch

Install

You will first need to setup a #3rd-Party Repo

https://www.pgadmin.org/download/pgadmin-4-apt/

   1 #
   2 # Setup the repository
   3 #
   4 
   5 ### Install the public key for the repository (if not done previously):
   6 #sudo curl https://www.pgadmin.org/static/packages_pgadmin_org.pub \
   7 #        | sudo apt-key add
   8 
   9 PUBKEY_URL='https://www.pgadmin.org/static/packages_pgadmin_org.pub'
  10 KEYRING_DIR="/etc/apt/keyrings"
  11 KEYRING_FILE="pgadmin4-keyring.gpg"
  12 KEYRING="$KEYRING_DIR/$KEYRING_FILE"
  13 curl "$PUBKEY_URL" \
  14         | gpg --dearmor \
  15                 > "$KEYRING"
  16 
  17 # Create the repository configuration file:
  18 REPO_URL="https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs)"
  19 SUITE="pgadmin4"
  20 COMPONENTS=(main)
  21 echo "deb [signed-by=$KEYRING] $REPO_URL $SUITE ${COMPONENTS[*]}" \
  22         > /etc/apt/sources.list.d/pgadmin4.list
  23 gpg -n -q --import --import-options import-show "$KEYRING"
  24 apt update

/etc/apt/preferences.d/pgadmin4

   1 Package:        *
   2 Pin:            release o=pgAdmin Development Team, a=pgadmin4
   3 Pin-Priority:   130

WGSI application for apache2

   1 #
   2 # Install pgAdmin
   3 #
   4 
   5 # Install for both desktop and web modes:
   6 sudo apt install pgadmin4
   7 
   8 # Install for desktop mode only:
   9 sudo apt install pgadmin4-desktop
  10 
  11 # Install for web mode only: 
  12 sudo apt install pgadmin4-web 
  13 
  14 # Configure the webserver, if you installed pgadmin4-web:
  15 sudo /usr/pgadmin4/bin/setup-web.sh

GUI application

   1 apt install pgadmin4

/etc/apache2/sites-available/pgadmin4.conf

   1  <VirtualHost *:80>
   2         Define          SERVER_NAME             servername.domain.tld
   3         ServerName      ${SERVER_NAME}
   4         ServerAlias     servername
   5 
   6         ServerAdmin webmaster@domain.tld
   7         DocumentRoot /var/www/html
   8 
   9         # Available loglevels: trace8, ..., trace1, debug, info, notice, warn,
  10         # error, crit, alert, emerg.
  11         # It is also possible to configure the loglevel for particular
  12         # modules, e.g.
  13         #LogLevel info ssl:warn
  14 
  15         ErrorLog ${APACHE_LOG_DIR}/pgadmin4_error.log
  16         CustomLog ${APACHE_LOG_DIR}/pgadmin4_access.log combined
  17 
  18         Include conf-available/pgadmin4.conf
  19 </VirtualHost>
  20 
  21 # vim: syntax=apache ts=4 sw=4 sts=4 sr noet

/etc/apache2/conf-available/pgadmin4.conf

   1 WSGIDaemonProcess pgadmin processes=1 threads=25
   2 WSGIScriptAlias /pgadmin4 /usr/share/pgadmin4/web/pgAdmin4.wsgi
   3 
   4 <Directory /usr/share/pgadmin4/web/>
   5         WSGIProcessGroup pgadmin
   6         WSGIApplicationGroup %{GLOBAL}
   7         Require all granted
   8 </Directory>

   1 a2disconf pgadmin4
   2 a2dissite 000-default.conf
   3 a2ensite pgadmin4
   4 systemctl restart apache2

Application Initialization

pgadmin application initialization

   1 cd /usr/share/pgadmin4/web
   2 python3 setup.py

setup.py has some options

--dump-servers Dump the servers in the DB
--servers One or more servers to dump
--load-servers Load servers into the DB
--user Dump/load servers for the specified username

Email

pgadmin needs to send a email during password change. So you should probably configure a mail server like postfix to relay the email to you mail-account.

You should also edit the source address to contain a valid domain.
/usr/share/pgadmin4/web/config.py

   1 # Flask-Security overrides Flask-Mail's MAIL_DEFAULT_SENDER setting, so
   2 # that should be set as such:
   3 #SECURITY_EMAIL_SENDER = 'no-reply@localhost'
   4 SECURITY_EMAIL_SENDER = 'pgadmin_no-reply@your.domain.tld'

Set password fpr user postgres

   1 su - postgres
   2 psql
   3 \password postgres

Access via ssh-tunnel

Create ssh local port forward to remote localhost

   1 ENTER
   2 ~C
   3 ssh> -L8080:localhost:80

connect to http://localhost:8080

Important paths

WebApp
/usr/share/pgadmin4/web

Data
/var/lib/pgadmin/

Logs
/var/log/pgadmin/pgadmin4.log

Trouble Shooting

Corruption

pgLoader

Migrate databases to postgresql.

Abbreviations

Abbrev.	Full Term
DDL	Data Definition Language
DML	Data Manipulation Language
WAL	Write Ahead Log

SQL

Quoting

String Constants (SQL Syntax Constants):
A string constant in SQL is an arbitrary sequence of characters bounded by single quotes (').
```
   1 SELECT 'This is a string';
```
Delimited identifier or quoted identifier (SQL Syntax Identifiers):
Are formed by enclosing an arbitrary sequence of characters in double-quotes ("). A delimited identifier is always an identifier, never a key word. This also applies to AS "output_name"
```
   1 UPDATE "my_table" SET "a" = 5;
```

In SQL quotes ' or " are escaped by doubling them '' "".

   1 postgres=# SELECT 'There''s no problem with quotes ''," anymore.'
   2 postgres-# AS "Doubling quotes ',""";
   3              Doubling quotes ',"             
   4 ---------------------------------------------
   5  There's no problem with quotes '," anymore.
   6 (1 Zeile)

SQL joins

There are more joins in PostgreSQL this is just reminder.

SCHEMAS

https://www.postgresqltutorial.com/postgresql-schema/

Definition

In PostgreSQL, a schema is a namespace that contains named database objects such as tables, views, indexes, data types, functions, and operators.

One schema belongs to only one database.
One database can have multiple schemas.
Users can only access objects in the schemas that they own, without the USAGE privilege.
Every user has the CREATE and USAGE on the public schema.

Usage

Schemas allow you to organize database objects e.g., tables into logical groups to make them more manageable.
Schemas enable multiple users to use one database without interfering with each other.

Operations

   1 # psql
   2 psql (12.3 (Debian 12.3-1+b1))
   3 Geben Sie »help« für Hilfe ein.
   4 
   5 postgres=# SELECT current_schema();
   6  current_schema 
   7 ----------------
   8  public
   9 (1 Zeile)
  10 
  11 postgres=# SHOW search_path;
  12          search_path         
  13 -----------------------------
  14  "$user", public, rockstable
  15 (1 Zeile)
  16 
  17 postgres=# SET search_path TO sales, rockstable;
  18 SET
  19 postgres=# SHOW search_path;
  20     search_path    
  21 -------------------
  22  sales, rockstable
  23 (1 Zeile)
  24 
  25 postgres=# SET search_path TO "$user", public, sales, rockstable;
  26 SET

Change search path permanently

   1 ALTER ROLE postgres SET search_path TO "$user", public, rockstable;

}}}

pgModeler

https://pgmodeler.io/

It can't be too bad to have a little plan. Design your model and later implement it.

pspg - PostgreSQL pager

github.com okbob/pspg

https://github.com/okbob/pspg Install pspg

   1 apt install pspg

Use it

   1 export PSQL_PAGER='pspg -X'
   2 psql

Extensions

postgresql-17-pg-checksums

Install extension

   1 apt install postgresql-17-pg-checksums