How to upgrade PostgreSQL from version 9 6 to version 10 1 without losing data

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

User · Answer

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

User · Answer

On Windows I kept facing different errors messages when trying to use pg upgrade    Saved a lot of time for me to just    Backup DB Uninstall all copies of PostgreSQL Install 9 5 Restore DB

User · Answer

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

User · Answer

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    gt  sudo bash   root  gt  su postgres   postgres   gt   etc init d postgresql start  gt   etc init d postgresql stop   And then for restoring database from a file    gt  psql -f  home ubuntu username Backup 93 sql postgres   Or if doesn t work try with this one    gt  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

User · Answer

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

User · Answer

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

User · Answer

For Mac via homebrew   brew tap petere postgresql    brew install  lt formula gt   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

User · Answer

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  lt username gt    psql -p 5432 -U  lt username gt  Stop old postgres running instance

User · Answer

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 directory Move 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 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

User · Answer

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    Stop current Postgres server   launchctl unload   Library LaunchAgents homebrew mxcl postgresql plist Initialize a new 9 4 database   initdb  usr local var postgres9 4 -E utf8 Install postgres 9 3  as it was no longer present on my machine    brew install homebrew versions postgresql93 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 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  Move new data into place   cd  usr local var mv postgres postgres9 3 mv postgres9 4 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 related libraries    pip install --upgrade psycopg2 gem uninstall pg gem install pg

User · Answer

Looks like the solution has been baked into Homebrew now     brew info postgresql        gt  Caveats To migrate existing data from a previous major version of PostgreSQL run    brew postgresql-upgrade-database

User · Answer

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

User · Answer

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

User · Answer

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  lt database gt   gt   d  backup postgresql 11  lt database gt  fc  or  pg dump -U postgres -Fc -d  lt database gt  -f d  backup postgresql 11  lt database gt  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

User · Answer

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

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

Examples related to postgresql

Examples related to upgrade