Why Pg_dump Is Amazing

Why Pg_dump Is Amazing
rhaas.blogspot.com

In this situation, pg_dump is really annoying because it will give you a schema-only dump, but that dump is not "stable". In the sense that

    - dump the schema
    - create a new database from the schema
    - dump the schema again

The other use case I rely on is just having a single place where the schema is defined is actually super helpful for debugging, and can be an entrypoint for other tools. Sometimes it's nicer to be able to open that up in your editor rather than have to explore through `psql` or read through many smaller migration files.

Because pg_dump does not generate a stable dump (same schema == same file contents), you're constantly getting noise or irrelevant changes to the file, which makes it essentially unusable or at least very annoying if you keep that file committed.

> Now, I expect that somebody is going to point out that pg_dump does not solve the dependency problem 100% reliably in all cases. And that's correct. However, I think that complaint just shows how spoiled PostgreSQL users are. Recreating a complex database schema without a dependency-aware tool like pg_dump is an absolute nightmare.

If you're interested in a tool that is dependency-aware, AND allows you to explicitly specify dependencies for the cases that pg_dump doesn't handle (primarily between functions and other objects, iirc), check out my cli tool pgmigrate [0]. You don't need to use it to manage your migrations, you can just use the `dump` subcommand. It's a little under-documented, and it's missing some obvious improvements (can't handle multiple postgres schemas (namespaces) right now, only lets you dump one at a time), but I've been relying on it and using it with pleasure.

[0]: https://github.com/peterldowns/pgmigrate

https://github.com/greenplum-db/gpbackup-archive

The disk binary format of pg_dump and COPY are opaque different formats, the COPY docs have this to say about the binary format:

> To determine the appropriate binary format for the actual tuple data you should consult the PostgreSQL source, in particular the send and recv functions for each column’s data type (typically these functions are found in the src/backend/utils/adt/ directory of the source distribution)

I didn’t find an easy to use 3rd party library for decoding or reading these native data types, although maybe one exists (then I would chalk my pain up to Google getting worse…)

It feels like such a loss to spend CPU cores parsing ASCII CSV, or even worse interpreting & executing millions of SQL insert statements. To approach any kind of reasonable throughput near the maximum of disk or network IO the only answer is to have a big batch job export the whole DB into a reasonable binary format like parquet/arrow/whatever, and then use a replication job to keep it up to date using deltas. You know, if you have the time and money to set up a whole data warehouse thing.

Then your transform job can read the 3rd party binary data format relatively quickly and do its transform, but when it comes to moving the data back into Postgres, the fastest I’ve found is to turn it back into CSV, stream the CSV into Postgres using COPY, and spend valuable Postgres CPU cycles having it re-parse the CSV again.

Has anyone found a faster pathway to write bulk data into Postgres, perhaps that doesn’t involve at least a one trip through ASCII?