Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Y9DbS-0003SB-PV for pgsql-admin@arkaria.postgresql.org; Thu, 08 Jan 2015 13:58:22 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Y9DbR-0005LP-MU for pgsql-admin@arkaria.postgresql.org; Thu, 08 Jan 2015 13:58:21 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1Y9DbQ-0005LI-K7 for pgsql-admin@postgresql.org; Thu, 08 Jan 2015 13:58:20 +0000 Received: from nm3-vm6.bullet.mail.ne1.yahoo.com ([98.138.91.96]) by magus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1Y9DbM-0005EK-33 for pgsql-admin@postgresql.org; Thu, 08 Jan 2015 13:58:19 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ymail.com; s=s2048; t=1420725493; bh=pIOm6SOebu+FoHznx4zBhPu+C1hdypA6P1Y4TD3rpZA=; h=Date:From:Reply-To:To:In-Reply-To:References:Subject:From:Subject; b=Gvs4Uly5N8XLDAUrAnd7BvmSOHiREx+Bdnv9cndPlDAjlp4d7YY+I4iT8BosvAizFYmz9AxR9gSJrz5S5841pCcgyLDSxw30I0pBIlLFboIqjyXEs4XFLVsSKwsXk7vdE17tVt5WGfUFy15ofgd6fqZp6A4vX9FACjgu0PysH2L1/NyXIgWPxeA6OkRmCgb96DWZF/D9sdCCq/f3mX3uO8ARexsJzv2A84AaCDcI340kH7eU2RvXVmJoESEeOxVEVDSdh7AQqssPd3eo4FqGN5SzclmZkMVObAYqgNwS9bZBA71Y8v+W4kT70kh9VGtenCViRT+Q/uB/wPswz7eRcw== DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s2048; d=ymail.com; b=iRMwqsE98p/YhzLsN7zQFJPuKWFKgMNNtx6DI/SSRuq4X/6XU/HVBVoER7Wsgw8Id2Brbkkwqar+hzNV1XmgqIihLR+J9XhhxFaL86/UPhAVmv0saQqCOyOrNDqwpHsQuCbKJyOyvbX8GCzVvqZK34BbUP6lmaX7BH5dpwPZ21D8mxzOIvgjGkZJyy89jj6WxM6+bDhEcQFzNhbpF6b+ncwH2sms3USatOR/3e/EJ34MmORqcxLUnlsJiM5AVmoQL/UFbujDOO9QV51yfLGSpTjTvi+qQGljCDTzM4UuYfF+FgSZnjJZHvc+S9frsBXi6gdEAkfJdIB0TZBCYSiGhg==; Received: from [98.138.101.128] by nm3.bullet.mail.ne1.yahoo.com with NNFMP; 08 Jan 2015 13:58:13 -0000 Received: from [98.138.226.165] by tm16.bullet.mail.ne1.yahoo.com with NNFMP; 08 Jan 2015 13:58:13 -0000 Received: from [127.0.0.1] by omp1066.mail.ne1.yahoo.com with NNFMP; 08 Jan 2015 13:58:13 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 420840.40640.bm@omp1066.mail.ne1.yahoo.com X-YMail-OSG: g3UJnukVM1mxKaXkhc7VWDRo51EK5IRszPFDH.055g2g5JhPVhQcLU2m7YRn1QX 1pxvIjwM3YYtJe5u23.Yjkaue7dMFfNJ4SpBia0vHXtZVFAhT4.kmLhytxfr4e.So2GapMh_NXhn xkZzo_MPK6QrN9ZWNATGaXHIqEAhl5mdRlu7h7f_LcvVJSB65Gi9lL9hr7wudN5uuBtJp9d07LBF X8RqaIA.RGOIcELyCfJzl_luLwIAIul0ISyeRYBPE66jvWT265e8UZEQws_k1hpCy2OD1ckwgnHq AEdB2px8dwxTsZ2QDuJrbDfvX3gfw8OcoIk7sKz9ob_VIU3uf7BgFyHefQXQJwCUs0TEaB.n6FS9 hiqILUHE4q715yVp._syvpviGOKKRngB90ydXXenmg6D8jqPx.U70L2Z4Yewv9Hzx4UZHAlgMrCe C_8LoTdRkf.NpOY7D4sNENSIQd0etMkJBAyEobwL_weqEaAOt1GtYOEgrxUD8TZJ.Bs9PMqzd1yl Rg1XbpCnfP4ZKu_zuUtj1MA2u6qj5ieb8ipTT1S_W3zyDa.YON8.zGDBFf35jNBHWa6yB6c9ngF2 N6qrULAZXBDSQkQO0.k0J_AtWaEZXCEJ3g3IfEOhBO2T9TV7HENeKg9J6Mgrl6Tci0PAqs3DcEmL M00fP4G9Bpc7ekzB_BOgbPeQ- Received: by 98.138.105.222; Thu, 08 Jan 2015 13:58:12 +0000 Date: Thu, 8 Jan 2015 13:58:11 +0000 (UTC) From: Kevin Grittner Reply-To: Kevin Grittner To: Robert Burgholzer , pgsql-admin Message-ID: <844546511.3807408.1420725491363.JavaMail.yahoo@jws10097.mail.ne1.yahoo.com> In-Reply-To: References: Subject: Re: PostgreSQL Dump rate is too slow MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit Content-Length: 1254 X-Pg-Spam-Score: -2.0 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org Robert Burgholzer wrote: > To elaborate, I have found the rsync used in my (most?) streaming > replication setups is 10x or more faster than pg_dump. If your > job specs required a pg_dump output file it would be fairly simple > to do the following (moree or less): > 1. setup a replicant (even on the same machine but different > drive if you wanted) > 2. make sure the replicant is off > 3. rsync the data dir to the replicant > 4. turn on the replicant > 5. execute pg_dump on the replicant If the original cluster is running, unless the steps for a PITR backup are followed, that rsync could generated a corrupted database. The corruption may not be immediately apparent, and might not happen every time, but the above is not safe without a few more steps. http://www.postgresql.org/docs/9.2/interactive/continuous-archiving.html This is not intended as a complete list, but among other things archiving should be working first, the rsync should exclude the postmaster.pid file and the files under the pg_xlog directory, rsync should be preceded by pg_start_backup() and followed by pg_stop_backup(), and a recovery.conf file is needed. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin