How to upgrade PostgreSQL from version 9.6 to version 10.1 without losing data? [closed]

Question

I'm using the PostgreSQL database for my Ruby on Rails application (on Mac OS X 10.9).

Are there any detailed instructions on how to upgrade PostgreSQL database?

I'm afraid I will destroy the data in the database or mess it up.

Piatt

Do backups in any case. – Schuman

Answer 1

Assuming you've used home-brew to install and upgrade Postgres, you can perform the following steps.

1. Stop current Postgres server:

   launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

2. Initialize a new 10.1 database:

   initdb /usr/local/var/postgres10.1 -E utf8

3. 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 directory

4. Move new data into place:

   cd /usr/local/var
   mv postgres postgres9.6
   mv postgres10.1 postgres
   

5. Restart Postgres:

   launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

6. Check /usr/local/var/postgres/server.log for details and to make sure the new server started properly.

7. Finally, re-install the rails pg gem

   gem 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.

Novobiocin

I hade to use the following command to initialize the database: initdb /usr/local/var/postgres9.4 -E utf8 --lc-collate=C --lc-ctype=utf-8 --lc-monetary=C --lc-numeric=C – Sconce

It follow your guide step by step. And everything worked! Thank you so much. – Diametral

Very helpful. Thank you very much. I was already afraid this would take me 2-3 hours to finish. But it took less than 5 minutes and I could continue programming. Kudos. – Shainashaine

Works great, thank you! I was getting an error about postgres missing pg_tblspc which is guess is common for Yosemite, but i was able to fix it with: mkdir /usr/local/var/postgres/pg_tblspc mkdir /usr/local/var/postgres/pg_twophase mkdir /usr/local/var/postgres/pg_stat_tmp – Yoshida

Be careful with the delete_old_cluster.sh command. I had first manually deleted the /usr/local/postgres9.3 directories, than ran this command and it seems I lost the entire /usr/local/var/postgres directory (I was able to restore it from Time Machine) – Pigeonhearted

After you reinstall your pg gem, you might need to do $ bundle update pg to keep your Gemfile.lock in sync. – Chargeable

If you're using Bundler, you should remove the gem using gem uninstall pg but then let bundler reinstall the correct version from Gemfile.lock by simply running bundle. – Stercoricolous

Wonderful stuff. Worth noting that if you're getting ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist: No such file or directory then this Thoughtbot post will help. – Wileen

Can you help me to do it on Ubuntu – Metrical

@Metrical it should be roughly the same steps, just replace the OS X specifics with those for Ubuntu; primarily steps 1 & 5, and the file paths. – Novobiocin

Step 4. Move new data into place: ... I must be missing something; how do I move old data into the upgraded db? – Cabby

--link might be quite useful if you don't want to copy data. – Lavabo

worked great for update from 9.4.0 to 9.5.4, although had to adjust the folder names (obviously). – Literate

I had to change the trust mode in pg_hba.conf for both (9.4 and 9.6 in my case) to "trust". – Linzer

This recipe assumes your database was initialized with utf8. If not you may get an error like: encodings for database "postgres" do not match: old "SQL_ASCII", new "UTF8" – Bronchiole

Minor update: brew now also has the option to use brew services stop postgresql and brew services start postgresql instead of directly calling launchctl unload and launchctl load. – Iulus

If sh** hits the fan, you can also try following gist.github.com/chbrown/647a54dc3e1c2e8c7395. – Punt

This answer worked for me and is really simple ; I don't know if your answer is outdated or does something different. If it is out of date, could you update it? – Fear

Yeah, this answer was originally written to upgrade between 7 and 8 (I believe), and at that time they did not have an automated way to upgrade the database. I agree that the homebrew upgrade script would be the current "best" answer. That said, this method still works even if it's not the "easiest" anymore. – Novobiocin

Answer 2

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 from 5432 to 5433;
• Start the old version postgres instance;
• Open a terminal and cd to the new version bin folder;
• Run pg_dumpall -p 5433 -U <username> | psql -p 5432 -U <username>
• Stop old postgres running instance;

Doughman

Thanks Christian, this is definately a great and easy solution, I successfully went from 9.3 to 9.5 like this – Upwind

Worked beautifully for upgrading from 9.1 to 9.5 on a Windows 2012 server. – Montes

