How to import an existing schema and tablespace into a postgres database, from a filesystem-level backup

All we need is an easy explanation of the problem, so here it is.

Follow-up question from:
Have postgres' pg_dump export an index

Say I have an existing database, and I want to add to it another schema with content. I saved that content previously through a filesystem-level backup of the tablespace that served the schema’s tables and indexes.

I understand that DATABASEs, TABLEs and INDEXes necesitate a tablespace to be stored into, but not SCHEMAs (their declaration is stored in the DATABASE’s tablespace?).

So, how do I make the DATABASEs tablespace understand that it needs to access a specific schema that would have its tables and indexes defined through a pre-existing tablespace.

Alternatively, can I make a CREATE TABLE t TABLESPACE tspace statement that would connect to the tspace TABLESPACE (as opposed to starting to write into it an empty instance of the table)?

(In other words, is the execution of a DDL statement stored into the DATABASEs tablespace, and nothing touches another tablespace?)

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Say I have an existing database, and I want to add to it another
schema with content. I saved that content previously through a
filesystem-level backup of the tablespace that served the schema’s
tables and index

Unfortunately, an isolated backup of a tablespace cannot be used for any purpose whatsoever.

I think what you’re trying to do is based on the premise that a tablespace could be more or less moved around, be possibly plugged to a different database or the same database at a different point in time, that kind of thing. But none of this is possible. A tablespace holds live metadata in rows and elsewhere that become wrong the moment you isolate it from the rest of the instance.

The results of DDL statements go into the pg_catalog schema of each database. pg_catalog also contains references to objects that are shared across all databases and not stored in any particular database, such as pg_authid or pg_database.

Looking at Have postgres' pg_dump export an index I can see where your idea is coming from. However I don’t see how @mustaccio answer has any practical use. If you have a FS-level image of a table plus its indexes, they having matching TIDs, and then what? There’s no practical use to these files outside of the rest of the cluster.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply