[Postgres] TUTO

--tuto tuning psql
--Spot a missing PostgreSQL index

--returns the tables which have been hit by sequential scans the most and tells us how many rows a sequential scan has hit on average.
SELECT schemaname, 
              relname, 
              seq_scan, 
              seq_tup_read, 
              idx_scan, 
              seq_tup_read / seq_scan AS avg
 FROM         pg_stat_user_tables 
 WHERE        seq_scan > 0 
 ORDER BY     seq_tup_read DESC;

--Finding slow queries to be executed on pgadmin
SELECT query, 
              total_exec_time, 
              calls, 
              mean_exec_time 
       FROM   pg_stat_statements 
       ORDER BY total_exec_time DESC;
       

[Postgres] Migration of roles + DB (including data and privs)

Se connecter sur le serveur cible.

 

 pg_dumpall --roles-only -h remotehost -U remoteuser  | psql -h localhost -U localuser 
 pg_dump -C -h remotehost -U remoteuser dbname | psql -h localhost -U localuser 

[Postgresql]Explain plan analyzer

Analyser un explain plan 

Un peu plus sur les indexes

 les indexes bitmap n existent pas en postgreSQL.

Il existe l équivalent, les indexes BRIN, que l'on met sur des tables volumineuses : 

https://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.5#BRIN_Indexes

 

Es tu au courant qu'il existe d autres types d indexes , le GiST: 

https://en.m.wikipedia.org/wiki/GiST

 

Le Gin qui peut être utilisé dans un contexte plus général : 

https://wiki.postgresql.org/wiki/GIN_generalization

 

Pour résumé : 

 

PostgreSQL inclut une prise en charge intégrée des index B-tree et de table de hachage réguliers, et quatre méthodes d'accès aux index : arbres de recherche généralisés (GiST), index inversés généralisés (GIN), GiST partitionné par espace (SP-GiST)[33] et bloc Indices de gamme (BRIN).

 

In choosing which index type to use, GiST or GIN, consider these performance differences:

 

GIN index lookups are about three times faster than GiST

 

GIN indexes take about three times longer to build than GiST

 

GIN indexes are moderately slower to update than GiST indexes, but about 10 times slower if fast-update support was disabled (see Section 54.3.1 for details)

 

GIN indexes are two-to-three times larger than GiST indexes

tables sans oid en big serial

select sch.nspname as schemaname,
       tab.relname as tablename,
       col.attname as columnname,
       col.attnum as columnnumber,
       col.atttypid,
       typ.typname as columntype
from pg_attribute col
join pg_type typ on col.atttypid = typ.oid
join pg_class tab on col.attrelid = tab.oid
join pg_namespace sch on tab.relnamespace = sch.oid
left join pg_attrdef def on tab.oid = def.adrelid
and col.attnum = def.adnum
left join pg_depend deps on def.oid = deps.objid
and deps.deptype = 'N'
where sch.nspname != 'information_schema'
       and sch.nspname not like 'pg_%' -- won't work if you have user schemas matching pg_
       and col.attnum > 0
       and typ.typcategory = 'N'
       and col.attnotnull = true
       and typ.typname = 'int4'
order by sch.nspname,
       tab.relname,
       col.attnum;


with seqs as
       (select oid,
             relname as sequencename
             from pg_class
             where relkind = 'S' )
select sch.nspname as schemaname,
       tab.relname as tablename,
       col.attname as columnname,
       col.attnum as columnnumber,
       col.atttypid,
       typ.typname as columntype,
       seq.sequencename
from pg_attribute col
join pg_type typ on col.atttypid = typ.oid
join pg_class tab on col.attrelid = tab.oid
join pg_namespace sch on tab.relnamespace = sch.oid
left join pg_attrdef def on tab.oid = def.adrelid
and col.attnum = def.adnum
left join pg_depend deps on def.oid = deps.objid
and deps.deptype = 'N'
left join seqs seq on deps.refobjid = seq.oid
where sch.nspname != 'information_schema'
       and sch.nspname not like 'pg_%' -- won't work if you have user schemas matching pg_
       and col.attnum > 0
       and typ.typcategory = 'N'
       and col.attnotnull = true
