Moving a PostgreSQL database from one server to another

[Scot L. Harris]

On Thu, 2005-01-13 at 17: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.
> 
> 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,


>From the man page:

Since pg_dumpall reads tables from all databases you will  most  likely
have  to connect as a database superuser in order to produce a complete 
dump. Also you will need superuser  privileges  to  execute  the  saved
script  in  order  to be allowed to add users and groups, and to create 
databases.

You probably need to do this using the postgres user not some other user
which may have limited permissions.

-- 
Scot L. Harris
webid at cfl.rr.com

If swimming is so good for your figure, how come whales look the
way they do?