pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Owner Issue When Migrating Database to New Server
2+ messages / 2 participants
[nested] [flat]

* Owner Issue When Migrating Database to New Server
@ 2007-08-15 11:29  Campbell, Lance <lance@uiuc.edu>
  0 siblings, 1 reply; 2+ messages in thread

From: Campbell, Lance @ 2007-08-15 11:29 UTC (permalink / raw)
  To: pgsql-admin

PostgreSQL: 8.2.4

OS: Linux Red Hat 4.x 64 bit

 

I have a database running on serverA with postgres user userA.  I want
to migrate the database to ServerB with a postgres user userB.  Notice
the users are not the same.  I did the following to put the database on
the new server:

 

1)       I created the database on serverB with userB.

2)       I then did pg_dump -h serverA -U userA dbname | psql -h serverB
-U userB dbname

 

This of course failed.  The user userA was not in the database found on
serverB.  I then did the following:

 

1)       I created the database on serverB with userB.

2)       On serverB I did pg_dump -h serverA -U userA dbname >
postgreSQL.out

3)       I then did a find and replace on userA with userB through NANO
on the file postgreSQL.out

4)       I then did psql -h serverB -U userB dbname -f postgreSQL.out

 

Is there a better way to do this? 

 

Thanks,

 

 

Lance Campbell

Project Manager/Software Architect

Web Services at Public Affairs

University of Illinois

217.333.0382

http://webservices.uiuc.edu

 

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

* Re: Owner Issue When Migrating Database to New Server
@ 2007-08-15 11:40  Milen A. Radev <milen@radev.net>
  parent: Campbell, Lance <lance@uiuc.edu>
  0 siblings, 0 replies; 2+ messages in thread

From: Milen A. Radev @ 2007-08-15 11:40 UTC (permalink / raw)
  To: pgsql-admin

Campbell, Lance написа:
> PostgreSQL: 8.2.4
> 
> OS: Linux Red Hat 4.x 64 bit
> 
>  
> 
> I have a database running on serverA with postgres user userA.  I want
> to migrate the database to ServerB with a postgres user userB.  Notice
> the users are not the same.  I did the following to put the database on
> the new server:
> 
>  
> 
> 1)       I created the database on serverB with userB.
> 
> 2)       I then did pg_dump -h serverA -U userA dbname | psql -h serverB
> -U userB dbname
> 
>  
> 
> This of course failed.  The user userA was not in the database found on
> serverB.  I then did the following:
> 
>  
> 
> 1)       I created the database on serverB with userB.
> 
> 2)       On serverB I did pg_dump -h serverA -U userA dbname >
> postgreSQL.out
> 
> 3)       I then did a find and replace on userA with userB through NANO
> on the file postgreSQL.out
> 
> 4)       I then did psql -h serverB -U userB dbname -f postgreSQL.out
> 
>  
> 
> Is there a better way to do this? 
> 


Yes - use the "--no-owner" option for "pg_dump" when creating the dump file.

-- 
Milen A. Radev




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


end of thread, other threads:[~2007-08-15 11:40 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-08-15 11:29 Owner Issue When Migrating Database to New Server Campbell, Lance <lance@uiuc.edu>
2007-08-15 11:40 ` Milen A. Radev <milen@radev.net>

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