elephant-from-plasticine
PostgreSQL

Upgrading to PostgreSQL 10 on Centos 7

32 Comments

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

nano /var/lib/pgsql/9.5/data/postgresql.conf

You can make your changes to the 10 configuration with:

nano /var/lib/pgsql/10/data/postgresql.conf

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

nano /var/lib/pgsql/9.5/data/pg_hba.conf

Edit the new configuration

nano /var/lib/pgsql/10/data/pg_hba.conf

Start the server

systemctl start postgresql-10.service

Analyze and optimize the new cluster

./analyze_new_cluster.sh

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!

32 thoughts on “Upgrading to PostgreSQL 10 on Centos 7”

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

    Reply

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

        Reply

        1. 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?

          Reply

          1. 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:

            #!/bin/sh

            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.

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

          Reply

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

        3. Installed Packages
          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.

          Reply

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

        Reply

        1. 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?

          Reply

          1. 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
            cd ~/
            ./delete_old_cluster.sh (only once you’re sure you don’t need the only 9.x database files)
            exit
            sudo yum remove postgresql95-server
            sudo yum remove pgdg-centos95

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

    Failure, exiting

    Please Help me ASAP

    Reply

    1. 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.
      -Adam

      Reply
  5. Pingback: Upgrading to PostgreSQL 11 on Centos 7 | Shernet

  7. 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
    Failure, exiting

    Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.