📗 PostgreSQL#

Overview

PostgreSQL is an object-relational database management system (ORDBMS).

Docs Wikipedia Michael Stonebraker

History

1974

Ingres INtelligent Graphic RElational System

1985

Post-Ingres

1995

Postgres95

1996

PostgreSQL

object-relational database management system (ORDBMS)
language PL/PgSql
tools

cli

psql

gui

pgAdmin

postgis

extension which allows GIS (Geographic Information Systems) objects

replication

many systems

system extensions
MVCC (Multiversion Concurrency Control)
- client/server : connections management
ACID

atomic

transaction is complete or not at all

consistent

in case of error the status reverts to the one before

isolated

no interference between transactions

durable

data are available after reboot

☰ Server > Cluster > Databases > Roles > Schemas > Tables

Server

several servers with replication

Cluster

instances with specific configuration and ressources

Database

set of structured data

Roles/Users

users (login,passwd)

Schema

namespace in db (group of elements)

Table

logical storespace

Field

column in table

Row

line with structured data

Base example

create database employee;
create table employee (id int PRIMARY KEY,
username varchar(255),
creation_date varchar not null default now()::date);
insert into employee (id, username) values (1, 'peter');
insert into employee (id, username) values (2, 'paul');
insert into employee (id, username) values (3, 'bobby');

Installation and Configuration Files#

Installation#

> apt install postgresql-13
> systemctl start/stop/status/reload postgresql
> systemctl enable --now postgresql

Main folders#

`/etc/postgresql/13/main`#

<version/pgcluster> configuration directory

/etc/postgresql/13/main
├── conf.d
├── environment
├── pg_ctl.conf
├── pg_hba.conf
├── pg_ident.conf
├── postgresql.conf
└── start.conf

`/var/lib/postgresql/13/main`#

> awk '/^data/' /etc/postgresql/13/main/postgresql.conf
data_directory = '/var/lib/postgresql/13/main'          # use data in another directory

`/usr/lib/postgresql/13/bin`#

clusterdb   pg_archivecleanup  pg_dump         pg_rewind        postgres
createdb    pg_basebackup      pg_dumpall      pg_standby       postmaster
createuser  pgbench            pg_isready      pg_test_fsync    psql
dropdb      pg_checksums       pg_receivewal   pg_test_timing   reindexdb
dropuser    pg_config          pg_recvlogical  pg_upgrade       vacuumdb
initdb      pg_controldata     pg_resetwal     pg_verifybackup  vacuumlo
oid2name    pg_ctl             pg_restore      pg_waldump

`/usr/bin/pg_*`#

/usr/bin/pg_archivecleanup  /usr/bin/pg_dropcluster  /usr/bin/pg_recvlogical
/usr/bin/pg_basebackup      /usr/bin/pg_dump         /usr/bin/pg_renamecluster
/usr/bin/pg_buildext        /usr/bin/pg_dumpall      /usr/bin/pg_restore
/usr/bin/pg_config          /usr/bin/pg_isready      /usr/bin/pg_upgradecluster
/usr/bin/pg_conftool        /usr/bin/pg_lsclusters   /usr/bin/pg_virtualenv
/usr/bin/pg_createcluster   /usr/bin/pg_receivewal
/usr/bin/pg_ctlcluster      /usr/bin/pg_receivexlog

Core and forked processus#

> awk '/^postgres/{$1=$2=$3=$4=$5=$6=$7=$8=$9=$10="" ; print}' <(ps auxfw)
          /usr/lib/postgresql/13/bin/postgres -D /var/lib/postgresql/13/main -c config_file=/etc/postgresql/13/main/postgresql.conf
          \_ postgres: 13/main: checkpointer
          \_ postgres: 13/main: background writer
          \_ postgres: 13/main: walwriter
          \_ postgres: 13/main: autovacuum launcher
          \_ postgres: 13/main: stats collector
          \_ postgres: 13/main: logical replication launcher

> ps -L -C postgres -o pid,pcpu,pmem,args
    PID %CPU %MEM COMMAND
0.0  2.4 /usr/lib/postgresql/13/bin/postgres -D /var/lib/postgresql/13/main -c config_file=/etc/postgresql/13/main/postgresql.conf
0.0 14.0 postgres: 13/main: checkpointer
0.0  8.5 postgres: 13/main: background writer
0.0  0.9 postgres: 13/main: walwriter
0.0  0.7 postgres: 13/main: autovacuum launcher
0.0  0.5 postgres: 13/main: stats collector
0.0  0.6 postgres: 13/main: logical replication launcher
0.0  1.2 postgres: 13/main: wpuser wpdb 192.168.122.1(37188) idle
0.0  1.2 postgres: 13/main: postgres postgres 127.0.0.1(35834) idle

PostgreSQL Binaries#

Common#

pg_ctl
- cluster management
- start/stop/kill
- init : data space creation
- promote : standby promotion
psql
- PG cluster client
- user and/or db
- interactive cli or sql script

> psql -c "alter user postgres with password 'turlututu'"

> \c postgres postgres
postgres=# \password titi
Enter new password for user "titi":
Enter it again:

postgres@debian02:~$ psql -c "select version();"
postgres@debian02:~$ cat sv.sql
select version();
postgres@debian02:~$ psql < sv.sql

Debian Specific#

pg_createcluster	Usage: /usr/bin/pg_createcluster [options] <version> <cluster name> [– <initdb options>]
pg_dropcluster	Usage: /usr/bin/pg_dropcluster [–stop] <version> <cluster>
pg_lsclusters	Usage: /usr/bin/pg_lsclusters [-hjs]
pg_ctlcluster	Usage: /usr/bin/pg_ctlcluster <version> <cluster> <action> [– <pg_ctl options>]

Backups Specific#

pg_dump	instance backup various formats : plain text, binary various levels : cluster/db/table/schema
pg_dumpall	full binary formated backup
pg_restore	restore from a previous pg_dumpall backup

Wrappers#

createdb	create a database
dropdb	remove a database
createuser	create a user
dropuser	remove a user

Maintenance#

reindexdb	reindex a database
vacuumdb	maintenace task (clean up)
vacuumlo	clean up large objects

