Hi,
I'm having a little difficultly with a database that I'm upgrading from
Postgres 7.2.1 to 7.3.4.
On the 7.2.1 server I did:
$ pg_dump fiveminute >fiveminute.db
$ pg_dump bandwidth >bandwidth.db
On the 7.3.2 server I did:
$ psql fiveminute <fiveminute.db
$ psql bandwidth <bandwidth.db
Both appear to import successfully. However if I then try to dump the
full database on 7.3.2 I get:
$ pg_dumpall >full.db
pg_dump: WARNING: ShmemAlloc: out of memory
pg_dump: Attempt to lock table "vs_dfa554862ac" failed. ERROR:
LockAcquire: lock table 1 is out of memory
pg_dumpall: pg_dump failed on bandwidth, exiting
$
I tried just dumping a single database and a different database. I get
the same error (note pg_dump gives no output):
$ pg_dump fiveminute
pg_dump: WARNING: ShmemAlloc: out of memory
pg_dump: Attempt to lock table "vs_surfinsavers" failed. ERROR:
LockAcquire: lock table 1 is out of memory
$
In these cases the postgres server processes don't consume much memory:
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
postgres 31279 0.0 0.0 414852 960 pts/0 S 10:26 0:01
/usr/bin/postmaster -p 5432
postgres 31281 0.0 0.0 415824 868 pts/0 S 10:26 0:00 \_
postgres: stats buffer process
postgres 31282 0.0 0.0 414892 968 pts/0 S 10:26 0:00 | \_
postgres: stats collector process
postgres 32161 98.9 1.2 416076 12476 pts/0 R 12:59 1:13 \_
postgres: postgres fiveminute [local] SELECT
The machine has 1Gb RAM, runs RedHat 9, kernel-smp-2.4.20-20.9, i386
dual PIII. gcc 3.2.2 was the compiler used.
I set the kernel shared memory higher:
$ cat /proc/sys/kernel/shmmax
805306368
In postgresql.conf I have set:
shared_buffers=50000
wal_buffers = 64
I have tried default values for these and also shared_buffers=80000.
I have also tried deleting /var/lib/pgsql, running initdb and
re-importing the data.
Same results every time. 100% reproducable.
The fiveminute database is a 2.9Gb dump file. The bandwidth database is
100Mb dump file. With indexes, 16Gb disk space is required. They are
simple databases with about 3000 tables. Columns are just date, time
and 64-bit integer fields.
Upgrading to postgres 7.3 would be useful. But if I cannot dump the
data after importing it, then I stand no chance of upgrading to 7.4 in
the future.
Does anybody have any thoughts ?
Regards,
Nick.
--
Nick Burrett
Network Engineer, Designer Servers Ltd. http://www.dsvr.co.uk
