PostgreSQL – How to Prevent Writes to a Schema Temporarily

Why don't you COMMENT ON the various SCHEMA components, that way your comments are in the schema, and will get dumped.

COMMENT stores a comment about a database object.

To modify a comment, issue a new COMMENT command for the same object. Only one comment string is stored for each object. To remove a comment, write NULL in place of the text string. Comments are automatically dropped when the object is dropped.

For reference, here's the solution that I came up with:

• Create a new schema dwh
• Build the new version of the DWH in that shema
• Drop the old public schema
• Rename the new dwh schema to public

(this assumes that your main schema is named public, of course)

This method works quite reliably and without interrupting/killing existing database connections, so I prefer the solution over my initial "create and rename database" approach. Here's the SQL that I use for building the DWH (it assumes that you're connected to the DWH database):

--we drop the temporary schema if it already exists
DROP SCHEMA IF EXISTS dwh CASCADE;
--we create the temporary schema
CREATE SCHEMA dwh;

--we modify the search path for this session
SET search_path to dwh;

--create your DWH here...

--now we commit the new version

--make sure you're connected to the correct database when executing this ;)
DROP SCHEMA IF EXISTS public CASCADE;

--we rename the DWH schema to public
ALTER SCHEMA dwh RENAME TO public;

--you might need to restore permissions on the new public schema...
GRANT USAGE ON SCHEMA public TO dwh_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO dwh_user;

Apart from needing to manually restore permissions and having to recreate extensions in the new schema, I found this solution to be quite optimal as it does not require superuser privileges for the DWH user, and works without creating/dropping any databases. I have not yet figured out a way to copy permissions from one schema to another, which is my only minor issue with this approach.