pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedCopy one database to another server
3+ messages / 3 participants
[nested] [flat]
* Copy one database to another server
@ 2013-05-02 22:01 Huan Ruan <huan.ruan.it@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: Huan Ruan @ 2013-05-02 22:01 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Copy one database to another server
@ 2013-05-02 22:19 Steve Crawford <scrawford@pinpointresearch.com>
parent: Huan Ruan <huan.ruan.it@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Steve Crawford @ 2013-05-02 22:19 UTC (permalink / raw)
To: Huan Ruan <huan.ruan.it@gmail.com>; +Cc: pgsql-admin
On 05/02/2013 03:01 PM, Huan Ruan wrote:
> Hi All
>
> We are migrating from Firebird to Postgres. One task we frequently
> perform is to copy one database from one server to another...
I think it would be helpful to know *why* you do this (backup,
forensics, development, shifting load among virtual machines or ???) as
well as a better idea of how often you do this, how long it takes and
the amount of downtime you consider acceptable. That way we may be
better able to come up with a way of solving the actual problem you are
facing.
There are a number of non-core replication solutions such as Slony and
many can migrate a single database or even specific table(s).
Cheers,
Steve
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Copy one database to another server
@ 2013-05-02 22:25 bricklen <bricklen@gmail.com>
parent: Huan Ruan <huan.ruan.it@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: bricklen @ 2013-05-02 22:25 UTC (permalink / raw)
To: Huan Ruan <huan.ruan.it@gmail.com>; +Cc: pgsql-admin
On Thu, May 2, 2013 at 3:01 PM, Huan Ruan <huan.ruan.it@gmail.com> wrote:
> 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?
FWIW, I recently set up a 160GB clone by simply piping from pg_dump over
the network into psql, alleviating the need to dump to disk. In my case,
the server did not have enough space to hold the pg_dump, so I had to
stream it.
pg_dump -Fp your_db | psql -h new_server -U postgres your_new_db
You could also search the archives for caveats and other (and safer) ways
to do this.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2013-05-02 22:25 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-05-02 22:01 Copy one database to another server Huan Ruan <huan.ruan.it@gmail.com>
2013-05-02 22:19 ` Steve Crawford <scrawford@pinpointresearch.com>
2013-05-02 22:25 ` bricklen <bricklen@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox