On Sun, Feb 27, 2011 at 2:52 AM, Alban Hertroys <dal...@solfertje.student.utwente.nl> wrote: Thank you for your kind replies.
> I noticed in your table definition that you seem to store timestamps in > text-fields. Restoring those from text-fields shouldn't make any difference, > but perhaps your locales are set up differently between the machines and > cause some type of conversion to take place? OK, Alban, I'm game. How would I check how locales are set up? Adrian, I found pg_indexes_size() is only in 9 (I have 8.4) but I got the same information from a query based on http://www.issociate.de/board/post/478501/How_much_space_do_database_objects_take_up_in_data_files.html I used: SELECT nspname, relname, pg_size_pretty(tablesize) AS tablesize, pg_size_pretty(indexsize) AS indexsize, pg_size_pretty(toastsize) AS toastsize, pg_size_pretty(toastindexsize) AS toastindexsize FROM (SELECT ns.nspname, cl.relname, pg_relation_size(cl.oid) AS tablesize, COALESCE((SELECT SUM(pg_relation_size(indexrelid))::bigint FROM pg_index WHERE cl.oid=indrelid), 0) AS indexsize, CASE WHEN reltoastrelid=0 THEN 0 ELSE pg_relation_size(reltoastrelid) END AS toastsize, CASE WHEN reltoastrelid=0 THEN 0 ELSE pg_relation_size((SELECT reltoastidxid FROM pg_class ct WHERE ct.oid = cl.reltoastrelid)) END AS toastindexsize FROM pg_class cl, pg_namespace ns WHERE cl.relnamespace = ns.oid AND ns.nspname NOT IN ('pg_catalog', 'information_schema') AND cl.relname IN (SELECT table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE')) ss ORDER BY tablesize+indexsize+toastsize+toastindexsize DESC; Here is what I see: nspname | relname | tablesize | indexsize | toastsize | toastindexsize ------------------------+----------------------------------+------------+------------+------------+---------------- public | big | 744 MB | 737 MB | 48 GB | 278 MB public | big | 503 MB | 387 MB | 99 GB | 278 MB Check out that toastsize delta. What makes up TOAST? How can I compare the two TOAST tables in detail? Tom suggested pgstattuple: table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent -----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+-------------- 779689984 | 1628348 | 500584290 | 64.2 | 30111 | 8275133 | 1.06 | 243295444 | 31.2 <-- database A (source, 50 GB) 527835136 | 1628348 | 500584290 | 94.84 | 0 | 0 | 0 | 9492072 | 1.8 <-- database B ( target, 100 GB) I used "dumpe2fs" to check the filesystems - block size is 4096 on both servers. One filesystem is on a hardware raid device, and one is on a software raid device. Thanks, Aleksey -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
