Thread: Strange results with pg_restore

I've already tested that on a Centos 5.7 VM and it works fine. But well, yesterday i was practicing creation of backups and restoring backups in my laptop with the next specs:

Core i7 2.2 Ghz

8GB RAM

30GB empty space

Windows 8.1

In my laptop i also have PostgreSQL 9.3. First i used the sample database pgdellstore, what i did is that i created a database, and ran the SQL file of the database. After that, i created a backup of that database pgdellstore with pg_dump, a custom format, without compression:

pg_dump -h localhost -p 5432 -U postgres -W -Fc -Z0 -C -d pgdellstore > pgdellstore.backup

And everything as normal. After that i created a second database and restored my custom backup with pg_restore trying to speed up using -j option:

pg_restore -h localhost -p 5432 -U postgres -W -d pgdellstore -j4 pgdellstore.backup

Also, before that, i backed up my postgresql.conf and edited the current one with the next parameters, according to some research that i did in google about performance tuning of pg_restore:

And it restored it in 2 seconds i think, for me it was extremely fast (is a database of 25MB aproximately) and i had doubts, so i have some queries to check database tables size and i compared the results of the first database (the one that i created with original sql script) and the second one (the one that i created using a custom backup of the first one) and i got this:

DB created with original SQL script:

 schemaname |  tablename   | reltuples |  tamanio   | tamanioord

DB created with custom backup based on first db

This is the query that i used to get those results:

As you can see, there are certain variations in size, the number of tuples remains, but the fields tamanio (formatted size) and tamanioord (size without format) have a difference, for example the table customers has 5016 kB versus 4992 kB . I'm afraid that in someway i lost data or something like that, or according to your experience that can be normal and doesn't mean that information is corrupted or something, because tomorrow when i will do that with productions databases it will be a chaos if information gets corrupted.

Also, my second concern is the next: 4 months ago, i did a routine maintenance of those production database, and first as usual i backup all the databases. I took one of the backups of a development database for testing purposes. The backup file weights 279MB and the database is like 1.8 GB  . So, yesterday i also practiced restoring this backup with pg_restore in a new database, with the same configuration from above. The command used was this:

pg_restore -h localhost -p 5432 -U postgres -W --create -d debdbrest -j4 devdb.backup

And it took only 1:30 mins aproximately. For me is suspiciously fast becuse when i was reading about pg_restore and how to enhace performance during backup restoring, i found various cases in previous postgresql versions about pg_restore is too slow, and also, i tried the same restore of the same backups 2 days ago but without postgresql.conf parameters modified and without using -j flag, and it started and after 2 hours it hadn't finished, so i cancelled; also in the first try i noted that it printed a lot of output in the console and in this second try it didn't print anything.

But that time reduction from more than 2 hours to 1:30 mins is too suspicious for me, but i don't know if, as the previous question that i have, you consider that this is normal (a backup of 279MB of a database of 1840MB restored in just 1.30 min).

Regards.