Hi,
Rainer Bauer <usenet@xxxxxxxxxx> 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
