Moving Postgres tablespaces and tables across EC2 instance

Question

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.

1. I have EC2 instance A with postgres setup as explained above.

2. I have another Amazon instance B running and I installed postgres on it as well.

3. 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.

4. Also, I want to create tablespaces on instance B, which will point to the newly attached volumes.

5. And when I start up this newly created postgres, I expect to see the tables populated with data from those volumes(database).

6. 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?

Answer 1

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.