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

[Postgresql]Explain plan analyzer

Analyser un explain plan 

[PostgreSQL]Un peu plus sur les indexes

PoWA (PostgreSQL Workload Analyzer) is a performance tool for PostgreSQL 9.4 and newer allowing to collect, aggregate and purge statistics on multiple PostgreSQL instances from various Stats Extensions.

Powa

[Postgres] Afficher plus d'infos

postgres=> dtvsi+
                                                          List of relations
     Schema     |             Name             |   Type   |        Owner        |          Table          |    Size    | Description

[POSTGRES] Privs d'un ROLE R/W

Exemple de création d'un rôle R/W avec les privileges.

[Postgres] Trouver les schemas

select nspname
from pg_catalog.pg_namespace where nspname not like 'pg%' and nspname <> 'information_schema';

[Postgres] Privilege par user

WITH "names"("name") AS (
  SELECT n.nspname AS "name"
    FROM pg_catalog.pg_namespace n
      WHERE n.nspname !~ '^pg_'
        AND n.nspname <> 'information_schema'
) SELECT "name",
  pg_catalog.has_schema_privilege(current_user, "name", 'CREATE') AS "create",
  pg_catalog.has_schema_privilege(current_user, "name", 'USAGE') AS "usage"
    FROM "names";

[PostgreSQL] Privilèges

Privs useful to any new user created.

[Postgres] How to Enable / Disable Archive Mode

 

https://orahow.com/enable-disable-archive-mode-in-postgres/