How to List All Schemas in the PostgreSQL Database: An Ultimate Guide

Data professionals waste 34% of their time managing fragmented database schemas and troubleshooting integration failures—a productivity drain that compounds as organizations scale across multiple PostgreSQL instances. This challenge intensifies when teams need to rapidly identify schema structures for debugging performance issues, validating access permissions, or coordinating data migrations across development environments.

PostgreSQL, an object-relational database management system, supports SQL for querying relational data and JSON for non-relational data. This flexibility helps you handle complex data types for applications ranging from web apps to enterprise systems.

Effective schema management is essential to fully utilize PostgreSQL's capabilities in application development. With proper schema management, you can ensure data integrity and enhance scalability in complex systems.

Efficient database administration—whether for troubleshooting, performance optimization, or access auditing—requires a clear overview of all database schemas. This article explains the different ways to postgres list schemas in PostgreSQL databases.

What Is a PostgreSQL Schema?

PostgreSQL schemas are logical containers that allow you to organize database objects like tables, views, data types, and operators. Objects such as views, materialized views, tables, sequences, and foreign tables must have unique names within a schema due to a shared namespace.

While objects of the same type cannot share names inside a single schema, identical object names can exist across different schemas. For instance, both schema_info and my_schema can contain a table named my_table without conflict.

Each database has a public schema by default. You can also create your own schemas with specific ownership and privileges using the CREATE SCHEMA command. If you no longer need a schema, remove it with DROP SCHEMA; if the schema contains objects, add the CASCADE option.

To create or access objects within a schema, use a qualified name:

schema.object

Example:

my_schema.my_table

To create my_table inside my_schema:

CREATE TABLE my_schema.my_table (
    id   INT,
    name TEXT
);

Because writing fully-qualified names is tedious, PostgreSQL lets you use unqualified names (e.g., just the table name). A search path tells PostgreSQL which schema(s) to consult when you reference an object without the schema prefix.

What Are the Different Methods to List All Schemas in a PostgreSQL Database?

There are several ways to list schemas, each offering different details and filtering options.

Common prerequisites

• Install the latest PostgreSQL version.
• Add PostgreSQL to your system PATH.

1. Using psql Meta-commands

psql is PostgreSQL's interactive CLI. In addition to SQL, it supports meta-commands (prefixed with \) that simplify administrative tasks.

Steps:

1. Connect to the PostgreSQL instance:

   psql -U postgres

2. Or connect to a specific database:

   psql -h <hostname> -U <username> -d <database_name>

3. List databases:

   \l

4. Switch to a database:

   \c <database_name>

5. List all schemas with their owners:

   \dn

For extra info (access privileges, descriptions):

   \dn+

2. Using information_schema

information_schema is an ANSI-SQL standard set of read-only views providing metadata about the current database.

Query:

SELECT schema_name
FROM information_schema.schemata;

3. Using pg_catalog

pg_catalog is the built-in system catalog schema. It's always first in the search path.

Query:

SELECT nspname AS schema_name
FROM pg_catalog.pg_namespace;

How Do You List Schemas With Privileges?

SELECT
    schemata.schema_name,
    schema_privileges.grantee,
    schema_privileges.privilege_type
FROM
    information_schema.schemata
LEFT JOIN
    information_schema.schema_privileges
ON  schemata.schema_name = schema_privileges.schema_name
ORDER BY
    schemata.schema_name,
    schema_privileges.grantee;

The LEFT JOIN ensures schemas without explicit privileges still appear.

How Do You List Schemas With Their Sizes?

SELECT
    nspname AS schema_name,
    pg_size_pretty(
        SUM(pg_total_relation_size(pg_class.oid))
    ) AS size
FROM pg_catalog.pg_namespace
JOIN pg_catalog.pg_class
     ON pg_class.relnamespace = pg_namespace.oid
GROUP BY nspname;

pg_total_relation_size computes total disk usage; pg_size_pretty formats it (KB, MB, GB).

What Are the Advanced Techniques for Zero-Downtime Schema Migrations?

Traditional ALTER TABLE operations frequently require exclusive locks that block read/write access during schema changes, creating unacceptable downtime windows for applications requiring continuous availability. Modern PostgreSQL environments demand migration strategies that maintain service availability while implementing schema modifications.

View-Based Schema Versioning

View-based migration systems enable simultaneous access to both pre-migration and post-migration schemas through abstraction layers. Tools like Reshape implement a three-phase protocol that eliminates locking through transactional view redirection:

1. Migration Initialization: Creates shadow tables with new schema structures while establishing real-time synchronization triggers between original and modified schemas
2. Gradual Application Rollout: Enables updated applications to reference new schema versions while legacy applications continue using original views through bidirectional data consistency triggers
3. Finalization: Atomically switches default view references to the new schema after validating data integrity, then removes legacy structures

