Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1emnlF-0005o1-Ma for pgsql-admin@arkaria.postgresql.org; Fri, 16 Feb 2018 21:41:41 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1emnlE-0002uW-Eu for pgsql-admin@arkaria.postgresql.org; Fri, 16 Feb 2018 21:41:40 +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.84_2) (envelope-from ) id 1emnlD-0002uN-TA for pgsql-admin@lists.postgresql.org; Fri, 16 Feb 2018 21:41:40 +0000 Received: from mail-qt0-x244.google.com ([2607:f8b0:400d:c0d::244]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1emnl6-0003cr-HR for pgsql-admin@lists.postgresql.org; Fri, 16 Feb 2018 21:41:38 +0000 Received: by mail-qt0-x244.google.com with SMTP id d8so5579407qtm.0 for ; Fri, 16 Feb 2018 13:41:32 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:subject:from:in-reply-to:date:cc :content-transfer-encoding:message-id:references:to; bh=0Q6OLbyzrNFe6m3wxwsqmCiwWPHKhJbmnbdlAdbuj1Q=; b=aLZaEfq7Uxap2xTa/2Qo+82272PvLUePH8CAflldGPr/RCKJrVENYgu0qTt0e2lVC1 BUWg/hO2DbTHImu0fnFyey3icdfet16mi5mcPOl+5rpARIicaniYapGptvhCFGSXuZKb /AQ0FVMIZnncq1CBGYQAjJ+55PEcTW98fweeqcuzWsTYufshMWV5z6o7NivmM2s+PyQn tPoubn+yjYYSoGTjnHYEFNpkwDeLJGh0+K2LyAjwD4fxXg1KyqL6RxffnfLOZmlaQ8Fx IQ/mU66pKnRV2YpfD5khY6U5xJSKU2QZBuIrUf+xa5e8b9mzEH/z6CUjmc1cPorDcSgL LjZQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:subject:from:in-reply-to:date:cc :content-transfer-encoding:message-id:references:to; bh=0Q6OLbyzrNFe6m3wxwsqmCiwWPHKhJbmnbdlAdbuj1Q=; b=NPh+we1muzFNm+Wx7blceSD4/W1yVTBwZeCm32hsp0xpDOhsZKr/ox3t3GuKCLVBeL KHbYXa5hb0v9VUKOX/bU/Y0OdnxSetou/+AHtOBV9hM7HNy6sHDHrAtanKMnmcPV9e78 2Zkbvnd9f8BU8dqcjys3cRMMEMnKV2bgJ+02XC8mLdH11+1SXbkVQzLxsZZ57mQRF9sU m4cvJ0jRgDEUGb3bja/oNM0e2R4MIAf5ccqXzZFt3ILhrPfWIb+7B/Dsc4stabXgv+iC XIpsHQk9yHsbgrf6aBJQRRZXOYqUOS5K0I+H+KeJXx3hJlGHN/vtUg3SKnjEsVgpyCk9 vzHQ== X-Gm-Message-State: APf1xPBY4zu1MHFx0WWLYPQ+Mfp9zBL/s9Ynd3v5ymJcQxpvg+79k1zY c2hljyNUfQoATiwmcJTjXN9NA4dE X-Google-Smtp-Source: AH8x226NznjhhSIrfiKuPrtCuIah/rmgRtXQY345nUY95VVxHs8DvkKQCErBvwn3rsli7xw3uiISvQ== X-Received: by 10.200.49.28 with SMTP id g28mr12051195qtb.279.1518817291198; Fri, 16 Feb 2018 13:41:31 -0800 (PST) Received: from [192.168.1.152] (pool-100-16-111-18.bltmmd.fios.verizon.net. [100.16.111.18]) by smtp.gmail.com with ESMTPSA id k2sm14273010qtk.60.2018.02.16.13.41.30 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Fri, 16 Feb 2018 13:41:30 -0800 (PST) Content-Type: multipart/alternative; boundary=Apple-Mail-5A7C200A-23E5-4330-8F22-6408B8BE02F1 Mime-Version: 1.0 (1.0) Subject: Re: How to move a postgres server From: John Scalia X-Mailer: iPad Mail (15D60) In-Reply-To: <23f201d3a76d$740ba370$5c22ea50$@gmail.com> Date: Fri, 16 Feb 2018 16:41:29 -0500 Cc: "Ahmed, Nawaz" , pgsql-admin@lists.postgresql.org Content-Transfer-Encoding: 7bit Message-Id: <096F9536-F957-4CC2-AB6E-FB228D64AD6C@gmail.com> References: <233701d3a759$091f98f0$1b5ecad0$@gmail.com> <23f201d3a76d$740ba370$5c22ea50$@gmail.com> To: Ron Watkins List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail-5A7C200A-23E5-4330-8F22-6408B8BE02F1 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Hi, I would say your easiest way to do this is to install the same version rpms o= n your new server compared to what is installed on the existing one. Then, y= ou=E2=80=99ve got a couple of ways to do the copy. First, you could simply b= uild a replica using pg_basebackup which would copy the entire cluster conte= nts onto your new server. If that sounds too difficult, then do an initdb on= the new server, then copy the *.conf filed from the existing server to it, a= nd restart the new instance. Now, you could run pg_dumpall on the old server= , and copy the SQL file to the new one and install it there. I=E2=80=99d say= the easiest way is to use pg_basebackup, but you=E2=80=99ll probably need t= o edit your existing pg_hba.conf in order to make it work, and btw, you=E2=80= =99d be running pg_basebackup from your new system. =E2=80=94 Jay Sent from my iPad > On Feb 16, 2018, at 4:31 PM, Ron Watkins wrote: >=20 > Im a relative newbie=E2=80=A6 > Can I create a new Postgresql server with the same name as the running ser= ver? > I need to preserve both the database name as well as the server name. The o= nly difference is the hostname. > We can take an outage. > =46rom what I read, it seems that all I need to do is copy the files from o= ne postgres server directory over to the other on the new host, then when th= e host reboots it will start that database automatically? > =20 > Also, im a bit worried about how to preserve the config of the server if I= am forced to create a new one rather than just copying it. How can I make s= ure the postgres server config is copied properly? > =20 > =20 > From: Ahmed, Nawaz [mailto:Nawaz@fast.au.fujitsu.com]=20 > Sent: Friday, February 16, 2018 1:52 PM > To: 'Ron Watkins'; pgsql-admin@lists.postgresql.org > Subject: RE: How to move a postgres server > =20 > =20 > =20 >=20 > =20 > From: Ron Watkins [mailto:rwatki@gmail.com]=20 > Sent: Saturday, 17 February 2018 6:05 AM > To: pgsql-admin@lists.postgresql.org > Subject: How to move a postgres server > =20 > I have a postgres server with 2 databases residing on a host which is bein= g de-commissioned. > I need to move the postgres server itself, including the databases to a ne= w host. > All the stuff I can find on the web talks about moving the database, but I= need to move the entire server. > Am I missing something here? > Am I suppose to create an identical server on the new host (with the same s= erver name), then backup/restore the databases individually? Is there not an= y way to move the entire server? > =20 > Hi, > =20 > The best way to do that with minimal downtime would be to use streaming re= plication. But that depends on a few other findings as below > =20 > 1) Version of the PostgreSQL server > 2) Size of the databases > 3) What OS: Linux/Win etc. and their versions > =20 > If the downtime is affordable, then build a new server with most recent pa= tch updates and an installation of the same PostgreSQL version on it. Then, s= hutdown the database cluster, copy the files across and bring up the cluster= on the new server. Although, I would not recommend this approach but this i= s the simplest way to move the PostgreSQL server when downtime is not a big d= eal. > =20 > ~ > =20 > Nawaz Ahmed > Disclaimer >=20 > The information in this e-mail is confidential and may contain content tha= t is subject to copyright and/or is commercial-in-confidence and is intended= only for the use of the above named addressee. If you are not the intended r= ecipient, you are hereby notified that dissemination, copying or use of the i= nformation is strictly prohibited. If you have received this e-mail in error= , please telephone Fujitsu Australia Software Technology Pty Ltd on + 61 2 9= 452 9000 or by reply e-mail to the sender and delete the document and all co= pies thereof. >=20 > =20 > Whereas Fujitsu Australia Software Technology Pty Ltd would not knowingly t= ransmit a virus within an email communication, it is the receiver=E2=80=99s r= esponsibility to scan all communication and any files attached for computer v= iruses and other defects. Fujitsu Australia Software Technology Pty Ltd does= not accept liability for any loss or damage (whether direct, indirect, cons= equential or economic) however caused, and whether by negligence or otherwis= e, which may result directly or indirectly from this communication or any fi= les attached. >=20 > =20 > If you do not wish to receive commercial and/or marketing email messages f= rom Fujitsu Australia Software Technology Pty Ltd, please email unsubscribe@= fast.au.fujitsu.com --Apple-Mail-5A7C200A-23E5-4330-8F22-6408B8BE02F1 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable Hi,