Boot and Shutdown#

Boot Sequence#

share memory allocation (shared_buffers default 128 Mb)
read control files , $PGDATA/global/pg_control
check recovery checkpoint
check WAL (Write-Ahead Logging ) transactions
no recovery > error
otherwise PG listen and handle the connections

> awk '/^shared_buffer/' /etc/postgresql/13/main/postgresql.conf
shared_buffers = 128MB                  # min 128kB

🤔

> systemctl cat postgresql | grep Exec
ExecStart=/bin/true
ExecReload=/bin/true

See Cluster.

😉

Shutdown Sequence#

OS send a shutdown signal
- if SIGQUIT (hard stop)
  
  no checkpoint
  
  no write mem to disk
  
  no control files update
  
  recovery
  
  transactions lost
- if SIGINT
  
  stop the new connections
  
  stop the current connections
  
  rollback the current transactions
- if SIGTERM
  
  stop the new connections
  
  end the current transactions
create a checkpoint
write mem to disk
update control files (pg_control)

PG Processus#

More ressources

Core and forked processus.

> ps -L -C postgres -o pid,pcpu,pmem,start,time,args
    PID %CPU %MEM  STARTED     TIME COMMAND
0.0  2.4 12:26:51 00:00:02 /usr/lib/postgresql/13/bin/postgres -D /var/lib/postgresql/13/main -c config_file=/etc/postgresql/13/main/postgresql.conf
0.0 14.0 12:26:51 00:00:00 postgres: 13/main: checkpointer
0.0  8.5 12:26:51 00:00:01 postgres: 13/main: background writer
0.0  0.9 12:26:51 00:00:01 postgres: 13/main: walwriter
0.0  0.7 12:26:51 00:00:01 postgres: 13/main: autovacuum launcher
0.0  0.5 12:26:51 00:00:01 postgres: 13/main: stats collector

Core#

postgresql -D <data_dir> -c <config_dir>

> /usr/lib/postgresql/13/bin/postgres --help | awk 'NR<5 || /^[[:space:]]*-[cDhN]/'
postgres is the PostgreSQL server.

Usage:
  postgres [OPTION]...
  -c NAME=VALUE      set run-time parameter
  -D DATADIR         database directory
  -h HOSTNAME        host name or IP address to listen on
  -N MAX-CONNECT     maximum number of allowed connections

Fork#

Engine processus#

checkpointer

write dirty pages (new or modified pages) on data files
writing frequency parameter by duration and/or by WAL switch number
recovery requirement
parameters checkpoint_segments checkpoint_timeout

> awk '/checkpoint/' /etc/postgresql/13/main/postgresql.conf
#checkpoint_timeout = 5min              # range 30s-1d
#checkpoint_completion_target = 0.5     # checkpoint target duration, 0.0 - 1.0
#checkpoint_flush_after = 256kB         # measured in pages, 0 disables
#checkpoint_warning = 30s               # 0 disables
#log_checkpoints = off

background writer
- write dirty pages on data files
walwriter
- write WAL buffer on disk
autovacuum launcher
- vacuum : garbage-collect and optionally analyze a database

stats collector

very important for the engine operation
parameter pg_stats_temp

> awk '/pg_stat_tmp/' /etc/postgresql/13/main/postgresql.conf
stats_temp_directory = '/var/run/postgresql/13-main.pg_stat_tmp'

logical replication launcher
- needed in case of replication
- launch a process for standby connexions

Connexions processus#

postgresql postgresql (idle)

for every connexion
ressource allocation
parameter : work_memory

> awk '/mem/' /etc/postgresql/13/main/postgresql.conf
#work_mem = 4MB                         # min 64kB
#hash_mem_multiplier = 1.0              # 1-1000.0 multiplier on hash table work_mem
#maintenance_work_mem = 64MB            # min 1MB
#autovacuum_work_mem = -1               # min 1MB, or -1 to use maintenance_work_mem
#logical_decoding_work_mem = 64MB       # min 64kB
#shared_memory_type = mmap              # the default is the first option
dynamic_shared_memory_type = posix      # the default is the first option

> ps auxfw | awk '/^postgres.*idle/{$1=$2=$3=$4=$5=$6=$7=$8=$9=$10=""; print}'
         \_ postgres: 13/main: postgres wpdb [local] idle
         \_ postgres: 13/main: postgres postgres [local] idle

PG Memory#

Tools

pgtune.leopard.in.ua

Very important for DB engine, ensure quick access to data.

Memory is not permanent:

scheduled shutdown : writing system > service stop > shutdown
unplanned shutdown : memory lost > recovery

main memories#

shared buffer

default 128 Mb
128 Kb + 16 Kb / connexion
this buffer is written on data files by backgroung writer

> awk '/^shared_buffer/' /etc/postgresql/13/main/postgresql.conf
shared_buffers = 128MB                  # min 128kB

wal buffer

support transactions archive
this buffer is written on wal files by wal writer

work mem

user default allocated memory

maintenance work mem

maintenenace tasks memory
affect recovery time/speed

shared memory = shared buffer + wal buffer + c-log-buffer

Main Directories#

🌐

`/etc/postgresql/13/main`#

Tree structure.

/etc/postgresql/13/main
├── conf.d
├── environment
├── pg_ctl.conf
├── pg_hba.conf
├── pg_ident.conf
├── postgresql.conf
└── start.conf

/etc/postgresql/13/main/postgresql.conf

> awk 'NR<4' /etc/postgresql/13/main/postgresql.conf
# -----------------------------
# PostgreSQL configuration file
# -----------------------------

