[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

[ora2pg] Conversion datatype

Conversion datatype 

useful to convert object into postgresql

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

Upgrade Casssandra

upgrade.txt

Backup Cassandra

Pour sauvegarde incrémentale: 

 

incremental_backups à true dans le fichier .yaml

 

https://docs.datastax.com/en/cassandra-oss/2.2/cassandra/configuration/configCassandra_yaml.html#configCassandra_yaml__incremental_backups

 

 

backup : 

nodetool -h localhost -p 7199 snapshot mykeyspace

https://docs.datastax.com/en/cassandra-oss/2.2/cassandra/operations/opsBackupTakesSnapshot.html


supprimer backup : 

nodetool -h localhost -p 7199 clearsnapshot

restaurer : 

cp snapshots/1535397029191/* /path/to/keyspace1/standard1/

sstableloader --nodes 172.17.0.2 /var/lib/cassandra/loadme/keyspace1/standard1-f8a4fa30aa2a11e8af27091830ac5256/

https://docs.datastax.com/en/cassandra-oss/3.0/cassandra/operations/opsBackupSnapshotRestore.html

https://docs.datastax.com/en/cassandra-oss/2.2/cassandra/tools/toolsBulkloader.html

Assessment ora2pg

Assessment ora2pg : 
ora2pg -t SHOW_REPORT -c ./config/ora2pg.conf --dump_as_html --cost_unit_value 5 --estimate_cost > ./reports/report.html
ora2pg -t SHOW_TABLE -c ./config/ora2pg.conf > ./reports/tables.txt
ora2pg -t SHOW_COLUMN -c ./config/ora2pg.conf > ./reports/columns.txt

Compare source VS target

ora2pg --type TEST --count_rows --conf confia2pg.conf

Tuning ora2pg

On peut gagner du temps :

1-Si on purge 

2-Si on exclut les tables vides

3-Si on calcule les stats 

4-Si on ajoute de la mémoire chez Oracle

5-Si on utilise les options de parallélisme ora2pg

6-Si on modifie la valeur limite par défaut des données à exporter.