I would say your eas= iest way to do this is to install the same version rpms on your new server c= ompared to what is installed on the existing one. Then, you=E2=80=99ve got a= couple of ways to do the copy. First, you could simply build a replica usin= g pg_basebackup which would copy the entire cluster contents onto your new s= erver. If that sounds too difficult, then do an initdb on the new server, th= en copy the *.conf filed from the existing server to it, and restart the new= instance. Now, you could run pg_dumpall on the old server, and copy the SQL= file to the new one and install it there. I=E2=80=99d say the easiest way i= s to use pg_basebackup, but you=E2=80=99ll probably need to edit your existi= ng pg_hba.conf in order to make it work, and btw, you=E2=80=99d be running p= g_basebackup from your new system.
=E2=80=94
Jay

=
Sent from my iPad

On Feb 16, 20= 18, at 4:31 PM, Ron Watkins <rwatki@g= mail.com> wrote:

Im a relative newbie=E2=80=A6

Can I cre= ate a new Postgresql server with the same name as the running server?

I need t= o preserve both the database name as well as the server name. The only diffe= rence is the hostname.

We can take an outage.

=46rom what I read, it seems that a= ll I need to do is copy the files from one postgres server directory over to= the other on the new host, then when the host reboots it will start that da= tabase automatically?

 

