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(at)alert(dot)infoplease(dot)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