Hi:
I'm in the midst of upgrading from 8.1.5
to 8.2.1 usng pg_dumpall and pg_restore.
My database is about 95 G in size and contains
several very large tables having indexed columns of
the geometry datatype (PostGis). Otherwise,
my database is pretty conventional. My problem:
the restore is taking forever. It's now day
12 and about 86 G has been restored.
Here are some details:
1) Hardware:
SunOS 5.9 Generic_117171-07 sun4u sparc
SUNW,Sun-Fire-V240
System Configuration: Sun Microsystems sun4u Sun Fire V240
System clock frequency: 167 MHZ
Memory size: 2GB
==================================== CPUs ====================================
E$ CPU CPU Temperature
CPU Freq Size Implementation Mask Die Amb. Status
Location
--- -------- ---------- ------------------- ----- ---- ---- ------
--------
0 1503 MHz 1MB SUNW,UltraSPARC-IIIi 3.4 - - online
MB/P0
1 1503 MHz 1MB SUNW,UltraSPARC-IIIi 3.4 - - online
MB/Pa
top reveals:
load averages: 0.04, 0.04, 0.05
48 processes: 47 sleeping, 1 on cpu
CPU states: 95.5% idle, 2.4% user, 2.1% kernel, 0.0% iowait, 0.0% swap
Memory: 2048M real, 1403M free, 118M swap in use, 3384M swap free
PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND
29520 postgres 1 60 0 59M 44M sleep 913:46 2.26% postgres
etc, etc.
2) Dump/restoration commands: pg_dumpall (no options) and
pg_restore (no options).
3) Restoration progress as of today:
day gigabytes restored (cumulative)
--- -------------------------------
1
2 7.7
3 17
4 26
5 35
6 43
7 52
8 57
9 83
10 84
11 85
12 86
In the beginning, I was getting about
9 G restored daily. But on day 9 and thereafter,
the rate slowed to 1 G. On day 9, the
restoration of the indices began. My biggest
indices are those covering the geometry columns.
My question: is my experience here atypical?
Thanks!
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