A problem of this solution is that if you had changed some Postgres config files (eg. postgresql.conf or pg_hba.conf) you would need to manually replicate those changes in the new installation. Instead, if you use pg_upgradecluster, config files get copied to the new cluster: manpages.ubuntu.com/manpages/precise/man8/… – Stelliform

Note that right after you'll start the command you will get one password prompt, but you have to enter 2 passwords one by one confirming each by Enter. Or you'll get pg_dumpall: could not connect to database "XXX": fe_sendauth: no password supplied – Incompressible

@Lu55 you can also setup trust in the origin database configuration to avoid having to input passwords. Btw, this method worked wonders to migrate 9.4 to 9.6 in my case. – Virginia

Technically this won't work for any-to-any version, just for source versions at or above 7.0, as the manual states: Current releases of the dump programs can read data from any server version back to 7.0. – Conlen

@Stelliform pb_upgradecluster is an Ubuntu-only util, op asked about OS X. – Kick

How exactly do you start a secondary instance when the primary one runs using systemctl, and how to you swap those afterwards ? – Inopportune

Is there an easier way to do this on Windows 10? I installed postgreSql 15 like 6 months ago, haven't used it yet (other than to create and store passwords, etc in a vault). I intend to use it now. Just want to upgrade to the latest minor release (15.3) without having to reinstall or go thru that much before I start doing major work in there. – Malvinamalvino

Answer 3

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

Metrical

The second to last sentence where you say "Mostly upgraded cluster will run on port number 5433" should probably say "The 9.3 cluster will be running on port number 5433 so that you can revert if necessary". – Vitovitoria

Note: For ubuntu 14.04 use "trusty-pgdb" instead of utopic-pgdb – Gabel

Note that this does NOT do an in-place upgrade. it's therefore completely useless for my 700 GB DB on a 1TB volume. – Sprinkle

I know this post is really old I am trying to go from 9.6 to 12 will this work with making such a large jump? I am also wondering if it is focal instead of utopic? – Arawak

Answer 4

If you are using homebrew and homebrew services, you can probably just do:

brew services stop postgresql
brew upgrade postgresql
brew postgresql-upgrade-database
brew services start postgresql

I think this might not work completely if you are using advanced postgres features, but it worked perfectly for me.

Malinda

Worked perfectly for macOS Mojave 10.14.2 - Thanks! – Hanes

Still working in 2021 on OS Catalina 10.15.7 – Malaria

Answer 5

Update: This process is the same for upgrading 9.5 through at least 11.5; simply modify the commands to reflect versions 9.6 and 10, where 9.6 is the old version and 10 is the new version. Be sure to adjust the "old" and "new" directories accordingly, too.

---

I just upgraded PostgreSQL 9.5 to 9.6 on Ubuntu and thought I'd share my findings, as there are a couple of OS/package-specific nuances of which to be aware.

