Thread: Downgrading v8.4 database to v8.3

Hi,

I use Debian GNU/Linux on various servers. Debian Stable a.k.a. Lenny
comes with PostgreSQL v8.3 while Debian Testing a.k.a Squeeze provides
PostgreSQL v8.4.

I need to move some databases written in Psql v8.4 to the stable
production server running v8.3. I tried pg_dump and pg_restore as well
as PgAdmin3.  I am unable to do so due to the backward
incompatibility. Is there anyway to do this?

Thanks in advance.

--
Jason Tan Boon Teck

Jason Tan Boon Teck wrote:
> I need to move some databases written in Psql v8.4 to the stable
> production server running v8.3. I tried pg_dump and pg_restore as well
> as PgAdmin3.  I am unable to do so due to the backward
> incompatibility. Is there anyway to do this?
>

You can try connecting a 8.3 client to the 8.4 server, then running
pg_dump from that 8.3 client.  That's a  common approach for
dump/restore when moving forward a version, and it may resolve your
issue when moving backward one too.  The 8.3 client shouldn't dump
anything the 8.3 server doesn't know how to restore.

--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us

Jason Tan Boon Teck <tanboonteck@gmail.com> wrote:

> I need to move some databases written in Psql v8.4 to the stable
> production server running v8.3.

> I am unable to do so due to the backward incompatibility.

You used features in 8.4 which aren't present in 8.3, or do you mean
something else?

> Is there anyway to do this?

I would try to use the newer pg_dump and investigate any warnings or
errors on reading into the older.

-Kevin

Why don't you use postgres 8.4 from lenny-backports?

Lenny comes with postgres 8.3, but you can add postgresql 8.4 from backports without problem. We have our production servers with lenny and postgresql 8.4. No issues.

Detailed instructions here:
http://www.backports.org/dokuwiki/doku.php?id=instructions

Squeeze is almost ready. So, I think it's better to go with postgres 8.4 now and upgrade lenny to squeeze in a few months without taking care about postgres in the future.

-----Original Message-----
From: Jason Tan Boon Teck <tanboonteck@gmail.com>
To: pgsql-admin@postgresql.org
Subject: [ADMIN] Downgrading v8.4 database to v8.3
Date: Wed, 5 May 2010 23:48:02 +0800

Hi,

I use Debian GNU/Linux on various servers. Debian Stable a.k.a. Lenny
comes with PostgreSQL v8.3 while Debian Testing a.k.a Squeeze provides
PostgreSQL v8.4.

I need to move some databases written in Psql v8.4 to the stable
production server running v8.3. I tried pg_dump and pg_restore as well
as PgAdmin3.  I am unable to do so due to the backward
incompatibility. Is there anyway to do this?

Thanks in advance.

-- 
Jason Tan Boon Teck

Greg Smith <greg@2ndquadrant.com> writes:
> Jason Tan Boon Teck wrote:
>> I need to move some databases written in Psql v8.4 to the stable
>> production server running v8.3. I tried pg_dump and pg_restore as well
>> as PgAdmin3.  I am unable to do so due to the backward
>> incompatibility. Is there anyway to do this?

> You can try connecting a 8.3 client to the 8.4 server, then running
> pg_dump from that 8.3 client.  That's a  common approach for
> dump/restore when moving forward a version, and it may resolve your
> issue when moving backward one too.  The 8.3 client shouldn't dump
> anything the 8.3 server doesn't know how to restore.

That's very likely to fail, and worse to do so silently, because 8.3
pg_dump doesn't know what's different about 8.4 system catalogs.

I think your only real recourse in this situation is to do a plain dump
from the 8.4 server (with 8.4 pg_dump) and then manually edit the dump
script to remove any 8.4-only syntax.  If your application isn't actually
using any 8.4-only features this should be a pretty trivial matter.
If it is, then of course you have some work to do.

            regards, tom lane

I have done that for one server today. But it would be a real pain to
do that for other servers that I do not have convenient access to.

And I sometimes need to install from DVD only without access to the
internet to pull from backports.


Jason

On Thu, May 6, 2010 at 12:09 AM, Iñigo Martinez Lasala
<imartinez@vectorsf.com> wrote:
> Why don't you use postgres 8.4 from lenny-backports?
>
> Lenny comes with postgres 8.3, but you can add postgresql 8.4 from backports
> without problem. We have our production servers with lenny and postgresql
> 8.4. No issues.
>
> Detailed instructions here:
> http://www.backports.org/dokuwiki/doku.php?id=instructions
>
> Squeeze is almost ready. So, I think it's better to go with postgres 8.4 now
> and upgrade lenny to squeeze in a few months without taking care about
> postgres in the future.
>
> -----Original Message-----
> From: Jason Tan Boon Teck <tanboonteck@gmail.com>
> To: pgsql-admin@postgresql.org
> Subject: [ADMIN] Downgrading v8.4 database to v8.3
> Date: Wed, 5 May 2010 23:48:02 +0800
>
> Hi,
>
> I use Debian GNU/Linux on various servers. Debian Stable a.k.a. Lenny
> comes with PostgreSQL v8.3 while Debian Testing a.k.a Squeeze provides
> PostgreSQL v8.4.
>
> I need to move some databases written in Psql v8.4 to the stable
> production server running v8.3. I tried pg_dump and pg_restore as well
> as PgAdmin3.  I am unable to do so due to the backward
> incompatibility. Is there anyway to do this?
>
> Thanks in advance.
>
> --
> Jason Tan Boon Teck
>
>
>



