[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.

[HVAULT] Roles creation for dMZR postgreSQL

Roles : own , app,  devpg

[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 

[ORACLE] Preupgrade fixups

fixup_b4_mig.pdf

[Cassandra] Changer mot de passe, changer nom de cluster

Changer mot de passe : 

 

modifier paramètres dans yaml : 

keystore_password
truststore_password 

 

Changer de nom de cluster :

1 -UPDATE system.local SET cluster_name = '<cluster_name>' where key='local';

2- nodetool flush system (recommended)

3. cluster_name update cassandra.yaml the name for the new cluster

4. Restart cassandra cluster

[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;

[Postgresql]Explain plan analyzer

Analyser un explain plan