Syntax error when trying to import database from two PostgreSQL databases

Question

I'm trying to export a database from one server and put it on another using phpPgAdmin. My process is like this:

1. select the database in server 1 and hit the export button
2. select "data and structure and select the option in the dropdown "copy"
3. download
4. create the a database of the same name in server 2 with the same name as the database from server 1
5. select that database, open up the SQL pane, and paste the SQL code I downloaded from server 1
6. Execute

That's when I get the error:

ERROR: syntax error at or near "OWNED"
LINE 73: ALTER SEQUENCE address_customer_id_seq OWNED BY address.cust...

Line 73 in its entirety is:

 ALTER SEQUENCE address_customer_id_seq OWNED BY address.customer_id;

I've read here that it may be because I've have slightly different versions of postgre on my two servers. So I checked with the server tech, who said that, yes, there was that issue. So he upgraded server 2. I'm getting the same error however.

Any idea what could be going on?

Thanks much.

• Craig Ringer: Is the target database an older version? What are the two versions you mention are "slightly different"? What pg_dump and pg_restore versions are being used?
• 1252748: @CraigRinger Thanks for the help. I've had to shoot these questions to the server admin, but I'll post the answers ASAP.
• Craig Ringer: SELECT version() on both servers, pg_dump --version on the machine used to take the dump. See stackoverflow.com/tags/postgresql/info . At a guess you or your server admin are confused about "slightly different" version and are assuming that (say) 9.2 and 9.1 are 100% SQL-level compatible.
• 1252748: @CraigRinger Server 1: PostgreSQL 8.4.13 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.4.6 20120305 (Red Hat 4.4.6-4), 64-bit --- Server 2: PostgreSQL 8.1.23 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-52). UGH! You're right!!
• Craig Ringer: rofl. "slightly different versions" indeed. postgresql.org/support/versioning . The 8.1 branch was released in 2005, 8.4 in 2009. Congratulations on actually running the latest 8.1, though, almost nobody who sticks to ancient versions keeps up with the patch releases.

Answer 1

PostgreSQL 8.4 will not produce a dump that restores to 8.1, as it’ll use features and syntax that do not exist in 8.1.

You might be able to downgrade by running 8.1’s pg_dump against the 8.4 database, but it’s most likely that the dump will simply fail.

Downgrading that far will be a challenge and may involve hand-editing the dump produced by 8.4’s pg_dump to make it 8.1-compatible.

8.1 is ancient and unsupported; its final end-of-life release was in November 2010. You shouldn’t even consider using it for any new project or tool, and really need to be planning an upgrade.

See the PostgreSQL version policy for just how different these versions are. You can learn more by reading the release notes for versions 8.2.0, 8.3.0 and 8.4.0. It is important to understand that application visible behavior changes are present in each release; you must test your applications and may need to enable some backward compatibility settings.

You should also read the upgrading a PostgreSQL cluster documentation—but be aware that pg_upgrade can not be used to upgrade a version older than 8.4.

This would all be much less painful if your install weren’t seven years out of date.

• 1252748: Excellent information. Thank you. Is it an easier process to upgrade to 8.4.13 on the other? Is there any risk of damage to data upgrading from something so outdated to the very newest version?
• 1252748: the server guy says: "Since the versions are patched, the number is not accurate. The number is different because the one server is CentOS 5 and the other one is CentOS 6".
• 1252748: With this difference is it possible that he is correct and these two machines are actually running different versions than what SELECT version() reports?
• Craig Ringer: @thomas Well, CentOS 5 is obsolete and end-of-life too. I don't know what you mean by "the versions are patched". Home-brew backported fixes, maybe? These are questions you need your server admin to answer, not me. All I can tell you is that even your "new" version is old and you really need to plan an update to your environment.
• Craig Ringer: @thomas Answer updated with a bit more info on upgrades. Really, it's not something there are nice neat canned answers for, especially in such an old and possibly custom patched environment. Maybe you should look here: postgresql.org/support/professional_support ? (Per my profile, I work for one of the listed companies).