Re: Space for pg_dump

Hi,

> Rainer Bauer <usenet(at)munnin(dot)com> writes:
>> Greg Smith wrote:
>>> Since running an entire pgdump can take forever on a big database,
>>> what I
>>> usually do here is start by running the disk usage query at
>>> http://wiki.postgresql.org/wiki/Disk_Usage
>
>> Interesting. However, the query gives an error if the table name
>> contains
>> upper case characters, like in my case "tblConnections":
>
>> ERROR: relation "public.tblconnections" does not exist.
>
>> Replacing all occurences of <relname> by <'"' || relname || '"'>
>> fixes the
>> error.
>
> That still fails if the table name contains double quotes. A proper
> solution is to use the table OID --- I've corrected the example.

If you have big toast tables you get wrong results with the query
suggested
at http://wiki.postgresql.org/wiki/Disk_Usage because it takes the
toasted
values not into account.

Simple example (take a look at the first row -> public.media):

SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_relation_size(C.oid)) AS "size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND nspname !~ '^pg_toast'
ORDER BY pg_relation_size(C.oid) DESC
LIMIT 20;
relation | size
---------------------------+------------
public.media | 727 MB
public.identifier_idx | 342 MB
public.media_pk | 190 MB
public.mediateypes_pk | 16 kB
public.mediaformats_uk | 16 kB
public.contentsizes_pk | 16 kB
public.contenttype_pk | 16 kB
public.mediaformats_pk | 16 kB
public.contenttypes | 8192 bytes
public.media_media_id_seq | 8192 bytes
public.contentsizes | 8192 bytes
public.mediaformats | 8192 bytes
public.mediatypes | 8192 bytes
public.vmedia2 | 0 bytes
public.vmedia | 0 bytes
(15 rows)

Now a fixed query which gets the sizes of the related pg_toast_oid and
pg_toast_oid_index too:

SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_relation_size(C.oid)
+ COALESCE((SELECT pg_relation_size(C2.oid) FROM
pg_class C2 WHERE C2.relname = 'pg_toast_' || C.oid ),0::bigint)
+ COALESCE((SELECT pg_relation_size(C3.oid) FROM
pg_class C3 WHERE C3.relname = 'pg_toast_' || C.oid || '_index'),
0::bigint)
) AS "size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND nspname !~ '^pg_toast'
ORDER BY pg_relation_size(C.oid)
+ COALESCE((SELECT pg_relation_size(C2.oid) FROM
pg_class C2 WHERE C2.relname = 'pg_toast_' || C.oid ),0::bigint)
+ COALESCE((SELECT pg_relation_size(C3.oid) FROM
pg_class C3 WHERE C3.relname = 'pg_toast_' || C.oid || '_index'),
0::bigint)
DESC
LIMIT 20;
relation | size
---------------------------+------------
public.media | 164 GB
public.identifier_idx | 342 MB
public.media_pk | 190 MB
public.contenttype_pk | 16 kB
public.contenttypes | 16 kB
public.contentsizes | 16 kB
public.contentsizes_pk | 16 kB
public.mediateypes_pk | 16 kB
public.mediaformats | 16 kB
public.mediatypes | 16 kB
public.mediaformats_pk | 16 kB
public.mediaformats_uk | 16 kB
public.media_media_id_seq | 8192 bytes
public.vmedia | 0 bytes
public.vmedia2 | 0 bytes
(15 rows)

There is a difference of about 163 GB (which is from the toast of
public.media)
relation | size
---------------------------------+------------
pg_toast.pg_toast_6366088 | 162 GB
pg_toast.pg_toast_6366088_index | 1832 MB
public.media | 727 MB

If you have only small or no toast tables the query from the wiki will
be working for you.

regards, jan