How to upgrade PostgreSQL from version 9.6 to version 10.1 without losing data?
Here is the solution for Ubuntu users
First we have to stop postgresql
sudo /etc/init.d/postgresql stop
Create a new file called /etc/apt/sources.list.d/pgdg.list and add below line
deb http://apt.postgresql.org/pub/repos/apt/ utopic-pgdg main
Follow below commands
wget -q -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql-9.4
sudo pg_dropcluster --stop 9.4 main
sudo /etc/init.d/postgresql start
Now we have everything, just need to upgrade it as below
sudo pg_upgradecluster 9.3 main
sudo pg_dropcluster 9.3 main
That's it. Mostly upgraded cluster will run on port number 5433. Check it with below command
sudo pg_lsclusters
Assuming you've used home-brew to install and upgrade Postgres, you can perform the following steps.
Stop current Postgres server:
launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
Initialize a new 10.1 database:
initdb /usr/local/var/postgres10.1 -E utf8
run
pg_upgrade
(note: change bin version if you're upgrading from something other than below):pg_upgrade -v \ -d /usr/local/var/postgres \ -D /usr/local/var/postgres10.1 \ -b /usr/local/Cellar/postgresql/9.6.5/bin/ \ -B /usr/local/Cellar/postgresql/10.1/bin/
-v
to enable verbose internal logging-d
the old database cluster configuration directory-D
the new database cluster configuration directory-b
the old PostgreSQL executable directory-B
the new PostgreSQL executable directoryMove new data into place:
cd /usr/local/var mv postgres postgres9.6 mv postgres10.1 postgres
Restart Postgres:
launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
Check
/usr/local/var/postgres/server.log
for details and to make sure the new server started properly.Finally, re-install the rails
pg
gemgem uninstall pg gem install pg
I suggest you take some time to read the PostgreSQL documentation to understand exactly what you're doing in the above steps to minimize frustrations.
Despite all answers above, here goes my 5 cents.
It works on any OS and from any-to-any postgres version.
- Stop any running postgres instance;
- Install the new version and start it; Check if you can connect to the new version as well;
- Change old version's
postgresql.conf
->port
from5432
to5433
; - Start the old version postgres instance;
- Open a terminal and
cd
to the new versionbin
folder; - Run
pg_dumpall -p 5433 -U <username> | psql -p 5432 -U <username>
- Stop old postgres running instance;