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.

About these ads

Nerd.

Tagged with: , , ,
Posted in PostgresDAC, PostgreSQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Archives
Map of wandering
Follow

Get every new post delivered to your Inbox.

Join 78 other followers

%d bloggers like this: