How do i upgrade my Postgresql 9.5 to Postgresql 10 on ubuntu 16.04

Question

I had a ubuntu machine on which postgresql 9.5 is running with approximately 12 GB of data. Now i want to upgrade my postgresql version from 9.5 to 10. I googled some articals but unable to upgrade and one more thing i do not want to completely uninstall postgres 9.5 and install postgres 10. My postgresql 9.5 settings are:

1-Postgresql config files are at:

/opt/PostgreSQL/9.5/

2- Postgresql 10 installed location

/etc/postgresql/10

Solution 1

The normal process to upgrade on Debian or Ubuntu is this one :

Stop the new updated version of PostreSQL :

service postgresql stop

Drop the new updated version cluster to be sure nothing is inside :

pg_dropcluster --stop 10 main

Then data migration :

pg_upgradecluster -m upgrade 9.5 main

Then stop previous version of PotsgreSQL :

pg_dropcluster 9.5 main --stop

Remove the old version :

apt-get autoremove --purge postgresql-9.5 

Then just restart PostgreSQL, the new version with database migrated :

service postgresql start

Solution 2

As already been suggested to use pg_clusters There is no need to stop postgresql when updating to pg10

In Ubuntu terminal, Write following commands- sudo apt-get install postgresql-10 sudo pg_dropcluster 10 main --stop sudo pg_upgradecluster 9.5 main sudo pg_dropcluster 9.5 main

This would firstly install postgresql-10 on your system and then tries to update the server side postgresql using pg_upgradecluster

This would also solve your problem that you do not want to remove pg-9.5

Solution 3

What do you mean by "unable to upgrade" exactly? Generally you can use pg_upgrade utility to in-place upgrade. You need to have the old and new versions installed separatelly at the same time.

The pg_upgrade documentation I linked gives exact instructions.

Comments

[Manish Yadav]

First thanks for your response.So first i have to install new postgresql 10 on ubuntu machine and after that perform the answer scripts.

[Manish Yadav]

by unable to upgrade mean i did not decided yet i had to install postgresql 10 on machine or something else

[Hervé Piedvache]

yes, that's the good way to do

[Manish Yadav]

can you please see my updated question.

[Manish Yadav]

sudo pg_upgradecluster 9.5 main not working for me because pg 9.5 is located at /opt/Postgresq/..

[Hervé Piedvache]

In this case simply do as explained in the PostgreSQL documentation : postgresql.org/docs/current/static/… you dump your 9.5 database with pg_dumpall then stop 9.5 and start your 10 version and integrate your dump in this version with a simple /usr/local/pgsql/bin/psql -d postgres -f your_dump