Also, im a bit worried about how to preserve the co= nfig of the server if I am forced to create a new one rather than just copyi= ng it. How can I make sure the postgres server config is copied properly?

 

 

From: Ahmed, Nawaz [mailto:Nawaz@fast.au.fujitsu.com]
Sent: Friday,= February 16, 2018 1:52 PM
To: 'Ron Watkins'; pgsql-admin@lists.postgresql.org
S= ubject: RE: How to move a postgres server

 

 

 

 

<= div style=3D"border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0i= n 0in">

From: Ron Watkins [mailto:rwatki@gmail.com]
Sent: Saturday, 17 Fe= bruary 2018 6:05 AM
To: pgsql-admin@lists.postgresql.org
Subject: How to mov= e a postgres server

 

I have a po= stgres server with 2 databases residing on a host which is being de-commissi= oned.

I need to move the postgres serve= r itself, including the databases to a new host.

All the stuff I can find on the web talks about moving the databas= e, but I need to move the entire server.

Am I missing something here?

Am I sup= pose to create an identical server on the new host (with the same server nam= e), then backup/restore the databases individually? Is there not any way to m= ove the entire server?

 

Hi,

 

The best way to do that with minimal downtime woul= d be to use streaming replication. But that depends on a few other findings a= s below

 

1)      V= ersion of the PostgreSQL server

2)<= span style=3D"font-size:7.0pt;font-family:"Times New Roman","= serif";color:#1F497D">      Size of the databases

3= )      What OS: Linux/Win etc. and their versions<= /o:p>

&n= bsp;

If= the downtime is affordable, then build a new server with most recent patch u= pdates and an installation of the same PostgreSQL version on it. Then, shutd= own the database cluster, copy the files across and bring up the cluster on t= he new server. Although, I would not recommend this approach but this is the= simplest way to move the PostgreSQL server when downtime is not a big deal.=

<= o:p> 

~

 

Nawaz Ahmed

Disclaime= r

The information in this e-ma= il is confidential and may contain content that is subject to copyright and/= or is commercial-in-confidence and is intended only for the use of the above= named addressee. If you are not the intended recipient, you are hereby noti= fied that dissemination, copying or use of the information is strictly prohi= bited. If you have received this e-mail in error, please telephone Fujitsu A= ustralia Software Technology Pty Ltd on + 61 2 9452 9000 or by reply e-mail t= o the sender and delete the document and all copies thereof.

 =

Whereas Fujitsu Australia Software Techno= logy Pty Ltd would not knowingly transmit a virus within an email communicat= ion, it is the receiver=E2=80=99s responsibility to scan all communication a= nd any files attached for computer viruses and other defects. Fujitsu Austra= lia Software Technology Pty Ltd does not accept liability for any loss or da= mage (whether direct, indirect, consequential or economic) however caused, a= nd whether by negligence or otherwise, which may result directly or indirect= ly from this communication or any files attached.

 

=

If you do not wish to receive commercial and/or mark= eting email messages from Fujitsu Australia Software Technology Pty Ltd, ple= ase email unsubscribe@fas= t.au.fujitsu.com

= --Apple-Mail-5A7C200A-23E5-4330-8F22-6408B8BE02F1--