Greetings,
I have 395M pg_dump from a PostgreSQL 7.4.2 database.
This dump is from one of our customer's servers. There is
a web-based administration UI which has been reported to
be extremely slow and unusable.
To see what's going on with their data I have grabbed a
copy of their nightly pg_dump output and attempting to
restore it on my development box, running PostgreSQL
7.4.12.
My dev box is much slower hardware than the customer's
server. Even with that difference I expected to be able to
pg_restore the database within one day. But no. After
leaving pg_restore running for about 2 days, I ctrl-C'ed
out of it (see copy/paste below along with other info).
I must say, that data was being restored, as I could do
select count(*) on tables which had their data restored and
I would get valid counts back.
The database contains 34 tables. The pg_restore seems to
restore the first 13 tables pretty quickly, but they do not have
many records. The largest amongst them with ~ 17,000 rows.
Then restore gets stuck on a table with 2,175,050 rows.
Following this table another table exists with 2,160,616
rows.
One thing worth mentioning is that the PostgreSQL package
that got deployed lacked compression, as in:
$ pg_dump -Fc dbname > dbname.DUMP
pg_dump: [archiver] WARNING: requested compression not available in
this installation -- archive will be uncompressed
Any suggestions as to what may be the problem here?
I doubt that the minor version mis-match is what's causing
this problem. (I am try this test on another machine with the
same version of PostgreSQL installed on it, and right now,
it is stuck on the first of the two huge tables, and it has
already been going for more than 2 hrs).
I'm open to any ideas and/or suggestions (within reason) :)
Best regards,
--patrick
me@devbox:/tmp$ date
Mon Apr 10 15:13:19 PDT 2006
me@devbox:/tmp$ pg_restore -ad dbname customer_db.DUMP ; date
^C
me@devbox:/tmp$ date
Wed Apr 12 10:40:19 PDT 2006
me@devbox:/tmp$ uname -a
Linux devbox 2.4.31 #6 Sun Jun 5 19:04:47 PDT 2005 i686 unknown
unknown GNU/Linux
me@devbox:/tmp$ cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 8
model name : Pentium III (Coppermine)
stepping : 6
cpu MHz : 731.477
cache size : 256 KB
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 2
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge
mca cmov pat pse36 mmx fxsr sse
bogomips : 1461.45
me@devbox:/tmp/$ cat /proc/meminfo
total: used: free: shared: buffers: cached:
Mem: 527499264 523030528 4468736 0 10301440 384454656
Swap: 1579204608 552960 1578651648
MemTotal: 515136 kB
MemFree: 4364 kB
MemShared: 0 kB
Buffers: 10060 kB
Cached: 374984 kB
SwapCached: 460 kB
Active: 79004 kB
Inactive: 306560 kB
HighTotal: 0 kB
HighFree: 0 kB
LowTotal: 515136 kB
LowFree: 4364 kB
SwapTotal: 1542192 kB
SwapFree: 1541652 kB
postgresql.conf changes on devbox:
checkpoint_segments = 10
log_pid = true
log_timestamp = true
The checkpoint_segments was changed to 10 after
seeing many "HINT"s in PostgreSQL log file about it.
Doesn't seem to have affected pg_restore performance.
