Cheatsheet Postgres
===

Références:
- https://gist.github.com/apolloclark/ea5466d5929e63043dcf#posgres-cheatsheet
- https://gist.github.com/Kartones/dd3ff5ec5ea238d4c546
- http://daminoux.fr/les-notions-de-base-de-postgresql/
- livre: http://shop.oreilly.com/product/9780596009762.do SQL cookbook O'Reilly
- the Art of Postgresql: https://theartofpostgresql.com/
- Postgresql tutorial: https://www.postgresqltutorial.com/
- Postgres exercises: https://pgexercises.com/questions/string/
- Free code camp YouTube: https://www.youtube.com/watch?v=qw--VYLpxG4
- Range: https://wiki.postgresql.org/images/7/73/Range-types-pgopen-2012.pdf

## Quick connect with Docker container

docker run --rm postgres:11 psql -A -h hostname -U postgres postgres -tc "SELECT json_agg(table) FROM table;" > out.json

## Serial et séquence

https://stackoverflow.com/a/27309311/2728710

Une colonne serial est un pseudo type.
C'est un entier avec des commandes DDL supplémentaires exécutées automatiquement.

The pseudo data type is resolved doing all of this:

- create a sequence named tablename_colname_seq
- create the column with type integer (or int2 / int8 respectively for smallserial / bigserial)
- make the column NOT NULL DEFAULT nextval('tablename_colname_seq')
- make the column own the sequence, so that it gets dropped with it automatically

Pour initialiser une colonne serial (utile après une migration qui a effacé toutes les données et recrée des données avec un id précalculé)

select pg_catalog.setval(pg_get_serial_sequence('askers', 'id'), (select max(id) from askers) + 1);

## Boostrap a project with Postgres

sudo -u postgres psql
postgres=# create database mydb;
postgres=# create user myuser with encrypted password 'mypass';
postgres=# grant all privileges on database mydb to myuser;

Ou, si on a un user maître avec le nom/mdp user/password :

psql postgres://user:password@localhost:5435
create user ozon with encrypted password 'ozon';
create database ozon with owner ozon encoding 'UTF8'; # Create a database (see LC_CTYPE and LC_COLLATE if needed)
alter user ozon with superuser; # if user need privileges to create extension, and so 

Mémo :
- see owner of database via listing all databases `\l`
- see owner of table via `select * from pg_tables where tablename = 'my-table'`;

## Boostrap a project with Postgres with docker-compose

_docker-compose.yml_

version: "3"
services:
   db:
      restart: always
      image: postgres:12.1
      healthcheck:
         test: ["CMD-SHELL", "pg_isready -U user"]
         interval: 5s
         timeout: 5s
         retries: 10
      environment:
         # master password
         - POSTGRES_USER=${POSTGRES_USER:-user}
         - POSTGRES_PASSWORD=${POSTGRES_PASSWORD:-password}
      ports:
         - 5454:5432
      volumes:
         - pg-art-pgdata:/var/lib/postgresql/data-pg-art

volumes:
   pg-art-pgdata:

Run with: `docker-compose up --build -d`

Connect with: `psql postgres://user:password@localhost:5454`

create user pom with encrypted password 'pom';

*How to connect via VPN?*

`ssh -L 1111:127.0.0.1:5434 52.143.173.205`

*How to connect via database URL?*

`psql postgres://my-user:my-password@localhost:1111/`

It allows to connect as if it was local on port 1111.

**Mac**
Sous Mac, j'utilise Postgres.app.
Le CLI psql est accessible via `/Applications/Postgres.app/Contents/Versions/10/bin`. Mettre ce chemin dans le PATH de bash_profile.

`psql` permet de se connecter avec le user Linux courant sur le port 5432.

`psql -U medle -p 5454 myDatabase` permet de se connecter à la base de données myDatabase en tant que utilisteur medle avec le port 5454.

`GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO medle;`

`GRANT CONNECT ON DATABASE <db_name> TO <user_name>;` autorise la connexion à un utilisateur

`GRANT USAGE ON SCHEMA public TO <user_name>;` accorde la permission au schéma public

