16th June 2022: PostgreSQL 14.4 Released!

pg_dumpbinary v2.5 released

Posted on 2021-09-09 by Gilles Darold
Related Open Source

Libreville, Gabon - Sept 8th, 2021

pg_dumpbinary

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.5 was released today, this new release adds support to data restoration in tables with altered structure.

pg_dumpbinary will now create a file meta-schema.<tablename>.txt per table that stores the tables columns list at export time. At restore time pg_restore binary will read this file if present, or get the target columns list from the pre-data section dumped. The target list will be appended to the COPY FROM command to avoid error messages like "ERROR: row field count is 28, expected 29". This is necessary for tables from upgraded extensions with more columns or if you try to import data only into an existent table with more columns.

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 :