> grep -v "^[[:space:]]*#.*\|^$" /etc/postgresql/13/main/postgresql.conf | column -t
data_directory              =  '/var/lib/postgresql/13/main'              #     use              data            in        another      directory
hba_file                    =  '/etc/postgresql/13/main/pg_hba.conf'      #     host-based       authentication  file
ident_file                  =  '/etc/postgresql/13/main/pg_ident.conf'    #     ident            configuration   file
external_pid_file           =  '/var/run/postgresql/13-main.pid'          #     write            an              extra     PID          file
listen_addresses            =  '*'
port                        =  5432                                       #     (change          requires        restart)
max_connections             =  100                                        #     (change          requires        restart)
unix_socket_directories     =  '/var/run/postgresql'                      #     comma-separated  list            of        directories
ssl                         =  on
ssl_cert_file               =  '/etc/ssl/certs/ssl-cert-snakeoil.pem'
ssl_key_file                =  '/etc/ssl/private/ssl-cert-snakeoil.key'
shared_buffers              =  128MB                                      #     min              128kB
dynamic_shared_memory_type  =  posix                                      #     the              default         is        the          first      option
max_wal_size                =  1GB
min_wal_size                =  80MB
log_line_prefix             =  '%m                                        [%p]  %q%u@%d          '               #         special      values:
log_timezone                =  'Europe/Paris'
cluster_name                =  '13/main'                                  #     added            to              process   titles       if         nonempty
stats_temp_directory        =  '/var/run/postgresql/13-main.pg_stat_tmp'
datestyle                   =  'iso,                                      mdy'
timezone                    =  'Europe/Paris'
lc_messages                 =  'en_US.UTF-8'                              #     locale           for             system    error        message
lc_monetary                 =  'en_US.UTF-8'                              #     locale           for             monetary  formatting
lc_numeric                  =  'en_US.UTF-8'                              #     locale           for             number    formatting
lc_time                     =  'en_US.UTF-8'                              #     locale           for             time      formatting
default_text_search_config  =  'pg_catalog.english'
include_dir                 =  'conf.d'                                   #     include          files           ending    in           '.conf'    from

/etc/postgresql/13/main/pg_hba.conf

> awk 'NR<2' /etc/postgresql/13/main/pg_hba.conf
# PostgreSQL Client Authentication Configuration File

> grep -v "^[[:space:]]*#.*\|^$" /etc/postgresql/13/main/pg_hba.conf
local   all             postgres                                peer
local   all             all                                     peer
host    all             all             127.0.0.1/32            md5
host    all             all             192.168.0.0/16          md5
host    all             all             ::1/128                 md5
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5

/etc/postgresql/13/main/pg_ident.conf

> awk 'NR<2' /etc/postgresql/13/main/pg_ident.conf
# PostgreSQL User Name Maps

`/var/lib/postgresql/13/main/`#

Tree structure.

/var/lib/postgresql/13/main
├── base
├── global
├── pg_commit_ts
├── pg_dynshmem
├── pg_logical
├── pg_multixact
├── pg_notify
├── pg_replslot
├── pg_serial
├── pg_snapshots
├── pg_stat
├── pg_stat_tmp
├── pg_subtrans
├── pg_tblspc
├── pg_twophase
├── PG_VERSION
├── pg_wal
├── pg_xact
├── postgresql.auto.conf
├── postmaster.opts
└── postmaster.pid

Name	Description
`/var/lib/postgresql/13/main/base`	- DBs disk volumes location. - template0 template1 : used during db creation. - oid classification : mapping table between oids and objects. - Free Space Map (FSM) and Visibility Map (VM) : used by vacuum.
`/var/lib/postgresql/13/main/global`	- control files : pg_control (called during boot process). - DB engine status.
`/var/lib/postgresql/13/main/pg_wala`	- Write ahead logging. - WAL files : defaut 16 Mb. - Recovery from checkpoint : replay transactions from wal files.
`/var/lib/postgresql/13/main/pg_act`	- Non serial transactions status.
`/var/lib/postgresql/13/main/pg_serial`	- Serial transactions status.
`/var/lib/postgresql/13/main/pg_stat`	- Statistics.
`/var/lib/postgresql/13/main/pg_tblspc`	- Tablespaces pointer.
`/var/lib/postgresql/13/main/pg_repslot`	- Replication informations.

> select relname, oid, relfilenode from pg_class where relname = 'employee';
 relname  |  oid  | relfilenode
----------+-------+-------------
 employee | 16404 |       16404
(1 row)

> select pg_relation_filepath('employee');

 pg_relation_filepath
----------------------
 base/16385/16404
(1 row)

/var/lib/postgresql/13/main/base/16385/13300_vm
/var/lib/postgresql/13/main/base/16385/13300_fsm
/var/lib/postgresql/13/main/base/16385/13300

Connexion and Authentication#

IP adress and Port#

> awk 'BEGIN { print "" }
{ for (i = 60; i <= 65; ++i)
    if ( NR == i )
        print $0
 }' /etc/postgresql/13/main/postgresql.conf

listen_addresses = '*'
#listen_addresses = 'localhost'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
port = 5432                             # (change requires restart)

> ss -ltn | grep :54
LISTEN 0      244          0.0.0.0:5432      0.0.0.0:*
LISTEN 0      244             [::]:5432         [::]:*

`pg_hba.conf`#

Read from top to bottom.

Reload is needed if modifications.

> awk 'NR<2' /etc/postgresql/13/main/pg_hba.conf
# PostgreSQL Client Authentication Configuration File

> awk '/max_conn|^#work_mem/' /etc/postgresql/13/main/postgresql.conf
max_connections = 100                   # (change requires restart)
#work_mem = 4MB                         # min 64kB

> awk 'BEGIN {
format="\n- %s\n- %s\n- %s\n- %s\n- %s\n"
} /TYPE/ {
printf format, $2, $3, $4, $5, $6
}' /etc/postgresql/13/main/pg_hba.conf

- TYPE
- DATABASE
- USER
- ADDRESS
- METHOD

> grep -v "^[[:space:]]*#.*\|^$" /etc/postgresql/13/main/pg_hba.conf
local   all             postgres                                peer
local   all             all                                     peer
host    all             all             127.0.0.1/32            md5
host    all             all             192.168.0.0/16          md5
host    all             all             ::1/128                 md5
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5

TYPE	connection type : “local”, “host”
DATABASE	“all”, “sameuser”, “samerole”, “replication”, a database name
USER	“all”, a user name, a group name prefixed with “+”, or a comma-separated list thereof
ADDRESS	a host name, an IP address and a CIDR mask, “samehost”, “samenet”
METHOD	“trust”, “reject”, “md5”, “password”, “scram-sha-256”, “gss”, “sspi”, “ident”, “peer”, “pam”, “ldap”, “radius” or “cert”

