pg_dumpbinary v2.7

Posted on 2022-03-15 by Gilles Darold
Related Open Source

Zurich, Switzerland - March 14th, 2022


pg_dumpbinary is a program used to dump a PostgreSQL database with data dumped in binary format. The resulting dump must be restored using pg_restorebinary that is provided with this tool.

pg_dumpbinary 2.7 was released today, this new release adds two new options to pg_restorebinary

  • -E, --exclude-ext EXTNAME name of an extension to not restore, it can be used multiple time.
  • --schema-exists add an IF NOT EXISTS clause to CREATE SCHEMA

they can be useful to fix extensions dependencies for example when the creation order have changed. They can be created manually and be excluded from the restoration, the extension's configuration tables data are still restored, this just remove the extension creation statement. If a schema exists before the restoration, the --schema-exists will prevent the command to fail at schema creation by appending a IF NOT EXISTS clause.

pg_dumpbinary 2.6 was released last week and adds a new command line option --load-via-partition-root to pg_dumpbinary to dump/restore data through the partitioned table only and not per partition like the default behavior. It makes the COPY statements target the root of the partitioning hierarchy rather than the partition, this may be useful when reloading data on a server where rows do not always fall into the same partitions as they did on the original server. This is the equivalent to the pg_dump option.

pg_dumpbinary is useful in some situations:

  • you have bytea that can not be exported by pg_dump because the total size of the escape/hex output exceed 1Gb.
  • you have custom type that stores \0 internally in bytea but data are returned as char/varchar/text which truncate data after the '\0'. In this case pg_dump will export data in the output type which will result in data lost.
  • any other case where binary format can be useful.

If you are in this case pg_dumpbinary will help you by dumping the PostgreSQL database in binary format. In all other cases you must use the pg_dump/pg_restore commands distributed with PostgreSQL.

See documentation for a complete description of the features.

Links & Credits

pg_dumpbinary is an open project from LzLabs GmbH. Any contribution to build a better tool is welcome. You just have to send your ideas, features requests or patches using the GitHub tools.

Links :