Re: Space for pg_dump

Hi,

> Rainer Bauer <usenet@munnin.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