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
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 |