(An updated version of this post for upgrading to PostgreSQL 11 is available here)
Here’s a quick rundown on upgrading a very simply configured PostgreSQL 9.x server to PostgreSQL 10 running on Centos 7.
First, and this goes without saying, backup your server!
In these examples, I’m using upgrading from PostgreSQL 9.5. If you’re upgrading from a different version, just replace 9.5 and 95 wherever you see it with your appropriate version number.
Install the repo RPM for PosgresSQL 10
sudo yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-1.noarch.rpm
Install PosgreSQL 10
sudo yum install postgresql10-server
Stop Postgresql 9.5 and Postgresql 10
sudo systemctl stop postgresql-9.5.service && sudo systemctl stop postgresql-10.service
Initialize the PostgreSQL 10 database
sudo su postgres cd ~/ /usr/pgsql-10/bin/initdb -D /var/lib/pgsql/10/data/
Migrate your database from the 9.x version to 10
/usr/pgsql-10/bin/pg_upgrade --old-datadir /var/lib/pgsql/9.5/data/ --new-datadir /var/lib/pgsql/10/data/ --old-bindir /usr/pgsql-9.5/bin/ --new-bindir /usr/pgsql-10/bin/
Make any necessary changes to postgresql.conf . I’d recommend making the changes to the new version rather than copying over postgresql.conf from 9.5, since there are a bunch of new options in the PostreSQL 10 version of the file.
You can view your 9.5 configuration with:
You can make your changes to the 10 configuration with:
If you need to connect from other servers, make sure to change:
#listen_addresses = 'localhost'
to (apostrophes may not survive copy/paste, may want to hand enter)
listen_addresses = '*'
(or whatever is appropriate for you)
Now do the same with pg_hba.conf
View the old configuration
Edit the new configuration
Start the server
systemctl start postgresql-10.service
Analyze and optimize the new cluster
If everything is working, set the PostgreSQL 10 service to start automatically
systemctl enable postgresql-10
If you wish to remove PostgreSQL 9.x and its data
./delete_old_cluster.sh exit sudo yum remove postgresql95-server sudo yum remove pgdg-centos95
That should do it!
30 thoughts on “Upgrading to PostgreSQL 10 on Centos 7”
Thank you! Most write ups are dealing with clusters not stand alone systems.
You’re welcome, glad I could help!
We need more guys like you. Hah. Cheers
Thanks man! ?
Thank you, I could successfully upgrade PSQL 9.6 to 10.4 🙂 The only thing that I have changed is the command “sudo su postgres” with “su – postgres”, otherwise I had an error saying that I have no permissons.
You’re welcome, glad to hear it!
Interesting about the sudo command, I’ll add a note about that.
Did you do that? How can I move psql’s attention to /usr/pgsql-10/bin/psql? Confusing.
Phil, I’m not sure what you mean by attention. Once you do sudo su postgres, you can do cd ~/ to get to the Postgres user’s home directory.
HI Adam. Thanks for the reply. I didn’t sign-in using the ID form below, so I’m replying here.
Under the user postgres, there is no bash script ~/./delete_old_cluster.sh nor is it found anywhere. Can I manually remove those tables in the data directory?
No problem Phil, I figured that was you above!
I checked the contents of delete_old_cluster.sh on my server and it very simply just deletes the PostgreSQL 9.x database from the filesystem.
Here’s what my looks like:
rm -rf ‘/var/lib/pgsql/9.5/data’
Once you’re sure all your data has been migrated to PostgreSQL 10, you can just run that command as the postgres user (or root should work too). I’d recommend backing it up first if it’s critical data.
For some reason, these comments aren’t allowing me to reply to your last post.
So there is no previous data directory from what I can see. But there is /usr/bin/psql which is 9.6. An there is /usr/pgsql-10/bin/psql. Should I just delete the previous and get on with my life? It seems there are no other versions, but psql wasn’t updated.
It sounds like PostgreSQL 9.x is still installed. Try running this to see what package it’s installed from:
yum list installed postgresql*
You could just remove the binaries, but it’s cleaner to remove the whole package.
postgresql.x86_64 9.2.24-1.el7_5 @base
postgresql-devel.x86_64 9.2.24-1.el7_5 @base
postgresql-libs.x86_64 9.2.24-1.el7_5 @base
postgresql10.x86_64 10.6-1PGDG.rhel7 @pgdg10
postgresql10-contrib.x86_64 10.6-1PGDG.rhel7 @pgdg10
postgresql10-libs.x86_64 10.6-1PGDG.rhel7 @pgdg10
postgresql10-server.x86_64 10.6-1PGDG.rhel7 @pgdg10
Did a `sudo yum remove postgresql.x86_64` and voila, the new `which psql` works.
Perfect. Thank you. I knew I was missing something.
Excellent! You’re welcome Phil, glad I could help!
Make sure to use the full path to the commands as above, that way you know you’re using the correct version. Hope that helps! Let me know if you still run into trouble.
I would rather leave it out, as it’s a waste of time. I can’t retype full paths every time I wish to use psql. Is there no way to force this? I have no clue why the old psql is there. Should the package manager have taken care of that?
You are able to run both PostgreSQL 9.x and 10.x side by side. When you install 10.x, old versions are not removed.
To remove them, run these commands from the instructions up top (changing the numbers to match your version numbers):
sudo su postgres
./delete_old_cluster.sh (only once you’re sure you don’t need the only 9.x database files)
sudo yum remove postgresql95-server
sudo yum remove pgdg-centos95
mirgate command not working while updating postgres version 9.3 to 10.
Giving Error like :
postgres@data]$ /usr/pgsql-10/bin/pg_upgrade –old-datadir /var/lib/pgsql/9.3/data/ –new-datadir /var/lib/pgsql/10/data/ –old-bindir /usr/pgsql-9.3/bin/ –new-bindir /usr/pgsql-10/bin/
check for “/var/lib/pgsql/9.3/data/base” failed: No such file or directory
Please Help me ASAP
Shivendra, it sounds like your data might be stored in a non-default location. Can you run this:
ps aux | grep -i pgsql
Then paste in the results here?
That should tell you what data directory PostgreSQL is using, and can use it where you specify -old-datadir
If that doesn’t work, let me know, I’ll give you an address to DM me at.
Works great. Upgraded from 9.2 to 10.6 – no problem
Glad to hear it R (@marxiis), thanks for letting me know!
data copy or move
After the migration you should have a working copy of your database in both the PostgreSQL 9 and 10 locations.
I have a base of 200gb, I need a place for another 200gb
Ah. I haven’t used it myself, but pg_upgrade as a –link option to upgrade without copying. https://www.postgresql.org/docs/10/pgupgrade.html
bash-4.2$ /usr/pgsql-10/bin/pg_upgrade –old-datadir /var/lib/pgsql/9.6/data/ –new-datadir /var/lib/pgsql/10/data/ –old-bindir /usr/pgsql-9.6/bin/ –new-bindir /usr/pgsql-10/bin/
Performing Consistency Checks
Checking cluster versions ok
connection to database failed: fe_sendauth: no password supplied
could not connect to source postmaster started with the command:
“/usr/pgsql-9.6/bin/pg_ctl” -w -l “pg_upgrade_server.log” -D “/var/lib/pgsql/9.6/data/” -o “-p 50432 -b -c listen_addresses=” -c unix_socket_permissions=0700 -c unix_socket_directories=’/var/lib/pgsql'” start
Hi c kumar! Can you run whois and confirm that you’re the user: postgres ?
Excellent write up but still had issues going from 9.2 to 10.x on CentOS 7. A config variable was changed from “unix_socket_directory” to “unix_socket_directories” which caused pg_upgrade to fail. Found the solution here: https://dba.stackexchange.com/questions/50135/pg-upgrade-unrecognized-configuration-parameter-unix-socket-directory/86929#86929?newreg=19cc884a20b74148bf080a794b475710
Glad it helped Dan!
Thanks for letting me know. I’ll add a note to the guide. Sorry for the delay getting back to you.