Dump/Restauration de BD Postgresql

Voici un extrait de documentation récupérée sur upgrade.odoo.com qui synthétise les différentes commandes de dump et restauration d'une BD Postgresql :

PostgreSQL tar-format compressed with gzip
Commands to create pg_dump --format=t dbname | gzip > dbname.tar.gz
Commands to restore createdb migrated_dbname
gunzip migrated.dbname.tar.gz | pg_restore -O -x -d migrated_dbname
Pros and cons Pros:
  1. The header of a PostgreSQL tar-format dump already contains the PostgreSQL version information so, the migration platform will not have to guess the version and restoring your dump will be faster
  2. Compressing using gzip method is faster than most other methods
Cons:
  1. gzip has a compression ratio that is less efficient than xz
PostgreSQL tar-format compressed with xz
Commands to create pg_dump --format=t dbname | xz > dbname.tar.xz
Commands to restore createdb migrated_dbname
xzcat migrated.dbname.tar.xz | pg_restore -O -x -d migrated_dbname
Pros and cons Pros:
  1. The header of a PostgreSQL tar-format dump already contains the PostgreSQL version information so, the migration platform will not have to guess the version and restoring your dump will be faster
  2. xz has a very good compression ratio
Cons:
  1. Compressing using xz method is slower than most other methods
PostgreSQL custom dump
Commands to create pg_dump --format=c dbname > dbname.dump
Commands to restore createdb migrated_dbname
pg_restore -O -x -d migrated_dbname < migrated.dbname.dump
Pros and cons Pros:
  1. The header of a PostgreSQL custom dump already contains the PostgreSQL version information so, the migration platform will not have to guess the version and restoring your dump will be faster
  2. It's compressed by default but compression is somewhat similar to gzip compression (which is not optimal)
  3. Compressing speed is relativelly fast compared to most of the other methods
  4. This is the default dump format used by Odoo server. You can use the Odoo web interface to restore a PostgreSQL custom dump
Cons:
  1. The compression ratio is similar to gzip compression and so, is not the best one if you prefer a small dump file
Plain text SQL
Commands to create pg_dump -O dbname > dbname.sql
Commands to restore createdb migrated_dbname
psql -d migrated_dbname < migrated.dbname.sql
Pros and cons Pros: None 
Cons:
  1. There is no header in a plain text SQL file. Wwe will not be able to determine the PostgreSQL version you are using so, the migration platform will have to guess your PostgreSQL version and restoring your dump will be slower
  2. Not compressed. Your database dump file could potentially be huge

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée.