Обсуждение: locking issues with pg_dump and vacuum (win server 2003)
Hello,
I am running Postgresql 8.1.5 on a Win Server 2003 machine, and am
1) running a pg_dump every day at 2am (during lowest usage time - practically idle time)
here is a script i use (this is in turn run from Scheduled Tasks in control panel):
-------------
@ECHO OFFECHO.
ECHO This is a batch file
@For /F "tokens=3 delims=/ " %%A in ('Date /t') do set dd=%%A
@For /F "tokens=2 delims=/ " %%B in ('Date /t') do set mm=%%B
@For /F "tokens=4 delims=/ " %%C in ('Date /t') do set yyyy=%%C
@For /F "tokens=1 delims=:" %%A in ('Time /t') do set hh=%%A
@For /F "tokens=2 delims=: " %%B in ('Time /t') do set min=%%B
@For /F "tokens=3 delims=: " %%C in ('Time /t') do set ampm=%%C
set tmp=%yyyy%%mm%%dd%%hh%%min%%ampm%
echo Current time is: %yyyy%-%mm%-%dd% %hh%:%min% %ampm%
echo File name will be: testdump2_%tmp%.txt
cd C:\Program Files\PostgreSQL\8.1\bin
pg_dump -U postgres --inserts athenav3DB>c:\BKP\PENELOPE-v3-dump2_%tmp%.txt
echo The pg_dump is complete
ECHO
CLOSE
EXIT
-----------------
when i check out the pg_locks view the next morning, i end up with 600+ locks. These locks all have the same pid, and I have verified that it is the exact time when the pg_dump was run by querying pg_stat_activity for that procpid. These in turn cause the # of connections in pg_stat_activity to grow, and eventually crashes a web server that is connected to it. From the script, can you see if i'm doing something wrong?
2) I also have auto - vacuuming on. Sometimes (can't seem to trace the exact cause of this one) the command "vacuum" ends up in pg_stat_activity in the current_query column, and does not get released unless i bring postgres itself down. Instead of ending, the vacuum ties up a process. These accumulate and also cause system failures. Is there a way to run/configure auto-vacuum to exit instead of hanging and tying up the said process?
Thank you in advance!
New! Open Hotmail faster on the new MSN homepage!