PG_BADGER

pgbadger -j 4 --outfile $HOME/rapport_complet.html -f stderr -p '%t:%r:%u@%d:[%p]:' /pglog/postgresql-2021-09-10_000000.log

Configuration application cloud postgres

USERNAME :: postgres://<instance>/<role>/user

PASSWORD ::postgres://<instance>/<role>/password

URL :: jdbc:postgresql://<instance>.<domain>:<port>/<dbname>

Ora2PG now supports oracle_fdw to increase the data migration speed

 

 

source : https://www.migops.com/blog/2021/07/01/ora2pg-now-supports-oracle_fdw-to-increase-the-data-migration-speed/

[PostgreSQL] Voir les tables partitionnées

WITH RECURSIVE partition_info(relid, relname, relsize, relispartition, relkind)
AS (SELECT oid AS relid, relname, pg_relation_size(oid) AS relsize, relispartition, relkind
      FROM pg_catalog.pg_class
     WHERE relkind = 'p' AND relname = 'mof_ple_part' -- Pour une table donnee.
     UNION ALL
    SELECT c.oid AS relid, c.relname AS relname, pg_relation_size(c.oid) AS relsize,
           c.relispartition AS relispartition, c.relkind AS relkind
      FROM partition_info AS p, pg_catalog.pg_inherits AS i, pg_catalog.pg_class AS c
     WHERE p.relid = i.inhparent AND c.oid = i.inhrelid AND c.relispartition)

SELECT * FROM partition_info;
 

select relnamespace::regnamespace::text schema_name, oid::regclass::text table_name from pg_class
where relkind = 'p' and oid in (select distinct inhparent from pg_inherits)
order by schema_name, table_name;

[PostgreSQL] Déploiement d'un cluster PATRONI

Convert a Standalone to a Patroni Cluster

Déployer le cluster PATRONI dans un nouvel environnement

[PostgreSQL] Voir les tablespaces

show default_tablespace ;

alter system set default_tablespace='tbs_msg_data' ;

select pg_reload_conf();

show default_tablespace 



show temp_tablespaces ;

alter system set temp_tablespaces='temp' ;

select pg_reload_conf();

show temp_tablespaces 

[PostgreSQL]Kill une session

SELECT *, pg_terminate_backend(pid)
FROM pg_stat_activity 
WHERE pid <> pg_backend_pid()
AND datname = '<dbname>';


# kill sessions
# trouver la sessions
select * from pg_stat_activity;
select pg_terminate_backend(pid) 
from pg_stat_activity
where pid = 'PID';

[HVAULT] Roles creation for dMZR postgreSQL- version batch

Roles creation for dMZR postgreSQL- version batch 

script pour la création d'une nouvelle base dans le cloud IBM avec les rôles dans Hashicorp Vault.

[Postgres] Migration of roles + DB (including data and privs)

Se connecter sur le serveur cible.

 

 pg_dumpall --roles-only -h remotehost -U remoteuser  | psql -h localhost -U localuser 
 pg_dump -C -h remotehost -U remoteuser dbname | psql -h localhost -U localuser 

[Postgres] User Privs

SELECT grantee AS user, CONCAT(table_schema, '.', table_name) AS table, 
    CASE 
        WHEN COUNT(privilege_type) = 7 THEN 'ALL'
        ELSE ARRAY_TO_STRING(ARRAY_AGG(privilege_type), ', ')
    END AS grants
FROM information_schema.role_table_grants
GROUP BY table_name, table_schema, grantee;