pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Postgres version change - pg_dump
9+ messages / 7 participants
[nested] [flat]

* Postgres version change - pg_dump
@ 2004-12-20 14:40 sarlav kumar <sarlavk@yahoo.com>
  2004-12-20 14:54 ` Re: [despammed] Postgres version change - pg_dump Andreas Kretschmer <akretschmer@despammed.com>
  2004-12-20 15:47 ` Re: Postgres version change - pg_dump Bruno Wolff III <bruno@wolff.to>
  2004-12-20 17:07 ` Re: [PERFORM] Postgres version change - pg_dump Stefan Weiss <spaceman@foo.at>
  2004-12-21 07:09 ` Re: [PERFORM] Postgres version change - pg_dump Iain <iain@mst.co.jp>
  0 siblings, 4 replies; 9+ messages in thread

From: sarlav kumar @ 2004-12-20 14:40 UTC (permalink / raw)
  To: pgsqlnovice <pgsql-novice@postgresql.org>; pgsql-performance

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.

^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: [despammed] Postgres version change - pg_dump
  2004-12-20 14:40 Postgres version change - pg_dump sarlav kumar <sarlavk@yahoo.com>
@ 2004-12-20 14:54 ` Andreas Kretschmer <akretschmer@despammed.com>
  3 siblings, 0 replies; 9+ messages in thread

From: Andreas Kretschmer @ 2004-12-20 14:54 UTC (permalink / raw)
  To: pgsqlnovice <pgsql-novice@postgresql.org>

am  20.12.2004, um  6:40:34 -0800 mailte sarlav kumar folgendes:
> 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,

No. This is the usual way to upgrade the database.


> could someone tell me what precautions I can take to avoid any
> problems?

You can hold the old database ;-)


Regards,
-- 
Andreas Kretschmer    (Kontakt: siehe Header)
               Tel. NL Heynitz:  035242/47212
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===    Schollglas Unternehmensgruppe    === 



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Postgres version change - pg_dump
  2004-12-20 14:40 Postgres version change - pg_dump sarlav kumar <sarlavk@yahoo.com>
@ 2004-12-20 15:47 ` Bruno Wolff III <bruno@wolff.to>
  2004-12-20 17:34   ` Re: Postgres version change - pg_dump sarlav kumar <sarlavk@yahoo.com>
  3 siblings, 1 reply; 9+ messages in thread

From: Bruno Wolff III @ 2004-12-20 15:47 UTC (permalink / raw)
  To: sarlav kumar <sarlavk@yahoo.com>; +Cc: pgsqlnovice <pgsql-novice@postgresql.org>; pgsql-performance

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 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.



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Postgres version change - pg_dump
  2004-12-20 14:40 Postgres version change - pg_dump sarlav kumar <sarlavk@yahoo.com>
  2004-12-20 15:47 ` Re: Postgres version change - pg_dump Bruno Wolff III <bruno@wolff.to>
@ 2004-12-20 17:34   ` sarlav kumar <sarlavk@yahoo.com>
  2004-12-20 18:27     ` Re: Postgres version change - pg_dump Kretschmer Andreas <andreas_kretschmer@despammed.com>
  2004-12-21 05:58     ` Re: [PERFORM] Postgres version change - pg_dump Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  0 siblings, 2 replies; 9+ messages in thread

From: sarlav kumar @ 2004-12-20 17:34 UTC (permalink / raw)
  To: Bruno Wolff III <bruno@wolff.to>; +Cc: pgsqlnovice <pgsql-novice@postgresql.org>; pgsql-performance

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.

^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Postgres version change - pg_dump
  2004-12-20 14:40 Postgres version change - pg_dump sarlav kumar <sarlavk@yahoo.com>
  2004-12-20 15:47 ` Re: Postgres version change - pg_dump Bruno Wolff III <bruno@wolff.to>
  2004-12-20 17:34   ` Re: Postgres version change - pg_dump sarlav kumar <sarlavk@yahoo.com>
@ 2004-12-20 18:27     ` Kretschmer Andreas <andreas_kretschmer@despammed.com>
  1 sibling, 0 replies; 9+ messages in thread

From: Kretschmer Andreas @ 2004-12-20 18:27 UTC (permalink / raw)
  To: pgsqlnovice <pgsql-novice@postgresql.org>

am  Mon, dem 20.12.2004, um  9:34:06 -0800 mailte sarlav kumar folgendes:
> 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?

