SimpleBackupsSimpleBackups

PostgreSQL Backup: Custom vs Plain Export

Last updated on

Originally posted on

When backing up a PostgreSQL database, choosing the right backup format is essential for ensuring flexibility, speed, and compatibility with your workflow. In SimpleBackups, we've made this choice straightforward by offering a "Quick Export" option. But what does this mean, and how does it differ from the default backup format? In this post, we’ll break down the different PostgreSQL backup formats, explore the pros and cons of each, and explain how our app makes backups easier.

Quick Export option

PostgreSQL Backup Formats

PostgreSQL offers multiple formats for database backups. The two primary options are the Custom Format (enabled by the --format=c flag) and the Plain Text Format (default). Let’s dive into each to understand what sets them apart.

1. Custom Format (Quick Export)

When you select the Quick Export in your backup options, the backup is created in Custom Format. This backup format is unique to PostgreSQL and provides several advantages.

Pros:

  • Efficient Storage: Custom Format compresses your backup, resulting in a smaller file size than the default Plain Text Format. This can save storage space and make the backup process faster.
  • Selective Restoration: With Custom Format, you can restore specific tables or schemas instead of the entire database. This is useful when you need to recover only a part of your data without affecting the rest.
  • Faster Restorations: Since the backup is structured in a compressed, binary form, restoring from Custom Format can be faster than Plain Text Format for large databases.

Cons:

  • PostgreSQL-Specific: The Custom Format is specific to PostgreSQL, so the backup can only be restored with pg_restore. You can convert one back to plain SQL with pg_restore -f dump.sql dump.dump if you need to read it.
  • Complexity: Because of the format’s unique structure, third-party tools may not natively support it, limiting cross-platform compatibility.

2. Plain Text Format (Default)

If you don’t select the Quick Export in your backup options, SimpleBackups will create your backup in Plain Text Format by default. Plain Text Format outputs the SQL commands necessary to recreate the database in a .sql file. Pros:

  • Universal Compatibility: Plain Text Format is a simple SQL dump, so any tool that can feed SQL to a server can restore it, and it moves cleanly between PostgreSQL versions. It is PostgreSQL SQL, though, so restoring it into MySQL or another engine still means rewriting the dialect-specific parts.
  • Readable and Modifiable: Since it’s a text file, you can open, read, and even edit it directly, which can be useful for troubleshooting or customization.

Cons:

  • Larger File Size: Unlike Custom Format, Plain Text Format does not compress the data by default, so backups are generally larger, which may lead to slower backup and restoration times.
  • All-or-Nothing Restoration: When restoring from Plain Text, you’re often limited to restoring the entire database, which may be inconvenient if you need only certain tables or schemas.

Choosing the Right Format

The choice between Custom Format and Plain Text Format largely depends on your use case:

  • If you need a fast, space-efficient backup and restoration process, especially for large databases, the Quick Export (Custom Format) option is likely your best bet.
  • If compatibility and readability are priorities, or you’re working in a mixed-database environment, then Plain Text Format might be more suitable.

Whichever you pick, the flag that produces it is -F, and the pg_dump and pg_restore guide covers the full set of options alongside it. The choice also decides your restore path: custom-format archives go back through pg_restore and plain files through psql, which how to restore a PostgreSQL backup walks through in both directions.

Once the format is settled, the next step is usually taking the dump on a schedule rather than by hand. The ultimate PostgreSQL backup script builds that out, and if you would rather not maintain it, SimpleBackups runs automated Postgres backups in either format and stores them where you choose.

FAQ

What is the difference between custom and plain PostgreSQL backup format?

Plain format produces a readable .sql file of SQL statements that you restore with psql. Custom format produces a compressed binary archive that you restore with pg_restore, and it supports restoring individual tables or schemas rather than the whole database.

Which pg_dump format should I use by default?

Custom format, produced with --format=c. It is compressed, it supports selective restore, and it restores in parallel. Plain format is the better choice only when you specifically need a file a human can read and edit.

How do I tell which format an existing dump file is in?

Run "pg_restore -l dump_name". It lists the table of contents of an archive and fails on a plain SQL file. You can also check the first bytes, since a custom-format archive begins with the marker PGDMP while a plain dump begins with readable SQL comments.

Can I convert a custom-format PostgreSQL dump to plain SQL?

Yes. Run pg_restore without a -d flag and it writes SQL to standard output, so "pg_restore -f dump.sql dump.dump" produces the plain equivalent. The reverse is not possible without restoring the plain file into a database and dumping it again.

This article is part of The complete guide to PostgreSQL backup, an honest, practical reference from the team that backs up PostgreSQL every day.


Table of contents