First, some basic data:
OS: Red Hat
Version 8.3
Size 15 GB
The directory which contain the PGDATA/data is full, and i move the entire directory data to another partition, and i link symbolic to PGDATA.
Now, when i try to start the server, keep on "startup process recovering "
When i try to connect, this is the message:
psql: FATAL: the database system is starting up
And never go on.
I attach another information that is interesting
---------------------------------------------------------------------------------------------------------------
$ ps aux | grep postgres
root 24586 0.0 0.1 4920 1236 pts/2 S 10:52 0:00 su - postgres
postgres 24587 0.0 0.1 4540 1440 pts/2 S+ 10:52 0:00 -bash
root 27444 0.0 0.1 4920 1240 pts/1 S 14:40 0:00 su - postgres
postgres 27445 0.0 0.1 4536 1436 pts/1 S+ 14:40 0:00 -bash
root 27593 0.0 0.1 4920 1244 pts/3 S 14:44 0:00 su - postgres
postgres 27594 0.0 0.1 4540 1468 pts/3 S 14:44 0:00 -bash
postgres 27725 4.1 0.8 283372 7236 pts/3 S 14:47 0:00 /usr/bin/postgres
postgres 27726 0.0 0.0 13952 696 ? Ss 14:47 0:00 postgres: logger process
postgres 27727 2.0 0.5 283640 4828 ? Ds 14:47 0:00 postgres: startup process recovering 000000010000001A00000074
postgres 27730 0.0 0.1 4260 940 pts/3 R+ 14:48 0:00 ps aux
postgres 27731 0.0 0.0 3920 696 pts/3 S+ 14:48 0:00 grep postgres
root 24586 0.0 0.1 4920 1236 pts/2 S 10:52 0:00 su - postgres
postgres 24587 0.0 0.1 4540 1440 pts/2 S+ 10:52 0:00 -bash
root 27444 0.0 0.1 4920 1240 pts/1 S 14:40 0:00 su - postgres
postgres 27445 0.0 0.1 4536 1436 pts/1 S+ 14:40 0:00 -bash
root 27593 0.0 0.1 4920 1244 pts/3 S 14:44 0:00 su - postgres
postgres 27594 0.0 0.1 4540 1468 pts/3 S 14:44 0:00 -bash
postgres 27725 4.1 0.8 283372 7236 pts/3 S 14:47 0:00 /usr/bin/postgres
postgres 27726 0.0 0.0 13952 696 ? Ss 14:47 0:00 postgres: logger process
postgres 27727 2.0 0.5 283640 4828 ? Ds 14:47 0:00 postgres: startup process recovering 000000010000001A00000074
postgres 27730 0.0 0.1 4260 940 pts/3 R+ 14:48 0:00 ps aux
postgres 27731 0.0 0.0 3920 696 pts/3 S+ 14:48 0:00 grep postgres
---------------------------------------------------------------------------------------------------------------
$ pg_ctl status
pg_ctl: server is running (PID: 27725)
/usr/bin/postgres
pg_ctl: server is running (PID: 27725)
/usr/bin/postgres
---------------------------------------------------------------------------------------------------------------
$ pg_controldata
pg_control version number: 843
Catalog version number: 200904091
Database system identifier: 5409925551577885669
Database cluster state: in crash recovery
pg_control last modified: Wed 23 Jun 2010 11:20:42 AM CLT
Latest checkpoint location: 1A/73839208
Prior checkpoint location: 1A/73839208
Latest checkpoint's REDO location: 1A/7382D978
Latest checkpoint's TimeLineID: 1
Latest checkpoint's NextXID: 0/47874035
Latest checkpoint's NextOID: 51407
Latest checkpoint's NextMultiXactId: 1647347
Latest checkpoint's NextMultiOffset: 3431468
Time of latest checkpoint: Sun 30 May 2010 11:10:30 AM CLT
Minimum recovery ending location: 0/0
Maximum data alignment: 4
Database block size: 8192
Blocks per segment of large relation: 131072
WAL block size: 8192
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum columns in an index: 32
Maximum size of a TOAST chunk: 2000
Date/time type storage: 64-bit integers
Float4 argument passing: by value
Float8 argument passing: by reference
pg_control version number: 843
Catalog version number: 200904091
Database system identifier: 5409925551577885669
Database cluster state: in crash recovery
pg_control last modified: Wed 23 Jun 2010 11:20:42 AM CLT
Latest checkpoint location: 1A/73839208
Prior checkpoint location: 1A/73839208
Latest checkpoint's REDO location: 1A/7382D978
Latest checkpoint's TimeLineID: 1
Latest checkpoint's NextXID: 0/47874035
Latest checkpoint's NextOID: 51407
Latest checkpoint's NextMultiXactId: 1647347
Latest checkpoint's NextMultiOffset: 3431468
Time of latest checkpoint: Sun 30 May 2010 11:10:30 AM CLT
Minimum recovery ending location: 0/0
Maximum data alignment: 4
Database block size: 8192
Blocks per segment of large relation: 131072
WAL block size: 8192
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum columns in an index: 32
Maximum size of a TOAST chunk: 2000
Date/time type storage: 64-bit integers
Float4 argument passing: by value
Float8 argument passing: by reference
---------------------------------------------------------------------------------------------------------------
The last day with transacctin, the log finish at follow:
$ tail PG_DATA/pg_log/postgresql-Sun.log
ERROR: null value in column "protocolo" violates not-null constraint
STATEMENT: INSERT INTO trx_5(nodo, puerto, dnis, ani, protocolo, time, status, service, fecha, trama) VALUES (2, 1, 900, 0, (SELECT pk FROM protocolos WHERE data = "" 247, (SELECT pk FROM status WHERE status = 55), (SELECT pk FROM services WHERE service = 0),now(), (SELECT pk FROM tramas WHERE equipo = 2 AND puerto = 1))
LOG: could not write temporary statistics file "pg_stat_tmp/pgstat.tmp": No space left on device
LOG: could not write temporary statistics file "pg_stat_tmp/pgstat.tmp": No space left on device
LOG: could not write temporary statistics file "pg_stat_tmp/pgstat.tmp": No space left on device
LOG: could not write temporary statistics file "pg_stat_tmp/pgstat.tmp": No space left on device
LOG: could not write temporary statistics file "pg_stat_tmp/pgstat.tmp": No space left on device
LOG: could not write temporary statistics file "pg_stat_tmp/pgstat.tmp": No space left on device
LOG: could not write temporary statistics file "pg_stat_tmp/pgstat.tmp": No space left on device
LOG: could not write temporary statistics file "pg_stat_tmp/pgstat.tmp": No space left on device
LOG: could not write temporary statistics file "pg_st-bash-3.2$
STATEMENT: INSERT INTO trx_5(nodo, puerto, dnis, ani, protocolo, time, status, service, fecha, trama) VALUES (2, 1, 900, 0, (SELECT pk FROM protocolos WHERE data = "" 247, (SELECT pk FROM status WHERE status = 55), (SELECT pk FROM services WHERE service = 0),now(), (SELECT pk FROM tramas WHERE equipo = 2 AND puerto = 1))
LOG: could not write temporary statistics file "pg_stat_tmp/pgstat.tmp": No space left on device
LOG: could not write temporary statistics file "pg_stat_tmp/pgstat.tmp": No space left on device
LOG: could not write temporary statistics file "pg_stat_tmp/pgstat.tmp": No space left on device
LOG: could not write temporary statistics file "pg_stat_tmp/pgstat.tmp": No space left on device
LOG: could not write temporary statistics file "pg_stat_tmp/pgstat.tmp": No space left on device
LOG: could not write temporary statistics file "pg_stat_tmp/pgstat.tmp": No space left on device
LOG: could not write temporary statistics file "pg_stat_tmp/pgstat.tmp": No space left on device
LOG: could not write temporary statistics file "pg_stat_tmp/pgstat.tmp": No space left on device
LOG: could not write temporary statistics file "pg_st-bash-3.2$
---------------------------------------------------------------------------------------------------------------
$ psql
psql: FATAL: the database system is starting up
psql: FATAL: the database system is starting up
---------------------------------------------------------------------------------------------------------------
In the server i don't have lsof, but this is the information that i get:
With vmstat, the %wa is over 50%. %CPU very slow, postgres don't use over 2%.
In the /proc/27725/ ( pid of postgres 27727 2.0 0.5 283640 4828 ? Ds 14:47 0:00 postgres: startup process recovering 000000010000001A00000074) the file io have a intensive rate of write: aprox 1GB/20 minutes.
Any suggest??
--
Sebastian Machuca
Estudiante Ingeniería Civil en Computación
+56 9 77449117
http://twitter.com/serroba
Sebastian Machuca
Estudiante Ingeniería Civil en Computación
+56 9 77449117
http://twitter.com/serroba
