Postgres – Summary Check of Size No of Tables Views and Rows

It is a good idea to do a general reconcilliation before and after a database dump and restore to peform some sense check as to whether restoration has completed successfully.

SELECT datname AS database_name, pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

And here is a further breakdown of numbers in a database

DO $$
DECLARE
    rec         RECORD;
    rec_count   bigint;
    total_rows  bigint := 0;
    total_items int := 0;
BEGIN
    -- Create a temporary table to store individual counts
    CREATE TEMP TABLE temp_table_counts (
        table_name text,
        row_count  bigint
    ) ON COMMIT DROP;

    -- Loop through each user table and view (exclude system schemas)
    FOR rec IN
        SELECT table_schema, table_name, table_type
        FROM information_schema.tables
        WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
          AND table_type IN ('BASE TABLE', 'VIEW')
        ORDER BY table_name
    LOOP
        BEGIN
            -- Dynamically count the rows for the current table/view
            EXECUTE format('SELECT count(*) FROM %I.%I', rec.table_schema, rec.table_name)
            INTO rec_count;
        EXCEPTION WHEN OTHERS THEN
            -- In case of error (for example, if the view is not countable), set to NULL
            rec_count := NULL;
        END;

        -- Insert the result into our temporary table.
        INSERT INTO temp_table_counts(table_name, row_count)
        VALUES (rec.table_schema || '.' || rec.table_name, rec_count);

        -- Accumulate totals (if count succeeded)
        IF rec_count IS NOT NULL THEN
            total_rows := total_rows + rec_count;
        END IF;
        total_items := total_items + 1;
    END LOOP;

    -- Return the per-table results
    RAISE NOTICE 'Table Name | Row Count';
    FOR rec IN 
        SELECT table_name, row_count 
        FROM temp_table_counts 
        ORDER BY table_name
    LOOP
        RAISE NOTICE '% | %', rec.table_name, rec.row_count;
    END LOOP;

    -- Return the summary totals
    RAISE NOTICE '--------------------------------------';
    RAISE NOTICE 'Total Rows: %, Total Tables/Views: %', total_rows, total_items;
END $$;

And on running this is the kind of result it should return