(I didn't want to have to dump and restore data manually, so several of the other answers here were not viable.)

In short, the process consists of installing the new version of PostgreSQL alongside the old version (e.g., 9.5 and 9.6), and then running the pg_upgrade binary, which is explained in (some) detail at https://www.postgresql.org/docs/9.6/static/pgupgrade.html .

The only "tricky" aspect of pg_upgrade is that failure to pass the correct value for an argument, or failure to be logged-in as the correct user or cd to the correct location before executing a command, may lead to cryptic error messages.

On Ubuntu (and probably Debian), provided you are using the "official" repo, deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main, and provided you haven't changed the default filesystem paths or runtime options, the following procedure should do the job.

Install the new version (note that we specify the 9.6, explicitly):

sudo apt install postgresql-9.6

Once installation succeeds, both versions will be running side-by-side, but on different ports. The installation output mentions this, at the bottom, but it's easy to overlook:

Creating new cluster 9.6/main ...
  config /etc/postgresql/9.6/main
  data   /var/lib/postgresql/9.6/main
  locale en_US.UTF-8
  socket /var/run/postgresql
  port   5433

Stop both server instances (this will stop both at the same time):

sudo systemctl stop postgresql

Switch to the dedicated PostgreSQL system user:

su postgres

Move into his home directory (failure to do this will cause errors):

cd ~

pg_upgrade requires the following inputs (pg_upgrade --help tells us this):

When you run pg_upgrade, you must provide the following information:
  the data directory for the old cluster  (-d DATADIR)
  the data directory for the new cluster  (-D DATADIR)
  the "bin" directory for the old version (-b BINDIR)
  the "bin" directory for the new version (-B BINDIR)

These inputs may be specified with "long names", to make them easier to visualize:

  -b, --old-bindir=BINDIR       old cluster executable directory
  -B, --new-bindir=BINDIR       new cluster executable directory
  -d, --old-datadir=DATADIR     old cluster data directory
  -D, --new-datadir=DATADIR     new cluster data directory

We must also pass the --new-options switch, because failure to do so results in the following:

connection to database failed: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/var/lib/postgresql/.s.PGSQL.50432"?

This occurs because the default configuration options are applied in the absence of this switch, which results in incorrect connection options being used, hence the socket error.

Execute the pg_upgrade command from the new PostgreSQL version:

/usr/lib/postgresql/9.6/bin/pg_upgrade --old-bindir=/usr/lib/postgresql/9.5/bin --new-bindir=/usr/lib/postgresql/9.6/bin --old-datadir=/var/lib/postgresql/9.5/main --new-datadir=/var/lib/postgresql/9.6/main --old-options=-cconfig_file=/etc/postgresql/9.5/main/postgresql.conf --new-options=-cconfig_file=/etc/postgresql/9.6/main/postgresql.conf

Logout of the dedicated system user account:

exit

The upgrade is now complete, but, the new instance will bind to port 5433 (the standard default is 5432), so keep this in mind if attempting to test the new instance before "cutting-over" to it.

Start the server as normal (again, this will start both the old and new instances):

systemctl start postgresql

If you want to make the new version the default, you will need to edit the effective configuration file, e.g., /etc/postgresql/9.6/main/postgresql.conf, and ensure that the port is defined as such:

port = 5432

If you do this, either change the old version's port number to 5433 at the same time (before starting the services), or, simply remove the old version (this will not remove your actual database content; you would need to use apt --purge remove postgresql-9.5 for that to happen):

apt remove postgresql-9.5

The above command will stop all instances, so you'll need to start the new instance one last time with:

systemctl start postgresql

As a final point of note, don't forget to consider pg_upgrade's good advice:

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
    ./analyze_new_cluster.sh

Running this script will delete the old cluster's data files:
    ./delete_old_cluster.sh

Sagittarius

For me in Mac Yosemite, PostgreSQL 9.2 -> 9.5: sudo su postgres, change all METHOD in both installation's pg_hba.conf to trust before pg_upgrade, running pg_upgrade in /private/tmp not ~ didn't work so sudo mkdir /foobar with chmod 777 /foobar and ran it there. At last the pg_upgrade command: /Library/PostgreSQL/9.5/bin/pg_upgrade -b /Library/PostgreSQL/9.2/bin -B /Library/PostgreSQL/9.5/bin -d /Library/PostgreSQL/9.2/data -D /Library/PostgreSQL/9.5/data -o -cconfig_file=/Library/PostgreSQL/9.2/data/postgresql.conf -O -cconfig_file=/Library/PostgreSQL/9.5/data/postgresql.conf – Gonidium

i was able to upgrade successfully from 9.5 to 11.5 had to add postgres apt repositories to ubuntu. and then just follow those steps : tecadmin.net/install-postgresql-server-on-ubuntu – Kamchatka

Answer 6

The user manual covers this topic in depth. You can:

• pg_upgrade in-place; or

• pg_dump and pg_restore.

If in doubt, do it with dumps. Don't delete the old data directory, just keep it in case something goes wrong / you make a mistake; that way you can just go back to your unchanged 9.3 install.

For details, see the manual.

If you're stuck, post a detailed question explaining how you're stuck, where, and what you tried first. It depends a bit on how you installed PostgreSQL too, as there are several different "distributions" of PostgreSQL for OS X (unfortunately). So you'd need to provide that info.

Aaron

Answer 7

Standing on the shoulders of the other poor creatures trodding through this muck, I was able to follow these steps to get back up and running after an upgrade to Yosemite:

Assuming you've used home-brew to install and upgrade Postgres, you can perform the following steps.

1. Stop current Postgres server:

   launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

2. Initialize a new 9.4 database:

   initdb /usr/local/var/postgres9.4 -E utf8

3. Install postgres 9.3 (as it was no longer present on my machine):

   brew install homebrew/versions/postgresql93

4. Add directories removed during Yosemite upgrade:

   mkdir -p /usr/local/var/postgres/{pg_tblspc,pg_twophase,pg_stat_tmp}/touch /usr/local/var/postgres/{pg_tblspc,pg_twophase,pg_stat_tmp}/.keep

5. run pg_upgrade:

   pg_upgrade -v -d /usr/local/var/postgres -D /usr/local/var/postgres9.4 -b /usr/local/Cellar/postgresql93/9.3.5/bin/ -B /usr/local/Cellar/postgresql/9.4.0/bin/

6. Move new data into place:

   cd /usr/local/var
   mv postgres postgres9.3
   mv postgres9.4 postgres
   

7. Restart Postgres:

   launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

8. Check /usr/local/var/postgres/server.log for details and to make sure the new server started properly.

9. Finally, re-install related libraries?

   pip install --upgrade psycopg2
   gem uninstall pg
   gem install pg
   

Heroin

pg_upgrade -v -d /usr/local/var/postgres -D /usr/local/var/postgres9.4 -b /usr/local/Cellar/postgresql93/9.3.*/bin/ -B /usr/local/Cellar/postgresql/9.4.*/bin/ # Minor versions might be different. – Randi

Thanks for this. I accidentally ran brew cleanup before migrating data and that caused postgres9.3 to be uninstalled. This helped. :) – Ultramicroscopic

