pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedOwner 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