Postgres Pro
Downloads
Home   >   mailing lists

Re: How to change all owners on all objects in a schema - Mailing list pgsql-admin

From Greg Spiegelberg
Subject Re: How to change all owners on all objects in a schema
Date
Msg-id BANLkTimHR07oOuqeUp-ix73tnxw8zbbL6w@mail.gmail.com
Whole thread Raw
In response to Re: How to change all owners on all objects in a schema  ("Kevin Grittner" <Kevin.Grittner@wicourts.gov>)
List pgsql-admin
Tree view
On Thu, Jun 23, 2011 at 2:41 PM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> wrote:

>              || ' set schema newschema;'

Oops; you wanted to change the owner, but I'll leave that as an
exercise for the reader.  :-)


Beat me to it  :)

Replace <SCHEMA>, <NEW_OWNER> and <DATABASE>

psql -qAt -d <DATABASE> -c "SELECT 'ALTER '||quote_ident(n.nspname)||'.'||case when c.relkind='r' then 'TABLE' else 'SEQUENCE' END||' public.'||quote_ident(relname)||' OWNER TO <NEW_OWNER>;'
  FROM pg_class c, pg_catalog.pg_namespace n
 WHERE c.relkind IN ('r','S')
   AND c.relnamespace=n.oid
   AND n.nspname='<SCHEMA>';" | psql -qAt -d <DATABASE>


pgsql-admin by date:

Previous
From: "Kevin Grittner"
Date:
Subject: Re: How to change all owners on all objects in a schema
Next
From: Bob Lunney
Date:
Subject: Parallel pg_dump on a single database