I wrote some records to a database to do some testing, which worked:
AutoDRS=# insert into job_classification
(dealer_id,date_changed,time_changed,jo
b_id) VALUES ('F65','1-Jul-2007','00:00',generate_series(1,1000000));
INSERT 0 1000000
AutoDRS=# insert into job_classification
(dealer_id,date_changed,time_changed,jo
b_id) VALUES ('F65','1-Jul-2007','00:00',generate_series(2000000,9000000));
INSERT 0 7000001
But then I immediately went to select from the table:
AutoDRS=# select count(*) from job_classification;
WARNING: could not write block 51773 of 16441/16443/16907
DETAIL: Multiple failures --- write error might be permanent.
ERROR: could not open relation 16441/16443/16907: No such file or directory
CONTEXT: writing block 51773 of relation 16441/16443/16907
AutoDRS=# select * from job_classification;
ERROR: could not open relation 16441/16443/2662: No such file or directory
AutoDRS=# vacuum full analyze;
ERROR: could not open relation 16441/16443/16907: No such file or directory
CONTEXT: writing block 51776 of relation 16441/16443/16907
Now I can't even change to the main postgres database...
AutoDRS=# \c postgres
FATAL: could not open relation 16441/16443/16700: Permission denied
CONTEXT: writing block 56798 of relation 16441/16443/16700
Previous connection kept
AutoDRS=# \q
And now I can't even reconnect at all...
C:\Program Files\PostgreSQL\8.3\bin>psql -U postgres
Password for user postgres:
psql: FATAL: could not open relation 16441/16443/16907: No such file or
directo
ry
CONTEXT: writing block 51779 of relation 16441/16443/16907
My pg_log is full of similar messages:
T:2007-12-07 15:45:52.781 WST D: U: >>>ERROR: could not open relation
16441/16443/16700: No such file or directory
T:2007-12-07 15:45:52.781 WST D: U: >>>CONTEXT: writing block 56799 of
relation 16441/16443/16700
T:2007-12-07 15:45:52.781 WST D: U: >>>WARNING: could not write block
56799 of 16441/16443/16700
T:2007-12-07 15:45:52.781 WST D: U: >>>DETAIL: Multiple failures ---
write error might be permanent.
T:2007-12-07 15:45:53.781 WST D: U: >>>ERROR: could not open relation
16441/16443/16700: No such file or directory
T:2007-12-07 15:45:53.781 WST D: U: >>>CONTEXT: writing block 56799 of
relation 16441/16443/16700
T:2007-12-07 15:45:53.781 WST D: U: >>>WARNING: could not write block
56799 of 16441/16443/16700
T:2007-12-07 15:45:53.781 WST D: U: >>>DETAIL: Multiple failures ---
write error might be permanent.
T:2007-12-07 15:45:54.781 WST D: U: >>>ERROR: could not open relation
16441/16443/16700: No such file or directory
T:2007-12-07 15:45:54.781 WST D: U: >>>CONTEXT: writing block 56799 of
relation 16441/16443/16700
T:2007-12-07 15:45:54.781 WST D: U: >>>WARNING: could not write block
56799 of 16441/16443/16700
T:2007-12-07 15:45:54.781 WST D: U: >>>DETAIL: Multiple failures ---
write error might be permanent.
I assume I somehow fubar'd something.
I've checked permissions on all the files and the postgres user that the
service runs under has full control of all directories.
One thing I did do was create a directory on a different disk, called
pg_xlog, shutdown pg, copied all files from the existing pg_xlog under
the data directory and deleted that and recreated it as a junction
point. At that stage I had neglected to create the archive_status
directory which pg did start complaining about:
T:2007-12-07 15:00:43.718 WST D: U: >>>FATAL: could not open archive
status directory "pg_xlog/archive_status": No such file or directory
T:2007-12-07 15:00:43.718 WST D: U: >>>LOG: archiver process (PID 7432)
exited with exit code 1
T:2007-12-07 15:01:23.484 WST D: U: >>>FATAL: could not open archive
status directory "pg_xlog/archive_status": No such file or directory
T:2007-12-07 15:01:23.484 WST D: U: >>>LOG: archiver process (PID 5444)
exited with exit code 1
It was creating files in the pg_xlog directory, and after creating the
archive_status directory it is now archiving them off to my WAL_Archive
directory.
So.. what have I done? Why did my insert statement claim it inserted all
the records when the logfile tells me that it couldn't write the blocks.
This is a fresh install of pg 8.3-beta4 on weendoze server 2003 (new
server too, no pre-existing database system) so I can just blast it and
restart, but I'm curious to know what I accidentally deleted since I'm
99.41421356237309504% sure I didn't delete anything other than some
files out of my WAL_Archive directory.
--
Paul Lambert
Database Administrator
AutoLedgers - A Reynolds & Reynolds Company
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
