0

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?)

  • May be you can explain what is it that you are actually trying to achieve, instead of describing your (impossible) solution to that problem. – mustaccio Jun 7 '18 at 17:53
  • @mustaccio I'm trying to build a database with a user-defined list of different filesystem-level backups. Ie take schema A from database 1, schema B from database 2; and build database 3 with A and B schemas; while using filesystem-level backup for performance reasons (because indexes takes days to build, file copying is much faster) – BenoitParis Jun 8 '18 at 10:46
  • Again, you're describing your "solution", not the problem. Any reason dblinks or federation don't work for you? – mustaccio Jun 8 '18 at 14:48
  • Please read What is the XY problem? – Sahap Asci Jun 9 '18 at 23:52
  • @mustaccio dblink would solve my problem, but that's a barrier in the optimizer that I don't want to be affected by – BenoitParis Jun 13 '18 at 16:57
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.

  • Thanks for the clarifications on my convoluted question ^^ What I'm trying to do is: import openstreetmap on pg+postgis once per country (index building expensive), and then build other databases cheaply from a country list. Say this time I only want France+Belgium+Luxembourg+Canada and don't want any of the Americas. How would you formulate that as a stackexchange question? I believe that's possible; I don't want to go the locally connected databases route, the optimizer will see that as a barrier. – BenoitParis Jun 13 '18 at 16:50
  • @BenoitParis: as you just wrote it, the problem statement makes sense to me. I do think there's no way at all though to import an already built index into a database... – Daniel Vérité Jun 14 '18 at 9:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.