--
Jason Tan Boon Teck

Iñigo Martinez Lasala <imartinez@vectorsf.com> writes:

> Why don't you use postgres 8.4 from lenny-backports?

Yes, use lenny-backports.

The reason postgresql-8.4 is not available in debian stable is that they
went to feature freeze before 8.4 was released.

Then debian has a very conservative approach to a stable distribution,
the same way PostgreSQL back branches are only updated with fixes, never
with new features.

Here new feature would be either a new package or a new version of a
package if the upgrade ain't for security purposes.

There's no reason to avoid lenny-backports. All the more when
considering PostgreSQL-8.4, which is stable software.

Regards,
--
dim

I don't think I have used any new v8.4 feature, since I finally had my
first encounter with v8.4 this week.

What should i be looking for in the dump script?


Jason

On Thu, May 6, 2010 at 12:13 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Greg Smith <greg@2ndquadrant.com> writes:
>> Jason Tan Boon Teck wrote:
>>> I need to move some databases written in Psql v8.4 to the stable
>>> production server running v8.3. I tried pg_dump and pg_restore as well
>>> as PgAdmin3.  I am unable to do so due to the backward
>>> incompatibility. Is there anyway to do this?
>
>> You can try connecting a 8.3 client to the 8.4 server, then running
>> pg_dump from that 8.3 client.  That's a  common approach for
>> dump/restore when moving forward a version, and it may resolve your
>> issue when moving backward one too.  The 8.3 client shouldn't dump
>> anything the 8.3 server doesn't know how to restore.
>
> That's very likely to fail, and worse to do so silently, because 8.3
> pg_dump doesn't know what's different about 8.4 system catalogs.
>
> I think your only real recourse in this situation is to do a plain dump
> from the 8.4 server (with 8.4 pg_dump) and then manually edit the dump
> script to remove any 8.4-only syntax.  If your application isn't actually
> using any 8.4-only features this should be a pretty trivial matter.
> If it is, then of course you have some work to do.
>
>                        regards, tom lane
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>



--
Jason Tan Boon Teck

Tom Lane wrote:
> That's very likely to fail, and worse to do so silently, because 8.3
> pg_dump doesn't know what's different about 8.4 system catalogs.
> I think your only real recourse in this situation is to do a plain dump
> from the 8.4 server (with 8.4 pg_dump) and then manually edit the dump
> script to remove any 8.4-only syntax.

I tried not to sound optimistic about it working.  Thought it might be
worth a shot though, on the possibility that a simple schema might not
trip over any of the 8.4 catalog changes.  Another idea here, along the
path of manually editing dumps, is to dump from both 8.3 and 8.4 clients
and look at the difference between the two, to help get an idea what
changed syntax is responsible for the problems.

--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us

Jason Tan Boon Teck <tanboonteck@gmail.com> writes:
> I don't think I have used any new v8.4 feature, since I finally had my
> first encounter with v8.4 this week.

> What should i be looking for in the dump script?

[ shrug... ]  You never explained what failed about your previous
attempt, so it's hard to give any detailed guidance.  The rule of
thumb is to fix whatever 8.3 complains about when you try to load
the dump.

            regards, tom lane

Dbname = perak
psql file was pg_dumped from v8.4

Trying to restore in v8.3:
postgres@gem:/mnt/d/0/psql$ pg_restore --disable-triggers -d perak
perak-100403_2330.psql
pg_restore: [archiver] unsupported version (1.11) in file header

Second attempt with "-i":
postgres@gem:/mnt/d/0/psql$ pg_restore --disable-triggers -i -d perak
perak-100403_2330.psql
pg_restore: [archiver] unsupported version (1.11) in file header

The database was inserted with records using PHP application developed
on machine using v8.3. It was used for 2 days in machine with v8.4.  I
wish to use this database for further development on the development
machine.


Jason

On Thu, May 6, 2010 at 12:38 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Jason Tan Boon Teck <tanboonteck@gmail.com> writes:
>> I don't think I have used any new v8.4 feature, since I finally had my
>> first encounter with v8.4 this week.
>
>> What should i be looking for in the dump script?
>
> [ shrug... ]  You never explained what failed about your previous
> attempt, so it's hard to give any detailed guidance.  The rule of
> thumb is to fix whatever 8.3 complains about when you try to load
> the dump.
>
>                        regards, tom lane
>



--
Jason Tan Boon Teck

Jason Tan Boon Teck <tanboonteck@gmail.com> writes:
> Trying to restore in v8.3:
> postgres@gem:/mnt/d/0/psql$ pg_restore --disable-triggers -d perak
> perak-100403_2330.psql
> pg_restore: [archiver] unsupported version (1.11) in file header

Use a plain text dump, not a -Fc dump.  You want text anyway so that you
can edit it if you have to.

            regards, tom lane