What format for pg_dump do you recommend?

Question

PostgreSQL

Hello everyone,

you need to re-upload the entire database (with everything that is) from one PostgreSQL (12.4) instance to AWS Aurora PostgreSQL compatible ... which format for pg_dump do you recommend custom or plain text and why?

AWS DB Migration is not suitable!

Have a great weekend,
Vadim

Answer 1

The more "dissimilar" the servers are, the simpler the dump format should be.
But in any case, you need to start simply with a compatibility check. Make a test database, a couple of tables with FK, one function, one procedure, one trigger... then back up both in script and archive formats and check how the recovery goes on your specific host.

Answer 2

Not important. Whichever is more convenient, use this one.
Customs and directory formats allow you to restore objects selectively, support parallel restore, and are compressed by default. Machine architecture and OS are irrelevant.
Problems can only be when restoring on a server with an older (major) version of PG, then it is better to use plain (even in this case there may be difficulties). When uploading to the new version, there should be no problems.

Answer 3

Binary (custom) format supports parallel restoring and dump takes less time due to compression. If the base is small, then these advantages, of course, are not decisive.