Postgresql – Generate table of all databases and schema with a single psql command

postgresqlschema

In PostgreSQL is it possible to generate a list of all databases and all schema with a single psql command?

To be concrete, let me provide an example.

I have two databases in a PotgreSQL database cluster: accounts and people. In the accounts database there are these schema (beyond the default): hr and finance. In the people database there are these schema (beyond the default): hr, admin, backup.

Is there a single psql command I can run that generates results something like this:

database | schema
-----------------
accounts |  hr
accounts |  finance
people   |  hr
people   |  admin
people   |  backup

Or is the only way to generate this information to connect multiple times and run separate queries?

Best Answer

In PostgreSQL is it possible to generate a list of all databases and all schema with a single psql command?

No. The list of schemas of a database is private to that database and cannot be obtained without being connected to it. The list of databases is available with \l or select * from pg_database.

Related Solutions

Sql-server – Whitelist-based SQL Server schema setup

They can read/write in every schema because they have db_datareader and db_datawriter fixed roles assigned. These roles allow the user to read and write to any user table in the database ( http://msdn.microsoft.com/en-us/library/ms189121.aspx ).

(btw db_ddladmin also allows them to run ANY DDL commands in the database, which might cause you some problems if they start dropping/modifying each others objects)

If you revoke these role grants, you can then assign explicit schema permissions to a user, ie:


GRANT select, insert ON SCHEMA :: schema1 to user1;

More details at http://msdn.microsoft.com/en-us/library/ms187940.aspx

If you can't revoke these roles because they're in use elsewhere, then you'll need to explicitly deny permissions on the schema for the users you don't want to have access. (A deny at the schema level overrides the database level grant from the fixed role).

Postgresql – How does Postgres 9.2’s streaming replication handle schema changes, and initial table setup

If you intend to use PostgreSQL's built-in streaming replication, then you seed the replica with a base backup - a file-system level copy of the original database. So it already has all the tables and data from the master. pg_basebackup is usually used for this.

Streaming replication reads changes at the block level from the write-ahead logs, so the databases are kept physically the same. Since DDL is, just like DML, recorded in the WAL, DDL and DML are both transparently replicated.

See the documentation on HA and replication in PostgreSQL for more information.

Best Answer

Related Solutions

Sql-server – Whitelist-based SQL Server schema setup

Postgresql – How does Postgres 9.2’s streaming replication handle schema changes, and initial table setup

Related Question