Postgresql - Page 9
-
-
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
-
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 sized
for directory where each file is one tablet
for TAR archive (bigger than custom format)-h
/--host
Specifies the host name of the machine on which the server is running-W
/--password
Forcepg_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.
-
[Postgres] User privs
SELECT grantee AS user, CONCAT(table_schema, '.', table_name) AS table,
CASE
WHEN COUNT(privilege_type) = 7 THEN 'ALL'
ELSE ARRAY_TO_STRING(ARRAY_AGG(privilege_type), ', ')
END AS grants
FROM information_schema.role_table_grants
GROUP BY table_name, table_schema, grantee; -
Démarrer le service postgres
-
[PROC]Ora2PG
-
[POSTGRESQL] Partitionnement
Super tuto pour le partitionnement de bons conseils et cas concrets :
https://www.enterprisedb.com/postgres-tutorials/strategy-migrating-partitioned-oracle-database-tables-postgresql
-
Restauration PostgreSQL
Voici la méthode de restauration pour une base de données Postgres :
On ne s'intéresse qu'aux données. Les privilèges, les utilisateurs, les rôles, les tablespaces ne sont pas impactés. Rapide et efficace. Pourra être exécutée sur demande lors d'un refresh ponctuel de base de données.
export :
pg_dump toto --create | gzip > toto.sql.gz
import :
dropdb toto
pv toto.sql.gz | gunzip | psqlAprès l'import le fichier export est supprimé.