Goal Find publications of tables that have been created in order to enable logical replication.
Notes If the publication covers all tables, then the query returns the number of base tables (excluding system tables) in the database.
Type General (Overview of some aspect of the database.)
License MIT License
Data Source INFORMATION_SCHEMA+system catalog
SQL Query
SELECT pubname AS publication_name,
puballtables AS is_all_tables,
pubinsert AS is_insert,
pubupdate AS is_update,
pubdelete AS is_delete,
pubtruncate AS is_truncate,
string_agg(n.nspname || '.' || c.relname, ',
' ORDER BY n.nspname, c.relname) AS published_tables, CASE WHEN Count(pr.oid)=0 AND puballtables=TRUE THEN (SELECT Count(*) AS c FROM INFORMATION_SCHEMA.tables WHERE table_type='BASE TABLE' AND table_schema NOT IN (SELECT schema_name FROM INFORMATION_SCHEMA.schemata WHERE schema_name<>'public' AND schema_owner='postgres' AND schema_name IS NOT NULL)) ELSE Count(pr.oid) END AS number_of_published_tables FROM pg_publication AS p LEFT JOIN pg_publication_rel AS pr ON p.oid=pr.prpubid LEFT JOIN pg_class AS c ON c.oid=pr.prrelid LEFT JOIN pg_namespace AS n ON c.relnamespace=n.oid GROUP BY pubname, puballtables, pubinsert, pubupdate, pubdelete, pubtruncate ORDER BY pubname;
Collections

This query belongs to the following collections:

NameDescription
Find problems by overviewQueries that results point to different aspects of database that might have problems. A human reviewer has to decide based on the results as to whether there are problems or not .
Categories

This query is classified under the following categories:

NameDescription
Distributed databaseQueries of this category provide information about the foreign table mechanism.