{{tag>Brouillon Postgres DB SQL}}

= 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

<code bash>
sudo su - postgres
psql
</code>

<code sql>
CREATE ROLE myuser WITH LOGIN PASSWORD 'P@ssw0rd';
CREATE DATABASE mydatabase OWNER myuser;
</code>

Connexion
<code bash>
psql -U myuser -h hostname -d mydatabase
</code>

Ou avec un fichier
<code - ~/.pgpass>
hostname:port:database:username:password
</code>
Échapper les caractères comme ':' du mot de passe avec un antislash 

Réindex
<code bash>
sudo -u postgres reindexdb --all
</code>


== Se connecter à une socket 

Voir [[Postgres - se connecter a une socket]]


== Commande psql

<code ->
\l
show databases

\d
show all

\dt
show tables

\ef
edit function

\x
row / line select
</code>

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

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''
<code ->
$ 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;
**************************
</code>


== Requêtes système

<code sql>
SELECT * FROM pg_stat_activity;
</code>

Export les requêtes en cours dans un fichier CSV
<code bash>
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
</code>




== 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

<code ->
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
</code>

Voir aussi : 
* https://docs.ansible.com/automation-controller/4.3.0/html/administration/performance.html
== Ansible

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


== Autres

=== Lors d'une mise à jour sous Debian

<code ->
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
</code>


=== 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

<code sql>
select DB,count(*) from performance_schema.processlist group by DB;
</code>