Copy one database to another server

From: Huan Ruan <huan(dot)ruan(dot)it(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Copy one database to another server
Date: 2013-05-02 22:01:39
Message-ID: CAD1stZuo0FyomMg+4HxoGXHnJayP6Bcn=QCngDR_1Asu6zcYQQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hi All

We are migrating from Firebird to Postgres. One task we frequently perform
is to copy one database from one server to another. It's easy to do in
Firebird as a database can basically be treated as a file (it shouldn't be
I know). This appears to be harder in Postgres, especially we will have
multiple databases in one Postgres cluster.

The goal is to be able to copy one database from one server to another
quickly; and the two servers may host different number of databases. The
main database is 400GB of size and some down time is acceptable.

We looked into a few options but none of them looks exactly optimal to our
current practice. Any suggestions will be appreciated.

1. backup/restore. Easy to do and requires no downtime, but slow.
2. pg_basebackup. Easy to do and requires no downtime, but it copies the
whole cluster; and requires archive mode on?
3. create a new db using the current one as a template. Easy to do and
speed is acceptable. A bit downtime is ok, but it's only on the current
server. Is there a way to move it to anther server?

If I make sure there are no connectiosn to a database, is it safe to just
copy that one folder of files for that database? If so, how can I merge
that into another cluster on another server?

In order to use pg_basebackup, another thought was to run multiple
instances of Postgres on each server, so copying one database is the same
as copying the whole cluster. Will there be much performance hit with this
approach?

Thanks in advance.

Cheers
Huan

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Steve Crawford 2013-05-02 22:19:35 Re: Copy one database to another server
Previous Message Dev Kumkar 2013-05-02 17:32:13 Re: Migration from Sybase to Postgresql