Yes. Call from the new server pg_dump with the credentials for the old
server, in other words, use the new version of pg_dump to generate a
dump from the old server.

>  
> Thanks,
> Saranya
> 
> Bruno Wolff III <bruno@wolff.to> wrote:

Please, read http://www.netmeister.org/news/learn2quote.html


Regards, Andreas
-- 
Diese Message wurde erstellt mit freundlicher Unterstützung eines freilau-
fenden Pinguins aus artgerechter Freilandhaltung.   Er ist garantiert frei
von Micro$oft'schen Viren. (#97922 http://counter.li.org)     GPG 7F4584DA
Was, Sie wissen nicht, wo Kaufbach ist? Hier: N 51.05082°, E 13.56889° ;-)



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: [PERFORM] Postgres version change - pg_dump
  2004-12-20 14:40 Postgres version change - pg_dump sarlav kumar <sarlavk@yahoo.com>
  2004-12-20 15:47 ` Re: Postgres version change - pg_dump Bruno Wolff III <bruno@wolff.to>
  2004-12-20 17:34   ` Re: Postgres version change - pg_dump sarlav kumar <sarlavk@yahoo.com>
@ 2004-12-21 05:58     ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  1 sibling, 0 replies; 9+ messages in thread

From: Christopher Kings-Lynne @ 2004-12-21 05:58 UTC (permalink / raw)
  To: sarlav kumar <sarlavk@yahoo.com>; +Cc: Bruno Wolff III <bruno@wolff.to>; pgsqlnovice <pgsql-novice@postgresql.org>; pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: [PERFORM] Postgres version change - pg_dump
  2004-12-20 14:40 Postgres version change - pg_dump sarlav kumar <sarlavk@yahoo.com>
@ 2004-12-20 17:07 ` Stefan Weiss <spaceman@foo.at>
  2004-12-20 17:30   ` Re: Postgres version change - pg_dump sarlav kumar <sarlavk@yahoo.com>
  3 siblings, 1 reply; 9+ messages in thread

From: Stefan Weiss @ 2004-12-20 17:07 UTC (permalink / raw)
  To: sarlav kumar <sarlavk@yahoo.com>; +Cc: pgsqlnovice <pgsql-novice@postgresql.org>; pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Postgres version change - pg_dump
  2004-12-20 14:40 Postgres version change - pg_dump sarlav kumar <sarlavk@yahoo.com>
  2004-12-20 17:07 ` Re: [PERFORM] Postgres version change - pg_dump Stefan Weiss <spaceman@foo.at>
@ 2004-12-20 17:30   ` sarlav kumar <sarlavk@yahoo.com>
  0 siblings, 0 replies; 9+ messages in thread

From: sarlav kumar @ 2004-12-20 17:30 UTC (permalink / raw)
  To: pgsql-performance

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.

^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: [PERFORM] Postgres version change - pg_dump
  2004-12-20 14:40 Postgres version change - pg_dump sarlav kumar <sarlavk@yahoo.com>
@ 2004-12-21 07:09 ` Iain <iain@mst.co.jp>
  3 siblings, 0 replies; 9+ messages in thread

From: Iain @ 2004-12-21 07:09 UTC (permalink / raw)
  To: sarlav kumar <sarlavk@yahoo.com>; pgsqlnovice <pgsql-novice@postgresql.org>; pgsql-performance

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 ----- 
  From: sarlav kumar
  To: pgsqlnovice ; pgsqlperform
  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. 

^ permalink  raw  reply  [nested|flat] 9+ messages in thread


end of thread, other threads:[~2004-12-21 07:09 UTC | newest]

Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-12-20 14:40 Postgres version change - pg_dump sarlav kumar <sarlavk@yahoo.com>
2004-12-20 14:54 ` Andreas Kretschmer <akretschmer@despammed.com>
2004-12-20 15:47 ` Bruno Wolff III <bruno@wolff.to>
2004-12-20 17:34   ` sarlav kumar <sarlavk@yahoo.com>
2004-12-20 18:27     ` Kretschmer Andreas <andreas_kretschmer@despammed.com>
2004-12-21 05:58     ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-12-20 17:07 ` Stefan Weiss <spaceman@foo.at>
2004-12-20 17:30   ` sarlav kumar <sarlavk@yahoo.com>
2004-12-21 07:09 ` Iain <iain@mst.co.jp>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox