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.
- backup/restore. Easy to do and requires no downtime, but slow.
- pg_basebackup. Easy to do and requires no downtime, but it copies the whole cluster; and requires archive mode on?
- 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