Host configuration#

> diff -u /etc/postgresql/13/main/pg_hba.conf{.back,}
--- /etc/postgresql/13/main/pg_hba.conf.back    2023-01-16 16:52:09.008559051 +0100
+++ /etc/postgresql/13/main/pg_hba.conf 2023-01-16 16:55:59.693713126 +0100
@@ -93,6 +93,7 @@
 # "local" is for Unix domain socket connections only
 local   all             all                                     peer
 # IPv4 local connections:
+host    wpdb            wpuser          192.168.0.0/16          md5
 host    all             all             127.0.0.1/32            md5
 # IPv6 local connections:
 host    all             all             ::1/128                 md5

Remote connection#

> psql -h 192.168.122.120 -U wpuser wpdb
Password for user wpuser:
psql (13.9 (Debian 13.9-0+deb11u1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

Peer configuration#

--- /etc/postgresql/13/main/pg_hba.conf.back    2023-01-16 21:34:47.131353273 +0100
+++ /etc/postgresql/13/main/pg_hba.conf 2023-01-18 14:00:11.036143144 +0100
@@ -87,6 +87,7 @@
 #
 # Database administrative login by Unix domain socket
 local   all             postgres                                peer
+local   mydb            guillaume                               peer

 # TYPE  DATABASE        USER            ADDRESS                 METHOD

guillaume@debian02:~$ psql mydb
psql (13.9 (Debian 13.9-0+deb11u1))
Type "help" for help.

mydb=>

Usage#

psql#

psql is the PostgreSQL interactive terminal.

-c, --command=COMMAND: run only single command (SQL or internal) and exit
-f, --file=FILENAME: execute commands from file, then exit
-L, --log-file=FILENAME: send session log to file
-q, --quiet: run quietly (no messages, only query output)
-s, --single-step: single-step mode (confirm each query)
-H, --html: HTML table output mode
-t, --tuples-only: print rows only

Awk is always helpful. 🤓

> psql --help | awk '/(^[[:space:]]*-[cLfHsqt].*$|^psql)/'

> psql -h 192.168.122.120 -U wpuser wpdb -L myfile.log -c "\dt+" -H > mytables.html

Backslash Commands#

grep vs awk 🤓

> grep "\(\\d[utvnm]\[S\|\\l\[\|\\dS\[S\|\\\d\[S\)" <(psql -c '\?')
  \d[S+]                 list tables, views, and sequences
  \d[S+]  NAME           describe table, view, sequence, or index
  \dm[S+] [PATTERN]      list materialized views
  \dn[S+] [PATTERN]      list schemas
  \dt[S+] [PATTERN]      list tables
  \du[S+] [PATTERN]      list roles
  \dv[S+] [PATTERN]      list views
  \l[+]   [PATTERN]      list databases

> awk '/(\\d[utvnmi]\[S|\\l\[|\\dS\[S|\\\d\[S)/' <(psql -c '\?')
  \d[S+]                 list tables, views, and sequences
  \d[S+]  NAME           describe table, view, sequence, or index
  \di[S+] [PATTERN]      list indexes
  \dm[S+] [PATTERN]      list materialized views
  \dn[S+] [PATTERN]      list schemas
  \dt[S+] [PATTERN]      list tables
  \du[S+] [PATTERN]      list roles
  \dv[S+] [PATTERN]      list views
  \l[+]   [PATTERN]      list databases

d[S+]	list tables, views, and sequences
d[S+] NAME	describe table, view, sequence, or index
di[S+] [PATTERN]	list indexes
dm[S+] [PATTERN]	list materialized views
dn[S+] [PATTERN]	list schemas
dt[S+] [PATTERN]	list tables
du[S+] [PATTERN]	list roles
dv[S+] [PATTERN]	list views
l[+] [PATTERN]	list databases

Cluster#

☰ - Server > Cluster > Databases > Roles > Schemas > Tables

> pg_ctlcluster 13 main status
pg_ctl: server is running (PID: 2671)
/usr/lib/postgresql/13/bin/postgres "-D" "/var/lib/postgresql/13/main" "-c" "config_file=/etc/postgresql/13/main/postgresql.conf"

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

> pg_createcluster 13 newone

> pg_ctlcluster 13 newone start
Warning: the cluster will not be running as a systemd service. Consider using systemctl:
  sudo systemctl start postgresql@13-newone

> systemctl cat postgresql@13-main.service | awk 'NR < 6{$1="";print}'

  /lib/systemd/system/postgresql@.service
  systemd service template for PostgreSQL clusters. The actual instances will
  be called "postgresql@version-cluster", e.g. "postgresql@9.3-main". The
  variable %i expands to "version-cluster", %I expands to "version/cluster".
  (%I breaks for cluster names containing dashes.)

> systemd-analyze dot 'postgresql.*' | dot -Tsvg > postgresql_systemd_dot_analyze.svg
    Color legend: black     = Requires
                  dark blue = Requisite
                  dark grey = Wants
                  red       = Conflicts
                  green     = After

postgresql_systemd_dot_analyze.svg

> ss -tupaln | awk '/:543/'
tcp   LISTEN 0      244          0.0.0.0:5432      0.0.0.0:*    users:(("postgres",pid=467,fd=5))
tcp   LISTEN 0      244        127.0.0.1:5433      0.0.0.0:*    users:(("postgres",pid=6508,fd=6))
tcp   LISTEN 0      244             [::]:5432         [::]:*    users:(("postgres",pid=467,fd=6))
tcp   LISTEN 0      244            [::1]:5433         [::]:*    users:(("postgres",pid=6508,fd=5))

> awk '/^postgres/{$1=$2=$3=$4=$5=$6=$7=$8=$9=$10="" ; print}' <(ps auxfw)
          /usr/lib/postgresql/13/bin/postgres -D /var/lib/postgresql/13/main -c config_file=/etc/postgresql/13/main/postgresql.conf
          \_ postgres: 13/main: checkpointer
          \_ postgres: 13/main: background writer
          \_ postgres: 13/main: walwriter
          \_ postgres: 13/main: autovacuum launcher
          \_ postgres: 13/main: stats collector
          \_ postgres: 13/main: logical replication launcher
          /usr/lib/postgresql/13/bin/postgres -D /var/lib/postgresql/13/newone -c config_file=/etc/postgresql/13/newone/postgresql.conf
          \_ postgres: 13/newone: checkpointer
          \_ postgres: 13/newone: background writer
          \_ postgres: 13/newone: walwriter
          \_ postgres: 13/newone: autovacuum launcher
          \_ postgres: 13/newone: stats collector
          \_ postgres: 13/newone: logical replication launcher

Database#

☰ - Server > Cluster > Databases > Roles > Schemas > Tables

Look for SQL command with psql:

> psql -c "\h CREATE DATABASE" | grep "\(CREATE DATABASE\|OWNER\|TEMPLATE\|ENCODING\|LC_\|CONNECTION\)"

Command: CREATE DATABASE

CREATE DATABASE name

[ WITH ] [ OWNER [=] user_name ]

[ TEMPLATE [=] template ]
[ ENCODING [=] encoding ]
[ LC_COLLATE [=] lc_collate ]
[ LC_CTYPE [=] lc_ctype ]
[ ALLOW_CONNECTIONS [=] allowconn ]
[ CONNECTION LIMIT [=] connlimit ]
[ IS_TEMPLATE [=] istemplate ]

> createdb -T template1 mydb
> createuser guillaume
> psql -c "\l+"
> psql -c "\du"
> psql -c "alter user guillaume with password 'xxxxxxxxx'"
> psql -c "alter database mydb owner to guillaume"
> dropdb mydb

Table#

☰ - Server > Cluster > Databases > Roles > Schemas > Tables

Tables are composed of rows and columns.
Fields data types : postgresql.org - DATATYPE-TABLE .

Classic#

CREATE TABLE table_name (column_name data_type,… );
DROP TABLE table_name;
TRUNCATE TABLE table_name;
ALTER TABLE table_name action;

> create table mytab (id int, field1 varchar(255));
CREATE TABLE
> insert into toto (id, field1) values (1, 'guillaume');
INSERT 0 1
> \dS mytab
                       Table "public.mytab"
 Column |          Type          | Collation | Nullable | Default
--------+------------------------+-----------+----------+---------
 id     | integer                |           |          |
 field1 | character varying(255) |           |          |

> alter table mytab rename field1 to username;
ALTER TABLE
> \dS mytab
                        Table "public.mytab"
  Column  |          Type          | Collation | Nullable | Default
----------+------------------------+-----------+----------+---------
 id       | integer                |           |          |
 username | character varying(255) |           |          |

Inherits#

> create table mytwotab (tel int) inherits (mytab);
CREATE TABLE
> \dS mytwotab
                      Table "public.mytwotab"
  Column  |          Type          | Collation | Nullable | Default
----------+------------------------+-----------+----------+---------
 id       | integer                |           |          |
 username | character varying(255) |           |          |
 tel      | integer                |           |          |
Inherits: mytab

Temporary#

only on memory

> create temporary table toto () inherits (mytab);
CREATE TABLE
> \dt+
                                 List of relations
  Schema   |   Name   | Type  |   Owner   | Persistence |    Size    | Description
-----------+----------+-------+-----------+-------------+------------+-------------
 pg_temp_3 | toto     | table | guillaume | temporary   | 0 bytes    |
 public    | mytab    | table | guillaume | permanent   | 8192 bytes |
 public    | mytwotab | table | guillaume | permanent   | 0 bytes    |

Partition#

🌐

postgresql.org - DDL-PARTITIONING-OVERVIEW

Index#

Pointer, increase the request speed.
Required reindexation.
Index types:
- b-tree
- hash
- Gist
- Gin
- Brin

🌐

B Tree#

Usage : < <= = >= >

See B-Tree on 📘 Ditaa.

> create table test_tree (id int);
> insert into test_tree select * from generate_series(1,2000000);
> \timing
> select count(*) from test_tree;
> select * from test_tree where id =1555555;
Time: 115.675 ms
> explain analyze select * from test_tree where id =1555555;
> create index idx_test_tree on test_tree (id);
> select * from test_tree where id =1555555;
Time: 0.464 ms
> \dS test_tree
             Table "public.test_tree"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           |          |
Indexes:
    "idx_test_tree" btree (id)

Hash#

Usage: = or !=

> create table test_hash (id int);
> insert into test_hash select * from generate_series(1,2000000);
> select count(*) from test_hash;
> \timing
> select * from test_hash where id = 1555555;
Time: 118.708 ms
> explain analyze select * from test_hash where id = 1555555;
> create index idx_test_hash on test_hash using hash (id);
> select * from test_hash where id = 1555555;
Time: 0.392 ms
> \dS test_hash;
             Table "public.test_hash"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           |          |
Indexes:
    "idx_test_hash" hash (id)

View#

avoid complex requests
ease user permissions management

> create materialized view vm_employee as select * from employee ;
> create view v_employee_all as select * from employee;
> \dmv+
                                      List of relations
 Schema |      Name      |       Type        |  Owner   | Persistence |  Size   | Description
--------+----------------+-------------------+----------+-------------+---------+-------------
 public | v_employee_all | view              | postgres | permanent   | 0 bytes |
 public | vm_employee    | materialized view | postgres | permanent   | 16 kB   |

Classic#

request alias

> create view v_employee_counter as select count(*) from employee;
> create view v_employee_id as select id from employee;
> select count from v_employee_counter;
 count
-------
     3
(1 row)

> \dv+
                                  List of relations
 Schema |        Name        | Type |  Owner   | Persistence |  Size   | Description
--------+--------------------+------+----------+-------------+---------+-------------
 public | v_employee_counter | view | postgres | permanent   | 0 bytes |
 public | v_employee_id      | view | postgres | permanent   | 0 bytes |
(2 rows)

> \dS v_employee_counter;
         View "public.v_employee_counter"
 Column |  Type  | Collation | Nullable | Default
--------+--------+-----------+----------+---------
 count  | bigint |           |          |

Expanded display with \x on/off

> select * from employee limit 1;
 id | username | creation_date
----+----------+---------------
  1 | peter    | 2023-01-18
(1 row)

> \x on
Expanded display is on.
> select * from employee limit 1;
-[ RECORD 1 ]-+-----------
id            | 1
username      | peter
creation_date | 2023-01-18

> create view v_employee_all as select * from employee;
> alter table employee add column creation_date varchar not null default now()::date;
> select * from v_employee_all limit 1;
-[ RECORD 1 ]---
id       | 1
username | peter

> drop view v_employee_all;
> create view v_employee_all as select * from employee;
> select * from v_employee_all limit 1;
-[ RECORD 1 ]-+-----------
id            | 1
username      | peter
creation_date | 2023-01-18

> select * from employee ;
 id | username
----+----------
  1 | peter
  2 | paul
  3 | bobby

> select * from v_employee_all ;
 id | username
----+----------
  1 | peter
  2 | paul
  3 | bobby

> delete from employee where id = 1;
> select * from v_employee_all ;
 id | username
----+----------
  2 | paul
  3 | bobby

> select * from employee ;
 id | username
----+----------
  2 | paul
  3 | bobby

Materialized#

snapshot

> create materialized view vm_employee as select * from employee ;
> delete from employee where id = 1;
> select * from employee ;
 id | username
----+----------
  2 | paul
  3 | bobby

> select * from vm_employee ;
 id | username
----+----------
  2 | paul
  3 | bobby
  1 | peter

> refresh materialized view vm_employee ;

Privileges#

Database Privilege#

More ressources

isolation + security
grants privileges on a database objects
grants membership in a role

> create database tata;
> alter database tata owner to tata;

Only owner connection.

> revoke all on database tata from public;

One user connection.

> grant connect on database tata to toto;

Can we do that ? 🥁

\x on \l+ ^[pt]*[sa] \du+ ^t[^oiu]t* \x off

Yes ! 🤓

> \x on \l+ ^[pt]*[sa] \du+ ^t[^oiu]t* \x off
Expanded display is on.
List of databases
-[ RECORD 1 ]-----+-------------------------------------------
Name              | postgres
Owner             | postgres
Encoding          | UTF8
Collate           | en_US.UTF-8
Ctype             | en_US.UTF-8
Access privileges |
Size              | 7925 kB
Tablespace        | pg_default
Description       | default administrative connection database
-[ RECORD 2 ]-----+-------------------------------------------
Name              | tata
Owner             | tata
Encoding          | UTF8
Collate           | en_US.UTF-8
Ctype             | en_US.UTF-8
Access privileges | =Tc/tata                                  +
                  | tata=CTc/tata
Size              | 7981 kB
Tablespace        | pg_default
Description       |

List of roles
-[ RECORD 1 ]----------
Role name   | tata
Attributes  | Create DB
Member of   | {}
Description |

Expanded display is off.

Role Privilege#

PostgreSQL manages database access permissions using the concept of roles.

privileges control access on databases, schemas, tables, columns,…
postgresql : users = roles (create user = create role)
user postgres : peer user (require Unix user), local, one by pg instance
pg_hba.conf : control the user authentication
inheritability, roles concept
user = login + passwd + pemrissions

conninfo Who am I ? 🤔

tata=> \conninfo
You are connected to database "tata" as user "titi" via socket in "/var/run/postgresql" at port "5432".

> psql -c '\h create role' | grep "[A-Z]\{2,\}"

Command: CREATE ROLE

CREATE ROLE name [ [ WITH ] option [ … ] ]

SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| REPLICATION | NOREPLICATION
| BYPASSRLS | NOBYPASSRLS
| CONNECTION LIMIT connlimit
| [ ENCRYPTED ] PASSWORD ‘password’ | PASSWORD NULL
| VALID UNTIL ‘timestamp’
| IN ROLE role_name [, …]
| IN GROUP role_name [, …]
| ROLE role_name [, …]
| ADMIN role_name [, …]
| USER role_name [, …]
| SYSID uid

URL: https://www.postgresql.org/docs/13/sql-createrole.html

> create role toto nologin nocreatedb;
> create role tutu with encrypted password 'test123' login nocreatedb;
> create role titi login nocreatedb;
> create role tata login createdb;

# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 tata      | Create DB                                                  | {}
 titi      |                                                            | {}
 toto      | Cannot login                                               | {}
 tutu      |                                                            | {}
 wpuser    |                                                            | {}

> alter role tata nologin nocreatedb;

> alter role tata login createdb;
> alter role tata encrypted password 'tata123';
> \du+ t[au]t*
                   List of roles
 Role name |  Attributes  | Member of | Description
-----------+--------------+-----------+-------------
 tata      | Create DB    | {}        |
 tutu      | Cannot login | {}        |

Schema Privilege#

schema is a db objects view (container of objects)
purpose : ease permissions management
default schema = public

Allow authorization to create schema.

> grant create on database tata to tata;

Revoke authorization to create schema.

> revoke create on database tata from toto;

> select * from information_schema.tables;
> select table_catalog, table_schema, table_name, table_type
from information_schema.tables;
> select table_catalog, table_schema, table_name, table_type
from information_schema.tables where table_schema = 'pg_catalog' limit 1;
> select table_catalog, table_schema, table_name, table_type
from information_schema.tables where table_schema = 'information_schema' limit 1;

Create a schema and get in it.

> show search_path;
   search_path
-----------------
 "$user", public

> create schema myschema;
> set search_path = myschema;

> show search_path;
 search_path
-------------
 myschema

Change a default schema.

> \c postgres postgres
> alter user tata set search_path = myschema;

Schema permission:

USAGE : reading permission in a schema
CREATE : table creation permission in a schema

Revoke all privileges for all (except the owner).

> \c tata tata
> revoke all on schema myschema from public;

Check from a remote client.

> psql -h debian02 -U tata -d tata -c '\dt' -c '\dn'
          List of relations
  Schema  |   Name   | Type  | Owner
----------+----------+-------+-------
 myschema | employee | table | tata
 myschema | tbl1     | table | tata
(2 rows)

   List of schemas
   Name   |  Owner
----------+----------
 myschema | tata
 public   | postgres
(2 rows)

> psql -h debian02 -U titi -d tata -c '\dt' -c '\dn'
Password for user titi:
Did not find any relations.
   List of schemas
   Name   |  Owner
----------+----------
 myschema | tata
 public   | postgres
(2 rows)

Enable usage privilege on a schema (public for all).

> \c tata tata
tata=> grant usage on schema myschema to titi;

# User with usage privilege
> psql -h debian02 -U titi -d tata -c 'set search_path = myschema' -c '\dt'
Password for user titi:
SET
          List of relations
  Schema  |   Name   | Type  | Owner
----------+----------+-------+-------
 myschema | employee | table | tata
 myschema | tbl1     | table | tata
(2 rows)
# User without usage privilege
> psql -h debian02 -U toto -d tata -c 'set search_path = myschema' -c '\dt'
SET
Did not find any relations.

> psql -h debian02 -U titi -d tata -c 'set search_path = myschema' -c 'select * from employee'
SET
ERROR:  permission denied for table employee

Enable create privilege on a schema (public for all).

> \c tata tata
tata=> grant create on schema myschema to titi;

# User with create privilege
> psql -h debian02 -U titi -d tata -c 'set search_path = myschema' -c 'create table tbl2 (id int);'
SET
CREATE TABLE
# User without create privilege
> psql -h debian02 -U toto -d tata -c 'set search_path = myschema' -c 'select * from employee'
SET
ERROR:  relation "employee" does not exist
LINE 1: select * from employee

Table Column Privileges#

Table privilege hooks :

select
insert
update
delete
truncate
references
trigger
all

Syntax:

grant all on table tbl1 to <role|public> ;
grant select(username) on table employee to <role|public> ;

revoke all on table tbl1 from <role|public> ;

Read-only user#

Create a new user.

> \c postgres postgres
> create role tutu encrypted password 'test123' login noinherit nocreatedb ;

Allow schema access.

> \c tata tata
> grant usage on SCHEMA myschema to tutu;

Change the default user’s schema.

> \c postgres postgres
> alter role tutu set search_path = myshema;

Allow table access.

> \c tata tata
> grant SELECT(username,creation_date) on employee TO tutu;

Check from a remote client.

> psql -h debian02 -U tutu -d tata -c '\dt' -c 'select username,creation_date from employee;'
          List of relations
  Schema  |   Name   | Type  | Owner
----------+----------+-------+-------
 myschema | employee | table | tata
 myschema | tbl1     | table | tata
 myschema | tbl2     | table | titi
(3 rows)

 username | creation_date
----------+---------------
 peter    | 2023-01-20
 paul     | 2023-01-20
 bobby    | 2023-01-20
(3 rows)

> psql -h debian02 -U tutu -d tata -c 'select * from employee;'
ERROR:  permission denied for table employee

> psql -h debian02 -U toto -d tata -c 'set search_path = myschema' -c '\dt'
SET
Did not find any relations.

🔒 Password on remote clients.

PGPASSWORD

> export PGPASSWORD=turlututu && psql -h debian02 -U titi -d postgres -c '\conninfo'
You are connected to database "postgres" as user "titi" on host "debian02" (address "192.168.122.120") at port "5432".

~/.pgpass

debian02:5432:tata:tata:tata123
debian02:5432:tata:tutu:test123

Logging#

🌐

Logs:

not to confuse with logfiles (WAL - transactions)
caution : more log = less perfs
logging collector
- split logging processus
- collect and write processes
one logs configuration by instance

log_destination
log_directory
log_filename standard strftime
logging_collector
log_statement
log_line_prefix
log_checkpoints
log_connections
log_disconnections
log_duration

Debug

increase log level when needed

On a database.

> alter database tata set log_statement = 'all';

On a user.

> alter role toto on database wpdb set log_statement = 'all';

Log slow queries.

log_min_duration = -1
-1 means nothing otherwise in ms
independant from log_statement

Create an admin user with local and remote access.

> su - posgtres
> psql -p 5433

> create role guillaume with encrypted password 'test123' login superuser createdb createrole;

> diff -u /etc/postgresql/13/newone/pg_hba.conf{.back,}
--- /etc/postgresql/13/newone/pg_hba.conf.back  2023-01-25 12:25:31.130237158 +0100
+++ /etc/postgresql/13/newone/pg_hba.conf       2023-01-25 12:20:27.241054275 +0100
@@ -91,8 +91,9 @@
 # TYPE  DATABASE        USER            ADDRESS                 METHOD

 # "local" is for Unix domain socket connections only
-local   all             all                                     peer
+local   all             guillaume                               trust
 # IPv4 local connections:
+host    all             guillaume       192.168.0.0/16          md5
 host    all             all             127.0.0.1/32            md5
 # IPv6 local connections:
 host    all             all             ::1/128                 md5

Update logging configuration.

> diff -s /etc/postgresql/13/newone/postgresql.conf{.back,}
60c60
< #listen_addresses = 'localhost'               # what IP address(es) to listen on;
---
> listen_addresses = '*'                # what IP address(es) to listen on;
426c426
< #log_destination = 'stderr'           # Valid values are combinations of
---
> log_destination = 'stderr'            # Valid values are combinations of
432c432
< #logging_collector = off              # Enable capturing of stderr and csvlog
---
> logging_collector = on                # Enable capturing of stderr and csvlog
438c438
< #log_directory = 'log'                        # directory where log files are written,
---
> log_directory = '/tmp'                        # directory where log files are written,
442c442
< #log_file_mode = 0600                 # creation mode for log files,
---
> log_file_mode = 0640                  # creation mode for log files,

Change a database and a role logs level.

> \c potsgres guillaume
> alter database employee set log_statement = 'all';
> alter database employee set log_statement = 'none';
> alter role guillaume set log_statement = "ddl";
> alter role guillaume set log_statement = "none";

Check the logs file.

> tail -3 /tmp/postgresql-2023-01-25_102032.log
2023-01-25 13:15:19.196 CET [3135] guillaume@postgres LOG:  statement: create role test login;
2023-01-25 13:15:43.474 CET [3135] guillaume@postgres LOG:  statement: alter role test with encrypted password 'test123';
2023-01-25 13:16:27.604 CET [3171] guillaume@postgres LOG:  statement: alter role guillaume set log_statement = "none";

SQL commands categories

Name	Description
DDL	Data Definition Language
DQL	Data Query Language
DML	Data Manipulation Language
DCL	Data Control Language
TCL	Transaction Control Language

Copy#

🌐

postgresql.org - sql-copy

> create table tbl1 (id int);
> insert into tbl1 select * from generate_series(1,2000000);
> copy tbl1 to '/tmp/export_tbl1.txt';
> copy tbl1 to '/tmp/export_tbl1.csv' csv header;


> copy (select count(*) from tbl1) to '/tmp/count.txt';
> copy tbl1 to program 'wc -l > /tmp/shell_count.txt';

> copy tbl1 from program 'curl http://example.com';

Backup#

🌐

pg_dump	extract a PostgreSQL database into a script file or other archive file
pg_dumpall	extract a PostgreSQL database cluster into a script file

pg_dump#

> pg_dump --help | awk 'NR<2 || /^[[:space:]]*-([hdpUfj]|F)/{c=2}c&&c --'

pg_dump dumps a database as a text file or to other formats.

  -f, --file=FILENAME          output file or directory name
  -F, --format=c|d|t|p         output file format (custom, directory, tar,
                               plain text (default))
  -j, --jobs=NUM               use this many parallel jobs to dump
  -v, --verbose                verbose mode
  -d, --dbname=DBNAME      database to dump
  -h, --host=HOSTNAME      database server host or socket directory
  -p, --port=PORT          database server port number
  -U, --username=NAME      connect as specified database user
  -w, --no-password        never prompt for password

> pg_dump --help | awk 'NR<3 || /^[[:space:]]*-([zsanNcC])/'

pg_dump dumps a database as a text file or to other formats.

  -a, --data-only              dump only the data, not the schema
  -s, --schema-only            dump only the schema, no data
  -c, --clean                  clean (drop) database objects before recreating
  -C, --create                 include commands to create database in dump
  -n, --schema=PATTERN         dump the specified schema(s) only
  -N, --exclude-schema=PATTERN do NOT dump the specified schema(s)

simple export

> pg_dump -U postgres tata > dump_tata.sql
> pg_dump -U postgres -f dump_tata_$(date +%F_%T|sed 's/[-:]//g').sql -d tata -C

export with compression

> pg_dump -U postgres -C tata | gzip -c > dump_tata_$(date +%F_%T|sed 's/[-:]//g').sql.gz

custom format

> pg_dump -U postgres -Fc tata -f dump_tata_$(date +%F_%T|sed 's/[-:]//g').dmp

> file -i dump_tata_20230125_145939.dmp
dump_tata_20230125_145939.dmp: application/octet-stream; charset=binary

separation of schemas and data

> pg_dump -U postgres -Fc tata --data-only -f dump_tata_$(date +%F_%T|sed 's/[-:]//g').dmp
> pg_dump -U postgres tata --schema-only -f dump_tata_$(date +%F_%T|sed 's/[-:]//g').sql

pg_dumpall#

format plain text (SQL)
especially for roles/users backups

> pg_dumpall --help | awk 'NR<3 || /(^[[:space:]]*-[lhUpfasc]|exists)/'

pg_dumpall extracts a PostgreSQL database cluster into an SQL script file.

  -f, --file=FILENAME          output file name
  -a, --data-only              dump only the data, not the schema
  -c, --clean                  clean (drop) databases before recreating
  -s, --schema-only            dump only the schema, no data
  --if-exists                  use IF EXISTS when dropping objects
  -h, --host=HOSTNAME      database server host or socket directory
  -l, --database=DBNAME    alternative default database
  -p, --port=PORT          database server port number
  -U, --username=NAME      connect as specified database user

> pg_dumpall -U postgres -f dumpall_schema_$(date +%F_%T|sed 's/[-:]//g').sql -s --if-exists -c

Recovery#

🌐

postgresql.org - app-pgrestore

1974	Ingres INtelligent Graphic RElational System
1985	Post-Ingres
1995	Postgres95
1996	PostgreSQL

cli	psql
gui	pgAdmin
postgis	extension which allows GIS (Geographic Information Systems) objects
replication	many systems

atomic	transaction is complete or not at all
consistent	in case of error the status reverts to the one before
isolated	no interference between transactions
durable	data are available after reboot

Server	several servers with replication
Cluster	instances with specific configuration and ressources
Database	set of structured data
Roles/Users	users (login,passwd)
Schema	namespace in db (group of elements)
Table	logical storespace
Field	column in table
Row	line with structured data

📗 PostgreSQL

Contents

📗 PostgreSQL#

Installation and Configuration Files#

Installation#

Main folders#

/etc/postgresql/13/main#

/var/lib/postgresql/13/main#

/usr/lib/postgresql/13/bin#

/usr/bin/pg_*#

Core and forked processus#

PostgreSQL Binaries#

Common#

Debian Specific#

Backups Specific#

Wrappers#

Maintenance#

Boot and Shutdown#

Boot Sequence#

Shutdown Sequence#

PG Processus#

Core#

Fork#

Engine processus#

Connexions processus#

PG Memory#

main memories#

Main Directories#

/etc/postgresql/13/main#

/var/lib/postgresql/13/main/#

Connexion and Authentication#

IP adress and Port#

pg_hba.conf#

Host configuration#

Remote connection#

Peer configuration#

Usage#

psql#

Backslash Commands#

Cluster#

Database#

Table#

Classic#

Inherits#

Temporary#

Partition#

Index#

B Tree#

Hash#

View#

Classic#

Materialized#

Privileges#

Database Privilege#

Role Privilege#

Schema Privilege#

Table Column Privileges#

Read-only user#

Logging#

Copy#

Backup#

pg_dump#

pg_dumpall#

Recovery#

`/etc/postgresql/13/main`#

`/var/lib/postgresql/13/main`#

`/usr/lib/postgresql/13/bin`#

`/usr/bin/pg_*`#

`/etc/postgresql/13/main`#

`/var/lib/postgresql/13/main/`#

`pg_hba.conf`#