Thread: Moving an existing database from an old version?

I'm sorry if this has been answered before, but I've searched through
the archives and haven't found anything.

I have a machine that was built about two years ago and runs Debian. I
just built a new machine running Fedora and PostgreSQL 8.x. The old
machine has an older version of PostgreSQL running on it that seems to
keep data in different locations than the new one. I have found the
.conf files and they don't seem to tell me where the data lives.

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

On 1/12/07, Howard Eglowstein <howard@yankeescientific.com> wrote:--
Chad
http://www.postgresqlforums.com/

Howard Eglowstein <howard@yankeescientific.com> writes:
> 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?

pg_dump (or better pg_dumpall) is the way to go.  You can't just copy
the files unless it's exactly the same major version of Postgres, which
it sounds like it isn't.  Skim through the manual's discussion of
backup and restore if you're not sure how to use pg_dump.

            regards, tom lane

> 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

Thank you all for the suggestions!! I wouldn't have guessed that you
_can't_ just move the data, but it makes sense that you can't. I'm still
having trouble figuring out where the data is on the existing machine.
I've looked in /usr/local and /usr/share for places it might be and
there doesn't seem to be any. Yet, pg_dump clearly knows how to find it.

Last question for now (before I fire up the machine and try again): How
does postgresql know where the data lives?

Thanks again to everyone!

Howard


Aarni Ruuhimäki wrote:
>> 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
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
>

On Saturday 13 January 2007 14:11, Howard Eglowstein wrote:
> I've looked in /usr/local and /usr/share for places it might be and
> there doesn't seem to be any. Yet, pg_dump clearly knows how to find it.
>
> Last question for now (before I fire up the machine and try again): How
> does postgresql know where the data lives?

Not sure about Debian but on an Ubuntu machine locate (pgsql, .bki,
pg_hba.conf, postmaster) gives

/var/lib/postgresql/8.0/main/base/
/usr/lib/postgresql/8.0/lib/
/etc/postgresql/8.0/main/pg_hba.conf
/usr/share/postgresql/8.0/postgres.bki
/usr/lib/postgresql/8.0/bin/postmaster

You tell Postgres where the data dir is with initdb command. -D is the path to
the datadir.

--

Have fun,

Aarni

When you start the database with pg_ctl (or if you run postmaster/postgres directly) then there is a -D switch which tells all of them where the data directory lives.  Take a look at your startup scripts and it should lead you to the location.  If you are using Linux, the standard location is /var/lib/pgsql/data.  Otherwise you should use "find" or "locate" to hunt down either postgresql.conf or PG_VERSION, both should live in the data directory.

--
Chad
http://www.postgresqlforums.com/