Mirroring a DB (was Re: [GENERAL] \d shows all my tables twice)

> By the way, I know about using pg_dump to backup the database and I do
> that.  Is there a good way to maintain a second identical copy of the
> database on another machine?  Will simply copying the dump over and
> restoring it with psql do the trick?  Would I need to delete an old copy
> of the same database first?  We have a somewhat slow Internet connection
> to our Linux system's location and it would be nice to have an alternate
> site with the same data.

We sometimes do:

 pg_dump -o -h <live> <table> | psql -h <mirror> <table>

(Note that you will probably want -z as well if pre-6.5)

This generally works, but has a habit recreating the views as actual
tables.  Often you can live with this, and there may be a simple way
to prevent it.  I just haven't found one yet.

--
Karl DeBisschop <kdebisschop@alert.infoplease.com>
617.832.0332 (Fax: 617.956.2696)

Information Please - your source for FREE online reference
http://www.infoplease.com  - Your Ultimate Fact Finder
http://kids.infoplease.com - The Great Homework Helper

Netsaint Plugins Development
http://netsaintplug.sourceforge.net