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 | 

# backup database with no format specification (default is SQL)
pg_dump -v -O -U <user> -d <database> -f <file dump.sql>

# Restore with psql
psql -vc -h <host> -U <user> -d <database> -f <file dump.sql>

# backup database
pg_dump -v -O -Fc -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>

Lister le contenu d'un dump:

pg_restore -l mydatabase.backup

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

mkdir <Tablespace-Path>
chown postgres:postgres <Tablespace-Path>


CREATE TABLESPACE <tableSpacename> OWNER <owner> LOCATION '<Tablespace-Path>';

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.”

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