Hi everybody,
Making backups on PostgreSQL takes few seconds and generates a compressed file of 80MB (for my database). But when I restore it, PgAdmin is frozen (Not Responding) and it takes ages: around 45mins
It’s restored correctly though.
Is that normal?
When I restore I just do right click on the database, then Restore and I select the compressed file.
Cheers.
I’ve tried from Windows command line. I used the command:
pg_restore.exe -h localhost -p 5432 -U postgres -d db_ag -v db_ag-20090630.backup
It seems to behave the same (as fast, with almost not CPU activity) but at least I get feedbacks printed in the console window.
Apparently it takes a lot of time to restore a table of 266807 rows.
45 minutes for a restore of that size is very long.
Do you have a lot of indices on this table? Any triggers ?
No nothing special, just simple text.
But when I restore other database It’s also slow. It looks more like a hardware speed problem or configuration problem
I checked my hard drive activity: never goes over 1MB/s during the restore process
Apparently it looks like it because of the hard drive which is at 100%
When I do nothing:
When I run a restore:
But I don’t understand why it’s so slow. There is nothing running on this server except a small SVN server. The hard drive is a standard 7200rpm one.
Hi Foobrother,
What are your shared_buffers, wal_buffers and checkpoint_segments settings ?
( see the postgresql.conf or execute the SQL “show ;” in a SQL terminal)
Also can you post the output of the restore ?
