1

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.

1252748
  • 14,597
  • 32
  • 109
  • 229
  • 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? – Craig Ringer May 10 '13 at 01:13
  • @CraigRinger Thanks for the help. I've had to shoot these questions to the server admin, but I'll post the answers ASAP. – 1252748 May 10 '13 at 01:14
  • `SELECT version()` on both servers, `pg_dump --version` on the machine used to take the dump. See http://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. – Craig Ringer May 10 '13 at 01:19
  • @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!! – 1252748 May 10 '13 at 01:27
  • *rofl*. "slightly different versions" indeed. http://www.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. – Craig Ringer May 10 '13 at 01:30
  • @how can I upgrade? The server admin is not exactly filling me with confidence(!) Is that possible to do in the WHM interface I have? Thanks so much! – 1252748 May 10 '13 at 01:35
  • What the heck is WHM? Do you mean cPanel? You're making a lot of assumptions about knowledge of your environment. Upgrades are non-trivial and require application compatibility testing. To learn more about upgrading PostgreSQL, read the release notes and the user manual. Start here: http://www.postgresql.org/docs/current/static/upgrading.html then Google and the release notes for each x.y.0 version between the old and new versions. – Craig Ringer May 10 '13 at 01:54

1 Answers1

4

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.

Martin
  • 2,573
  • 28
  • 22
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • 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 May 10 '13 at 01:37
  • 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 May 10 '13 at 01:45
  • With this difference is it possible that he is correct and these two machines are actually running different versions than what `SELECT version()` reports? – 1252748 May 10 '13 at 01:47
  • @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 May 10 '13 at 01:50
  • @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: http://www.postgresql.org/support/professional_support/ ? (Per my profile, I work for one of the listed companies). – Craig Ringer May 10 '13 at 01:59