PostgreSQL in IBM Cloud.

Création d'une base postgresql avec role hvault en mode batch dans le cloud IBM..

Migration of Synonyms from Oracle to PostgreSQL

Migration of Synonyms from Oracle to PostgreSQL - MigOps

Migration from Oracle Legacy to postgreSQL dMZR

Migration from Oracle Legacy to postgreSQL dMZR

POST MIGRATION ajouter un booleen

# changer type en booleen
alter table "<your_table>"
alter column "<your_column>"
set data type boolean
using case
    when "<your_column>" = '1' then true
    when "<your_column>" = '0' then false
    else null
end;

 

alter database <dbname> set search_path = "$user", public, <schema> ;

# post migration pour un schema

for tablename in $(psql -U postgres -h <hostname> -d <dbname> -t -c "select table_name as _table from information_schema.tables t where t.table_schema='<schema>' order by _table asc"); 
do
 echo $tablename
 psql -U postgres -h <hostname> -d <dbname> -c "analyze ${tablename};"
 psql -U postgres -h <hostname> -d <dbname> -c "select count(*) ${tablename} from ${tablename};" >> rowcount.log
done

# post migration pour plusieurs schemas

#!/bin/sh
schema="('sche1','sche2','sche3','...')"

namespace="."
dbname="dbname"
host="hostname"

 for tablename in $(psql -U postgres -h $host -d $dbname -t -c "select table_schema||'.'||table_name as _table from information_schema.tables t where t.table_schema in $schema order by _table asc");
        do
        echo "psql -U postgres -h $host -d $dbname -t -c "select table_schema||'.'||table_name as _table from information_schema.tables t where t.table_schema in $schema order by _table asc""
        echo $tablename
          psql -U postgres -h ${host} -d ${dbname} -c "analyze ${tablename};"
          psql -U postgres -h ${host} -d ${dbname} -c "select count(*) as nr_rows, '${tablename}' as tablename from ${tablename};" >> logs/rowcount.log
 done





#update sequ

1-    Récupérer la séquence sur Oracle : 
SELECT sequence_name, last_number FROM user_sequences; 
2-    Mettre à jour la séquence sur Postgres : 
SELECT setval('<nom_sequence',<last_number> , true); 

Difference between Clustered and Non-clustered index

link Difference between Clustered and Non-clustered index (guru99.com)

Modify the owner of the tables and the sequences (post migration)

Modify the owner of the tables and the sequences (post migration) 




#change table owner
for tbl in `psql -h $SERVER$DOMAIN -p $PORT -U <dbauser> -qAt -c "select tablename from pg_tables where schemaname = '<your_schema>';" <database>` ; do  psql -h $SERVER$DOMAIN -p $PORT -U <dbauser> -c "alter table "$tbl" owner to <your_role>" <database>; done

#change sequence owner
for tbl in `psql -h $SERVER$DOMAIN -p $PORT -U <dbauser> -qAt -c "select sequence_name from information_schema.sequences where sequence_schema = '<your_schema>';" <database>` ; do  psql -h $SERVER$DOMAIN -p $PORT -U <dbauser> -c "alter sequence "$tbl" owner to <your_role>" <database>; done

Status instance postgres

POSTGRES_IS_RUNNING=$(export PGPASSWORD=${PG_PWD}; psql --host=${MACH_DEST} --port=${PORT} --user=${PG_USER} --dbname=postgres --quiet --tuples-only --no-psqlrc --command "SELECT 1;" 2>/dev/null)




INSTANCE_STATUS=$(export PGPASSWORD=${PG_PWD}; psql --host=${MACH_DEST} --port=${PORT} --user=${PG_USER} --dbname=postgres --quiet --tuples-only --no-psqlrc --command="SELECT CASE pg_is_in_recovery() WHEN true THEN 'IN RECOVERY' ELSE 'OPEN' END;" 2>/dev/null)

Calcul de stats postgreSQL

$PGHOME/bin/vacuumdb --port=$PORT --username=$PG_USER --analyze-only --verbose --jobs=$NB_JOBS $PG_DATABASE" 2>&1

DEPLOY HA postgres

deploy_HA.txt

About JSON in RDBMS

2 links :

JSONB PostgreSQL: How To Store & Index JSON Data (scalegrid.io)

PostgreSQL JSON Tutorial (postgresqltutorial.com)