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 1fuhJm-0001z0-A8 for pgsql-admin@arkaria.postgresql.org; Tue, 28 Aug 2018 16:58:14 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fuhJk-0001GZ-8W for pgsql-admin@arkaria.postgresql.org; Tue, 28 Aug 2018 16:58:12 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fuhJj-0001GS-Tg for pgsql-admin@lists.postgresql.org; Tue, 28 Aug 2018 16:58:12 +0000 Received: from st14p37im-asmtp002.me.com ([17.41.209.31]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1fuhJd-0002vl-I6 for pgsql-admin@lists.postgresql.org; Tue, 28 Aug 2018 16:58:10 +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 <0PE600F00KFIFP00@st14p37im-asmtp002.me.com> for pgsql-admin@lists.postgresql.org; Tue, 28 Aug 2018 16:58:04 +0000 (GMT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=mac.com; s=04042017; t=1535475484; bh=jO5o3yrvKJnNP+0sNhYTViXLqdZb4zAN+BK39mEFAf0=; h=From:Message-id:Content-type:MIME-version:Subject:Date:To; b=Pd7ibvUtRU2BHqjB4uuPfcc7F8+oADuGCrzvosZRXF+1Xr7pn8c41VHCVit5OlLer aQVkZh5rZa0t0vL/JQdxEGnUxxApL0dvqYMMFbYjG3In0fcKQwlO/rPq4yfyt4YV71 Tur4s6KgPwWoSXp5emaAB8Kbq7Cg9nBik0L6X+i5w2NcMVl9hTEIA6kTJrni37W6y+ KEA+U/EPdXquzY7fL5LhNzIP2upJ1kweabgQeOREW4DBn62IGyzIgYSpK0nYGSShPO LrQfsgFweVYdNf3rGOB9D7l8AIZUmisSh8J7cTN7CMNoT7rdx9rKQ+fcOSlUoLKHGy v2j8VUbX3IaeQ== 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 <0PE600D4RKGN2Q30@st14p37im-asmtp002.me.com>; Tue, 28 Aug 2018 16:58:01 +0000 (GMT) X-Proofpoint-Spam-Details: rule=notspam policy=default score=0 spamscore=0 clxscore=1011 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-1808280170 X-Proofpoint-Virus-Version: vendor=fsecure engine=2.50.10434:,, definitions=2018-08-28_07:,, signatures=0 From: Evan Bauer Message-id: <1F9927D1-5C55-48B9-8C6D-ECE3F6BDA3ED@mac.com> Content-type: multipart/alternative; boundary="Apple-Mail=_29B619B6-7F7B-46E2-98A0-5715B47E243A" 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 12:57:59 -0400 In-reply-to: <44555188-c787-b441-5c69-c56f72905bf4@gmail.com> Cc: pgsql-admin@lists.postgresql.org To: Ron References: <44555188-c787-b441-5c69-c56f72905bf4@gmail.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=_29B619B6-7F7B-46E2-98A0-5715B47E243A Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii Ron, A couple of starting questions: What is the size and latency of the network pipe between the primary and = backup servers? =20 What is the size of the database you need to restore?=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? Cheers, - Evan Evan Bauer eb@evanbauer.com +1 646 641 2973 Skype: evanbauer > 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 --Apple-Mail=_29B619B6-7F7B-46E2-98A0-5715B47E243A Content-Transfer-Encoding: 7bit Content-Type: text/html; charset=us-ascii Ron,

A couple of starting questions:

  1. What is the size and latency of the network pipe between the primary and backup servers?  
  2. What is the size of the database you need to restore? 
  3. 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?

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.


--Apple-Mail=_29B619B6-7F7B-46E2-98A0-5715B47E243A--