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.

PaGoDump 8.4.1 is available

New PaGoDump is available! At last. We faced with a lot of mystical barriers during this release. It supposed to be public a week ago. Whatever…

New release will provide a brand new functionality — custom library selection!

Now two libraries will be available: 8.3.8 and 8.4.1, but this is only the beginning — 8.2.x branch will be included in the very next release.

P.S. Kishkin, where you got to? 🙂

Building PostgreSQL 8.0.x using MinGW under Win… Magician wanted

Compiling the fresh PostgreSQL releases is not a problem under Windows using MinGW as you probably know.

One of my clients asked me to build pg_dump\pg_restore if it’s possible of course. Yes, I know that 8.0.x and 8.1.x branches are not supported anymore. At least officially. I was just curious.

Well, I spent a day making compiler happy. I built the client library libpq.dll, but dump and restore utilities are beyond my power.

The last words were:

pg_backup_archiver.o:pg_backup_archiver.c:(.text+0x43a): undefined reference to `__’
collect2: ld returned 1 exit status
make: *** [pg_dump] Error 1

Possibly my changes caused this. Since I’m not so familiar with GCC universe I’m still confused about it. Gurus’ advices appreciated.

And the last but not least. Is there any chance that MinGW compatibility patches for 8.0.x and 8.1.x branches will be committed officially?


PS Kids don’t do drugs!

PaGoDump’s version leap

From reliable sources it became known that next release of PaGoDump – a GUI Windows utility for backing up a PostgreSQL database built with MicroOLAP PostgresDAC – will have 8.4.0 version string.

This is only to avoid confusion. Henceforth PaGoDump will have the same version as the PostgreSQL server it was built for.

If some releases will come out during the same server release, then only the build number will be increased.

Next official release of the PaGoDump is scheduled at the end of this week.