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

“We recommend that active production databases be vacuumed frequently (at least nightly), in order to remove dead rows. After adding or deleting a large number of rows, it might be a good idea to issue a VACUUM ANALYZE command for the affected table. This will update the system catalogs with the results of all recent changes, and allow the PostgreSQL query planner to make better choices in planning queries.” https://www.postgresql.org/docs/9.5/sql-vacuum.html

vacuum analyse verbose;

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;

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