Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-admin-owner+M42110=pgsql+2Dadmin=arkaria.postgresql.org@postgresql.org>)
 id 1W9IJh-0006LE-53
 for pgsql-admin@arkaria.postgresql.org; Fri, 31 Jan 2014 17:55:49 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-admin-owner+M42110=pgsql+2Dadmin=arkaria.postgresql.org@postgresql.org>)
 id 1W9IJg-0000Zt-IJ
 for pgsql-admin@arkaria.postgresql.org; Fri, 31 Jan 2014 17:55:48 +0000
Received: from makus.postgresql.org ([2001:4800:7903:4::125])
 by malur.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <cs_dba@consistentstate.com>) id 1W9IJf-0000Zm-Co
 for pgsql-admin@postgresql.org; Fri, 31 Jan 2014 17:55:47 +0000
Received: from mail-ig0-f176.google.com ([209.85.213.176])
 by makus.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <cs_dba@consistentstate.com>) id 1W9IJc-0002YT-Ow
 for pgsql-admin@postgresql.org; Fri, 31 Jan 2014 17:55:46 +0000
Received: by mail-ig0-f176.google.com with SMTP id j1so20311333iga.3
 for <pgsql-admin@postgresql.org>; Fri, 31 Jan 2014 09:55:42 -0800 (PST)
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
 d=1e100.net; s=20130820;
 h=x-gm-message-state:message-id:date:from:user-agent:mime-version:to
 :cc:subject:content-type:content-transfer-encoding;
 bh=uc/Ry/xBw09apYYE3mVsfKrL0VeBRr/ramPMHVUNHxg=;
 b=iLyHuT5dcR1NPI6dglCX+M0bRj1Jg2cb6tWW2FeTaF/Mp8bEJpM4PX6DGSfqW1xE97
 H0BiSVtFqOUhItHil3niwo99Pd7I6oLd5u/iDcMdLxBculV0tbRFHiy4f3G3I/eH0/q3
 KEVbZd2pVXOh0Hp7p8pso4ckGKj9P0VLqlyTvATE5Oa4iJzOSb47V1TVHTTdiCTg43Af
 bkisKoD57JFo9plmR8aFAGoqqTT0nE3Eg6+53S1VlWUKUvlpvDiU0Z6fodm7hmE9C4bE
 Tuft/t2xaKJ6p+eYwTvw2sj6rUd80Nzh5n9OkVouPMmvNzGHsZxbSXgNcya/3XeZdNyE
 AGtg==
X-Gm-Message-State: 
 ALoCoQnSdmOqkW5gN3ZFkEn7NcqKvxanMG2wMMFoj5+UNkkbeCkT+kIMNgOB8yvE6yAFgBwEgU60
X-Received: by 10.50.79.168 with SMTP id k8mr41504199igx.18.1391190942401;
 Fri, 31 Jan 2014 09:55:42 -0800 (PST)
Received: from Kevins-MacBook-Pro.local ([67.148.186.194])
 by mx.google.com with ESMTPSA id da10sm33427935igc.1.2014.01.31.09.55.41
 for <multiple recipients>
 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128);
 Fri, 31 Jan 2014 09:55:41 -0800 (PST)
Message-ID: <52EBE39C.4000105@consistentstate.com>
Date: Fri, 31 Jan 2014 10:55:40 -0700
From: CS DBA <cs_dba@consistentstate.com>
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9;
 rv:24.0) Gecko/20100101 Thunderbird/24.0.1
MIME-Version: 1.0
To: pgsql-admin@postgresql.org
CC: cs_dba@consistentstate.com
Subject: odd pg_start_backup() issue
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Pg-Spam-Score: -2.6 (--)
List-Archive: <http://archives.postgresql.org/pgsql-admin>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-admin.postgresql.org>
List-Owner: <mailto:pgsql-admin-owner@postgresql.org>
List-Post: <mailto:pgsql-admin@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-admin>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
X-Mailing-List: pgsql-admin
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

Hi All;

I'm setting up PITR based backups on a production db server.
When the script runs the pg_start_backup it takes forever, like 30minutes+

I can run a checkpoint while waiting for it to complete and it finishes 
almost immediately

For now we're executing the start backup like this (in the automated 
base backup script:
echo "checkpoint; select pg_start_backup('$tag'); checkpoint;" | psql

we tried it like this and id did not help:
echo "checkpoint; select pg_start_backup('$tag');" | psql
so we added the checkpoint after the pg_start_backup command

Next I'll try this (I assume the first checkpoint is simply un-needed):
echo "select pg_start_backup('$tag'); checkpoint;" | psql

However, this  works well and the pg_start_backup finishes in less than 
1minute,
but if I leave the checkpoint command(s) out then we're back to 
30minutes or more for the pg_start_backup...

We're running version 9.2.4 on a Centos 6 server

Thoughts?

Thanks in advance


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin