Hi PG Listers,
Postgres newbie here with a DB maintenance problem. I'm running a nightly maintenance script on a PG database with ~50 tables and ~2.8 million rows distributed rather unevenly across those tables. It is updated hourly with 7-10k new rows per hour.
Every night I run a bash script that contains sequential psql calls to do various maintenance jobs on the DB. The hourly updating is paused during this maintenance time. The maintenance script:
1. Clusters several of the indexes on the largest 5 tables
2. Deletes data older than 2 weeks from a subset (10) of the tables
3. Reindexes the tables I've deleted info from (10)
4. Vacuum full analyze on the entire DB
This script has been running successfully for several months (70-90 min each night). Out of the blue I'm now getting the following error message after each psql command, except the last vacuum full analyze command:
ERROR: could not create relation 1663/835021/4294967254: File exists
The last set of numbers iterates upwards slowly while the first two sets have not changed over the several nights that this problem has occurred. The script eventually does finish its work but ~3x slower. Subsequent hourly updates run smoothly as do normal database queries.
I have tried rebooting the server and also restarting postmaster with no success. I have also run several of the psql command individually and have had no problems. Other databases on the same server run similar nightly maintenance routines and have not reported any similar issues. Having a hard time finding others who have run into this problem? Any advice is greatly appreciated - Thanks!
Postgres 8.0.8 with
PostGIS 1.0.4 on
FC 4
Jesse
--
Jesse Cleary
Department of Marine Sciences
UNC Chapel Hill
334 Chapman Hall
(919) 962-4987
jcleary@xxxxxxxxxxxxx
