2

I know I can list my postgres databases with the following command:

# list only the databases
psql -U openerp -q -t -c "select datname from pg_database;" template1

Or

# show a full listing of databases
psql template1 -U openerp -l

How can I filter the databases by owner 'openerp'? datdba field is just a number... how can I map it?

1 Answer 1

2

For things like this, you'll want to query pg_database directly. The owner field is a reference to pg_catalog.pg_authid.oid, but this is a superuser-only accessible table. There's a view, pg_catalog.pg_roles that exposes the id as the oid attribute, so you can:

select 
  datname,
  rolname AS dbowner
from pg_database d 
     inner join pg_roles r on (d.datdba = r.oid);
2
  • U're right. After digging for hours I found this approach (yes, i'm a pg n00b) Jun 14, 2015 at 16:10
  • It works! I just added a where clause to filter by my desired rolname. where rolname='ramy' . Sep 16, 2020 at 17:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.