Thread: Clone a database to other machine

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.

On Mon, 2003-07-28 at 15:38, 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.
>
> Any other suggestion?

Why not send them gzipped output from pg_dumpall along with an
initdb script?  It's smaller, and "position independent".

--
+-----------------------------------------------------------------+
| Ron Johnson, Jr.        Home: ron.l.johnson@cox.net             |
| Jefferson, LA  USA                                              |
|                                                                 |
| "I'm not a vegetarian because I love animals, I'm a vegetarian  |
|  because I hate vegetables!"                                    |
|    unknown                                                      |
+-----------------------------------------------------------------+

hi Scott:

Would you be kind to tell me what commercial replication package out there?
Thanks!

Basically, my server will not be 24x7. So it is ok to shut down the machine.
And we only want to clone the database for a brand new machine.

Thanks!

> Basically, my server will not be 24x7. So it is ok to shut down the machine.
> And we only want to clone the database for a brand new machine.

It sounds like loading it from a dumpall would be your best choice,
since you can use the same dumpall to ensure that each new clone has the
same starting point.
--
Mike Nolan

On Mon, 28 Jul 2003, Terence Chang wrote:

> hi Scott:
>
> Would you be kind to tell me what commercial replication package out there?
> Thanks!
>
> Basically, my server will not be 24x7. So it is ok to shut down the machine.
> And we only want to clone the database for a brand new machine.

The standard one is eRServer (I think that's the spelling) from the good
folks at www.pgsql.com

It's code base will likely be folded into the postgresql contrib directory
in the next few months.  Note that eRServer is provided free WITH A
yearly support contract (which is not, of course, free.)

On Mon, Jul 28, 2003 at 01:38:34PM -0700, Terence Chang wrote:
> 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.

Not unless all the databases are shut down.  You could use pg_dump.

A
--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110