跨 EC2 实例移动 Postgres 表空间和表
我有在 Amazon EC2 实例上运行的 postgres 数据库。我创建的表空间很少 一些每月表,这样每个表都位于单独的表空间上。为了获得最大性能,我在单独的 amazon ebs 卷上创建了每个表空间。
我想将其中一些表移动到不同的实例和数据库。我将用一个例子来解释它。 可以说。
我有 EC2 实例 A,如上文所述,采用了 postgres 设置。
我有另一个 Amazon 实例 B 正在运行,并且我也在其上安装了 postgres。
我想为 B 上 A 中的某些表创建相同的表结构。我想从实例 A 中分离卷并将其附加到实例 B。
此外,我想在实例 B 上创建表空间,该表空间将指向新附加的卷。
当我启动这个新创建的 postgres 时,我希望看到表中填充了这些卷(数据库)中的数据。
最后我将从 A 中删除这些表
我知道我的写作生疏,但找不到更好的方法来提出问题。
沿着这些思路的事情是可能的吗?有没有任何指示可以实现这样的目标?
I have postgres database running on Amazon EC2 instance. I have few tablespaces created for
some monthly tables, such that each table is on individual tablespace. To get the maximum performance, I have created each tablespace on individual amazon ebs volume.
I want to move some of this tables to different instance and database. I will explain it with one example.
Lets say.
I have EC2 instance A with postgres setup as explained above.
I have another Amazon instance B running and I installed postgres on it as well.
I want to create the same table structure for some of the tables present in A on B. I want to detach the volumes from instance A and attach it to instance B.
Also, I want to create tablespaces on instance B, which will point to the newly attached volumes.
And when I start up this newly created postgres, I expect to see the tables populated with data from those volumes(database).
finally I will delete those tables from A
I know I am being rusty in writing, but couldn't find a better way to ask the question.
Is something along these lines is possible? Are there any pointers for achieving something like this?
不,
表空间目录中的数据只是数据。您还需要 pg_catalog 架构中的表中的元数据,以及来自 pg_clog 和 pg_xlog 的信息来访问它。
如果您想使用卷来移动内容,则必须立即移动整个安装(所有表空间,包括 pg_default)。否则,你需要使用pg_dump/pg_restore来传输数据。
No.
The data in the tablespace directory is only the data. You also need the metadata that's in the tables in the pg_catalog schema, as well as the information from pg_clog and pg_xlog to access it.
If you want to move things across using volumes, you must move the entire installation at once (all the tablespaces, including pg_default). Otherwise, you need to use pg_dump/pg_restore to transfer the data over.