`GRANT SELECT, UPDATE, INSERT ON ALL TABLES IN SCHEMA public TO <user_name>;` accorde tous les droits sur toutes les tables à l'utilisateur

`GRANT SELECT, UPDATE, INSERT ON <table_name> TO <user_name>;` accorde tous les droits sur une table à l'utilisateur

`select current_user;` see the current user

`select current_database();` see the current database

`select version();` see the PostgreSQL version

`\?` see general help

`\h <Command>` see help for the command (like `\h ALTER TABLE`)

`\c` or `use medle` change database

`\l` see all databases

`\dt` see tables of the current database

`\du` see all users and their assigned roles

`\l` list all available databases

`\d <table>` describe a table (or `\d+ <table>`)

`\dn` list available schema

`\g` execute de last command

`\s` see the history of command

`\i filename` execute pgsql commands from a filename

`\timing` turn on query execution time (say it again to turn it off)

`\q` quit session

`ALTER ROLE <user_name> WITH PASSWORD '<password>';` modifie le mot de passe pour l'utilisateur

`medle=> \conninfo`
"You are connected to database "medle" as user "medle" via socket in "/tmp" at port "5432".`"

`alter table etablissement_sante owner to medle;` pour changer le propriétaire d'une table

`CREATE DATABASE mydb;` pour créer une base de données

`DROP DATABASE mydb;` pour suppprimer une base de données

`CREATE SCHEMA factbook;` créer un schéma

`SET search_path = my_schema, "$user", public;` modifier l'ordre de recherche des objets (tablest, etc.), seulement pour la session de l'utilisateur

`ALTER ROLE your_role SET search_path = my_schema, "$user", public;`idem, mais persistant pour le rôle

## SQL query avec date et interval

update conferences 
set "expiresAt" = now() - interval '2 days' 
where id = 'a4264225-9ada-4c5e-90ae-5f73de095c3c';

## Nom de colonne en Camel Case

select "expiresAt" from conferences;

## Ajouter le client psql

`sudo yum install postgresql-devel postgresql-libs`

## Supprimer tout un schéma

psql postgres://USER:PASSWORD@HOSTNAME/DATABASE -c " DROP SCHEMA public CASCADE; CREATE SCHEMA public; GRANT USAGE ON SCHEMA public to PUBLIC; GRANT CREATE ON SCHEMA public to PUBLIC; COMMENT ON SCHEMA public IS 'standard public schema';"

https://www.shanestillwell.com/2018/04/04/delete-all-tables-in-postgres/

## Recherche plein texte et trigramme

https://www.miximum.fr/blog/la-recherche-plein-texte-avec-postgresql/
https://medium.com/@sukorenomw/when-to-use-postgresql-full-text-search-and-trigram-indexes-4c7c36223421

## Concept 1 serveur / n bases

1 serveur Postgres peut avoir n bases. C'est ce qu'on va utiliser pour créer plusieurs bases par PR ou branche Git.
L'utilisateur qui crée une base a un full accès sur celle-ci. Donc pas besoin d'utilisateur applicatif medle comme je faisais auparavant.

## Extension

