Обсуждение: Copy one database to another server

Поиск
Список
Период
Сортировка

Copy one database to another server

От
Huan Ruan
Дата:
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



Re: Copy one database to another server

От
Steve Crawford
Дата:
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

Re: Copy one database to another server

От
bricklen
Дата:
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.