Skip site navigation (1) Skip section navigation (2)

Re: Moving an existing database from an old version?

From: Aarni Ruuhimäki <aarni(at)kymi(dot)com>
To: howard(at)yankeescientific(dot)com
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Moving an existing database from an old version?
Date: 2007-01-13 10:38:28
Message-ID: 200701131238.28737.aarni@kymi.com (view raw or flat)
Thread:
Lists: pgsql-novice
> What I want to do is simply transplant the existing application onto the
> new database. I'd be happy to simply copy the files over and hack the
> .conf files appropriately, or if it's better, can I use PG_DUMP to
> somehoe backup the old data and restore it to the new installation?
>
> I appreciate any pointers you may have or suggestions as to where else I
> might look.
>
> Or if you want to help out in exchange for lunch money, that could be
> arranged too. :)
>
> Thanks!
>
> Howard
>

Hi Howard,

Here's a rough guide line how to move or upgrade your db.

Just did this once again. (From FC2 / 8.0.1 to CentOs 4.4 / 8.1.5)

----
Take a dump from your db:

$pg_dumpall -c > dump_file_for_new

If you're upgrading on the same machine:

Shutdown postmaster:

Datadir out of the way:

#mv /usr/share/pgdata /usr/share/pgdata_old

New datadir:

#mkdir /usr/share/pgdata
#chown postgres /usr/share/pgdata

Previous install out of the way:

#mv /usr/share/pgsql /usr/share/pgsql_old

New istall:

Extract your new Pg:

#gunzip postgresql-8.x.tar.gz
#tar -xf postgresql-8.x.tar

Configure, make and install, with --prefix=/path/ you can specify install 
directory, ie. the -L swicth in initdb, default is /usr/local/pgsql/:

#cd postgresql-8.x
#./configure --with...

#gmake or make
#gmake install or make install

Initdb:

#chown -R postgres /usr/local/pgsql/
#su postgres
$/usr/local/pgsql/bin/initdb -D /usr/share/pgdata -L /usr/local/pgsql/share -E 
ENCODING

Edit & save .conf files

Start the server:

$/usr/local/pgsql/bin/postmaster -D /usr/share/pgdata -B no. of buffers -N no. 
of connections &

You might want to add -i for outside connections (edit pg_hba.conf 
accordingly)

Read your dump file in:

$psql -E template1 < dump_file_for_new
---

Cheers,

Aarni




In response to

Responses

pgsql-novice by date

Next:From: Howard EglowsteinDate: 2007-01-13 12:11:07
Subject: Re: Moving an existing database from an old version?
Previous:From: Tom LaneDate: 2007-01-13 03:16:46
Subject: Re: Moving an existing database from an old version?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group