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

L'extension oracle_fdw

#se connecter au serveur target
psql -U <muyser>
CREATE EXTENSION oracle_fdw;
dew
CREATE SERVER orasrv FOREIGN DATA WRAPPER oracle_fdw (dbserver '<servername>:<port>/<SID>');
des+
GRANT USAGE ON FOREIGN SERVER orasrv TO <muyser>;
CREATE USER MAPPING FOR <muyser> SERVER orasrv OPTIONS ( USER '<oracle_user>', PASSWORD 'xxxx');
deu+
CREATE FOREIGN TABLE ext_<ORATBL> ( id int OPTIONS (key 'true'), name varchar(64), t_data timestamp) SERVER orasrv OPTIONS (SCHEMA '<ORAUSER>' , TABLE '<ORATBL>');

tuto : https://www.postgresql.fastware.com/postgresql-insider-fdw-ora-bas

Pré requis : 
- avoir une VM avec postgresql d'installé sur le meme VLAN qu'une base oracle avec Oracle instantclient-basic, instantclient-devel d'installé
- Ensure that pg_config is in your path, and ORACLE_HOME as well as LD_LIBRARY_PATH are configured
- avoir accès au compte postgres dans la bdd
- avoir accès à une base oracle en sysdba
- The <oracle_user> will obviously need CREATE SESSION privilege and the right to select from the table or view in question.

Bon à savoir : If a NUMBER is converted to a boolean, 0 means false, everything else true.

télécharger l'extension 

 

On tire la chasse et on nettoie ?

PostgreSQL - vacuumdb - vacuumdb — ramasse les ordures et analyse une base de données PostgreSQL Synopsi - Français (runebook.dev)

Calcul stats PostgreSQL

for tablename in $(psql -U postgres -h $2 -d $1 -t -c "select table_name as _table from information_schema.tables t where t.table_schema='sche_admin' order by _table asc"); do

  echo $tablename

  psql -U postgres -h $2 -d $1 -c "analyze ${tablename};"

  psql -U postgres -h $2 -d $1 -c "select count(*) ${tablename} from ${tablename};" >> compte_pgsql.log

done

 

En // :

vacuumdb --port=$PORT --username=$PG_USER --analyze-only --verbose --jobs=4  $PG_DATABASE