Changing owner of all tables in a database in PostgreSQL

You can make a full duplicate of a database with CREATE DATABASE aiida_clone WITH TEMPLATE aiida_original_db OWNER new_user;.

Note, however, that if the new OWNER (here new_user) is different than the original one, still each table will be owned by the original owner, meaning that new_user will not be able to, e.g., check the content of the table. To fix this, you'll need also to change the owner of each table.

To do this, while still in psql, first connect to the database, using (replace aiida_clone with your new DB name):

\c aiida_clone

Then, you can run (change new_user with the correct value)

select 'ALTER TABLE ' || t.tablename || ' OWNER TO new_user;' from  pg_tables t  where t.schemaname = 'public';

This will not run any actual operation, but just print all the commands to run. Then, just copy-paste them and run them all.

To do a final check, run

\d

and check that all tables are now correctly owned.