Re: Export and import from one postgres server to another

On 05/29/2012 02:27 AM, Alexander Reichstadt wrote:

Hi,

I am trying to "simply" export my postgres database from one server and then import it into another. I thought I could use PhpPgAdmin, but the hints on the web don't seem to work. One said to simply get a dump from one phpPgAdmin, the go to the other server, select the database, click the SQL button and then select the dump file. When I do this the import fails because the dump file begins with a \connect statement.

I don't really know why pg_dump includes that \connect , but it isn't the only problem you'll hit with that approach. PgAdmin-III seems to wrap the invoked SQL in a transaction, and the dump will try to (re)-create the `postgres' role, which will fail and abort the transaction.

The usual way to dump and restore is to use `pg_dump -Fc -f mydb.backup mydb' when backing up, then either PgAdmin-III's "Restore" dialog or `pg_restore --dbname mydb mydb.backup' when restoring. You'll have to create the database prior to restoring to it.

I wrote a bit of a grumble about the usability issues around dump/reload especially between *nix and Windows recently:
   http://blog.ringerc.id.au/2012/05/postgresql-usability-pgadmin-iii-and-pg.html
and would be interested in your comments/experiences because more knowledge will help produce a better UI if I ever get the time to have a go at addressing some of this, or if someone else does.

--
Craig Ringer