Moving a PostgreSQL database from one server to another

[Tim Brier]

You have to have the database running to use pg_dump.

To dump the databse: pg_dump name_of_database > file_to_save_to

copy the file_to_save_to to your server.

To restore the database: if the database doesn't exist then create the 
database.  createdb -T template1 -E encoding_type name_of_database

Then use psql name_of_database < file_to_save_to

You're done.

There are options in postgresql to create the database, but this 
sometimes causes conflicts, depending on your schema.

Tim.

Tony Dietrich wrote:
> On Thursday 13 Jan 2005 22:15, Rodolfo J. Paiz wrote:
> 
>>On Thu, 2005-01-13 at 13:23 -0500, Scot L. Harris wrote:
>>
>>>I think what you are looking for is the
>>>
>>>pg dump
>>>
>>>or
>>>
>>>pg dumpall
>>>
>>>commands.
>>
>>Fantastic. After some more thrashing around, it seems those are commands
>>to be typed in at the BASH shell, not inside psql... that had me fooled
>>for a little while. But I eventually managed to find "pg_dump",
>>"pg_dumpall" and "pg_restore". Great, says I.
>>
>>Nope. I've been through the pg_dump man page backwards and forwards and
>>have tried lots of iterations of the command. While the PostgreSQL
>>service is started, and I can access the database properly with psql,
>>pg_dump runs for less than one second and then exits. No error is given,
>>no output results, nothing.
>>
>>This is FC3, everything is updated, and I've tried setting every useful
>>parameter I can think of, even setting host, user, and -W to force a
>>password prompt. Nada, zilch, zippo. No logs anywhere I can find,
>>either. /var/log/pgsql is entirely empty.
> 
> 
> You missed a step the original reply stated.
> TURN OFF the PSQL server before dumping.
> A running server hooks into the db and blocks the dump.
> 
> If you can't shutdown the server to do the dump on a production machine, you 
> are limited to SQL to backup the data, assuming the remote server is 
> available through the network.  Look up how to use PSQL as a SRDBM.
> 
>>Since surely I'm not the first one to want a database backed up or
>>transferred to another server, anyone who got this working have any
>>suggestions?
>>
>>Thanks,
>>
>>--
>>Rodolfo J. Paiz <rpaiz at simpaticus.com>
> 
>