GET _cluster/allocation/explain\?pretty

GET _cat/shards?v=true&h=index,shard,prirep,state,node,unassigned.reason&s=state

GET _cat/health?v

GET _cat/indices?v

{
  "type": "fs",
  "settings": {
    "location": "my_backup_location"
  }
}

POST /_snapshot/my_repository/_verify

POST /_snapshot/my_repository/_analyze?blob_count=10&max_blob_size=1mb&timeout=120s

GET /_snapshot/my_repository

DELETE /_snapshot/my_repository

POST /_snapshot/my_repository/_cleanup

{
  "indices": "index_a,index_b"
}

PUT /_snapshot/my_repository/my_snapshot

GET /_snapshot/my_repository/my_snapshot

POST /_snapshot/my_repository/my_snapshot/_restore

DELETE /_snapshot/my_repository/my_snapshot

GET /_cat/snapshots/repo1?v=true&s=id

# use with klist :
ldapsearch -Y GSSAPI -LLL -H ldap://ad1 -b 'dc=corp,dc=domain,dc=net'

# with password
ldapsearch -LLL -H ldap://ad1 -b "dc=corp,dc=domain,dc=net" -D "cn=administrator,cn=users,dc=corp,dc=domain,dc=net" -w mypassword

ldapsearch -LLL -H ldap://ad1 -b "dc=corp,dc=domain,dc=net" -D "CN=name,OU=users,OU=domain,DC=corp,dc=domain,dc=net" -w xxxxx

sh -c 'nohup mysqldump -h mydb.hostname -u admin --triggers --routines --events --compress --all-databases --pXXXXX  > mydatabase.dmp &'

SELECT
  TABLE_NAME AS `Table`,
  ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`
FROM
  information_schema.TABLES
WHERE
  TABLE_SCHEMA = "bookstore"
ORDER BY
  (DATA_LENGTH + INDEX_LENGTH)
DESC;

SELECT table_name, data_free/1024/1024 AS data_free_MB, table_rows FROM information_schema.tables
  WHERE engine LIKE 'InnoDB';

SELECT table_schema AS "Database",
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
FROM information_schema.TABLES
GROUP BY table_schema;

SELECT default_character_set_name FROM information_schema.SCHEMATA
WHERE schema_name = "schemaname";

SELECT CCSA.character_set_name FROM information_schema.`TABLES` T,
       information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA
WHERE CCSA.collation_name = T.table_collation
  AND T.table_schema = "schemaname"
  AND T.table_name = "tablename";

SELECT character_set_name FROM information_schema.`COLUMNS`
WHERE table_schema = "schemaname"
  AND table_name = "tablename"
  AND column_name = "columnname";

sh -c 'nohup mysqldump -h mydb.hostname -u admin --triggers --routines --events --compress --all-databases --pXXXXX  > mydatabase.dmp &'

> create user 'root'@'%' IDENTIFIED BY 'password'
> grant all privileges on *.* to 'root'@'%' with grant option;
> flush privileges;
> show grants for current_user;

psql -d database -U yser -W
psql -U user -h host "dbname=db sslmode=require"

\c dbname username
\l : list all databases
\dx : liste des extensions installées
\dt : list all tables
\d table_name : describe a table
\dn : list available schema
\dv : list available functions
\dv : list available views
\du : list all users and their assign roles

\g : execute previous command
\s <filename> : command history (save into filename)
\i filename : execute command from filename

\timing : turn on / off execution time
\e : launch EDITOR defined in your ENV
\ef <function_name> : execute a function named from editor

\a : command switches from aligned to non-aligned column output.
\H : command formats the output to HTML format.

SELECT version();

# See ROLES
psql -U postgres -c "SELECT * FROM pg_roles;"

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

postgres=# connect mydb;
postgres=# SELECT pg_size_pretty(pg_database_size('mydb'));

postgres=# connect mydb;
mydb=# SELECT pg_size_pretty( pg_total_relation_size('mytbl'));

SELECT
    nom_base,
    schema_objet_locke,
    nom_objet_locke,
    type_objet_locke,
    duree_bloquage,
    pid_session_bloquante,
    user_session_bloquante,
    client_session_bloquante,
    derniere_requete_session_bloquante,
    heure_debut_session_bloquante,
    heure_debut_requete_bloquante,
    pid_session_bloquee,
    user_session_bloquee,
    client_session_bloquee,
    derniere_requete_session_bloquee,
    heure_debut_requete_bloquee,
    heure_debut_session_bloquee
FROM
(
SELECT distinct
    RANK() OVER (PARTITION BY c.pid ORDER BY g.query_start DESC) as rang,
    c.datname AS nom_base,
    e.nspname AS schema_objet_locke,
    d.relname AS nom_objet_locke,
CASE
    WHEN d.relkind IN ('t','r') THEN 'table'
    WHEN d.relkind = 'i' THEN 'index'
    WHEN d.relkind = 's' THEN 'sequence'
    WHEN d.relkind = 'v' THEN 'vue'
ELSE d.relkind::text
END AS type_objet_locke,
    TO_CHAR(now()-c.query_start,'DD')||'j '||TO_CHAR(now()-c.query_start,'HH24:MI:SS') AS duree_bloquage,
    g.pid AS pid_session_bloquante,
    g.usename AS user_session_bloquante,
    g.client_addr AS client_session_bloquante,
    g.query AS derniere_requete_session_bloquante,
    TO_CHAR(g.backend_start,'YYYYMMDD HH24:MI:SS') AS heure_debut_session_bloquante,
    TO_CHAR(g.query_start,'YYYYMMDD HH24:MI:SS') AS heure_debut_requete_bloquante,
    c.pid AS pid_session_bloquee,
    c.usename AS user_session_bloquee,
    c.client_addr AS client_session_bloquee,
    c.query AS derniere_requete_session_bloquee,
    TO_CHAR(c.query_start,'YYYYMMDD HH24:MI:SS') AS heure_debut_requete_bloquee,
    TO_CHAR(c.backend_start,'YYYYMMDD HH24:MI:SS') AS heure_debut_session_bloquee
FROM
    pg_locks AS a,
    pg_locks AS b,
    pg_stat_activity AS c,
    pg_class AS d,
    pg_namespace AS e,
    pg_locks AS f,
    pg_stat_activity AS g
WHERE a.pid = b.pid
    AND a.pid = c.pid
    AND b.relation = d.oid
    AND d.relnamespace = e.oid
    AND b.relation = f.relation
    AND b.pid <> f.pid
    AND f.pid = g.pid
    AND c.query_start >= g.query_start
    AND a.granted IS FALSE
    AND b.relation::regclass IS NOT NULL
    AND e.nspname NOT IN ('pg_catalog','pg_toast','information_schema')
    AND e.nspname NOT LIKE 'pg_temp_%'
    AND f.granted is true
) AS resultat
WHERE rang = 1
ORDER BY resultat.heure_debut_requete_bloquee,resultat.heure_debut_requete_bloquante ;

# Backup single database
pg_dump my_database > my_database.sql
# Restore single database
psql my_database < my_database.sql

# Backup All databases
pg_dumpall > alldb.sql
# Restore All databases
psql < alldb.sql

# Backup Compressed database
pg_dump mydb | gzip > mydb.sql.gz
# Restore compressed database
gunzip -c mydb.sql.gz | psql mydb