During a database restore, sometimes you may need to change the owner of a table or a sequence to another user, for instance if you take a production database and put it on your integration for your developers (anonymized, of course ;)).
Rather than tediously doing an ALTER TABLE table by table, here are 2 SQL queries that will allow you to generate the SQL to quickly change the owner of all tables and sequences:
select 'alter table ' || relname || ' owner to mynewowner;' from pg_class join pg_namespace on pg_class.relnamespace = pg_namespace.oid where pg_namespace.nspname = 'public' and relkind='r';
select 'alter sequence ' || relname || ' owner to mynewowner;' from pg_class join pg_namespace on pg_class.relnamespace = pg_namespace.oid where pg_namespace.nspname = 'public' and relkind='S';
Result for tables:
?column?
-----------------------------------------------------------------
alter table batch_job_execution owner to mynewowner;
alter table purchase owner to mynewowner;
alter table serviceprice owner to mynewowner;
alter table account owner to mynewowner;
alter table product owner to mynewowner;
Result for sequences:
?column?
----------------------------------------------------------------
alter sequence batch_job_seq owner to mynewowner;
alter sequence batch_step_execution_seq owner to mynewowner;
alter sequence cgufile_id_seq owner to mynewowner;
You only need to copy this result directly or integrate it into a shell script that will run it automatically for you.