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. 

 

[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 

 

[ora2pg] Assessment

To be able to say “Yeah, we migrate to PostgreSQL”, we have to be confident by studying the technical aspects of it.

For that, we assess the feasibility of the migration from oracle to postgres.

The assessments consist in validating the :

• Metadata structure and datatypes
• Data consistency
• Data integrity

 

For this, we use the free tool called ora2Pg.

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