-----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----- -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
