Re: How to move a postgres server

[John Scalia <jayknowsunix@xxxxxxxxx>]

Hi,

I would say your easiest way to do this is to install the same version rpms on your new server compared to what is installed on the existing one. Then, you’ve got a couple of ways to do the copy. First, you could simply build a replica using pg_basebackup which would copy the entire cluster contents 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, 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’d say the easiest way is to use pg_basebackup, but you’ll probably need to edit your existing pg_hba.conf in order to make it work, and btw, you’d be running pg_basebackup from your new system.
—

Jay

Sent from my iPad

On Feb 16, 2018, at 4:31 PM, Ron Watkins <rwatki@xxxxxxxxx> wrote:

Im a relative newbie…

Can I create a new Postgresql server with the same name as the running server?

I need to preserve both the database name as well as the server name. The only difference is the hostname.

We can take an outage.

From what I read, it seems that all 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 database automatically?

 

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 sure the postgres server config is copied properly?

 

 

From: Ahmed, Nawaz [mailto:Nawaz@xxxxxxxxxxxxxxxxxxx]
Sent: Friday, February 16, 2018 1:52 PM
To: 'Ron Watkins'; pgsql-admin@xxxxxxxxxxxxxxxxxxxx
Subject: RE: How to move a postgres server

 

 

 

 

From: Ron Watkins [mailto:rwatki@xxxxxxxxx]
Sent: Saturday, 17 February 2018 6:05 AM
To: pgsql-admin@xxxxxxxxxxxxxxxxxxxx
Subject: How to move a postgres server

 

I have a postgres server with 2 databases residing on a host which is being de-commissioned.

I need to move the postgres server itself, including the databases to a new 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 server name), then backup/restore the databases individually? Is there not any way to move the entire server?

 

Hi,

 

The best way to do that with minimal downtime would be to use streaming replication. But that depends on a few other findings as below

 

1)      Version of the PostgreSQL server

2)      Size of the databases

3)      What OS: Linux/Win etc. and their versions

 

If the downtime is affordable, then build a new server with most recent patch updates and an installation of the same PostgreSQL version on it. Then, shutdown 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 is the simplest way to move the PostgreSQL server when downtime is not a big deal.

 

~

 

Nawaz Ahmed

Disclaimer

The information in this e-mail 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 notified that dissemination, copying or use of the information is strictly prohibited. If you have received this e-mail in error, please telephone Fujitsu Australia Software Technology Pty Ltd on + 61 2 9452 9000 or by reply e-mail to the sender and delete the document and all copies thereof.

 

Whereas Fujitsu Australia Software Technology Pty Ltd would not knowingly transmit a virus within an email communication, it is the receiver’s responsibility to scan all communication and any files attached for computer viruses and other defects. Fujitsu Australia Software Technology Pty Ltd does not accept liability for any loss or damage (whether direct, indirect, consequential or economic) however caused, and whether by negligence or otherwise, which may result directly or indirectly from this communication or any files attached.

 

If you do not wish to receive commercial and/or marketing email messages from Fujitsu Australia Software Technology Pty Ltd, please email unsubscribe@xxxxxxxxxxxxxxxxxxx