Dump and restore of PostgreSQL: version compatibility FAQ

Compress PostgreSQLMany 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.

Advertisements

PostgresDAC v2.11.0 with comprehensive dump\restore functionality is out!

This release is fully dedicated to the dump and restore functionality. Libraries are rewritten from scratch for the latest 9.3 and 9.2 servers. Support for previous versions is discontinued. New modern thread friendly Directory output format introduced. New parallel dump mode implemented. A bunch of features added as well as all known bugs are fixed.

Full change log:
[!] v9.3.1 client libraries added (x86 and x64 platforms)
[!] v9.3.1 dump & restore libraries (pg_dump.dll, pg_restore.dll) added
[!] TPQSQLDump supports Directory output format from now
[!] TPQSQLDump supports parallel processing
[+] v9.2.5 dump & restore libraries (pg_dump-9.2.5.dll, pg_restore-9.2.5.dll) added
[+] doNoSynchronizedSnapshots option added to TPSQLDump.Options
[+] roNoSecurityLabels option added to TPSQLRestore.Options
[+] TPSQLDump.ExcludeTablesData property added
[+] TPSQLDump.Jobs property added
[+] TPSQLDump.Sections property added
[+] TPSQLRestore.SchemaName property added
[+] TPSQLRestore.Sections property added
[+] TPSQLRestore.TableNames property added
[*] Make TPSQLDump put SEQUENCE SET items in the data not pre-data section of the archive
[*] Support for old dump and restore libraries is discontinued (v8.x, v9.1)
[*] tar files emitted by TPSQLDump are POSIX conformant now
[*] TPSQLDump excludes data of unlogged tables when running on a hot-standby server
[*] TPSQLDump outputs functions in a more predictable order
[*] TPSQLRestore now accepts POSIX-conformant tar files
[-] Fixed bugs in the restore.sql script emitted byTPSQLDump in tar output format
[-] Fixed TPSQLDump for views with circular dependencies and no relation options
[-] Fixed TPSQLDump’s handling of DROP DATABASE commands if doClean option specified

You’re welcome to download the PostgresDAC v2.11.0 right now at:
http://microolap.com/products/connectivity/postgresdac/download/
or login to your private area on our site at
http://microolap.com/my/downloads/