Moving a PostgreSQL database from one server to another

[James Mckenzie]

Does pg_dump still work???  That is probably the best method to move PostgreSQL databases around.

Export:

pg_dump NameOfDatabase > DumpofDatabase.NameOfDatabase.pgsql

Import

pgsql NameOfDatabase < DumpofDatabase.NameOfDatabase.pgsql

and if you need to clear out an old database before importing a newer version:

dropdb NameOfDatabase.

James McKenzie

-----Original Message-----
From: Joe Conway <mail at joeconway.com>
Sent: Jan 13, 2005 1:19 PM
To: For users of Fedora Core releases <fedora-list at redhat.com>
Subject: Re: Moving a PostgreSQL database from one server to another

Edward Croft wrote:
> On Thu, 2005-01-13 at 11:28 -0600, Rodolfo J. Paiz wrote:
>><SNIP>
>>I guess I need a backup/restore or an export/import... but for some

> I do this all the time. Postgres stores its data files
> in /var/lib/pgsql/data by default. Stop the server on your laptop. This
> will close the files. You can then copy that whole directory over. As
> long as you install into the same directory, you should be able to just
> restart postgres. You may need to make changes to the pg_hba.conf and
> pg_ident.conf. 
> 

A couple of safety tips worth mentioning on the above procedure:
1. Make sure the directory/files on the new server are still owned by
    the database user -- usually postgres.
2. This will only work if the hardware architecture (i.e. i686 versus
    x86_64) and database version (e.g. 7.4.x) is the same on the two
    machines.

HTH,

Joe

-- 
fedora-list mailing list
fedora-list at redhat.com
To unsubscribe: http://www.redhat.com/mailman/listinfo/fedora-list