Thread: moving postgres data
Hi all I have data in a postgres database on one server and have to move it to a new server that has been set up with a new version of postgres. The database is an exact copy. what is the best way of moving the thing across? do i set up the tables on the new box and write scripts to move it across or is there another way of doing this? I don't have root access on the new server but can probably talk the sys admin into a little work if i have to. ta
On Wed, Oct 31, 2001 at 11:17:01AM +0200, Duncan Adams (DNS) wrote: > Hi all > > I have data in a postgres database on one server and have to move it to a > new server that has been set up with a new version of postgres. The > database is an exact copy. what is the best way of moving the thing across? > do i set up the tables on the new box and write scripts to move it across or > is there another way of doing this? > See pg_dump and restore in Reference Manual - PostgreSQL Client Applications. http://www.postgresql.org/idocs/index.php?app-pgdump.html http://www.postgresql.org/idocs/index.php?app-pgrestore.html If you want to move all databases use pg_dumpall http://www.postgresql.org/idocs/index.php?app-pg-dumpall.html - Einar Karttunen
At 09:17 31/10/01, you wrote: >I have data in a postgres database on one server and have to move it to a >new server that has been set up with a new version of postgres. The >database is an exact copy. what is the best way of moving the thing across? >do i set up the tables on the new box and write scripts to move it across or >is there another way of doing this? Easiest way would be to dump the database to a text file: % pg_dump yourdatabase > /path/to/dumpfile copy it to the new server, and read it to an empty database using psql -e: % psql -e yourdatabase < /newpath/to/dumpfile This way all the schema are recreated, and the data loaded into them, automatically -- you don't have to set anything up by hand. See the documentation on pg_dump for more information. best, Mo Mo Holkar Digital Mind Games -- log on to take over mo.holkar@digitalmindgames.com http://www.digitalmindgames.com
ta, looks like that will do it. -----Original Message----- From: Einar Karttunen [mailto:ekarttun@cs.Helsinki.FI] Sent: Wednesday, October 31, 2001 11:52 AM To: Duncan Adams (DNS) Cc: pgsql-novice@postgresql.org Subject: Re: moving postgres data On Wed, Oct 31, 2001 at 11:17:01AM +0200, Duncan Adams (DNS) wrote: > Hi all > > I have data in a postgres database on one server and have to move it to a > new server that has been set up with a new version of postgres. The > database is an exact copy. what is the best way of moving the thing across? > do i set up the tables on the new box and write scripts to move it across or > is there another way of doing this? > See pg_dump and restore in Reference Manual - PostgreSQL Client Applications. http://www.postgresql.org/idocs/index.php?app-pgdump.html http://www.postgresql.org/idocs/index.php?app-pgrestore.html If you want to move all databases use pg_dumpall http://www.postgresql.org/idocs/index.php?app-pg-dumpall.html - Einar Karttunen