Faire `create extension` pour ajouter une extension (en tant qu'admin).

## Chargement d'un dump Postgres

_Sur le nouveau PG managé sur Azure_

psql "postgresql://dev1%40medledevserver:~=*Bn6#2fCdCReZxc*[e-mail]:5432/dev1?sslmode=require" < backup_2020-02-18_14:51:24.sql

- attention: le mdp n'est pas bien parsé. Mettre un mdp sans caractère spéciaux
- faire un `pg_dump` avec `--no-owner` pour ne pas avoir de référence à l'utilisateur medle

host: medledevserver.postgres.database.azure.com
user: medleadmin@medledevserver
pass: sq;h9w[M5qwm1

user app: dev1@medledevserver
password: 01VihjO5xderUnTqmzcb

## Knex / pg + SSL

Problème d'URL Postgres en https. On a beau mettre une url en `?ssl=true` ou bien ajouter `ssl: true` dans `knexfile.js`, ce n'est pas pris en compte à cause d'un bug dans la librairie de parse de l'URL utilisé par Knex.
Pour corriger cela, il faut modifier la librairie pg dans le fichier _knexfile.js_.

const pg = require("pg")
pg.defaults.ssl = true

## Timezone posix/Europe/Paris

Changer le knexfile.js avec un champ timezone qu'il comprend. En effet, `posix/Europe/Paris` ne fait pas partie de la liste des timezones que propose Postgre managé par Azure.

posix/Europe/Paris -> Europe/Paris.

## JSONB

À requêter de la forme:

with nb_locations as (
    select id, coalesce(jsonb_array_length(extra_data->'location'), 0) as num
    from acts) select id, num 
    from nb_locations where num = 1;

## Migrations sur PG managée Azure

- connexion à la prod: `ssh -L 1111:127.0.0.1:5434 factory@40.89.136.101`
- arrêt du front:
- `sudo docker container ls`
- `sudo docker stop medle_app_1`
- lancement du script de backup: `./scripts/backup.sh`
- chargement de la nouvelle base:

    psql "postgresql://prod%40medleprodserver.postgres.database.azure.com:[e-mail]/prod?sslmode=require" < backup_2020-03-03_14:45:55.sql

Un warning: `ERROR: unrecognized configuration parameter "default_table_access_method"``

- modification du .env de prod:

    # Common variables

    NODE_ENV=production
    APP_URL=https://medle.fabrique.social.gouv.fr
    API_URL=https://medle.fabrique.social.gouv.fr/api
    POSTGRES_USER=medle # à changer ? 
    POSTGRES_PASSWORD=R5J8Kzpu5s@K&Z # à changer?
   
   # DB
   DATABASE_URL=psql://dev1%40medledevserver:[e-mail]:5432/medle

    # JWT
    JWT_SECRET=mw6msg$pu^$AFd

    # Test variables
    # TEST_CURRENT_DATE=10/09/2019

## Voir toutes les contraintes

SELECT con.*
FROM pg_catalog.pg_constraint con
INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid
INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace
WHERE nsp.nspname = 'public'
AND rel.relname = 'employments';

Changer le schéma et le nom de la table selon vos besoins.

## Backup de la dev et dump en local

_Octobre 2020_

Utilisation du serveur bastion pour se connecter en local sur la dev.

Rappel URL de connexion : `psql postgres://dev1%40medledevserver:01VihjO5xderUnTqmzcb@localhost:2345/dev1`

Attention :
`createdb: could not connect to database template1: could not connect to server: No such file or directory`

> Attention: la db de medle a le même port que celle de emjpm, donc bien arrêter l'une pour lancer l'autre__

# En local

# 1. dump de la base dev1 sur le PG managé de recette
pg_dump -Fc dev1 --host=localhost --port=2345 --username=dev1@medledevserver > dev1.dump

# Optionnel. Le superadmin donne le droit à l'utilisateur medle de créer des db 
#psql postgres://medle:jJFWsfW5ePbN7J@localhost:5434
psql postgres://user:password@localhost:5434
# alter user medle createdb
alter user medle superuser # nécessaire aussi pour éviter les problèmes de droits manquants pour ajouter des extensions

# 2. Supprime si besoin la db dev1 par le user "user"

dropdb --host=localhost --port=5434 --username=user --echo dev1

# (archive). Suppression du user dev1, si besoin (ancien, maintenant c'est le user medle qui va avoir le ownership de dev1)

#dropuser --host=localhost --port=5434 --username=user --echo dev1

# (archive). Création du user dev1, par le user "user" (mettre mdp = password pour faire simple) (ancien)

#createuser --host=localhost --port=5434 --username=user --pwprompt --echo dev1

# 3. Créer la db dev1 par le user "user" et en ajouter le propriétaire medle
createdb --host=localhost --port=5434 --username=user --owner=medle --echo dev1 # marche avec medle, depuis qu'il a le droit de créer une db

# 4. Appliquer le dump dev1.dump sur la db dev1, par le user medle
pg_restore -d dev1 --host=localhost --port=5434 --username=medle --no-owner dev1.dump 

=> Problème avec le user dev1 pour le pg_restore. Pas le droit de créer des extensions. C'est pour cela que medle est superuser aussi.