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.

Development using Lazarus and PostgresDAC. Installation


There are a lot of tools for PostgreSQL development. I want to propose you one more. Why Lazarus:

  • Lazarus is shipped with open-source Pascal compiler;
  • it has a high degree of Delphi compatibility;
  • availability on a variety of platforms, including Windows, Mac OS X, and Linux;
  • complete source code is available;

Installing Lazarus

Go to Lazarus at SourceForge.

Then at the Downloads Section get proper installer. I used lazarus-1.0.8-fpc-2.6.2-win32.exe and lazarus-1.0.8-fpc-2.6.2-win64.exe in my tests. These are the latest releases at the moment.

Here are the screen shots of the installation process where you can see what I had chosen:
Lazarus Installation

Path to install

Full installation

I have Lazarus installed into C:\lazarus\ and hereafter I will use this path.

Getting PostgresDAC

Now it’s time for PostgresDAC. At the moment many Laarus IDE functions require the source and warns if the source of a unit is missing. Thus we need PostgresDAC with sources version. Sources are available for licensed users in their profile.

MicroOLAP Profile

I have extracted PostgresDAC sources to C:\PostgresDAC\.

Install PostgresDAC into the IDE

In the Lazarus IDE choose open package and select dclPostgresDACL.lpk. This is the design time package. Then in the Package window choose Use → Install.
Install package

Confirmation about rebuild will appear.


Choose “Yes”. Wait for some time. After successful rebuild Lazarus will reopen itself. Tada! We got it!

PostgresDAC in the Lazarus

Now we may open some demo projects and play some time.
GridDemo for PostgresDAC in the Lazarus

Do we need LIMIT clause in UPDATE and DELETE statements for PostgreSQL?

Was working with MySQL recently. Noticed that UPDATE command of it has special LIMIT clause which is really useful as for me, e.g.

UPDATE `Product_download` SET `version`='9.2.1' WHERE `product_download_id`=367 LIMIT 1;

Number into words convertion in PostgreSQL

I was playing with some unit tests for PostgresDAC recently. And one test case was to check TPSQLDataset.Locate routine which allows partial-string and case-sensitive matching options. So I want to have some test result set with integer and text columns. The simplest for me are numbers in both representations: using digits and using words, e.g.

  1. one
  2. two
  3. three
  4. four
  5. etc.

Since I hadn’t much spare time I used nice VALUES command:

VALUES (1, 'one'), (2, 'two'), (3, 'three');

Considering I needed special names for columns I used more complex query:

SELECT col1, col2::varchar(10) FROM 
(VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS t(col1, col2);

I did my task well. However I was wondering is there any built-in function in PostgreSQL which may convert integer to such words representation. The answer is: not exactly.

I found some references to cash_words function. But there is no any information (except entry in 7.3 release notes) in the official documents. The use case is quite simple:

SELECT cash_words(gen.i::money) FROM generate_series(1, 10) AS gen(i);
cash_words text

"One dollar and zero cents" "Two dollars and zero cents" "Three dollars and zero cents" "Four dollars and zero cents" "Five dollars and zero cents" "Six dollars and zero cents" "Seven dollars and zero cents" "Eight dollars and zero cents" "Nine dollars and zero cents" "Ten dollars and zero cents"

I’m fine with this result set, who needs only numbers may trim values using standard functions.

PS In case someone wants to create his own implementation using C (or plpgsql, who knows), cash_words function may be found in the \src\backend\utils\adt\cash.c file. It uses num_word private routine.

PGConf.EU 2012: My schedule

PGConf.EUWell, I suppose it’s time to choose the most interesting talks to visit at PGConf.EU 2012. I sketched out a rough list.

Wednesday, October 24

11:10 - 12:00
Writing a foreign data wrapper
Bernd Helmle

12:10 - 13:00
MultiMaster Replication: Applications, Comparison, Implementation
Andres Freund, Simon Riggs

14:00 - 14:50
Understanding EXPLAIN's output
Guillaume Lelarge

15:20 - 16:10
Range Types in PostgreSQL 9.2 - Your Life Will Never Be The Same
Jonathan S. Katz

16:20 - 17:10
Gianni Ciolli

Thursday, October 25

09:30 - 10:20
Graphs and topology with PostgreSQL and PostGIS
Vincent Picavet

10:50 - 11:40
Universal Data Access with SQL/MED
David Fetter

11:50 - 12:40
Elephants and Windmills
Josh Berkus

13:40 - 14:30
Inside PostgreSQL Shared Memory
Bruce Momjian

14:40 - 15:30
Embracing the Web with JSON and PLV8
Will Leinweber

Friday, October 26

09:30 - 10:20
PostBIS - A Bioinformatics Booster for PostgreSQL
Michael Schneider

10:50 - 11:40
PostgreSQL makes dev happy, a pgAgent + pl/pgsql use case
Julien Rouhaud

11:50 - 12:40
Debugging complex SQL queries with writable CTEs
Gianni Ciolli

13:40 - 14:30
Using PostgreSQL for storing time-series data
Sebastian Harl

Meet me at the PGConf.EU soon!

How to move data from one schema to another in Postgres?

I saw a question today :

I have PostpreSQL 9.1. In my database there are 2 schemas: public and test. How can I quickly move all objects and data from public to test?

Opps. I’m stuck with this issue for some time. I was thinking about dump and restore tricks, about direct changes to the pg_catalog schema etc. I even appeared on the IRC channel with this question.

There I had conversation with Jon SuckMojo Erdman, who’s first thought was dump way either. But then we have found a simple and elegant solution independently.

Assuming schema test is empty:





Backward compatibility? Never heard of it!

Playing with fresh PostgreSQL 9.2beta2 I cannot find spclocation column of a system catalog pg_tablespace. First my thought was that I’m too tired. But then I found mentioning about this at pgsql-hackers by Magnus Hagander:

And IIRC, we don’t actually *use* spclocation anywhere. How about we
just get rid of them as independents?

Oh, really?!!! WTF? How about other developers, ha? I’m using this. I really do. Don’t know how about other guys, but I will need additional nasty checks for server version to know what should I use: pg_get_tablespace_location(oid) or tablespace.spclocation

Thanks a lot guys! Way to go!