On Mon, 28 Jul 2003, Terence Chang wrote:
> Hi there:
>
> I am just wondering if this is a doable method to clone a database to
> other machine. I have three identical linux serves with postgresql
> server installed at the same location. I have a new customized database
> and initial data load into server 1. Can I just copy all of the database
> files physically from server 1 to server 2 and server 3? I assume this
> is one of the method to restore a database.
>
> What I am trying to do is to sell a solution package to clients. The
> solution will include Linux + Apache + PHP + PostgreSQL. So every
> customers will have the same database configuration initially.
There are few options.
1: Use a commercial replication package. For databases with lots of
activity, this choice is often best.
2: Shutdown both databases, copy the entire $PGDATA directory to another
machine of the same architecture, and restart both databases. This should
work, but is considered bad form. Plus your database is down while you do
it.
3: use pg_dump to move it:
pg_dump -h mainserver dbname|psql -h backupserver dbname
If your data doesn't change much, this should work fine.
4: Write your own triggers and use /contrib/dblink to shuffle the data
back and forth.
Often this answer gives you just enough to get the job done.