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 1fuhM2-0002B9-U4 for pgsql-admin@arkaria.postgresql.org; Tue, 28 Aug 2018 17:00:35 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fuhM0-0003Wn-JG for pgsql-admin@arkaria.postgresql.org; Tue, 28 Aug 2018 17:00:32 +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 1fuhM0-0003Wg-9t for pgsql-admin@lists.postgresql.org; Tue, 28 Aug 2018 17:00:32 +0000 Received: from mail-oi0-x242.google.com ([2607:f8b0:4003:c06::242]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1fuhLx-00085k-0v for pgsql-admin@lists.postgresql.org; Tue, 28 Aug 2018 17:00:31 +0000 Received: by mail-oi0-x242.google.com with SMTP id t68-v6so4074106oie.12 for ; Tue, 28 Aug 2018 10:00:28 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=subject:to:cc:references:from:message-id:date:user-agent :mime-version:in-reply-to:content-language; bh=g+qYx3sXU9CI8ZkQprOAgZQEfGYunsBKY2GhzqzlLlU=; b=NfwrdJOiskOLgldSbctpp+gTdTN/Ah3rYkbK2vG12+KnA1hrCp/mCQJQsQoBa+Gujx GWTUqqSSVS5S/2F4n8RATPrs4qbkfixoA9OJS+B+4C/1f7dM0sdjLkukW4m0VUQ/ubSS Ng1td3du8L9YXbeT/aegUhY07D8Ssa7IrT9itr45nfvCbpi6PFrEo8t54dSfy5XQGlQ4 VeRBRRehuuMnMfcBBF3s7MMMrCiDbkLfEeEyq+0JUctBkZBwGtZSR7nLsDvCjnVBHEBt wM5eLvkJ/efRnEF5rjyXfbZphOdzfEJgE7JUaQzwEY2A4Ph+tKZ6B9y8aiRTUskiUWf+ ps9g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:cc:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=g+qYx3sXU9CI8ZkQprOAgZQEfGYunsBKY2GhzqzlLlU=; b=LjGun6S1cDn8Om23rOhhsjmFOlLDRrGcItnWuiOry1Cp/x3DXo5C9OYkWdxjKv4m1n qwaQd8yhnKx7QXbTYLiL4DC9BLP+EV9/4Q32QOuJABrO4M/mL5KOHCbHYwUMo69aEgPl SB22dhct0Z5AqxqWQ8vmFUsv4XBkU1NnXAMvcTQjLIci3sF7XSzxTLwE3Sh40mPG7/FP kGQpuzS2pwDnIQs8Dn1y5EqjO4Q/XGnP+eme6iYrsLmmN87fF5l36/2aFq5b1V2WY+bg p0lCl/qXDhXP6IKp8eTY2ATZa1lb6eL0HOs5Z1fuAFKcx8dkrJulK/52vsnvzKP8mBsP fycQ== X-Gm-Message-State: APzg51DpBz/69vTAEqaQr/2hJsrSyeS3bfDQDy8RHa9OaVqb/2pg+gPp J3SgSm33L0qguxF5AaNMCP9b0Cq8 X-Google-Smtp-Source: ANB0VdbP6QcVtUi/IcdRr9VC/UYWfc6cu+x0oGtZzj7FPBOdmURSEZLQgPCWQs17Txy/ys1dbfjX7A== X-Received: by 2002:aca:524a:: with SMTP id g71-v6mr1998671oib.351.1535475626048; Tue, 28 Aug 2018 10:00:26 -0700 (PDT) Received: from [192.168.1.10] (ip70-171-116-89.no.no.cox.net. [70.171.116.89]) by smtp.googlemail.com with ESMTPSA id f4-v6sm2070099oic.52.2018.08.28.10.00.24 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Tue, 28 Aug 2018 10:00:25 -0700 (PDT) Subject: Re: More efficient pg_restore method? To: Evan Bauer Cc: pgsql-admin@lists.postgresql.org References: <44555188-c787-b441-5c69-c56f72905bf4@gmail.com> <1F9927D1-5C55-48B9-8C6D-ECE3F6BDA3ED@mac.com> From: Ron Message-ID: Date: Tue, 28 Aug 2018 12:00:24 -0500 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101 Thunderbird/52.9.1 MIME-Version: 1.0 In-Reply-To: <1F9927D1-5C55-48B9-8C6D-ECE3F6BDA3ED@mac.com> Content-Type: multipart/alternative; boundary="------------797A1C05B6CBFFEAC3D59A39" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------797A1C05B6CBFFEAC3D59A39 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit 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 > > wrote: >> >> >> Pg 9.6.9 on Linux... >> >> Given a backup server storing a "format=directory" 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. --------------797A1C05B6CBFFEAC3D59A39 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit 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=directory" 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.
--------------797A1C05B6CBFFEAC3D59A39--