Received: from malur.postgresql.org ([2a02:16a8:dc51::56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fuhYO-0002jA-7u for pgsql-admin@arkaria.postgresql.org; Tue, 28 Aug 2018 17:13:20 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fuhYM-0003CH-Al for pgsql-admin@arkaria.postgresql.org; Tue, 28 Aug 2018 17:13:18 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fuhYM-0003B5-14 for pgsql-admin@lists.postgresql.org; Tue, 28 Aug 2018 17:13:18 +0000 Received: from st14p37im-asmtp002.me.com ([17.41.209.31]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1fuhYI-0008Mf-Fc for pgsql-admin@lists.postgresql.org; Tue, 28 Aug 2018 17:13:16 +0000 Received: from process-dkim-sign-daemon.st14p37im-asmtp002.me.com by st14p37im-asmtp002.me.com (Oracle Communications Messaging Server 8.0.2.2.20180531 64bit (built May 31 2018)) id <0PE600K00KPO6200@st14p37im-asmtp002.me.com> for pgsql-admin@lists.postgresql.org; Tue, 28 Aug 2018 17:13:12 +0000 (GMT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=mac.com; s=04042017; t=1535476392; bh=/JUtxMKDEU7cOLw6FCxS0Wubz5jkQ8fvD1O8RdDV6UI=; h=From:Message-id:Content-type:MIME-version:Subject:Date:To; b=kmvoA/NVT2GpI10O182+NyhyerdQLj2PN9Mkib5/JD7FefstxdFMc8y/S7W5Cb8JJ 6GwOW9lc+xbNxUrOZCvcHqZWb5CR2bAp9wFi0Hzz2FHR0JyNEJkDXSEconpfQ9ul/x FG7H34pPtMUgIL6TXuv+uMjB0F8YPYzMTlWC0Ltz/1ZH7tNpRiUVLEuOo8ZyOk2Is1 wGGP3ghNPXOekdjfXsiVSBS+es/lWtjzBldGZvlWpKH/qEJ34Mg8Vr67ZzOQGgmFg7 V5zrbygaG5+tNBu2HC9MmaV2AmBs93Yw2kw7ZDZx+g5L8RbJuRW4YwKUVjBYC5RQGk bXLxq3Grirg/g== Received: from icloud.com ([127.0.0.1]) by st14p37im-asmtp002.me.com (Oracle Communications Messaging Server 8.0.2.2.20180531 64bit (built May 31 2018)) with ESMTPSA id <0PE600BJYL5WJX20@st14p37im-asmtp002.me.com>; Tue, 28 Aug 2018 17:13:10 +0000 (GMT) X-Proofpoint-Spam-Details: rule=notspam policy=default score=0 spamscore=0 clxscore=1015 suspectscore=0 malwarescore=0 phishscore=0 adultscore=0 bulkscore=0 classifier=spam adjust=0 reason=mlx scancount=1 engine=8.0.1-1707230000 definitions=main-1808280172 X-Proofpoint-Virus-Version: vendor=fsecure engine=2.50.10434:,, definitions=2018-08-28_07:,, signatures=0 From: Evan Bauer Message-id: <931DC3D5-F530-4626-8191-52780D974833@mac.com> Content-type: multipart/alternative; boundary="Apple-Mail=_A3996CE3-64EB-4201-8785-7F94DDFA4326" MIME-version: 1.0 (Mac OS X Mail 11.5 \(3445.9.1\)) Subject: Re: More efficient pg_restore method? Date: Tue, 28 Aug 2018 13:13:08 -0400 In-reply-to: Cc: pgsql-admin@lists.postgresql.org To: Ron References: <44555188-c787-b441-5c69-c56f72905bf4@gmail.com> <1F9927D1-5C55-48B9-8C6D-ECE3F6BDA3ED@mac.com> X-Mailer: Apple Mail (2.3445.9.1) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_A3996CE3-64EB-4201-8785-7F94DDFA4326 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 Ron, Even with the speed of your WAN circuit, I would try to do all restores = locally. The latency on a WAN connection (especially with all of the = files in a format=3Ddirectory backup) is a concern. The outline of how = I would do it are: rsync or ftp the files to either the database server=E2=80=99s local = storage or to or LAN-connected SAN/NAS storage Verify the copy with a checksum=20 Perform the pg_restore locally Cheers, - Evan Evan Bauer eb@evanbauer.com +1 646 641 2973 Skype: evanbauer > On Aug 28, 2018, at 13:00, Ron wrote: >=20 > On 08/28/2018 11:57 AM, Evan Bauer wrote: >> Ron, >>=20 >> A couple of starting questions: >>=20 >> What is the size and latency of the network pipe between the primary = and backup servers? =20 >=20 > 10Gb WAN. Don't know the latency. >=20 >> What is the size of the database you need to restore?=20 >=20 > 6.5TB dump directory, 3.0TB data/base directory. >=20 >> Is there a reason not to do a network copy of the backup directory = contents to the database server and run the pg_restore locally? >=20 > Option #3!!! I'll research that. >=20 >>=20 >> Cheers, >>=20 >> - Evan >>=20 >> Evan Bauer >> eb@evanbauer.com >> +1 646 641 2973 >> Skype: evanbauer >>=20 >>=20 >>> On Aug 28, 2018, at 12:48, Ron > wrote: >>>=20 >>>=20 >>> Pg 9.6.9 on Linux... >>>=20 >>> Given a backup server storing a "format=3Ddirectory" database = backup, and a database server, should I: >>>=20 >>> Option #1: run pg_restore on the backup server and "push" the data = to the database server via port 5432, or >>> Option #2: have the backup server serve the dump directory via NFS, = and run pg_restore on the database server, pulling the data via nfs = protocol? >>>=20 >>> (It'll be a multi-threaded restore over a 10Gb pipe.) >>>=20 >>> --=20 >>> Angular momentum makes the world go 'round. >>>=20 >>=20 >=20 > --=20 > Angular momentum makes the world go 'round. --Apple-Mail=_A3996CE3-64EB-4201-8785-7F94DDFA4326 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 Ron,

Even = with the speed of your WAN circuit, I would try to do all restores = locally.  The latency on a WAN connection (especially with all of = the files in a format=3Ddirectory backup) is a concern.  The = outline of how I would do it are:

  1. rsync or ftp the files to either the database server=E2=80=99s = local storage or to or LAN-connected SAN/NAS storage
  2. Verify the copy with a checksum 
  3. Perform the pg_restore locally

Cheers,

- Evan

Evan Bauer
eb@evanbauer.com
+1 646 641 2973
Skype: evanbauer


On Aug 28, 2018, at 13:00, Ron <ronljohnsonjr@gmail.com> wrote:

On 08/28/2018 11:57 AM, Evan Bauer wrote:
Ron,

A = couple of starting questions:

  1. What is the size and latency of the network pipe between the = primary and backup servers?  

10Gb WAN.  Don't know the = latency.

  1. What = is the size of the database you need to restore? 

6.5TB dump directory, 3.0TB data/base = directory.

  1. Is = there a reason not to do a network copy of the backup directory contents = to the database server and run the pg_restore = locally?

Option #3!!!  I'll research that.


Cheers,

- Evan

Evan Bauer
eb@evanbauer.com
+1 = 646 641 2973
Skype: evanbauer


On Aug 28, 2018, at 12:48, Ron <ronljohnsonjr@gmail.com> wrote:


Pg 9.6.9 on Linux...

Given a = backup server storing a "format=3Ddirectory" database backup, and a = database server, should I:

Option #1: run = pg_restore on the backup server and "push" the data to the database = server via port 5432, or
Option #2: have the backup server = serve the dump directory via NFS, and run pg_restore on the database = server, pulling the data via nfs protocol?

(It'll be a multi-threaded restore over a 10Gb pipe.)

-- 
Angular = momentum makes the world go 'round.



-- 
Angular = momentum makes the world go 'round.

= --Apple-Mail=_A3996CE3-64EB-4201-8785-7F94DDFA4326--