PostgreSQL – How to Show Number of Tables for Each Database

postgresqlselecttable

Related to my previous question, I use the code below to get all databases:

SELECT datname
FROM pg_database
WHERE NOT datistemplate
  AND datallowconn
  AND datname <> 'postgres';

How to get the number of tables from each database returned from the command above?

There is no master database in Postgres like Microsoft SQL Server, where I could do a query for all the databases.

Is it possible in Postgres?

Thank you

Best Answer

You can use something like the following which will list every table (with their row counts) for every schema in the database:

select n.nspname as table_schema,
       c.relname as table_name,
       c.reltuples as rows
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where c.relkind = 'r'
      and n.nspname not in ('information_schema','pg_catalog')
order by c.reltuples desc;

I believe you'll need to use dynamic SQL in order to run it in every database. Here's a walkthrough.

Related Solutions

PostgreSQL – How to Detach All Other Users from a Database

You could try connecting to the database as the postgres user and running:

SELECT pg_terminate_backend( procpid )
FROM pg_stat_activity
WHERE procpid <> pg_backend_pid( )    -- 1. don't terminate your own session
    AND datname =                     -- 2. don't terminate connections to 
    (SELECT datname                   --    other databases in the cluster
       FROM pg_stat_activity
      WHERE procpid = pg_backend_pid( )
    );

update An even better query gets rid of the subselect:

SELECT pg_terminate_backend( procpid )
FROM pg_stat_activity
WHERE procpid <> pg_backend_pid( )
    AND datname = current_database( );

Postgresql – Is it possible for heroku postgres follower database to follow non-heroku postgres master

It's not supported in either direction, with the most pressing problem being the platform and architecture dependence of that replication method.

In the case of migrating to the service, the problem is doubly so, because slurping random, physical base backups from the internet as would be required to set up replication as it is seen today is a somewhat ticklish sounding proposition.

It sucks, and I'd like to lift that restriction some day, if that and the other problems (some not) can be solved.

Please feel free to write into postgres@heroku.com if you want to discuss your problem in more detail, although I'm most unsure if we have the technology to make this a super-easy proposition right now. Still, the main way we can make it better is by studying it, if you'd be so kind to share.

Best Answer

Related Solutions

PostgreSQL – How to Detach All Other Users from a Database

Postgresql – Is it possible for heroku postgres follower database to follow non-heroku postgres master

Related Question