将表空间从一个Postgresql实例复制到另一个 [英] Copying a tablespace from one postgresql instance to another
问题描述
我正在寻找一种将数据库从一台postgresql服务器快速克隆到另一台服务器的方法。
I'm looking for a way to quickly "clone" a database from 1 postgresql server to another.
假设...
- 我在HostA上运行了一个PostgreSQL服务器，服务2个数据库
- 我在HostA上安装了2个设备，每个设备存储数据库之一的数据（即1个数据库=> 1个表空间=> 1个设备）
- 我能够通过一些谨慎的方法获取安全的时间点快照
- 我能够始终如一地安全地生成所用2种设备中任何一种的克隆（我甚至可以假设两个数据库都只接收过读取）
- 主机涉及的是CentOS 5.4
- I have a postgresql server running on HostA, serving 2 databases
- I have 2 devices mounted on HostA, each device stores the data for one of the database (i.e. 1 database => 1 tablespace => 1 device )
- I am able to obtain a "safe" point in time snapshot through some careful method
- I am able to consistently and safely produce a clone of any of the 2 devices used (I could even assume both database only ever receive reads)
- The hosts involved are CentOS 5.4
是否可以在HostB上托管第二个postgresql服务器，挂载其中一个克隆的设备并获取相应的数据库流行成为现实？ （有点像在MySQL中复制MyISAM表文件时的情况。）
Is it possible to host a second postgresql server on HostB, mount one of the cloned devices and get the corresponding database to "pop" into existence? (Kind of like when you copy the MyISAM table files in MySQL).
如果有可能，其作用机理是什么（即我应该研究什么DDL或pg命令）？
If this is at all possible, what is the mechanism (i.e. what DDL should I look into or pg commands)?
对于我来说，能够相互隔离地移动单个数据库很重要。但是，如果这不可能，一种类似的方法是否可以在服务器级别使用（尝试通过将datadir复制到具有相同postgresql安装的主机来克隆和重新生成服务器）？
It's important for me to be able to move individual databases in isolation of each other. But if this isn't possible, would a similar approach work at a server level (trying to clone and respawn a server by copying the datadir over to a host with the same postgresql install) ?
推荐答案
不容易，因为数据库之间共享许多文件，这意味着同一安装中的每个数据库都依赖于此。
Not easily, because there are a number of files that are shared between databases which means each database in the same install is dependent on this.
您可以在服务器级别或集群级别执行此操作，但不能在单个数据库级别执行此操作。只要确保在整个数据目录和所有外部表空间上复制/克隆即可。只要您可以原子地进行克隆（在同一文件系统上，或者可以在整个文件系统上进行原子克隆的系统上），您甚至无需停止hostA上的数据库即可。
You can do it at the server level, or at the cluster level, but not at individual database level. Just be sure to copy/clone over the whole data directory and all external tablespaces. As long as you can make the clone atomically (either on the same filesystem or with a system that can do atomic clones across filesystems), you don't even need to stop the database on hostA to do it.
这篇关于将表空间从一个Postgresql实例复制到另一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
