How to upgrade PostgreSQL from version 9.6 to vers

问题:

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.

回答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/
   

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.

回答2:

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

回答3:

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;

回答4:

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.

回答5:

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
   

回答6:

Update: This process is the same for upgrading 9.6 to 10; 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

回答7:

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.

回答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
....

回答9:

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.

回答10:

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

回答11:

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 :)

回答12:

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/

回答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