Upgrade PostgreSQL from 10 to 13 on Centos 7

Upgrade PostgreSQL from 10 to 13 on CentOS 7

This article shows you how to upgrade PostgreSQL from 10 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.

If you are looking for upgrade PostgreSQL 11 to 13, click here.

Let's get started!

  1. Login as root
    sudo su

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

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

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

  5. Stop old PostgreSQL
    sudo systemctl stop postgresql-10

  6. Change user to "postgres"
    su - postgres

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

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

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

  10. Exit to root
    exit

  11. Start the PostgreSQL 13
    systemctl start postgresql-13

  12. Change user to "postgres"
    su - postgres

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

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

  15. Exit again to root
    exit

  16. 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/10/data/postgresql.conf
      • /var/lib/pgsql/10/data/pg_hba.conf
      • /var/lib/pgsql/13/data/postgresql.conf
      • /var/lib/pgsql/13/data/pg_hba.conf

  17. If you are done with these config files, restart the service
    systemctl restart postgresql-13

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

  19. At this point, you have to test your application. You need to restart your application (GitLab or whatever you use). If everything is working fine, let's continue by removing old packages and files:
    • sudo yum remove postgresql10*
    • rm -rf /var/lib/pgsql/10
    • su - postgres -c "/var/lib/pgsql/delete_old_cluster.sh"

That's all.

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

 

Get Free $100 Credit on DigitalOcean. Click here

Comments