Answer 8

Looks like the solution has been baked into Homebrew now:

$ brew info postgresql
...
==> Caveats
To migrate existing data from a previous major version of PostgreSQL run:
  brew postgresql-upgrade-database
....

Carilyn

Answer 9

On Windows I kept facing different errors messages when trying to use pg_upgrade.

Saved a lot of time for me to just:

1. Backup DB
2. Uninstall all copies of PostgreSQL
3. Install 9.5
4. Restore DB

Nicolis

Did this for 9.5 to 9.6 and worked perfectly as well. Good to be able to do easy exact upgrade as well, rather than "latest". I used Big SQL to get the right installer (openscg.com/bigsql/postgresql/installers.jsp). – Complect

Answer 10

This did it for me.

https://gist.github.com/dideler/60c9ce184198666e5ab4

Short and to the point. I honestly don't aim to understand the guts of PostgreSQL, I want to get stuff done.

Firmament

This uses Ubuntu's pg_upgradecluster tool which can be much slower than PostgreSQL's pg_upgrade tool and of course is only available on Ubuntu. – Alphonsa

@Alphonsa Not true. I have it on my Debian jessie. Had 10+ DBs and an amount of ~400MB of database data was converted in a blink of an eye. Then again, I am using a virtual Debian on an SSD. – Firmament

Let me correct myself: pg_upgradecluster is part of "Debian PostgreSQL infrastructure", so only available on Debian-based distros. About the speed pg_upgrade offers the "--link" option, which linking not copying data where possible: postgresql.org/docs/9.4/static/pgupgrade.html – Alphonsa

Perhaps we aim for different outcomes. After a distro-wide software upgrade I ended up with 2 version of PostgreSQL and the data my projects used was stuck on the older version (9.3). So I just hunted down the link above (in my answer) and upgraded it, got rid of the old "cluster" and the older PG version. – Firmament

I tried to upgrade from 9.4 to 11 with pg_upgradecluster 9.4 main but I get the error Error: specified cluster does not exist ... I guess, I have to install postgresql-9.4 again first with this guide: wiki.postgresql.org/wiki/Apt#Quickstart – Anomaly

Answer 11

My solution for upgrading from Postgresql 11 to Postgresql 12 on Windows 10 is the following.

As a first remark you will need to be able stop and start the Postgresql service. You can do this by the following commands in Powershell.

Start: pg_ctl start -D “d:\postgresql\11\data”

Stop: pg_ctl stop -D “d:\postgresql\11\data”

Status: pg_ctl status -D “d:\postgresql\11\data”

It would be wise to make a backup before doing the upgrade. The Postgresql 11 instance must be running. Then to copy the globals do

