Hi PG Listers,
Postgres newbie here with a DB maintenance problem. I'm running a nightly maintenance script on a PG database with ~50
tablesand ~2.8 million rows distributed rather unevenly across those tables. It is updated hourly with 7-10k new rows
perhour.
Every night I run a bash script that contains sequential psql calls to do various maintenance jobs on the DB. The
hourlyupdating 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
thefollowing 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
thisproblem has occurred. The script eventually does finish its work but ~3x slower. Subsequent hourly updates run
smoothlyas 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
commandindividually and have had no problems. Other databases on the same server run similar nightly maintenance
routinesand have not reported any similar issues. Having a hard time finding others who have run into this problem?
Anyadvice 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@email.unc.edu