Re: database is bigger after dump/restore - why? (60 GB to 109 GB) - Mailing list pgsql-general

On Sun, Feb 27, 2011 at 2:52 AM, Alban Hertroys
<dalroi@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'tmake any difference, but perhaps your locales are set up differently between the machines and cause some type
ofconversion 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