Re: Export and import from one postgres server to another

On Mon, May 28, 2012 at 1:27 PM, Alexander Reichstadt
<info(at)apfeltaste(dot)net> 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. The escape char doesn't seem to go through the web frontend or something. But without the connect statement it ends up telling me no database selected. Please, can someone let me know how to do this?

command line tools and simple scripting are the way to do this. it
works equally well on *nix and windows. a simple script might be:

psql -h restoredb -c "drop database if exists mydb"
psql -h restoredb -c "create database mydb"
pg_dump -h dumpdb | psql restoredb

you'll probably want to add some logging and error checking (this will
work differently depending on o/s) but that's the basic mechanism.
Once you have it scripted, it then becomes a matter of scheduling the
backup and restore.

Another fancier approach to dealing with this is to look at setting up
replication.

merlin