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

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


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match