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

Migration de base oracle vers postgreSQL

Script de déploiement en préparation d'une migration de base oracle vers postgreSQL (dMZR) avec roles hashicorp.

EDB feedback

Why Mastercard's Secret to Zero Downtime is Postgres (enterprisedb.com)

[Cloud IBM PSQL] Role creation with HVAULT (static dba)

script : prepare_dba_static_role.sh

 

[Cloud IBM PSQL] Role creation with HVAULT

Roles creation using hvault : 

- dynamic roles

The roles are created with this command :

vault write -ns="$VAULT_NAMESPACE/$ECOSYSTEM" database/postgres/${SERVER}/roles/${ROLE} 
db_name=${SERVER} 
creation_statements=@${ORA2PG_HOME}/dynamic_role/${TYPE}/create_dyna_role_${TYPE}_${ASSET}.sql 
revocation_statements=@${ORA2PG_HOME}/dynamic_role/${TYPE}/drop_dyna_role_${TYPE}_${ASSET}.sql 
default_ttl=${ROTATION} 
max_ttl=${ROTATION}

[PostgreSQL] Privilèges

Privs useful to any new user created.

[PostgreSQL] Ora2pg Install

yum install oracle-instantclient12.2-basic-12.2.0.1.0-1.x86_64.rpm
yum install oracle-instantclient12.2-devel-12.2.0.1.0-1.x86_64.rpm
yum install oracle-instantclient12.2-jdbc-12.2.0.1.0-1.x86_64.rpm
yum install oracle-instantclient12.2-sqlplus-12.2.0.1.0-1.x86_64.rpm
yum -y install perl-DBI
yum install gcc
yum install glibc-devel
yum install perl-ExtUtils-MakeMaker -y
yum install perl-open.noarch -y

export ORACLE_HOME=/usr/lib/oracle/12.2/client64
export LD_LIBRARY_PATH=/usr/lib/oracle/12.2/client64/lib

tar -xvzf ora2pg-23.0.tar.gz
cd ora2pg-23.0/
perl Makefile.PL
make && make install

tar -xvzf DBD-Oracle-1.83.tar.gz
cd DBD-Oracle-1.83
perl Makefile.PL
make && make install

[PostgreSQL] pgBackRest sauvegarder la standby

Backup From a Standby | pgBackRest (pmatseykanets.github.io)

[PostgreSQL] Vérifier le statut de la réplication

On master:

select * from pg_stat_replication;

On replica (streaming replication in my case):

select * from pg_stat_wal_receiver;

On your master, pg_stat_replication provides data about ongoing replication:

select client_addr, state, sent_location, write_location,
        flush_location, replay_location from pg_stat_replication;

On postgresql v10:

select client_addr, state, sent_lsn, write_lsn,
    flush_lsn, replay_lsn from pg_stat_replication;

On server

postgres=# select usename,application_name,client_addr,backend_start,state,sync_state from pg_stat_replication ;


On client

postgres=# select pg_is_in_recovery();

postgres=# select pg_last_xlog_receive_location();

postgres=#    SELECT CASE WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location()
                  THEN 0
                ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())
              END AS log_delay;

postgres=# select pg_last_xlog_replay_location();

[PostgreSQL] COMPRESS

Detecting inline, inline-compressed and TOAST storage

+--+--------------+------------------+----------+-----------+-------------+
|id|full_name     |uncompressed_bytes|compressed|out_of_line|bytes_on_disk|
+--+--------------+------------------+----------+-----------+-------------+
|1 |joe toast     |4004              |false     |true       |4000         |
|2 |joe compressed|3000              |true      |false      |44           |
|3 |joe inline    |10                |false     |false      |11           |

[PostgreSQL] Find all the table and index size

SELECT
    TableName
    ,pg_size_pretty(pg_table_size(TableName)) AS TableSize
    ,pg_size_pretty(pg_indexes_size(TableName)) AS IndexSize
    ,pg_size_pretty(pg_total_relation_size(TableName)) AS TotalSize
FROM 
(
     SELECT ('"' || table_schema || '"."' || table_name || '"') AS TableName
     FROM information_schema.tables
) AS Tables
ORDER BY 4 DESC

source :How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database (tutorialdba.com)

[PostgreSQL] Streaming Replication Monitoring Script

Script de création d'une fonction "streaming_slave_check" pour la réplication en postgreSQL.

[PostgreSQL] repmgr tuto

Tutoriel d'installation pour repgmr

[PostgreSQL] Anonymisation des données

Procédure d'anonymisation des données

[PostgreSQL] Déploiement d'un cluster PATRONI

Convert a Standalone to a Patroni Cluster

Déployer le cluster PATRONI dans un nouvel environnement

[PostgreSQL]Kill une session

SELECT *, pg_terminate_backend(pid)
FROM pg_stat_activity 
WHERE pid <> pg_backend_pid()
AND datname = '<dbname>';

select pg_terminate_backend(pid) 
from pg_stat_activity
where pid = '<PID>';

Configuration application cloud postgres

USERNAME :: postgres://<instance>/<role>/user

PASSWORD ::postgres://<instance>/<role>/password

URL :: jdbc:postgresql://<instance>.<domain>:<port>/<dbname>

Changer le owner des tables

#Change owner of tables qui sont à postgres

for tbl in `psql -qAt -c "select tablename from pg_tables where tableowner = 'postgres' and schemaname='SCHEMA';" dbname` ; 
do  psql -c "alter table SCHEMA."$tbl" owner to NEW_OWNER" dbname ; done

Postgres kill session

# kill sessions
# trouver la sessions
select * from pg_stat_activity;
select pg_terminate_backend(pid) 
from pg_stat_activity
where pid = 'PID';

Migration assessment

Anonymization & Data Masking for PostgreSQL

Anonymisation des données dans postgres avec l'extension anon :

PostgreSQL Anonymizer (postgresql-anonymizer.readthedocs.io)

Export de la base : 

Anonymous Dumps - PostgreSQL Anonymizer (postgresql-anonymizer.readthedocs.io)

 

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 

 

Calculate the value for memory postgresql parameters

Postgresql parameter : PostgreSQL Documentation: shared_buffers parameter (postgresqlco.nf)

(aide)tuner les paramètres postgresql : 

https://pgtune.leopard.in.ua/
https://www.enterprisedb.com/postgres-tutorials/how-tune-postgresql-guc-parameters
https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
postgresqltuner.pl

parameter_working.xlsx

Ora2PG now supports oracle_fdw to increase the data migration speed

 

 

source : https://www.migops.com/blog/2021/07/01/ora2pg-now-supports-oracle_fdw-to-increase-the-data-migration-speed/

Les extensions indispensables

Pg_stat_statement

Pg_store_plan 

Pgsentinel

• pg_buffercache : état du cache
• pgstattuple (fragmentation des tables et index)
• Autoexplain

Source : https://public.dalibo.com/exports/formation/manuels/modules/x1/x1.handout.html

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)

Modifier l'encodage PostgreSQL en UTF8

How to change PostgreSQL database encoding to UTF8 (shubhamdipt.com)

 

PostgreSQL bonnes pratiques

Restriction du schéma « public »
Définir des nouveaux tablespaces pour les données et indexes et temp et changer le nom du tablespace par défaut (data et temp)

 

PG_BADGER

pgbadger -j 4 --outfile $HOME/rapport_complet.html -f stderr -p '%t:%r:%u@%d:[%p]:' /pglog/postgresql-2021-09-10_000000.log

Calcul de stats postgreSQL

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