Postgres Pro
Downloads
Home   >   mailing lists

Thread: Postgres version change - pg_dump

Postgres version change - pg_dump

From
sarlav kumar
Date:
Hi All,
 
Thanks to everyone for helping with my previous questions.
 
I have a test database running on Postgres 7.3.2.
 
 version                          
-------------------------------------------------------------
 PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC 2.96
I have another server where a newer version of postgres that came with the Fedora Core 3 package installed.
 
version                                                        
-------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 7.4.6 on i386-redhat-linux-gnu, compiled by GCC i386-redhat-linux-gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6)
 
I would like to do a pg_dump on the test database, and restore it in the new database on Postgres 7.4.6. I would like to know if there would be any problem due to the postgres version/OS change. If so, could someone tell me what precautions I can take to avoid any problems?
 
Thanks in advance,
Saranya
 
 
 

Do you Yahoo!?
Yahoo! Mail - Find what you need with new enhanced search. Learn more.

Re: Postgres version change - pg_dump

From
Bruno Wolff III
Date:
On Mon, Dec 20, 2004 at 06:40:34 -0800,
  sarlav kumar <sarlavk@yahoo.com> wrote:
>
> I would like to do a pg_dump on the test database, and restore it in the new database on Postgres 7.4.6. I would like
toknow if there would be any problem due to the postgres version/OS change. If so, could someone tell me what
precautionsI can take to avoid any problems? 

You should use the 7.4.6 version of pg_dump to dump the old database. Note
you still need to be running the 7.3.2 server for the old database.
pg_dump will be just acting like a client connecting over the network
and will work with older versions of the server.

Re: Postgres version change - pg_dump

From
Stefan Weiss
Date:
sarlav kumar wrote, On 2004-12-20 15:40:
> I would like to do a pg_dump on the test database, and restore it in
> the new database on Postgres 7.4.6. I would like to know if there
> would be any problem due to the postgres version/OS change. If so,
> could someone tell me what precautions I can take to avoid any
> problems?

Apart from using the pg_dump from 7.4.6 (see Bruno's answer), you should
take care to use the same locale in the new database cluster. I have had
problems in the past with unique constraints that could not be restored
due to different locale settings. See here:

  http://www.spinics.net/lists/pgsql/msg05363.html

In my case it was not enough to create the database with a different
encoding, I had to re-initdb the whole cluster :-/


cheers,
stefan

Re: Postgres version change - pg_dump

From
sarlav kumar
Date:
Hi,
 
I think I miscommunicated something. I am doing a pg_dump from Postgres 7.3.2. I am restoring it on Postgres 7.4.6 on Fedora Core 3 on a different server.
 
I tried doing the dump and restoring it on the new DB. I did not have any problem with the UNIQUE contraint so far. But I got an error message saying table "session" does not exist, though it does exist in the old database(from where I did the dump).
 
I also got another error saying "user abc does not exist". On the old DB I have different set of users, with different privileges granted to each of them on the tables. I guess I need to create these set of users in the new DB before doing the dump. Am I right?

I am new to postgres administration. So I am not sure what you mean by "same locale in the new database cluster". Could you please explain or point me to a source where I can learn from?
 
Thanks a lot for the help,
Saranya
 

 

Do you Yahoo!?
Jazz up your holiday email with celebrity designs. Learn more.

Re: Postgres version change - pg_dump

From
sarlav kumar
Date:
Hi,
 
From what I understand, I need to execute the pg_dump command from the new server( so that it will use the 7.4.6 version), but connect to the old DB. Am I right?
 
Thanks,
Saranya

Bruno Wolff III <bruno@wolff.to> wrote:
On Mon, Dec 20, 2004 at 06:40:34 -0800,
sarlav kumar wrote:
>
> I would like to do a pg_dump on the test database, and restore it in the new database on Postgres 7.4.6. I would like to know if there would be any problem due to the postgres version/OS change. If so, could someone tell me what precautions I can take to avoid any problems?

You should use the 7.4.6 version of pg_dump to dump the old database. Note
you still need to be running the 7.3.2 server for the old database.
pg_dump will be just acting like a client connecting over the network
and will work with older versions of the server.

Do you Yahoo!?
Jazz up your holiday email with celebrity designs. Learn more.

Re: Postgres version change - pg_dump

From
Christopher Kings-Lynne
Date:
Hi Sarlav,

>  From what I understand, I need to execute the pg_dump command from the
> new server( so that it will use the 7.4.6 version), but connect to the
> old DB. Am I right?

Basically.

The truth is Sarlav, that any pg_dump version before the new 8.0 version
is likely to have errors restoring.  You should restore the dump like this:

psql -f dump.sql database

And then when you get errors, you will see the line number of the error.
  Then you can edit the dump to fix it.

Chris

Re: Postgres version change - pg_dump

From
"Iain"
Date:
As others have already said, use the newer version of pg_dump and it should go ok.
 
I had lots of problems restoring 7.1 dumps into 7.4 database, but it goes smoothly if I use the 7.4 version of pg_dump.
 
Assuming you have 2 servers, the old one and a new one, call pg_dump from your new server as follows:
 
pg_dump --username=postgres --host=192.168.x,x  <other options>
 
and use the IP address of the old server for the --host parameter.
 
You may need to edit the pg_hba.conf file on the old server to allow the connection from the new server.
 
This is pretty convenient as you don't even have to copy the dump file from the old server.
 
I was thinking you could set up a backup server in this way. On a busy system, it may take a load of the main server so that running backups with users online shouldn't be a problem. That's in theory anyway.
 
regards
Iain
 
----- Original Message -----
Sent: Monday, December 20, 2004 11:40 PM
Subject: [PERFORM] Postgres version change - pg_dump

Hi All,
 
Thanks to everyone for helping with my previous questions.
 
I have a test database running on Postgres 7.3.2.
 
 version                          
-------------------------------------------------------------
 PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC 2.96
I have another server where a newer version of postgres that came with the Fedora Core 3 package installed.
 
version                                                        
-------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 7.4.6 on i386-redhat-linux-gnu, compiled by GCC i386-redhat-linux-gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6)
 
I would like to do a pg_dump on the test database, and restore it in the new database on Postgres 7.4.6. I would like to know if there would be any problem due to the postgres version/OS change. If so, could someone tell me what precautions I can take to avoid any problems?
 
Thanks in advance,
Saranya
 
 
 

Do you Yahoo!?
Yahoo! Mail - Find what you need with new enhanced search. Learn more.