Best way to change the owner of a PostgreSQL database and their tables?

Question

I am trying to change the owner of a PostgreSQL database (version > 8.2) and its tables.

I read this solution:

Modify OWNER on all tables simultaneously in PostgreSQL

But is this the best way to do it (for recent versions of PostgreSQL)?. It seems that there is a function REASSIGN OWNED which is better, but this one changes every database owned by the old_role, doesn't it? I only want it for one database.

Like this post:

REASSIGN OWNED BY for 1 specified database

I am not going to change the owner postgres, which is the best way nowadays?

Thank you in advance

Answer 1

According to the manual:

Because REASSIGN OWNED does not affect objects within other databases, it is usually necessary to execute this command in each database

which would seem to meet your requirements, although it also says the command would affect table spaces (which are not specific to the current database).

The second SO answer you linked applies to the special case of the postgres user, which owns the system catalogs. You cannot change the ownership of these.

Answer 2

The two methods that spring to mind for me are:

1) First alter the database name, and then perhaps right a quick script which changes the owner on the current tables to the old tables.

ALTER DATABASE <dbname> OWNER TO <newowner>;
\o altertable.sql
SELECT 'ALTER TABLE ' || table_name || ' OWNER TO <newowner>; ' FROM information_schema WHERE table_schema = <oldowner> and table_catalog = '<dbname>';
\o
\i altertable.sql

The above will generate your commands, then just pop them into a file and execute it.

2) The other option would be to use pg_dump to dump your database in plain text mode and then alter the appropriate strings using search and replace:

pg_dump -Fp -f dbbackup.dmp <dbname>
vi dbbackup.dmp
:%s/oldowner/newowner/g
save and exit

Hope this helps.