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)

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

~/.pgpass

hostname:port:database:username:password

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

Réindex

sudo -u postgres reindexdb --all

Voir Postgres - se connecter a une socket

\l
show databases

\d
show all

\dt
show tables

\ef
edit function

\x
row / line select

Avoir le détail des commandes cachées que psql fait Par 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;
**************************

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

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

#!/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

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

A tester :

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

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