How can you rename a schema containing an extension without breaking tables in other schemas that rely on this extension (PostGIS)?

I'm trying to temporarily rename a schema "dest" to "public" in order to save a backup with the name "dest. In order to do this:

  1. Rename public to public_old
  2. Rename dest to public
  3. pg_dump ...

However, step 1 causes the geometry type in public to become public_old.geometry:

\d dest.geo_choice
                                       Table "dest.geo_choice"
           Column           |   Type   |                          Modifiers
 geom                       | geometry |

ALTER SCHEMA public RENAME TO public_old;

                                             Table "dest.geo_choice"
           Column           |        Type         |                          Modifiers
 geom                       | public_old.geometry |

select GeometryType(geom) from dest.geo_choice;
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

How can I avoid this breakage so we can carry out pg_dump?

  • How difficult is it to reinstall the extension in the new public schema? – Lennart May 17 '18 at 2:57
  • Uninstall it first from the old one and then reinstall it? I guess that would work as a workaround. – Steve Bennett May 17 '18 at 4:04
  • Actually no. When you uninstall it, you'd lose all extension-specific data in both schemas. – Steve Bennett May 17 '18 at 4:05

Your Answer

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

Browse other questions tagged or ask your own question.