How to upgrade PostgreSQL from 11 to 12
Tags: postgres, linux, tutorial
Published: 2019-10-03 19:40:07

---
title: How to upgrade PostgreSQL from 11 to 12
published: true
description: Easy step-by-step tutorial on upgrading PostgreSQL using the pg_upgrade tool.
tags: postgres, linux, tutorial
canonical_url: https://www.kostolansky.sk/posts/upgrading-to-postgresql-12/
---

The new [PostgreSQL 12](https://www.postgresql.org/about/news/1976/) has just been released. There are multiple ways to upgrade from the old version 11, and the easiest one is by using the [pg_upgrade](https://www.postgresql.org/docs/12/pgupgrade.html) tool. Here is a quick tutorial for Ubuntu (or Debian) systems. And, please, do not forget to back up your data!

Update packages and install the new PostgreSQL 12.

```bash
sudo apt-get update
sudo apt-get install postgresql-12 postgresql-server-dev-12
```

Check if there are any differences in the config files.

```bash
diff /etc/postgresql/11/main/postgresql.conf /etc/postgresql/12/main/postgresql.conf
diff /etc/postgresql/11/main/pg_hba.conf /etc/postgresql/12/main/pg_hba.conf
```

Stop the PostgreSQL service.

```bash
sudo systemctl stop postgresql.service
```

Log in as the `postgres` user.

```bash
sudo su postgres
```

Check clusters (notice the `--check` argument, this will not change any data).

```bash
/usr/lib/postgresql/12/bin/pg_upgrade \
  --old-datadir=/var/lib/postgresql/11/main \
  --new-datadir=/var/lib/postgresql/12/main \
  --old-bindir=/usr/lib/postgresql/11/bin \
  --new-bindir=/usr/lib/postgresql/12/bin \
  --old-options '-c config_file=/etc/postgresql/11/main/postgresql.conf' \
  --new-options '-c config_file=/etc/postgresql/12/main/postgresql.conf' \
  --check
```

Migrate the data (without the `--check` argument).

```bash
/usr/lib/postgresql/12/bin/pg_upgrade \
  --old-datadir=/var/lib/postgresql/11/main \
  --new-datadir=/var/lib/postgresql/12/main \
  --old-bindir=/usr/lib/postgresql/11/bin \
  --new-bindir=/usr/lib/postgresql/12/bin \
  --old-options '-c config_file=/etc/postgresql/11/main/postgresql.conf' \
  --new-options '-c config_file=/etc/postgresql/12/main/postgresql.conf'
```

Go back to the regular user.

```bash
exit
```

Swap the ports for the old and new PostgreSQL versions.

```bash
sudo vim /etc/postgresql/12/main/postgresql.conf
# ...and change "port = 5433" to "port = 5432"

sudo vim /etc/postgresql/11/main/postgresql.conf
# ...and change "port = 5432" to "port = 5433"
```

Start the PostgreSQL service.

```bash
sudo systemctl start postgresql.service
```

Log in as the `postgres` user again.

```bash
sudo su postgres
```

Check the new PostgreSQL version.

```bash
psql -c "SELECT version();"
```

Run the generated `analyze_new_cluster` script.

```bash
./analyze_new_cluster.sh
```

Back to normal user.

```bash
exit
```

Check which old PostgreSQL packages are installed.

```bash
apt list --installed | grep postgresql
```

Remove the old PostgreSQL packages (from the listing above).

```bash
sudo apt-get remove postgresql-11 postgresql-server-dev-11
```

Remove the old configuration.

```bash
sudo rm -rf /etc/postgresql/11/
```

Log in as the `postgres` user once more.

```bash
sudo su postgres
```

Finally, drop the old cluster data.

```bash
./delete_old_cluster.sh
```

Done!


---

Original article: [How to upgrade PostgreSQL from 11 to 12](https://www.kostolansky.sk/posts/upgrading-to-postgresql-12/)