https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-18-04

Pour se connecter en admin :

sudo -i -u postgres ou sudo -u postgres psql

Connexion:

psql

Pour quitter:

\q

Les fichiers de configuration sont sous /etc/postgresql/

man createuser: “define a new PostgreSQL user account”

$ sudo -u postgres createuser --interactive
Enter name of role to add: roge
Shall the new role be a superuser? (y/n) y

Creation d'un utilisateur Lambda

$ sudo -u postgres psql
psql (10.6 (Ubuntu 10.6-0ubuntu0.18.04.1))
Type "help" for help.

postgres=# CREATE USER <nom_utilisateur>;

#Par défaut, votre nouvel utilisateur n'a aucun droit. Donnez-lui la possibilité de créer de nouvelles bases de données :
postgres=# ALTER ROLE <nom_utilisateur>  WITH CREATEDB; #N'oubliez pas le point-virgule à la fin…
ALTER ROLE
postgres=# ALTER ROLE <nom_utilisateur>  WITH ENCRYPTED PASSWORD 'mot de passe';
ALTER ROLE
postgres=# \q

Modification du mot de passe d'un utilisateur

ALTER USER user_name WITH PASSWORD 'new_password';

Il faut préciser le “owner” (utilisateur propriétaire) de la base :

createdb -O roge test

Liste des bases :

psql -l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 test      | roge     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

# Afficher le cluster
$ pg_lsclusters
Version Cluster   Port Status Owner    Data directory                     Log file
9.1     main      5432 online postgres /var/lib/postgresql/9.1/main       /var/log/postgresql/postgresql-9.1-main.log

# Supprimer le cluster
$ pg_dropcluster 9.1 main --stop

# Créer le cluster
$ pg_createcluster --locale fr_FR.UTF-8 9.1 main

# Démarrage
$ pg_ctlcluster 9.1 main start

# backup database
pg_dump -v -U <user> -d <database> -f <file dump>

# Restore with:
# -c clean before 
# -v verbose
# -1  --single-transaction : Execute the restore as a single transaction (that is, wrap the emitted commands in BEGIN/COMMIT). This ensures that either all the commands complete successfully, or no changes are applied. This option implies --exit-on-error.

pg_restore -vc -U <user> -d <database> -1 <file dump>

Variante pour un dump de type SQL:

createdb -O user  dbName
psql -U user -d dbName -f /data/....dbFile.sql

command line cheatsheet

$ psql <db>
<db>$ \l #list databases
<db>$ \c <database> #connect to a database

https://docs.postgresql.fr/7.4/ddl-schemas.html

“Les schémas sont comparables aux répertoires au niveau du système d'exploitation. sauf que les schémas ne peuvent pas être imbriqués.”

CREATE SCHEMA mon_schema;
# supprimer un schéma vide
DROP SCHEMA mon_schema;
# supprimer un schéma non vide
DROP SCHEMA mon_schema CASCADE;

“Par défaut, les utilisateurs ne peuvent pas accéder aux objets dans les schémas qui ne leurs appartiennent pas. Pour autoriser cela, le propriétaire du schéma doit donner le privilège USAGE sur le schéma. Pour autoriser les utilisateurs à manipuler les objets d'un schéma, des privilèges supplémentaires devront peut-être être accordés, suivant l'objet.”

“ il est recommandé de fortement augmenter les paramètres shared_buffers et work_mem qui indiquent la quantité de mémoire maximale à utiliser pour un certain nombre d'opérations. L'utilitaire pg_tune peut être utilisé pour obtenir des indications sur les valeurs à utiliser, suivant la configuration matérielle (même si les valeurs exactes dépendent de l'utilisation qui est faite de la base).” http://blog.pilotsystems.net/2011/aout/cas-pratique-doptimisation-de-postgresql

“PostgreSQL ™ dispose d'une fonctionnalité optionnelle mais hautement recommandée appelée autovacuum, dont le but est d'automatiser l'exécution des commandes VACUUM et ANALYZE . Une fois activé, autovacuum vérifie les tables ayant un grand nombre de lignes insérées, mises à jour ou supprimées. Ces vérifications utilisent la fonctionnalité de récupération de statistiques ; du coup, autovacuum ne peut pas être utilisé sauf si track_counts est configuré à true. Dans la configuration par défaut, l'autovacuum est activé et les paramètres liés sont correctement configurés.” https://docs.postgresql.fr/10/maintenance.html#autovacuum

select * from pg_settings
where name like 'autovacuum%'
or name = 'track_counts';

“ANALYZE collecte des statistiques sur le contenu des tables de la base de données et stocke les résultats dans le catalogue système pg_statistic. L'optimiseur de requêtes les utilise pour déterminer les plans d'exécution les plus efficaces. Dans la configuration par défaut de PostgreSQL™, le démon autovacumm (voir Section 24.1.6, « Le démon auto-vacuum ») l'analyse automatique des tables quand elle est remplie de données sont la première fois, puis à chaque fois qu'elles sont modifiées via les opérations habituelles. Quand l'autovacuum est désactivé, il est intéressant de lancer ANALYZE périodiquement ou juste après avoir effectué de grosses modifications sur le contenu d'une table.” https://docs.postgresql.fr/10/sql-analyze.html

Le module pg_stat_statements qui permet de récolter des statistiques sur les requêtes les plus consommatrices. Pour l’activer, il faut modifier le fichier de configuration postgresql.conf et ajouter à la fin:

shared_preload_libraries = 'pg_stat_statements'

# Increase the max size of the query strings Postgres records
track_activity_query_size = 2048

# Track statements generated by stored procedures as well
pg_stat_statements.track = all

Puis executer la commande SQL suivante:

CREATE EXTENSION pg_stat_statements;

Pour logger les requêtes qui durent plus de n milliseconds :

ALTER DATABASE "ma-bd" SET log_min_duration_statement = 10;

Pour logger les requêtes il faut modifier le fichier de configuration postgresql.conf et ajouter à la fin:

# - Where to Log -

log_destination = 'csvlog'              # Valid values are combinations of
                                        # stderr, csvlog, syslog, and eventlog,
                                        # depending on platform.  csvlog
                                        # requires logging_collector to be on.

# This is used when logging to stderr:
logging_collector = on          # Enable capturing of stderr and csvlog
                                        # into log files. Required to be on for
                                        # csvlogs.
                                        # (change requires restart)

# These are only used if logging_collector is on:
log_directory = '/var/log/postgresql/'                # directory where log files are written,
                                        # can be absolute or relative to PGDATA
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,
                                        # can include strftime() escapes

select * from pg_settings;

https://www.synbioz.com/blog/pg-foreign-table

https://blog.imaginea.com/foreign-data-wrapper-query-performance/

https://www.percona.com/blog/2018/08/21/foreign-data-wrappers-postgresql-postgres_fdw/

https://severalnines.com/blog/overview-logical-replication-postgresql