Moving a PostgreSQL database from one server to another

[Tony Dietrich]

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>

-- 
Tony Dietrich
-------------
All art is but imitation of nature.
  -- Lucius Annaeus Seneca