Robust way to build a DWH without downtime
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.