[PostgreSQL] 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.

 

[PostgreSQL]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;

[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 Un tour d'horizon !

PostgreSQL around the world

 

PostgreSQL 16 is out.

https://www.postgresql.org/docs/16/release-16.html

 

Digging into PostgreSQL iceberg

https://avestura.dev/blog/explaining-the-postgres-meme

 

PostgreSQL partitioning

https://www.timescale.com/blog/when-to-consider-postgres-partitioning/

 

Who does PostgreSQL

https://momjian.us/main/blogs/pgblog/2023.html#September_11_2023

 

[Postgres] NEWS

There is some new about PostgreSQL world.

 

Some useful tricks and tips about PostgreSQL, like how to get unused index list (but so many more like displaying fun smiley instead of blanks for NULL…)

https://www.crunchydata.com/postgres-tips

 

Some basics about transactions with examples

https://stormatics.tech/salmans-planet-postgresql/how-to-execute-transactions-in-postgresql

 

Huge progress with replication; implement bi-directional replication with upcoming PostgreSQL 16

https://www.postgresql.fastware.com/blog/bi-directional-replication-using-origin-filtering-in-postgresql

POSTGRES Backup / restore

Petites bases :

#backup

-C : ajouter le create database.

pg_dump -d $DB > $DB.sql
#restore
psql < $DB.sql

 

Grosses bases :

#backup
on exporte la structure :

 pg_dump -s $DB> $DBstruct.sql

 psql $DB < $DBstruct.sql

on exporte les données : 
pg_dump -Fc -a -d $DB > $DB.dmp
pg_restore -Fc -d $DB $DB.dmp

Ajout du GZIP pour le dump : 
pg_dump -U <user> <database> | gzip -c > backup.gz

Ajout du GZIP pour l'import : 
gzip -d backup.gz | psql -d <database> -U <user>

You might need to be logged in as postgres in order to have full privileges on databases.

su - postgres
psql -l                      # will list all databases on Postgres cluster

pg_dump/pg_restore

  pg_dump -U username -f backup.dump database_name -Fc 

switch -F specify format of backup file:

• c will use custom PostgreSQL format which is compressed and results in smallest backup file size
• d for directory where each file is one table
• t for TAR archive (bigger than custom format)
• -h/--host Specifies the host name of the machine on which the server is running
• -W/--password Force pg_dump to prompt for a password before connecting to a database

restore backup:

   pg_restore -d database_name -U username -C backup.dump

[Postgres] VACUUM

Comprendre : autovacuum_freeze_max_age

 

Quand le vacuum ne suffit plus pour réinitialiser le XID d'une table, il faut faire un VACUUM FULL de cette table.