No control over max.num. WAL files

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hello

I am trying to move a postgres cluster with 90 databases and around
140GB of data between two servers (8.3.12 -> 8.3.15).

I am using 'pg_dumpall | psql' in the process and everything works ok
until our pg_xlog partition gets full.

According to the documentation [1] we can expect a maximum of
(3 * checkpoint_segments + 1 segment files) WAL files in pg_xlog.

In our system this will be (3 * 128 + 1) = 385 WAL files (~6GB)

We have taken this into account + some extra space.

Our pg_xlog partition is ~8GB and under the restore process 486 WAL
files were created in this partition. The partition got full and
everything crashed.

Our question is: How can we get 486 WAL files generated in our pg_xlog
partition if the documentation says that in the worst case we will get
385 WAL files?

These are the relevant parameters we have changed in postgresql.conf:

archive_mode | off
checkpoint_segments | 128
default_statistics_target | 100
maintenance_work_mem | 512MB
max_fsm_pages | 800000
max_fsm_relations | 8000
shared_buffers | 10GB
wal_buffers | 512kB
wal_sync_method | fdatasync
work_mem | 16MB

And these the relevant error messages:

PANIC: could not write to file "pg_xlog/xlogtemp.25133": No space left
on device
LOG: WAL writer process (PID 25133) was terminated by signal 6: Aborted
LOG: terminating any other active server processes
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back
the current transaction and exit, because another server process exited
abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and
repeat your command.
STATEMENT: CREATE INDEX attachments_textsearch ON attachments USING gin
(textsearchable);
FATAL: the database system is in recovery mode
LOG: all server processes terminated; reinitializing
LOG: database system was interrupted; last known up at 2011-05-20
17:46:18 CEST
LOG: database system was not properly shut down; automatic recovery in
progress
LOG: redo starts at 12/6FD38F70
FATAL: the database system is in recovery mode
LOG: could not open file "pg_xlog/0000000100000013000000B0" (log file
19, segment 176): No such file or directory
LOG: redo done at 13/AFFFFCE8
LOG: autovacuum launcher started
LOG: database system is ready to accept connections

As you can see the last SQL statement before the crash is:
CREATE INDEX attachments_textsearch ON attachments USING gin
(textsearchable);

Maybe the problem is related to this?

Any ideas?, thanks in advance.

[1] http://www.postgresql.org/docs/8.3/interactive/wal-configuration.html

regards,
- --
Rafael Martinez Guerrero
Center for Information Technology
University of Oslo, Norway

PGP Public Key: http://folk.uio.no/rafael/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.14 (GNU/Linux)

iEYEARECAAYFAk3c6FsACgkQBhuKQurGihT/pgCcD5nA8E5VHIHf984VjrHDk3YT
yAAAoIiW5CClJ7CN9bu+Ib89IckHmMEf
=H5W3
-----END PGP SIGNATURE-----