Re: Importing the dump file in postgresql-7.4.23

On Fri, Jul 15, 2011 at 1:11 AM, saravanan <saravanan.kcse@gmail.com> wrote:
> Hi,
>
> I tried to troubleshoot the errors while importing dumps in PostgreSQL -
> 8.0.0 version. But still i'm getting some permission level errors. Before
> importing the dumps, i used to install some functions like
> ltree,tsearch2,pgcrypto from directory
> /usr/local/src/postgresql-8.0/contrib. So that i can avoid some errors.

Some tips on how to backup / restore across versions.

1: pg assumes you're going from an old to a new version.  i.e. 7.4 to
8.3 etc.  Going the other way is not officially supported and you're
on your own.
2: Run both versions of the db server at once.  Use the pg_dump and
pg_dumpall from the higher version, i.e. the destination db.  So if
you're going from 7.4 to 8.4, use 8.4's pg_dump to dump data.
3: use pg_dumpall --globals to get a list of users to import to the
new database.
4: create tablespaces ahead of time if needed.

so:

pg_dumpall --globals -h pg74host -U postgres | psql -h pg84host -U postgres
createdb -h pg84host dbname
pg_dump -h pg74host dbname | psql -h pg84host dbname

Should get mostly get you there.