This approach allows column renames and type changes with minimal latency impact while maintaining full write throughput during migration processes.

Concurrent Index Operations

PostgreSQL's CONCURRENT options for index operations provide non-blocking alternatives to standard DDL commands. When creating or dropping indexes, concurrent operations prevent table locks that would otherwise halt application access:

CREATE INDEX CONCURRENTLY idx_schema_name ON my_table (column_name);
DROP INDEX CONCURRENTLY idx_old_schema;

These operations take longer to complete but maintain application availability throughout the migration process. For high-volume environments, concurrent index management becomes essential for maintaining SLA commitments during schema evolution.

Logical Replication for Schema Changes

Logical replication enables schema modifications by maintaining synchronized replicas with different structures. This technique proves particularly valuable for major schema restructuring where traditional migration approaches would require extended downtime periods.

How Do Modern Declarative Schema Management Tools Enhance PostgreSQL Administration?

Traditional migration approaches rely on imperative change scripts that create cumulative technical debt through sequential modifications. Modern declarative tools address these limitations by managing schema state rather than change sequences, reducing operational complexity and improving team collaboration.

State-Based Schema Management

Tools like Atlas introduce differential migration through declarative schema definitions. Rather than maintaining sequential migration scripts, you define desired schema states in configuration files:

schema "production" {
  table "users" {
    column "id" {
      type = bigint
      primary_key = true
    }
    column "email" {
      type = varchar(255)
      unique = true
    }
  }
}

The engine automatically generates optimized migration paths by comparing actual database states with desired configurations, prioritizing non-blocking operations and data preservation strategies.

GitOps-Inspired Schema Workflows

Modern schema management platforms like Bytebase implement review workflows that mirror software development practices. These systems provide:

• SQL linting with compliance rule enforcement
• Automated drift detection through schema snapshot comparisons
• Role-based access control integration with approval workflows
• Real-time collaboration features for distributed teams

Drift Detection and Reconciliation

Declarative tools excel at identifying schema drift—unauthorized changes that occur outside managed migration processes. These systems continuously monitor schema states and alert administrators to discrepancies between expected and actual configurations.

Advanced reconciliation algorithms calculate minimal operation sequences needed to restore desired states while preserving data integrity. This capability proves essential in environments where multiple teams might make ad-hoc schema modifications that compromise consistency.

How Can You List All Schemas Using Python (psycopg2)?

1. Install the driver:

   pip install psycopg2

2. Sample script:

   import psycopg2

   conn = psycopg2.connect(
       dbname="postgres_database_name",
       user="postgresDB_username",
       password="postgresDB_password",
       host="host_address",
       port="port_number"
   )
   cur = conn.cursor()

   cur.execute("""
       SELECT schema_name
       FROM information_schema.schemata
       WHERE schema_name NOT IN ('information_schema', 'pg_catalog');
   """)

   schemas = cur.fetchall()
   for schema in schemas:
       print(schema[0])

   cur.close()
   conn.close()

Why Is Listing Schemas Useful?

• Understanding Database Structure – Provides a high-level view for easier maintenance and scalability.
• Identifying Available Schemas – Quickly see which schemas exist in multi-schema environments.
• Access Control & Permissions – Verify or adjust who can access what.
• Performance Monitoring – Spot storage inefficiencies and optimize queries.
• Full-Text Search Index Availability – Ensure indexes (e.g., GIN, GiST) live in the correct schema for efficient querying.

How Does Airbyte Help With PostgreSQL Schema Management?

Airbyte is a data-movement platform with 600+ pre-built connectors, including PostgreSQL. After configuring a pipeline, Airbyte can automatically check for schema changes (every 15 minutes in Cloud, every 24 hours self-hosted) to keep integrations in sync.

Airbyte's open-source foundation provides flexibility for custom schema management workflows while maintaining enterprise-grade security and governance capabilities. The platform supports deployment across cloud, hybrid, and on-premises environments, ensuring your PostgreSQL schema management aligns with your infrastructure requirements.

For organizations managing multiple PostgreSQL instances, Airbyte's automated schema detection capabilities reduce the manual overhead of tracking schema changes across distributed environments. If you need tailored guidance, you can connect with experts.

Conclusion

You now know several ways to postgres list schemas in PostgreSQL—via psql meta-commands, information_schema views, and pg_catalog tables—as well as how to retrieve additional details such as privileges and sizes, and even automate the task via Python. Modern approaches like zero-downtime migrations and declarative schema management provide additional capabilities for complex production environments. Understanding and monitoring schemas ensures better organization, security, and performance of your PostgreSQL databases.

Further reading

• PostgreSQL Use Cases
• Database Schema Migration