--_07489d36-2893-4a0a-a335-f3fa68afb70d_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hello=2C
I am running Postgresql 8.1.5 on a Win Server 2003 machine=2C and am
1) running a pg_dump every day at 2am (during lowest usage time - practical=
ly idle time)here is a script i use (this is in turn run from Scheduled Tas=
ks in control panel):------------- [at] ECHO OFF
ECHO.
ECHO This is a batch file
[at] For /F "tokens=3D3 delims=3D/ " %%A in ('Date /t') do set dd=3D%%A
[at] For /F "tokens=3D2 delims=3D/ " %%B in ('Date /t') do set mm=3D%%B
[at] For /F "tokens=3D4 delims=3D/ " %%C in ('Date /t') do set yyyy=3D%%C
[at] For /F "tokens=3D1 delims=3D:" %%A in ('Time /t') do set hh=3D%%A
[at] For /F "tokens=3D2 delims=3D: " %%B in ('Time /t') do set min=3D%%B
[at] For /F "tokens=3D3 delims=3D: " %%C in ('Time /t') do set ampm=3D%%C
set tmp=3D%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=2C=
i end up with 600+ locks. These locks all have the same pid=2C 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=2C and eventually crashes a web server that is co=
nnected to it. From the script=2C can you see if i'm doing something wrong?
2) I also have auto - vacuuming on. Sometimes (can't seem to trace the exac=
t cause of this one) the command "vacuum" ends up in pg_stat_activity in th=
e current_query column=2C and does not get released unless i bring postgres=
itself down. Instead of ending=2C the vacuum ties up a process. These accu=
mulate 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 Messenger faster on the MSN homepage
http://go.microsoft.com/?linkid=3D9677405=
--_07489d36-2893-4a0a-a335-f3fa68afb70d_
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<html>
<head>
<style>
..hmmessage P
{
margin:0px=3B
padding:0px
}
body.hmmessage
{
font-size: 10pt=3B
font-family:Verdana
}
</style>
</head>
<body class=3D'hmmessage'>
<span class=3D"Apple-style-span" style=3D"font-family: Tahoma=2C Verdana=2C=
Arial=2C sans-serif=3B "><div><br></div><div>Hello=2C</div><div><br></div>=
<div>I am running Postgresql 8.1.5 on a Win Server 2003 machine=2C and am&n=
bsp=3B</div><div><br></div><div>1) running a pg_dump every day at 2am (duri=
ng lowest usage time - practically idle time)</div><div>here is a script i =
use (this is in turn run from Scheduled Tasks in control panel):</div><div>=
-------------</div> [at] ECHO OFF<br>ECHO.<br>ECHO This is a batch file<br> [at] For =
/F "tokens=3D3 delims=3D/ " %%A in ('Date /t') do set dd=3D%%A<br> [at] For /F "=
tokens=3D2 delims=3D/ " %%B in ('Date /t') do set mm=3D%%B<br> [at] For /F "toke=
ns=3D4 delims=3D/ " %%C in ('Date /t') do set yyyy=3D%%C<br> [at] For /F "tokens=
=3D1 delims=3D:" %%A in ('Time /t') do set hh=3D%%A<br> [at] For /F "tokens=3D2 =
delims=3D: " %%B in ('Time /t') do set min=3D%%B<br> [at] For /F "tokens=3D3 del=
ims=3D: " %%C in ('Time /t') do set ampm=3D%%C<br>set tmp=3D%yyyy%%mm%%dd%%=
hh%%min%%ampm%<br>echo Current time is: %yyyy%-%mm%-%dd% %hh%:%min% %ampm%<=
br>echo File name will be: testdump2_%tmp%.txt<br><br>cd C:\Program Files\P=
ostgreSQL\8.1\bin<br>pg_dump -U postgres --inserts athenav3DB>=3Bc:\BKP\P=
ENELOPE-v3-dump2_%tmp%.txt<br>echo The pg_dump is complete<br>ECHO<br>CLOSE=
<br>EXIT</span><div><font class=3D"Apple-style-span" face=3D"Tahoma=2C Verd=
ana=2C Arial=2C sans-serif">-----------------</font></div><div><font class=
=3D"Apple-style-span" face=3D"Tahoma=2C Verdana=2C Arial=2C sans-serif">whe=
n i check out the pg_locks view the next morning=2C i end up with 600+ lock=
s. These locks all have the same pid=2C and I have verified that it is the =
exact time when the pg_dump was run by querying pg_stat_activity for that p=
rocpid. These in turn cause the # of connections in pg_stat_activity to gro=
w=2C and eventually crashes a web server that is connected to it. From the =
script=2C can you see if i'm doing something wrong?</font></div><div><font =
class=3D"Apple-style-span" face=3D"Tahoma=2C Verdana=2C Arial=2C sans-serif=
"><br></font></div><div><font class=3D"Apple-style-span" face=3D"Tahoma=2C =
Verdana=2C Arial=2C sans-serif"><br></font></div><div><font class=3D"Apple-=
style-span" face=3D"Tahoma=2C Verdana=2C Arial=2C sans-serif">2) I also hav=
e auto - vacuuming on. Sometimes (can't seem to trace the exact cause of th=
is one) the command "vacuum" ends up in pg_stat_activity in the current_que=
ry column=2C and does not get released unless i bring postgres itself down.=
Instead of ending=2C the vacuum ties up a process. These accumulate and al=
so cause system failures. Is there a way to run/configure auto-vacuum to ex=
it instead of hanging and tying up the said process?</font></div><div><font=
class=3D"Apple-style-span" face=3D"Tahoma=2C Verdana=2C Arial=2C sans-seri=
f"><br></font></div><div><font class=3D"Apple-style-span" face=3D"Tahoma=2C=
Verdana=2C Arial=2C sans-serif"><br></font></div><div><font class=3D"Apple=
-style-span" face=3D"Tahoma=2C Verdana=2C Arial=2C sans-serif">Thank you in=
advance!</font></div><div><font class=3D"Apple-style-span" face=3D"Tahoma=
=2C Verdana=2C Arial=2C sans-serif"><br></font></div><div><font class=3D"Ap=
ple-style-span" face=3D"Tahoma=2C Verdana=2C Arial=2C sans-serif"><br></fon=
t></div>
<hr />New! Open Hotmail faster on the new <a href=3D'http://g=
o.microsoft.com/?linkid=3D9677400' target=3D'_new'>MSN homepage!</a></body>
</html>=
--_07489d36-2893-4a0a-a335-f3fa68afb70d_--
