The following are my notes on how to upgrade a PostgreSQL server. A few things to consider:
- I run a CentOS machine - you should be able to follow along with most OSs and package managers, though.
- This is a v11 to v12 upgrade - if this is not the case for you, just edit the commands.
- This upgrade strategy will incur on some database downtime - this should be minimal if you properly prepare.
$ yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm $ yum install postgresql12-server $ yum install postgresql12-contrib (needed for some pg extensions) $ systemctl stop postgresql-11 $ su - postgres $ /usr/pgsql-12/bin/initdb -D /var/lib/pgsql/12/data/ // Optional: psql -> CREATE EXTENSION pg_stat_statements; // Notice the --link flag, without this the uprade will take much longer and duplicate data across the filesystem. $ /usr/pgsql-12/bin/pg_upgrade --old-datadir /var/lib/pgsql/11/data/ --new-datadir /var/lib/pgsql/12/data/ --old-bindir /usr/pgsql-11/bin/ --new-bindir /usr/pgsql-12/bin/ --link // Edit /var/lib/pgsql/12/data/pg_hba.conf and copy settings from previous version. // Edit /var/lib/pgsql/12/data/postgresql.conf and copy settings from previous version - Or visit take the opportunity to use PGTune. // Make sure to address the following: // listen_addresses = '*' (this will allow remote connections, so make sure you know what you are doing). // log_timezone = 'UTC' // timezone = 'UTC' (switch back to root) $ systemctl start postgresql-12.service $ su - postgres // Depending on the size and nature of your database, this could take quite a while to run, but at this point the database is up and running! $ ./analyze_new_cluster.sh (switch back to root) $ systemctl enable postgresql-12 $ su - postgres $ ./delete_old_cluster.sh