blog
Table des matières
- 2026:
- 2025:
8 billet(s) pour mars 2026
| Notes conteneurs oras artifact OCI | 2026/03/23 21:13 | Jean-Baptiste |
| Notes podman secret | 2026/03/23 15:10 | Jean-Baptiste |
| Notes ansible podman | 2026/03/23 14:08 | Jean-Baptiste |
| Notes podman volume | 2026/03/23 14:00 | Jean-Baptiste |
| Find list - Trouver des fichiers à partir d'une liste | 2026/03/18 14:32 | Jean-Baptiste |
| AWX inventaire vault | 2026/03/17 18:04 | Jean-Baptiste |
| AWX - Configuration git en local (sans serveur web) | 2026/03/05 16:24 | Jean-Baptiste |
| OpenSMTP | 2026/03/03 16:58 | Jean-Baptiste |
Notes profiling kernel linux perf
Notes Presse-papier
Voir :
- Qlipper
- ClipIt
- Kpcli
- xclip
- xsel
- autocutsel
- pastebinit
http://tech.dcolon.org/wordpress/copy-and-paste-for-keepass-under-linux/
autocutsel & autocutsel -s PRIMARY &
Notes Postgres
Voir :
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
Postgres HA
- WITNESS-SERVER
Voir :
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
- ~/.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
Se connecter à 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 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;
**************************
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
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 :
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 :
Autres
SELECT DB,COUNT(*) FROM performance_schema.processlist GROUP BY DB;
Notes Postgres Python
Voir :
Voir aussi :
- pg8000
- python-sqlalchemy
Exemple
Vacuum
dbname = 'dbname' user = 'postgres' host = '192.168.1.10' password = 'password' import psycopg2 c = "dbname='%s' user='%s' host='%s' password='%s'" conn = psycopg2.connect(c % (dbname, user, host, password)) conn.set_session(autocommit=True) cur=conn.cursor() cur.execute("VACUUM FULL ANALYSE") cur.close() conn.close()
Query select - Fetch
cur=conn.cursor() cur.execute("SELECT plop.purge()") if cur.rowcount > 0: row = cur.fetchone() else: row = None while row is not None: print(row) row = cur.fetchone() cur.close() conn.commit() conn.close()
with statement
Source : https://www.psycopg.org/docs/usage.html#with-statement
conn = psycopg2.connect(DSN) with conn: with conn.cursor() as curs: curs.execute(SQL1) with conn: with conn.cursor() as curs: curs.execute(SQL2) conn.close()
Warning
Unlike file objects or other resources, exiting the connection’s with block doesn’t close the connection, but only the transaction associated to it. If you want to make sure the connection is closed after a certain point, you should still use a try-catch block :
conn = psycopg2.connect(DSN) try: # connection usage finally: conn.close()
Notes Postgres PL/PGSQL
Voir :
CREATE OR REPLACE FUNCTION ADD(a INT,b INT) RETURNS BIGINT AS $$ DECLARE c INT; BEGIN c:=a+b; RAISE NOTICE 'Arg1 : %',a; RAISE NOTICE 'Arg2 : %',b; RAISE NOTICE 'Return : %',c; IF c<0 THEN RAISE EXCEPTION 'Error c negativ'; END IF; RETURN c; END; $$ LANGUAGE PLPGSQL;
Call function
Si aucun résultat n'est attendu Utiliser PERFORM à la place de SELECT\ Note : A la place de SELECT pensez à utiliser SELECT INTO Voir aussi EXECUTE.
CREATE OR REPLACE FUNCTION foo() RETURNS void AS $$ BEGIN RAISE NOTICE 'Hello from void function'; END; $$ LANGUAGE plpgsql; -- direct call from SQL SELECT foo(); -- in PLpgSQL DO $$ BEGIN SELECT foo(); -- is not allowed PERFORM foo(); -- is ok END; $$;
SELECT ma_function(); SELECT * FROM ma_function(); DO $$ BEGIN PERFORM ma_function(); END $$;
Ne rien faire
Utiliser NULL
BEGIN y := x / 0; EXCEPTION WHEN division_by_zero THEN NULL; -- ignore the error END;
cast conversion type - date
db1=> SELECT now();
now
-------------------------------
2019-12-11 15:54:57.984691+00
(1 row)
db1=> SELECT now()::timestamp;
now
----------------------------
2019-12-11 15:55:05.608026
(1 row)
DEBUG
Avec RAISE NOTICE
CREATE OR REPLACE FUNCTION plop() RETURNS INT AS $$ DECLARE query1 text; WN_RESULT INT; -- res RECORD; res table%rowtype; BEGIN WN_RESULT := 1; BEGIN FOR res IN SELECT * FROM table1 WHERE maintained=FALSE LOOP query1='UPDATE table SET nbcount=subquery.count,maintained=true FROM (SELECT COUNT(*) FROM ' || res.atable || ') AS subquery WHERE atable=' || quote_literal(res.atable) ; RAISE NOTICE 'DEBUG: %', query1 ; EXECUTE query1 ; END LOOP; EXCEPTION WHEN OTHERS THEN RAISE WARNING 'plop() exception[%][%]', SQLSTATE, SQLERRM; WN_RESULT := -1; END; RETURN WN_RESULT; END; $$ LANGUAGE plpgsql; SELECT plop();
blog.txt · Dernière modification : de 127.0.0.1