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
$ 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;