Re: pg_dump makes our system unusable - any way to pg_dump in the middle of the day? (postgres 8.4.4)

I'm using cruisecontrol and ant to back up our database at certain times on certain days of the week. Cruisecontrol sends out completion emails when it's done. I don't think pgsql has a good built in way to schedule backups. Cruisecontrol offers supreme flexibility with relatively little work.

I'll post my config files from ant and cruisecontrol that handle the backup in case you're interested. Cruisecontrol runs as a service on windows and linux and periodically checks if it should run its job. It can check a file for modifications or simply run the job at certain times. I have it doing the backup job
At 6am on Wed and Sat. On those times it fires off an Ant job which does the actual db backup using pg_dump ( see ant config ). Cruisecontrol comes packaged with the Ant program. This might be overkill but it's a valuable skill to be able to use cruisecontrol and ant to automate just about anything.

http://cruisecontrol.sourceforge.net/

Cruisecontrol config:

<?xml version="1.0" encoding="UTF-8"?>

<cruisecontrol>
<property name="machine.name" value="meqdbserver-dt" />
<property name="webbase" value="http://${machine.name}:8080/" />
<property name="buildroot.dir" value="C:\systest\" />
<property name="antroot.dir" value="${buildroot.dir}ant\" />
<property name="anthome.dir" value="${antroot.dir}apache-ant-1.7.0" />
<property name="antupdaterbuild.file" value="systest-ant-updater.build" />
<property name="antfilesupdater.dir" value="${buildroot.dir}updater\" />
<property name="build.file" value="${antroot.dir}dbmaintenance-ant.build" />
<property name="cruise.dir" value="C:\Program Files (x86)\CruiseControl\" />
<property name="xsl.dir" value="${buildroot.dir}cruisecontrol\dbbackupxsl\" />
<property name="css.dir" value="${buildroot.dir}cruisecontrol\css\" />

<project name="Backup Regression Testing Database" buildafterfailed="false" requireModification="false">
<schedule>
<ant
anthome="${anthome.dir}"
time="0600"
day="wednesday"
buildfile="${build.file}"
target="dump"
uselogger="true" />
<ant
anthome="${anthome.dir}"
time="0600"
day="saturday"
buildfile="${build.file}"
target="dump"
uselogger="true" />
</schedule>

<publishers>
<htmlemail mailhost="smtpemail.host.com"
returnaddress="${machine(dot)name}(at)myemail(dot)com"
buildresultsurl="${webbase}cruisecontrol/buildresults/${project.name}"
logdir="${cruise.dir}logs\${project.name}"
xsldir="${xsl.dir}"
failasimportant="true"
css="${css.dir}cruisecontrol.css"
defaultsuffix="@myemail.com">
<ignore user="User" />
<always address="user1" />
<always address="user2" />
</htmlemail>

<artifactspublisher
dir="${antroot.dir}backup_dir"
dest="artifacts/${project.name}"
moveInsteadOfCopy="true"
/>
</publishers>
</project>
</cruisecontrol>

Ant config:

<?xml version="1.0" encoding="UTF-8"?>

<project name="system-orion" default="test">
<property name="pgdump.exe" value="C:\Program Files\PostgreSQL\9.0\bin\pg_dump.exe" />
<property name="db.name" value="testruns" />
<property name="backup.format" value="Fc" />
<property name="temp.dir" value="backup_dir" />

<taskdef resource="net/sf/antcontrib/antlib.xml">
<classpath>
<pathelement location="antcontrib\ant-contrib-1.0b3.jar"/>
</classpath>
</taskdef>

<target name="echo-filesize">
<length file="${file.name}" property="file.length.bytes" />

<script language="javascript">
<![CDATA[
var length_bytes = project.getProperty("file.length.bytes");
var length_kbytes = Math.round((length_bytes / 1024) * Math.pow(10,2))
/ Math.pow(10,2);
var length_mbytes = Math.round((length_kbytes / 1024) * Math.pow(10,2))
/ Math.pow(10,2);
project.setNewProperty("fs.length.kb", length_kbytes);
project.setNewProperty("fs.length.mb", length_mbytes);
]]>
</script>

<echo message="${file.name} is ${fs.length.kb} kilobtyes ( ${fs.length.mb} megabytes )"/>
</target>

<target name="dump" >
<tstamp><format property="time.stamp" pattern="MM-dd-yyyy_hh-mm-ss" /></tstamp>
<property name="backup.name" value="${backup.format}_DB_${db.name}_ON_${time.stamp}.backup" />
<delete dir="${temp.dir}" />
<mkdir dir="${temp.dir}" />
<property name="file.name" value="${temp.dir}\${backup.name}" />
<exec executable="${pgdump.exe}" failonerror="true">
<arg value="-${backup.format}" />
<arg value="--username=username" />
<arg value="--file=${file.name}" />
<arg value="${db.name}" />
</exec>
<antcall target="echo-filesize" />
</target>
</project>

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Aleksey Tsalolikhin
Sent: Thursday, February 24, 2011 5:39 PM
To: pgsql-general
Subject: [GENERAL] pg_dump makes our system unusable - any way to pg_dump in the middle of the day? (postgres 8.4.4)

I read in the pg_dump man page that pg_dump does not block other users accessing the database (readers or writers).

In practice, if I pg_dump our 100 GB database, our application, which is half Web front end and half OLTP, at a certain point, slows to a crawl and the Web interface becomes unresponsive. I start getting check_postgres complaints about number of locks and query lengths. I see locks around for over 5 minutes.

I've had to abort pg_dump as it made the system unusable.

Can I pg_dump without breaking the system?

Best,
-at

--
Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org<mailto:pgsql-general(at)postgresql(dot)org>) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general