Odoo Database Upgrade FAQ

5. What are the pros and cons of each database dump methods and how to dump/restore a database with the method of your choosing?

PostgreSQL tar-format compressed with gzip

commands to create
pg_dump --format=t dbname | gzip > dbdump.tar.gz
commands to restore
createdb upgraded_dbname
cat upgraded_dbdump.tar.gz | gzip -d | pg_restore -O -x -d upgraded_dbname
pros and cons
Pros:
  1. the header of a PostgreSQL tar-format dump already contains the PostgreSQL version information so, the Upgrade 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 > dbdump.tar.xz
commands to restore
createdb upgraded_dbname
cat upgraded_dbdump.tar.xz | xz -d | pg_restore -O -x -d upgraded_dbname
pros and cons
Pros:
  1. the header of a PostgreSQL tar-format dump already contains the PostgreSQL version information so, the Upgrade 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 > dbdump.dump
commands to restore
createdb upgraded_dbname
pg_restore -O -x -d upgraded_dbname < upgraded_dbdump.dump
pros and cons
Pros:
  1. the header of a PostgreSQL custom dump already contains the PostgreSQL version information so, the Upgrade 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 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 -x dbname > dbdump.sql
commands to restore
createdb upgraded_dbname
psql -d upgraded_dbname < upgraded_dbdump.sql
pros and cons
Pros: /
Cons:
  1. there is no header in a plain text SQL file
    we will not be able to determine the PostgreSQL version you are using so, the Upgrade 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