Many newbies are confused about how PostgreSQL dump and restore work. And even more questions appeared about version mismatches of the server itself and tools used. After fresh PostgresDAC release we’ve faced with the same questions.
Utilities used for dump and restore are called pg_dump and pg_restore respectively. Delphi developers may use TPSQLDump and TPSQLRestore components which are fully compatible with native tools.
So here I’m trying to create some kind of a FAQ.
Let’s assume version is a concatenation of Major, Minor and Build numbers separated by dots, e.g. 9.3.2
Q: Can I downgrade the database version, e.g. from 9.3 to 8.4?
A: If you mean official supported way of doing this, then no, you can’t. You may however use pg_dump in plain text output mode and manually fix all incompatibilities.
Q: Can I create dump of old server with new pg_dump?
A: Yes, sure. All versions since 7.0 are supported now. But if you use custom format, be ready the same or higher version of pg_restore will be needed.
Q: Can I create dump of new server with old pg_dump?
A: pg_dump must have the same major and minor version or higher, e.g. pg_dump v9.2.3 will work on v9.2.5, 9.2.x, 9.1.x, 8.x etc. and will not work for versions like 9.3.x, 9.4.x etc.
Q: What if I want to restore custom dump archive to the old server anyway?
A: You should use pg_restore to generate plain SQL script, manually edit it to fix version mismatches.
Q: Will pg_restore “understand” archives of newer versions of pg_dump?
A: No. The rule is simple: pg_restore will work with pg_dump output of the same major and minor version or lower, e.g. pg_restore v9.3.x will understand pg_dump output v9.3.x, 9.2.x, 9.1.x, 8.x etc. and will not work with output from versions like 9.4.x.
Q: Can I use new output format of the pg_dump with old server version, e.g. directory output for 8.4 server?
A: Yes, you can.
Q: Can I use new parallel functionality with old servers?
A: Yes, you can, but there is a limitation for pre-9.2 servers. See description for –no-synchronized-snapshots parameter.
PS All these statements are correct for the PostgresDAC’s TPSQLDump and TPSQLRestore components.