On Sun, Feb 27, 2011 at 2:52 AM, Alban Hertroys <dalroi@xxxxxxxxxxxxxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
