Thread: Compatible Dumps

Is there a way to make a postgresql 8 database dump work with 7.4? I.E I
want to do a dump from 8.0 on one machine and put it into a database
running on 7.4 on another?

Thanks

Jake

Jake Stride wrote:
> Is there a way to make a postgresql 8 database dump work with 7.4? I.E I
> want to do a dump from 8.0 on one machine and put it into a database
> running on 7.4 on another?

Haven't tried this yet, but apart from turning off dollar-quoting for
functions, it should just work. If you want to test it, try a
schema-only dump+restore first.

--
   Richard Huxton
   Archonet Ltd

Richard Huxton wrote:

> Jake Stride wrote:
>
>> Is there a way to make a postgresql 8 database dump work with 7.4?
>> I.E I want to do a dump from 8.0 on one machine and put it into a
>> database running on 7.4 on another?
>
>
> Haven't tried this yet, but apart from turning off dollar-quoting for
> functions, it should just work. If you want to test it, try a
> schema-only dump+restore first.
>
That helps a bit, but I still have the following issue:

ERROR:  function pg_catalog.pg_get_serial_sequence("unknown", "unknown")
does not exist

Any ideas how to get around this?

Thanks

Jake

Jake Stride wrote:
> That helps a bit, but I still have the following issue:
>
> ERROR:  function pg_catalog.pg_get_serial_sequence("unknown", "unknown")
> does not exist
>
> Any ideas how to get around this?

Hmm - before 8.0 there wasn't an easy way to figure out the name of a
sequence attached to a column. This function is the easy way to do that,
but is (of course) not present in 7.x

Assuming your sequences all have default names, and are in the public
schema, you could write a simple function:

CREATE FUNCTION my_pg_get_serial_sequence(text,text) RETURNS text AS '
   SELECT ''public.'' || $1 || ''_'' || $2 || ''_seq'';
' LANGUAGE SQL;

Then a quick bit of sed/perl search & replace-ing and you're away.

This *will* break if the target table (1st param) is in a different
schema than public, or if you have non-default sequence names.
--
   Richard Huxton
   Archonet Ltd

Richard Huxton <dev@archonet.com> writes:

> This *will* break if the target table (1st param) is in a different schema than
> public, or if you have non-default sequence names.

Including if you've renamed a serial column since creating it, or if your
serial column has an extremely long name.

In practice it works well enough though.

--
greg