Home / server / Questions / 84927
Accepted
KTamas
Asked: 2009-11-16 07:48:20 +0800 CST

PostgreSQL 7.4 => 8.1 migration: timezone issue

  • 772

Okay, so I'm migrating Project Open to a new server (opensource project management system built on OpenACS which uses AOLServer) and ran into a very strange issue: importing a 7.4 dump automatically adjusts "timestamp with time zone" values to GMT from GMT+1/+2 (CET), which seriously messes up things since I got a lot of 2009-01-10 00:00:00+02 which turns into 2009-01-09 22:00:00+00 essentially jumping a day back, and that messes up some features. How can I turn off this adjustment feature? Thanks in advance.

For the record the server both the old and the new server is set to CET, not GMT.

Edit: no, I can't use 8.3/8.4.

linux

1 Answers

  1. Best Answer

    Try exporting using the newer versions of pg_dump or pg_dumpall

    I've had a number of odd things arise doing PostgreSQL dumps/restores from/to the versions you've specified. I'm assuming you're moving from RedHat or CentOS 4.x to 5.x. I've found issues with the pg_dump and pg_dumpall output from the CentOS/RHEL 4.x versions. In all cases, my problems were resolved by performing the dump of the old database using a higher version on PostgreSQL.

    If your target is the newer server, use that system to run the dump over the network or install a newer version of PostgreSQL (from the 8.1.15 source in /opt, for instance) on the source server in order to use its pg_dump command. See if that resolves the issue.

    • 3