Upgrade PostgreSQL from 11 to 13 on CentOS 7

Upgrade PostgreSQL from 11 to 13 on Centos 7

This article show you how to upgrade PostgreSQL from 11 to 13 on CentOS 7. I tested it multiple times without any issue. This operation takes around 20-30 minutes, depends on your database size.

Probably it works from 10 to 13 or 12 to 13 and so on. If you tested with different versions, let us know in comment section!

Let's get started!

  1. Create a backup of current database(s)
    su - postgres -c "pg_dumpall -p 5432 > /tmp/dump_file.sql"

  2. Install necessary packages
    sudo yum -y install postgresql13-server postgresql13-contrib

  3.  Inicialize the DB
    /usr/pgsql-13/bin/postgresql-13-setup initdb

  4. Stop old PostgreSQL
    sudo systemctl stop postgresql-11

  5. Change user to postgres
    su - postgres

  6. Change directory to home (required to avoid error in next step)
    cd

  7. Check that everything is fine
    /usr/pgsql-13/bin/pg_upgrade --old-datadir=/var/lib/pgsql/11/data --new-datadir=/var/lib/pgsql/13/data --old-bindir=/usr/pgsql-11/bin --new-bindir=/usr/pgsql-13/bin --old-options '-c config_file=/var/lib/pgsql/11/data/postgresql.conf' --new-options '-c config_file=/var/lib/pgsql/13/data/postgresql.conf' --check

  8. Run, if everything is fine
    /usr/pgsql-13/bin/pg_upgrade --old-datadir=/var/lib/pgsql/11/data --new-datadir=/var/lib/pgsql/13/data --old-bindir=/usr/pgsql-11/bin --new-bindir=/usr/pgsql-13/bin --old-options '-c config_file=/var/lib/pgsql/11/data/postgresql.conf' --new-options '-c config_file=/var/lib/pgsql/13/data/postgresql.conf'

  9. Exit to root
    exit

  10. Start the PostgreSQL 13
    systemctl start postgresql-13

  11. Change user to postgres
    su - postgres

  12. Check version number
    psql -c "SELECT version();"

  13. Analyze new cluster
    /var/lib/pgsql/analyze_new_cluster.sh

  14. Exit again to root
    exit

  15. Copy all config settings from postgresql.conf and pg_hba.conf. If you have any command to do that, share with us in comment section.
    • These are located here:
      • /var/lib/pgsql/11/data/postgresql.conf
      • /var/lib/pgsql/11/data/pg_hba.conf
      • /var/lib/pgsql/13/data/postgresql.conf
      • /var/lib/pgsql/13/data/pg_hba.conf
  16. If you are done with these config files, restart the service
    systemctl restart postgresql-13

  17. Enable auto-start on startup
    systemctl enable postgresql-13

  18. At this point you have to test your application. You need to restart your application (GitLab or whatever you use). If everything is works fine, let's continue with remove old packages:
    • sudo yum remove postgresql11*
    • rm -rf /var/lib/pgsql/11
    • su - postgres -c "/var/lib/pgsql/delete_old_cluster.sh"

That's all.

If you have any question, feel free to ask in comment section!

 

Get Free $100 Credit on DigitalOcean. Click here

Comments