order by sch.nspname,
       tab.relname,
       col.attnum;

select sch.nspname as schemaname,
       tab.relname as tablename,
       col.attname as columnname,
       col.attnum as columnnumber,
       col.atttypid,
       typ.typname as columntype
from pg_attribute col
join pg_type typ on col.atttypid = typ.oid
join pg_class tab on col.attrelid = tab.oid
join pg_namespace sch on tab.relnamespace = sch.oid
left join pg_attrdef def on tab.oid = def.adrelid
and col.attnum = def.adnum
left join pg_depend deps on def.oid = deps.objid
and deps.deptype = 'N'
where sch.nspname != 'information_schema'
       and sch.nspname not like 'pg_%' -- won't work if you have user schemas matching pg_
       and col.attnum > 0
       and typ.typcategory = 'N'
       and col.attnotnull = true
       and typ.typname = 'int4'
order by sch.nspname,
       tab.relname,
       col.attnum;


with seqs as
       (select oid,
             relname as sequencename
             from pg_class
             where relkind = 'S' )
select sch.nspname as schemaname,
       tab.relname as tablename,
       col.attname as columnname,
       col.attnum as columnnumber,
       col.atttypid,
       typ.typname as columntype,
       seq.sequencename
from pg_attribute col
join pg_type typ on col.atttypid = typ.oid
join pg_class tab on col.attrelid = tab.oid
join pg_namespace sch on tab.relnamespace = sch.oid
left join pg_attrdef def on tab.oid = def.adrelid
and col.attnum = def.adnum
left join pg_depend deps on def.oid = deps.objid
and deps.deptype = 'N'
left join seqs seq on deps.refobjid = seq.oid
where sch.nspname != 'information_schema'
       and sch.nspname not like 'pg_%' -- won't work if you have user schemas matching pg_
       and col.attnum > 0
       and typ.typcategory = 'N'
       and col.attnotnull = true
order by sch.nspname,
       tab.relname,
       col.attnum;

[Postgres] Afficher plus d'infos

postgres=> dtvsi+
                                                          List of relations
     Schema     |             Name             |   Type   |        Owner        |          Table          |    Size    | Description

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

restore de blobs

SELECT lo_unlink(oid) FROM pg_largeobject_metadata;
WARNING:  out of shared memory
ERROR:  out of shared memory
HINT:  You might need to increase max_locks_per_transaction.

 

Migration of Synonyms from Oracle to PostgreSQL

Migration of Synonyms from Oracle to PostgreSQL - MigOps

PostgreSQL in IBM Cloud.

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

Deploy new postgreSQL instance dMZR

Deploy new postgreSQL instance dMZR

About JSON in RDBMS

2 links :

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

PostgreSQL JSON Tutorial (postgresqltutorial.com)

Migration from Oracle Legacy to postgreSQL dMZR

//on the ora2Pg management server : 

0- prerequisite 

create role role_own_DB login;

 

1 - export schema

gives the metadata

 

// on the dMZR jump server (inside ibm cloud)

2 - create the roles in hashicorp vault ( script : new_deploy.sh) 

create the roles : dba/own/app

 

//on the ora2Pg management server : 

3 - import schema (script : automation.sh)

import the metadata and upload the data inside the DB postgresql.

4 - backup the postgresql database (pg_dump)

pg_dump --format custom --clean --no-owner --no-privileges apro > bckp_DB.dmp

5 - transfert the backup to dMZR jump server

 

// on the dMZR jump server 

5 - restore only the schema (metadata and data)  on ibmclouddb (pg_restore)

pg_restore --role=role_own_DB -n <schemaTOIMPORT> -d ibmclouddb bckp_DB.dmp

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>';