Notes Postgres

Voir :

https://linuxfr.org/users/spacefox/journaux/postgresql-ne-faites-pas-ca

Voir le client pgcli en ligne de commande avec autocomplétion et coloration syntaxique http://blog.adminrezo.fr/2016/01/mycli-pgcli-mysql-postregsql-clients/

Schéma arbre hiérarchie CSV

http://www.davidchriqui.com/fast-closure-tree-building-from-csv-in-plain-postgresql-and-a-little-bit-of-ruby/

Postgres HA

https://support.ptc.com/help/thingworx/platform/r9/en/index.html#page/ThingWorx/Help/ThingWorxHighAvailability/ExampleDeploymentofPostgreSQLHAwithPgpoolII.html

WITNESS-SERVER

Voir :

https://opensource.com/article/23/2/manage-large-postgres-databases

Supervision :

temBoard Agent

DSN: pgsql:host=localhost;port=5432;dbname=testdb;user=myuser;password=mypass (See PDO PostgreSQL)

Notes

Création de la DB

sudo su - postgres psql
CREATE ROLE myuser WITH LOGIN PASSWORD 'P@ssw0rd'; CREATE DATABASE mydatabase OWNER myuser;

Connexion

psql -U myuser -h hostname -d mydatabase

Ou avec un fichier

hostname:port:database:username:password

Échapper les caractères comme ':' du mot de passe avec un antislash

Réindex

sudo -u postgres reindexdb --all

Se connecter à une socket

Voir Postgres - se connecter a une socket

Commande psql

\l
show databases
\d
show all
\dt
show tables
\ef
edit function
\x
row / line select

echo-hidden - commande cachées - psql détaillé commande SQL

Avoir le détail des commandes cachées que psql faitPar exemple pour avoir le detail de la commande \dt C'est possible avec l'option --echo-hidden ou -E

$ env PGPASSWORD=$TF_VAR_pgpass psql -E -q -h jbl1-rdsscm-dev-env.cuapezqvgl58.eu-central-1.rds.amazonaws.com -U $TF_VAR_pguser --dbname=$TF_VAR_pgname
postgres=> \dt
********* QUERY **********
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'table' WHEN 'I' THEN 'index' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','p','')
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************

Requêtes système

SELECT * FROM pg_stat_activity;

Export les requêtes en cours dans un fichier CSV

psql -h /tmp -p 5432 -q --csv -c "SELECT * FROM pg_stat_activity;" 2>>/tmp/log_query.err | gzip > /tmp/log_query_$(date +%Y-%m-%d-%H%M).csv.gz

Config

Recomandation RedHat pour AAP

Source : https://access.redhat.com/documentation/fr-fr/red_hat_ansible_automation_platform/2.2/html/red_hat_ansible_automation_platform_installation_guide/planning-installation

max_connections == 1024
shared_buffers == ansible_memtotal_mb*0.3
work_mem == ansible_memtotal_mb*0.03
maintenance_work_mem == ansible_memtotal_mb*0.04

Voir aussi :

https://docs.ansible.com/automation-controller/4.3.0/html/administration/performance.html

Ansible

#!/usr/bin/ansible-playbook --- - name: Postgres Select Example hosts: localhost gather_facts: false tasks: - name: Select from users table postgresql_query: login_host: db1.acme.local login_user: pg_user login_password: "P@ssw0rd!" login_db: db1 login_port: 5455 query: "SELECT * FROM users LIMIT 10;" register: db_result - name: DEBUG 10 debug: var: db_result

Autres

Lors d'une mise à jour sous Debian

o resolve the situation, before upgrading, execute:
# su - postgres
$ pg_lsclusters
$ pg_ctlcluster 9.4 main start
$ pg_dumpall --cluster 9.4/main | pigz > 9.4-main.dump.gz
$ cp -a /etc/postgresql/9.4/main 9.4-main.config
$ pg_dropcluster 9.4 main --stop
Then after the upgrade, execute:
# su - postgres
$ pg_createcluster 9.4 main
$ cp 9.4-main.config/* /etc/postgresql/9.4/main
$ pg_ctlcluster 9.4 main start
$ zcat 9.4-main.dump.gz | psql -q
$ rm -rf 9.4-main.config 9.4-main.dump.gz

Force drop db while others may be connected

A tester :

https://dba.stackexchange.com/questions/11893/force-drop-db-while-others-may-be-connected

Autres

SELECT DB,COUNT(*) FROM performance_schema.processlist GROUP BY DB;