pg_dumpall -U postgres -g -f d:\bakup\postgresql\11\globals.sql

and then for each database

pg_dump -U postgres -Fc <database> > d:\backup\postgresql\11\<database>.fc

or

pg_dump -U postgres -Fc -d <database> -f d:\backup\postgresql\11\<database>.fc

If not already done install Postgresql 12 (as Postgresql 11 is also installed this will be on port 5433)

Then to do the upgrade as follows:

1) Stop Postgresql 11 service (see above)

2) Edit the postgresql.conf file in d:\postgresql\12\data and change port = 5433 to port = 5432

3) Edit the windows user environment path (windows start then type env) to point to Postgresql 12 instead of Postresql 11

4) Run upgrade by entering the following command.

pg_upgrade `
-b “c:\program files\postgresql\11\bin” `
-B “c:\program files\postgresql\12\bin” `
-d “d:\postgresql\11\data” `
-D “d:\postgresql\12\data” --username=postgres

(In powershell use backtick (or backquote) ` to continue the command on the next line)

5) and finally start the new Postgresql 12 service

pg_ctl start -D “d:\postgresql\12\data”

Konstantin

Answer 12

My solution was to do a combination of these two resources:

https://gist.github.com/tamoyal/2ea1fcdf99c819b4e07d

and

http://www.gab.lc/articles/migration_postgresql_9-3_to_9-4

The second one helped more then the first one. Also to not, don't follow the steps as is as some are not necessary. Also, if you are not being able to backup the data via postgres console, you can use alternative approach, and backup it with pgAdmin 3 or some other program, like I did in my case.

Also, the link: https://help.ubuntu.com/stable/serverguide/postgresql.html Helped to set the encrypted password and set md5 for authenticating the postgres user.

After all is done, to check the postgres server version run in terminal:

sudo -u postgres psql postgres

After entering the password run in postgres terminal:

SHOW SERVER_VERSION;

It will output something like:

 server_version 
----------------
 9.4.5

For setting and starting postgres I have used command:

> sudo bash # root
> su postgres # postgres

> /etc/init.d/postgresql start
> /etc/init.d/postgresql stop

And then for restoring database from a file:

> psql -f /home/ubuntu_username/Backup_93.sql postgres

Or if doesn't work try with this one:

> pg_restore --verbose --clean --no-acl --no-owner -h localhost -U postgres -d name_of_database ~/your_file.dump

And if you are using Rails do a bundle exec rake db:migrate after pulling the code :)

Entirety

Answer 13

For Mac via homebrew:

brew tap petere/postgresql,

brew install <formula> (eg: brew install petere/postgresql/postgresql-9.6)

Remove old Postgres:

brew unlink postgresql

brew link -f postgresql-9.6

If any error happen, don't forget to read and follow brew instruction in each step.

Check this out for more: https://github.com/petere/homebrew-postgresql

Fiester

Answer 14

On Windows 10 since I had npm, I installed rimraf package. npm install rimraf -g

Backup all your databases one by one using command pg_dump -U $username --format=c --file=$mydatabase.sqlc $dbname

Then Installed Latest PostgreSQL Version i.e. 11.2 which prompted me to use port 5433 this time.

Followed by Uninstall of older versions of PostgreSQL mine was 10. Note the uninstaller may give a warning of not deleting folder C:\PostgreSQL\10\data. That's why we have the next step using rimraf to permanently delete the folder and it's sub-folders.

change into PostgreSQL install directory and ran the command rimraf 10. 10 is a directory name. Note use your older version of PostgreSQL i.e. 9.5 or something.

Now add C:\PostgreSQL\pg11\bin, C:\PostgreSQL\pg11\lib into the Windows environmental variables. Note my new installed version is 11 thus why I am using pg11.

Navigate to C:\PostgreSQL\data\pg11 then open postgresql.conf edit port = 5433 to port = 5432

That's it. Open cmd and type psql -U postgres

You can now restore all your backed databases one by one using the command pg_restore -U $username --dbname=$databasename $filename

Fran

Answer 15

I think this is best link for your solution to update postgres to 9.6

https://sandymadaan.wordpress.com/2017/02/21/upgrade-postgresql9-3-9-6-in-ubuntu-retaining-the-databases/

Straightout