Postgres Port Change

Will Adkin will.adkin at lineal.co.uk
Thu Feb 3 17:11:07 UTC 2022


Thanks Doug, most helpful!

Best regards

Will



[Lineal Software Solutions]        <http://www.lineal.co.uk>

[Twitter]<https://twitter.com/linealsoftware>  [Facebook] <https://www.facebook.com/linealsoftware?fref=ts&ref=br_tf>   [Google +] <https://plus.google.com/112982815000763473226/about>   [LinkedIn] <https://www.linkedin.com/company/1859834?trk=tyah&trkInfo=clickedVertical%3Acompany%2Cidx%3A1-1-1%2CtarId%3A1437729256906%2Ctas%3Alineal%20s>

[https://s3.amazonaws.com/htmlsig-assets/spacer.gif]

Will Adkin  / Software Development Manager
will at lineal.co.uk<mailto:will at lineal.co.uk>

Lineal Software Solutions
01271 375999
Commercial House, The StrandBarnstaple, Devon, EX31 1EU
www.lineal.co.uk<http://www.lineal.co.uk>



This e-mail message may contain confidential or legally privileged information and is intended only for the use of the intended recipient(s). Any unauthorized disclosure, dissemination, distribution, copying or the taking of any action in reliance on the information herein is prohibited. E-mails are not secure and cannot be guaranteed to be error free as they can be intercepted, amended, or contain viruses. Anyone who communicates with us by e-mail is deemed to have accepted these risks. Lineal is not responsible for errors or omissions in this message and denies any responsibility for any damage arising from the use of e-mail. Any opinion and other statement contained in this message and any attachment are solely those of the author and do not necessarily represent those of the company

On 3 Feb 2022, at 16:29, Doug Easterbrook <doug at artsman.com<mailto:doug at artsman.com>> wrote:

hi Will

you can install postgres 14 beside postgres 10.    as others point out, one will have port 5432, the other will have some other port, typically 5433


on a mac, they will be installed into
/Library/PostgreSQL/10 ….   and
/Library/PostgreSQL/14

you can change the port of either one by changing the postgrsql.conf to be any port you want.



however, since I see you want to make sure of your data file restores before deleting the other version…. it implies to me that you want to to a quick transition.


so I have another idea for you.



step 1: make a backup of your database.

pg_dump -F c -v  <databasename>     > /PathToFileName.backup




Step 2: restore the database to your current postgres directory

make a new database name such as    <databasename.temp>

restore

pg_restore -F c -v -d <databasename.temp>  < /PathToFileName.backup

if you can log in and see your data in the restored <databasename.temp>. you know the restore works





step 3: uninstall postgres 10

yes, un-install it.   This will delete the postgres binaries but it WILL NOT delete the postgres data directories.

this has the advantage, that if you want to reload postgres 10, you just reinstall it and you’ll have postgres 10 with your old data directories again.   You
‘ll likely never have to do this step ….

we have done the above when we recovered clients from disks that have hard crashed and all we can get is the data directory recovered fir a file recovery service.  we move the ‘data’ directory to our machines, reinstall the version of postgres and … the database lives.     so, yes, we have done this in real life and it works.



step 4: install postgres 14

this gives you postgres on port 5432





step 5: restore the database to the new postgres installation


pg_restore -F c -v -d <databasename>  < /PathToFileName.backup




recap:

step 1: backup the database
step 2: restore to a new database within your old postgres and check it out
step 3: uninstall postgres 10
step 4: install postgres 14
step 5: restore the database to postgres 14



we have done this probably a 1000 times as we migrated hundred of clients from postgres 8, to 9, to 9.1, 9.2, to 9.6 to 10, to 11, to 12, to 13 to 14


it is a reliable process.




if you really don’t trust things, then an alternative is to add

step 2:
install postgres 14 on another machine
restore the database on the original machine
restore the database on postgres 14 on another machine
connect to the postgres 14 machine and make sure you see your data


in other words.. restore the backup to two machines..


then go and do the rest of the steps.









Doug Easterbrook
Arts Management Systems Ltd.
mailto:doug at artsman.com
http://www.artsman.com<http://www.artsman.com/>
Phone (403) 650-1978

On February 3, 2022, at 1:36 AM, Will Adkin via omnisdev-en <omnisdev-en at lists.omnis-dev.com<mailto:omnisdev-en at lists.omnis-dev.com>> wrote:

Hi List,

Following the Postgres discussion with interest - thanks to all contributors!

We are about to upgrade all clients from PG10 to PG14,  we'd like to install PG14 side by side and fully restore to it before deleting 10, however 14 would end up with a different port number in this case. How do we change the the port of a live database so we can then switch the port number of 14 back and not have to mess with client config files?


Best regards

Will



[Lineal Software Solutions]        <http://www.lineal.co.uk<http://www.lineal.co.uk/>>

[Twitter]<https://twitter.com/linealsoftware>  [Facebook] <https://www.facebook.com/linealsoftware?fref=ts&ref=br_tf>   [Google +] <https://plus.google.com/112982815000763473226/about>   [LinkedIn] <https://www.linkedin.com/company/1859834?trk=tyah&trkInfo=clickedVertical%3Acompany%2Cidx%3A1-1-1%2CtarId%3A1437729256906%2Ctas%3Alineal%20s>

[https://s3.amazonaws.com/htmlsig-assets/spacer.gif]

Will Adkin  / Software Development Manager
will at lineal.co.uk<mailto:will at lineal.co.uk><mailto:will at lineal.co.uk>

Lineal Software Solutions
01271 375999
Commercial House, The StrandBarnstaple, Devon, EX31 1EU
www.lineal.co.uk<http://www.lineal.co.uk/><http://www.lineal.co.uk<http://www.lineal.co.uk/>>



This e-mail message may contain confidential or legally privileged information and is intended only for the use of the intended recipient(s). Any unauthorized disclosure, dissemination, distribution, copying or the taking of any action in reliance on the information herein is prohibited. E-mails are not secure and cannot be guaranteed to be error free as they can be intercepted, amended, or contain viruses. Anyone who communicates with us by e-mail is deemed to have accepted these risks. Lineal is not responsible for errors or omissions in this message and denies any responsibility for any damage arising from the use of e-mail. Any opinion and other statement contained in this message and any attachment are solely those of the author and do not necessarily represent those of the company

_____________________________________________________________
Manage your list subscriptions at https://lists.omnis-dev.com<https://lists.omnis-dev.com/>
Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com




More information about the omnisdev-en mailing list