Search Postgresql Archives

No control over max.num. WAL files

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



-----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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux