When working with PostgreSQL, ensuring database integrity is crucial for maintaining reliable data. Unlike SQL Server's DBCC CHECKDB command, PostgreSQL offers several different approaches to verify database consistency.

PostgreSQL provides these primary methods for checking database integrity:

-- Check for corrupt pages in all databases
SELECT * FROM pg_catalog.pg_stat_database WHERE datname = 'your_database';

-- Verify table structure
VACUUM (VERBOSE, ANALYZE) tablename;

-- Check for orphaned files
SELECT * FROM pg_ls_dir('pg_wal');

One effective method is using pg_dump, which inherently performs consistency checks:

pg_dump -Fc -v your_database > /dev/null

This command will fail if it encounters any corruption while dumping the database.

For more thorough checks, install the amcheck extension:

CREATE EXTENSION amcheck;

-- Check B-tree index integrity
SELECT bt_index_check(c.oid)
FROM pg_index i
JOIN pg_class c ON i.indexrelid = c.oid
WHERE c.relkind = 'i';

For production environments, consider setting up monitoring:

-- Create a function for regular checks
CREATE OR REPLACE FUNCTION check_db_integrity()
RETURNS void AS $$
BEGIN
    PERFORM bt_index_check(c.oid)
    FROM pg_index i
    JOIN pg_class c ON i.indexrelid = c.oid
    WHERE c.relkind = 'i';
    
    -- Add additional checks here
END;
$$ LANGUAGE plpgsql;

If you detect issues, these steps can help:

• Restore from backup if available
• Use pg_resetwal for transaction log issues
• Consider third-party tools like pg_repack

PostgreSQL offers several approaches for database integrity checking that serve similar purposes to SQL Server's DBCC CHECKDB:

-- Basic table verification
VACUUM (VERBOSE, ANALYZE) table_name;

-- Advanced corruption detection (requires contrib module)
CREATE EXTENSION amcheck;
SELECT bt_index_check('index_name'::regclass);

For thorough integrity verification similar to DBCC CHECKDB, the pg_catcheck extension provides extensive checking:

-- Installation
CREATE EXTENSION pg_catcheck;

-- Run catalog verification
SELECT * FROM pg_catcheck();

PostgreSQL 11+ includes checksum verification at the file level:

-- Enable checksums during initdb
initdb --data-checksums

-- Verify checksums on running cluster
pg_checksums --check -D /path/to/data/directory

For periodic monitoring, create a verification function:

CREATE OR REPLACE FUNCTION verify_database_integrity() RETURNS TABLE (
    object_type text,
    object_name text,
    issue text
) AS $$
BEGIN
    -- Check for orphaned TOAST tables
    RETURN QUERY
    SELECT 'TOAST table'::text, t.relname::text, 
           'Orphaned TOAST table'::text
    FROM pg_class t
    WHERE t.relkind = 't'
    AND NOT EXISTS (
        SELECT 1 FROM pg_class c 
        WHERE c.reltoastrelid = t.oid
    );

    -- Add more verification checks here
END;
$$ LANGUAGE plpgsql;

Consider these additional tools for comprehensive checking:

• pg_repack for online table verification and maintenance
• Bucardo's pg_dump_validate for backup verification
• WAL verification tools for logical replication setups