Postgres Pro
Downloads
Home   >   mailing lists

Thread: moving a database.

moving a database.

From
LWATCDR
Date:
I have an old postgres 7.1 data base that I must migrate to a new
server running 8.2.6.
The problem is everything I have tried has failed. The database has
blobs in it but other than that it is pretty simple.
So my question is what command line should I use to try and migrate
this old database to the new server?
I set up the old one years ago and it frankly has just worked but I
have to move it to a new machine and I am way out of practice.
So what command line should I use on the new server to try and copy
the database from the old server?

Re: moving a database.

From
Tom Lane
Date:
LWATCDR <lwatcdr@gmail.com> writes:
> I have an old postgres 7.1 data base that I must migrate to a new
> server running 8.2.6.
> The problem is everything I have tried has failed. The database has
> blobs in it but other than that it is pretty simple.
> So my question is what command line should I use to try and migrate
> this old database to the new server?

You want to pg_dump (or pg_dumpall, but pg_dump'ing each database
would give you a bit more control) and load the resulting script
into the new server.

If possible, use the 8.2 pg_dump to do this, as it's a lot more
featureful than what came with 7.1 --- in particular it will
handle blobs better.

I have a vague recollection that there are some corner cases that
changed in the COPY data format since 7.1.  If you are having
trouble with getting the data reloaded, try using the -d option
to pg_dump.

            regards, tom lane

Re: moving a database.

From
Frank Bax
Date:
LWATCDR wrote:
> I have an old postgres 7.1 data base that I must migrate to a new
> server running 8.2.6.
> The problem is everything I have tried has failed. The database has
> blobs in it but other than that it is pretty simple.
> So my question is what command line should I use to try and migrate
> this old database to the new server?
> I set up the old one years ago and it frankly has just worked but I
> have to move it to a new machine and I am way out of practice.
> So what command line should I use on the new server to try and copy
> the database from the old server?


Just use your backup/restore procedure (you do have one, right? and you
test it regularly, right?); backup on 7.1; restore on 8.2.

If it doesn't work; post your commands and an error message.

Re: moving a database.

From
LWATCDR
Date:
will pg_dumpall handle BLOBs?

Re: moving a database.

From
Chuck
Date:
At 11:56 AM -0500 1/10/08, Frank Bax wrote:
>LWATCDR wrote:
>>I have an old postgres 7.1 data base that I must migrate to a new
>>server running 8.2.6.
>>The problem is everything I have tried has failed. The database has
>>blobs in it but other than that it is pretty simple.
>>So my question is what command line should I use to try and migrate
>>this old database to the new server?
>>I set up the old one years ago and it frankly has just worked but I
>>have to move it to a new machine and I am way out of practice.
>>So what command line should I use on the new server to try and copy
>>the database from the old server?
>
>
>Just use your backup/restore procedure (you do have one, right? and
>you test it regularly, right?); backup on 7.1; restore on 8.2.
>
>If it doesn't work; post your commands and an error message.

Could you post a backup/restore procedure or point to some
documentation that suggests one? I need to set this up for an 8.2.5
cluster and I'm new to Postgres. It sounds like a few people could
possibly benefit as well.

Thanks,
Chuck

Re: moving a database.

From
Frank Bax
Date:
Chuck wrote:
> Could you post a backup/restore procedure or point to some documentation
> that suggests one?


http://www.postgresql.org/docs/8.2/static/backup.html