Skip to content

Instantly share code, notes, and snippets.

@noelruault

noelruault/potgresql-upgrade.md

Last active June 18, 2025 08:35
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save noelruault/c62c4113d0d40a4502c1a27f4abff405 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save noelruault/c62c4113d0d40a4502c1a27f4abff405 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. noelruault revised this gist Oct 27, 2021. 1 changed file with 4 additions and 4 deletions.
    8 changes: 4 additions & 4 deletions potgresql-upgrade.md
    View file
    Original file line number Diff line number Diff line change
    @@ -131,14 +131,14 @@ First create a **backup** of all the databases for that (You can continue from B
    # TYPE DATABASE USER ADDRESS METHOD
    # Database administrative login by Unix domain socket
    local all postgres peer -> trust
    local all postgres trust # peer -> trust
    # "local" is for Unix domain socket connections only
    local all all peer -> trust
    local all all trust # peer -> trust
    # IPv4 local connections:
    host all all 127.0.0.1/32 scram-sha-256 -> trust
    host all all 127.0.0.1/32 trust # scram-sha-256 -> trust
    # IPv6 local connections:
    host all all ::1/128 scram-sha-256 -> trust
    host all all ::1/128 trust # scram-sha-256 -> trust
    # Allow replication connections from localhost, by a user with the
    # replication privilege.
    local replication all peer
  2. noelruault revised this gist Oct 27, 2021. 1 changed file with 4 additions and 4 deletions.
    8 changes: 4 additions & 4 deletions potgresql-upgrade.md
    View file
    Original file line number Diff line number Diff line change
    @@ -131,14 +131,14 @@ First create a **backup** of all the databases for that (You can continue from B
    # TYPE DATABASE USER ADDRESS METHOD
    # Database administrative login by Unix domain socket
    local all postgres ~~peer~~ trust
    local all postgres peer -> trust
    # "local" is for Unix domain socket connections only
    local all all ~~peer~~ trust
    local all all peer -> trust
    # IPv4 local connections:
    host all all 127.0.0.1/32 ~~scram-sha-256~~ trust
    host all all 127.0.0.1/32 scram-sha-256 -> trust
    # IPv6 local connections:
    host all all ::1/128 ~~scram-sha-256~~ trust
    host all all ::1/128 scram-sha-256 -> trust
    # Allow replication connections from localhost, by a user with the
    # replication privilege.
    local replication all peer
  3. noelruault revised this gist Oct 27, 2021. 1 changed file with 116 additions and 29 deletions.
    145 changes: 116 additions & 29 deletions potgresql-upgrade.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,6 +1,59 @@
    # Instructions to upgrade Postgres: version 10 to 14
    # Instructions to Upgrade PostgreSQL to v14 (Ubuntu)

    ## 1. Backup
    - [Migration plan](#migration-plan)
    - [Phase one: Develop automation in a isolated environment](#phase-one-develop-automation-in-a-isolated-environment)
    - [Phase two: Ensure the integrity of the migration and make a copy of the database](#phase-two-ensure-the-integrity-of-the-migration-and-make-a-copy-of-the-database)
    - [Phase three: Freeze the system and migrate](#phase-three-freeze-the-system-and-migrate)
    - [Phase four: Test the upgrade end-to-end in staging/production](#phase-four-test-the-upgrade-end-to-end-in-staging-production)
    - [Rollback plan](#rollback-plan)
    - [Playbook](#playbook)
    - [1. Backup](#1-backup)
    - [2. Pull repos and install PostgreSQL 14](#2-pull-repos-and-install-postgresql-14)
    - [3. Migrate the data](#3-migrate-the-data-choose-one-option)
    - [4. Restart and check version](#4-restart-and-check-version)
    - [5. Cleanup](#5-cleanup)
    - [Sources](#sources)

    ## Migration plan

    ### Phase one: Develop automation in a isolated environment

    - **Develop a playbook** and **tests** on a similar PostgreSQL environment (created using a back-up from staging) for these tests.
    - Create an image of staging in your cloud provider.
    - Initialise a new VM/Docker/? based on this image. (*use a backup from staging to get the upgrade project in contact with the environment)*
    - Run the pertinent commands to develop a migration note/script.

    ### Phase two: Ensure the integrity of the migration and make a copy of the database

    - Iterate and test the integration craft a set of end-to-end tests. Validate the crafted end-to-end tests in staging.
    - Execute a **snapshot** from the database disk that could be used in a restore scenario.

    ### Phase three: Freeze the system and migrate

    - Execute any **pre-checks** for the project.
    - **Stop the traffic and applications that access the database.**
    - Do not receive traffic.
    - Stop the proxy.
    - Stop the middleware.
    - **Execute the migration** (note/script) to carry out the PostgreSQL upgrade.

    ### Phase four: Test the upgrade end-to-end in staging-production

    - **Verify the integrity** and status of the database. Execute the end-to-end tests.
    - Start the applications connected to the DB and execute tests suites. Execute local unit tests on the upgraded database.

    ## Rollback plan

    - Stop the cluster with PostgreSQL 14.
    - Restore the configuration to PostgreSQL 10.
    - Initialize the database in version 10.
    - Start receiving traffic

    ## Playbook

    The next commands were executed in an *Ubuntu 18.04.4 LTS*.

    ### 1. Backup

    First create a **backup** of all the databases for that (You can continue from B if you dont need a backup)

    @@ -9,7 +62,7 @@ First create a **backup** of all the databases for that (You can continue from B
    2. Create a backup .sql file for all the data you have in all the databases
    `pg_dumpall > backup.sql`

    ## 2. Pull repos and install PostgreSQL 14
    ### 2. Pull repos and install PostgreSQL 14

    1. Download ca-certificates

    @@ -35,7 +88,7 @@ First create a **backup** of all the databases for that (You can continue from B
    4. (*Optional)* Check installed versions
    `dpkg -l 'postgres*' | grep ^i`

    ## 3. Migrate the data (*choose one option*)
    ### 3. Migrate the data (*choose one option*)

    1. (*Option 1: Manual*)
    1. Stop postgreSQL
    @@ -47,21 +100,53 @@ First create a **backup** of all the databases for that (You can continue from B
    ```bash
    cd /var/lib/postgresql && ls
    # output: 10 14
    pg_lsclusters
    # Ver Cluster Port Status Owner Data directory Log file
    # 10 main 5433 down,binaries_missing postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log
    # 14 main 5432 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
    ```

    4. Run `pg_upgrate` to migrate the data
    4. Run `[pg_upgrade](https://www.postgresql.org/docs/14/pgupgrade.html)` to migrate the data

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

    5. Update anything required by the `pg_upgrade` command (like `update_extensions.sql`)
    5. Edit `pg_hba.conf` and change peer to trust to allow incoming connections

    ```bash
    sudo vim /etc/postgresql/14/main/pg_hba.conf
    # change 'peer' to 'trust' for all/all for both host and local / 127.0.0.1/32
    ```

    - Example file result

    ```bash
    # TYPE DATABASE USER ADDRESS METHOD
    # Database administrative login by Unix domain socket
    local all postgres ~~peer~~ trust
    # "local" is for Unix domain socket connections only
    local all all ~~peer~~ trust
    # IPv4 local connections:
    host all all 127.0.0.1/32 ~~scram-sha-256~~ trust
    # IPv6 local connections:
    host all all ::1/128 ~~scram-sha-256~~ trust
    # Allow replication connections from localhost, by a user with the
    # replication privilege.
    local replication all peer
    host replication all 127.0.0.1/32 scram-sha-256
    host replication all ::1/128 scram-sha-256
    ```

    6. Update anything required by the `pg_upgrade` command (like `update_extensions.sql`)

    Note: When upgrading, the executable outputted:

    @@ -72,15 +157,15 @@ First create a **backup** of all the databases for that (You can continue from B

    Now is the time to do so. 🙂

    6. Switch to regular user
    7. Switch to regular user
    `exit`
    7. Swap the ports and delete the old version.
    8. Swap the ports and delete the old version.

    ```bash
    sudo vim /etc/postgresql/14/main/postgresql.conf
    #change port to 5432
    # change port to 5432
    sudo vim /etc/postgresql/10/main/postgresql.conf
    #change port to 5433
    # change port to 5433
    ```

    2. (*Option 2: Automatic. Use at your own risk* 😵‍💫)
    @@ -97,7 +182,7 @@ First create a **backup** of all the databases for that (You can continue from B
    # pg_upgradecluster [-v newversion] oldversion name [newdatadir]
    ```

    ## 4. Restart and check version
    ### 4. Restart and check version

    1. Start the postgresql service
    `sudo systemctl restart postgresql.service`
    @@ -106,9 +191,9 @@ First create a **backup** of all the databases for that (You can continue from B
    3. Check your new postgres version
    `psql -c "SELECT version();"`

    ## 5. Cleanup
    ### 5. Cleanup

    A.k.a cleanup up the old version's mess (manual version of `pg_dropcluster` stated in [5.b]())
    A.k.a cleanup up the old version's mess (manual version of `pg_dropcluster` stated in `5.b`)
    1. (*Option 1: Manual*)
    1. Return as a normal(default user)
    @@ -127,21 +212,23 @@ A.k.a cleanup up the old version's mess (manual version of `pg_dropcluster` stat
    5. Go inside psql folder and remove old data (`/var/lib/postgresql`)
    ```bash
    rm -rf '/var/lib/postgresql/10/main' # by the time this guide was written: equivalent to ./delete_old_cluster.sh
    rmdir 10 # remove folder 10 if empty
    rm delete_old_cluster.sh # remove script file, equivalent to the first line of this snippet and not useful anymore
    sudo rm -rf '/var/lib/postgresql/10/main' # by the time this guide was written: equivalent to ./delete_old_cluster.sh
    rmdir 10 # remove folder 10 if empty
    sudo rm delete_old_cluster.sh # remove script file, equivalent to the first line of this snippet and not useful anymore
    ```
    2. (*Option 2: Automatic. Use at your own risk* 😵‍💫)
    1. Use `pg_dropcluster`
    ```bash
    sudo pg_dropcluster 10 main --stop
    ```
    `sudo pg_dropcluster 10 main --stop`
    ---
    **Note:** If everything works well in 2-3, we dont have to apply the backup as we have already migrated the data from the older version to the newer version, the backup is just in case anything goes wrong.
    **Note:** If everything works well in 2-3, we dont have to apply the backup as we have already migrated the data from the older version to the newer version, the backup is just in case if anything goes wrong.
    ---
    ## Sources
    - [https://stackoverflow.com/questions/60409585/how-to-upgrade-postgresql-database-from-10-to-12-without-losing-data-for-openpro](https://stackoverflow.com/questions/60409585/how-to-upgrade-postgresql-database-from-10-to-12-without-losing-data-for-openpro)
    - [https://cloud.google.com/community/tutorials/setting-up-postgres](https://cloud.google.com/community/tutorials/setting-up-postgres)
    - [cloud.google.com/tutorials/setting-up-postgres](https://cloud.google.com/community/tutorials/setting-up-postgres)
    - [gitlab.com/blog/2020/09/11/gitlab-pg-upgrade](https://about.gitlab.com/blog/2020/09/11/gitlab-pg-upgrade/)
    - [stackoverflow.com - upgrade-postgresql-without-losing-data](https://stackoverflow.com/questions/60409585/how-to-upgrade-postgresql-database-from-10-to-12-without-losing-data-for-openpro)
  4. noelruault revised this gist Oct 22, 2021. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions potgresql-upgrade.md
    View file
    Original file line number Diff line number Diff line change
    @@ -87,13 +87,13 @@ First create a **backup** of all the databases for that (You can continue from B
    1. Delete the PostgreSQL brand new cluster to migrate the old one instead

    ```bash
    sudo [pg_dropcluster](http://manpages.ubuntu.com/manpages/trusty/man8/pg_dropcluster.8.html) 14 main --stop # Docs: http://manpages.ubuntu.com/manpages/trusty/man8/pg_dropcluster.8.html
    sudo pg_dropcluster 14 main --stop # Docs: http://manpages.ubuntu.com/manpages/trusty/man8/pg_dropcluster.8.html
    ```

    2. Upgrade / Migrate

    ```bash
    sudo [pg_upgradecluster](http://manpages.ubuntu.com/manpages/trusty/man8/pg_upgradecluster.8.html) -v 14 10 main # Docs: http://manpages.ubuntu.com/manpages/trusty/man8/pg_upgradecluster.8.html
    sudo pg_upgradecluster -v 14 10 main # Docs: http://manpages.ubuntu.com/manpages/trusty/man8/pg_upgradecluster.8.html
    # pg_upgradecluster [-v newversion] oldversion name [newdatadir]
    ```

  5. noelruault revised this gist Oct 22, 2021. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion potgresql-upgrade.md
    View file
    Original file line number Diff line number Diff line change
    @@ -132,7 +132,7 @@ A.k.a cleanup up the old version's mess (manual version of `pg_dropcluster` stat
    rm delete_old_cluster.sh # remove script file, equivalent to the first line of this snippet and not useful anymore
    ```
    2. (Option 2*: Automatic. Use at your own risk* 😵‍💫)
    2. (*Option 2: Automatic. Use at your own risk* 😵‍💫)
    1. Use `pg_dropcluster`
    ```bash
  6. noelruault revised this gist Oct 22, 2021. 1 changed file with 18 additions and 11 deletions.
    29 changes: 18 additions & 11 deletions potgresql-upgrade.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,6 +1,4 @@
    # Instructions to upgrade PostgreSQL: version 10 to 14

    <!-- @import "[TOC]" {cmd="toc" depthFrom=1 depthTo=6 orderedList=false} -->
    # Instructions to upgrade Postgres: version 10 to 14

    ## 1. Backup

    @@ -39,7 +37,7 @@ First create a **backup** of all the databases for that (You can continue from B

    ## 3. Migrate the data (*choose one option*)

    1. *(Option 1: Manual)*
    1. (*Option 1: Manual*)
    1. Stop postgreSQL
    `sudo systemctl stop postgresql.service`
    2. Make sure you are logged in as postgres user
    @@ -63,7 +61,17 @@ First create a **backup** of all the databases for that (You can continue from B
    --new-options '-c config_file=/etc/postgresql/14/main/postgresql.conf'
    ```

    5. Update anything required by (like `update_extensions.sql`)
    5. Update anything required by the `pg_upgrade` command (like `update_extensions.sql`)

    Note: When upgrading, the executable outputted:

    ```bash
    Your installation contains extensions that should be updated with the ALTER EXTENSION command.
    The file update_extensions.sql, when executed by psql by the database superuser will update these extensions.
    ```

    Now is the time to do so. 🙂

    6. Switch to regular user
    `exit`
    7. Swap the ports and delete the old version.
    @@ -75,7 +83,7 @@ First create a **backup** of all the databases for that (You can continue from B
    #change port to 5433
    ```

    2. *(Option 2: Automatic. Use at your own risk* 😵‍💫*)*
    2. (*Option 2: Automatic. Use at your own risk* 😵‍💫)
    1. Delete the PostgreSQL brand new cluster to migrate the old one instead

    ```bash
    @@ -89,8 +97,7 @@ First create a **backup** of all the databases for that (You can continue from B
    # pg_upgradecluster [-v newversion] oldversion name [newdatadir]
    ```


    ## 4. **Restart and check version**
    ## 4. Restart and check version

    1. Start the postgresql service
    `sudo systemctl restart postgresql.service`
    @@ -99,11 +106,11 @@ First create a **backup** of all the databases for that (You can continue from B
    3. Check your new postgres version
    `psql -c "SELECT version();"`

    ## 5. **Cleanup**
    ## 5. Cleanup

    A.k.a cleanup up the old version's mess (manual version of `pg_dropcluster` stated in [5.b]())
    1. Option 1 (Manual)
    1. (*Option 1: Manual*)
    1. Return as a normal(default user)
    `exit`
    2. Uninstall postgres packages **if present**
    @@ -125,7 +132,7 @@ A.k.a cleanup up the old version's mess (manual version of `pg_dropcluster` stat
    rm delete_old_cluster.sh # remove script file, equivalent to the first line of this snippet and not useful anymore
    ```
    2. Option 2 (Automatic)
    2. (Option 2*: Automatic. Use at your own risk* 😵‍💫)
    1. Use `pg_dropcluster`
    ```bash
  7. noelruault revised this gist Oct 22, 2021. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion potgresql-upgrade.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,4 +1,4 @@
    # Instructions to upgrade Postgres: version 10 to 14
    # Instructions to upgrade PostgreSQL: version 10 to 14

    <!-- @import "[TOC]" {cmd="toc" depthFrom=1 depthTo=6 orderedList=false} -->

  8. noelruault revised this gist Oct 22, 2021. 1 changed file with 19 additions and 15 deletions.
    34 changes: 19 additions & 15 deletions potgresql-upgrade.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,4 +1,6 @@
    # Postgres upgrade: 10 to 14
    # Instructions to upgrade Postgres: version 10 to 14

    <!-- @import "[TOC]" {cmd="toc" depthFrom=1 depthTo=6 orderedList=false} -->

    ## 1. Backup

    @@ -46,7 +48,7 @@ First create a **backup** of all the databases for that (You can continue from B

    ```bash
    cd /var/lib/postgresql && ls
    # 10 14
    # output: 10 14
    ```

    4. Run `pg_upgrate` to migrate the data
    @@ -73,21 +75,22 @@ First create a **backup** of all the databases for that (You can continue from B
    #change port to 5433
    ```

    2. *(Option 2: Automatic. Use at your own risk* 😵‍💫)
    2. *(Option 2: Automatic. Use at your own risk* 😵‍💫*)*
    1. Delete the PostgreSQL brand new cluster to migrate the old one instead

    ```bash
    sudo pg_dropcluster 14 main --stop # Docs: http://manpages.ubuntu.com/manpages/trusty/man8/pg_dropcluster.8.html
    sudo [pg_dropcluster](http://manpages.ubuntu.com/manpages/trusty/man8/pg_dropcluster.8.html) 14 main --stop # Docs: http://manpages.ubuntu.com/manpages/trusty/man8/pg_dropcluster.8.html
    ```

    2. Upgrade / Migrate

    ```bash
    sudo pg_upgradecluster -v 14 10 main # Docs: http://manpages.ubuntu.com/manpages/trusty/man8/pg_upgradecluster.8.html
    sudo [pg_upgradecluster](http://manpages.ubuntu.com/manpages/trusty/man8/pg_upgradecluster.8.html) -v 14 10 main # Docs: http://manpages.ubuntu.com/manpages/trusty/man8/pg_upgradecluster.8.html
    # pg_upgradecluster [-v newversion] oldversion name [newdatadir]
    ```

    ## 4. Restart and check version

    ## 4. **Restart and check version**

    1. Start the postgresql service
    `sudo systemctl restart postgresql.service`
    @@ -96,9 +99,9 @@ First create a **backup** of all the databases for that (You can continue from B
    3. Check your new postgres version
    `psql -c "SELECT version();"`

    ## 5. Cleanup
    ## 5. **Cleanup**

    **Cleanup** up the old version's mess (manual version of `pg_dropcluster` stated in [5.b]())
    A.k.a cleanup up the old version's mess (manual version of `pg_dropcluster` stated in [5.b]())
    1. Option 1 (Manual)
    1. Return as a normal(default user)
    @@ -114,11 +117,12 @@ First create a **backup** of all the databases for that (You can continue from B
    `sudo rm -rf /etc/postgresql/10/`
    4. Login as postgres user
    `sudo su postgres`
    5. Go inside psql folder (`/var/lib/postgresql`)
    5. Go inside psql folder and remove old data (`/var/lib/postgresql`)
    ```bash
    ./delete_old_cluster.sh # by the time this guide was written: equivalent to rm -rf '/var/lib/postgresql/10/main'
    rm -rf 10 && rm -rf delete_old_cluster.sh
    rm -rf '/var/lib/postgresql/10/main' # by the time this guide was written: equivalent to ./delete_old_cluster.sh
    rmdir 10 # remove folder 10 if empty
    rm delete_old_cluster.sh # remove script file, equivalent to the first line of this snippet and not useful anymore
    ```
    2. Option 2 (Automatic)
    @@ -128,9 +132,9 @@ First create a **backup** of all the databases for that (You can continue from B
    sudo pg_dropcluster 10 main --stop
    ```
    If everything works well in 2-3, we dont have to apply the backup as we have already migrated the data from the older version to the newer version, the backup is just in case if anything goes wrong.
    **Note:** If everything works well in 2-3, we dont have to apply the backup as we have already migrated the data from the older version to the newer version, the backup is just in case if anything goes wrong.
    ### Sources
    ## Sources
    - [StackOverflow: how-to-upgrade-postgresql-database-from-10-to-12-without-losing-data](https://stackoverflow.com/questions/60409585/how-to-upgrade-postgresql-database-from-10-to-12-without-losing-data-for-openpro)
    - [cloud.google.com/setting-up-postgres](https://cloud.google.com/community/tutorials/setting-up-postgres)
    - [https://stackoverflow.com/questions/60409585/how-to-upgrade-postgresql-database-from-10-to-12-without-losing-data-for-openpro](https://stackoverflow.com/questions/60409585/how-to-upgrade-postgresql-database-from-10-to-12-without-losing-data-for-openpro)
    - [https://cloud.google.com/community/tutorials/setting-up-postgres](https://cloud.google.com/community/tutorials/setting-up-postgres)
  9. noelruault revised this gist Oct 22, 2021. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions potgresql-upgrade.md
    View file
    Original file line number Diff line number Diff line change
    @@ -77,13 +77,13 @@ First create a **backup** of all the databases for that (You can continue from B
    1. Delete the PostgreSQL brand new cluster to migrate the old one instead

    ```bash
    sudo [pg_dropcluster](http://manpages.ubuntu.com/manpages/trusty/man8/pg_dropcluster.8.html) 14 main --stop # Docs: http://manpages.ubuntu.com/manpages/trusty/man8/pg_dropcluster.8.html
    sudo pg_dropcluster 14 main --stop # Docs: http://manpages.ubuntu.com/manpages/trusty/man8/pg_dropcluster.8.html
    ```

    2. Upgrade / Migrate

    ```bash
    sudo [pg_upgradecluster](http://manpages.ubuntu.com/manpages/trusty/man8/pg_upgradecluster.8.html) -v 14 10 main # Docs: http://manpages.ubuntu.com/manpages/trusty/man8/pg_upgradecluster.8.html
    sudo pg_upgradecluster -v 14 10 main # Docs: http://manpages.ubuntu.com/manpages/trusty/man8/pg_upgradecluster.8.html
    # pg_upgradecluster [-v newversion] oldversion name [newdatadir]
    ```

  10. noelruault revised this gist Oct 22, 2021. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion potgresql-upgrade.md
    View file
    Original file line number Diff line number Diff line change
    @@ -73,7 +73,7 @@ First create a **backup** of all the databases for that (You can continue from B
    #change port to 5433
    ```

    2. *(Option 2: Automatic. Use at your own risk* 😵‍💫*)*
    2. *(Option 2: Automatic. Use at your own risk* 😵‍💫)
    1. Delete the PostgreSQL brand new cluster to migrate the old one instead

    ```bash
  11. noelruault created this gist Oct 22, 2021.
    136 changes: 136 additions & 0 deletions potgresql-upgrade.md
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,136 @@
    # Postgres upgrade: 10 to 14

    ## 1. Backup

    First create a **backup** of all the databases for that (You can continue from B if you dont need a backup)

    1. Log in as postgres user
    `sudo su postgres`
    2. Create a backup .sql file for all the data you have in all the databases
    `pg_dumpall > backup.sql`

    ## 2. Pull repos and install PostgreSQL 14

    1. Download ca-certificates

    ```bash
    sudo apt update
    sudo apt-get install wget ca-certificates
    ```

    2. Add the GPG key and PostgreSQL repository

    ```bash
    wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
    sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'
    ```

    3. Update apt and get latest postgres version

    ```bash
    sudo apt update
    sudo apt -y install postgresql=14\* postgresql-client=14\* postgresql-contrib=14\*
    ```

    4. (*Optional)* Check installed versions
    `dpkg -l 'postgres*' | grep ^i`

    ## 3. Migrate the data (*choose one option*)

    1. *(Option 1: Manual)*
    1. Stop postgreSQL
    `sudo systemctl stop postgresql.service`
    2. Make sure you are logged in as postgres user
    `sudo su postgres`
    3. And you are running the next commands from a directory that is writable by the postgres user like `/var/lib/postgresql`

    ```bash
    cd /var/lib/postgresql && ls
    # 10 14
    ```

    4. Run `pg_upgrate` to migrate the data

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

    5. Update anything required by (like `update_extensions.sql`)
    6. Switch to regular user
    `exit`
    7. Swap the ports and delete the old version.

    ```bash
    sudo vim /etc/postgresql/14/main/postgresql.conf
    #change port to 5432
    sudo vim /etc/postgresql/10/main/postgresql.conf
    #change port to 5433
    ```

    2. *(Option 2: Automatic. Use at your own risk* 😵‍💫*)*
    1. Delete the PostgreSQL brand new cluster to migrate the old one instead

    ```bash
    sudo [pg_dropcluster](http://manpages.ubuntu.com/manpages/trusty/man8/pg_dropcluster.8.html) 14 main --stop # Docs: http://manpages.ubuntu.com/manpages/trusty/man8/pg_dropcluster.8.html
    ```

    2. Upgrade / Migrate

    ```bash
    sudo [pg_upgradecluster](http://manpages.ubuntu.com/manpages/trusty/man8/pg_upgradecluster.8.html) -v 14 10 main # Docs: http://manpages.ubuntu.com/manpages/trusty/man8/pg_upgradecluster.8.html
    # pg_upgradecluster [-v newversion] oldversion name [newdatadir]
    ```

    ## 4. Restart and check version

    1. Start the postgresql service
    `sudo systemctl restart postgresql.service`
    2. Log in as postgres user
    `sudo su postgres`
    3. Check your new postgres version
    `psql -c "SELECT version();"`

    ## 5. Cleanup

    **Cleanup** up the old version's mess (manual version of `pg_dropcluster` stated in [5.b]())
    1. Option 1 (Manual)
    1. Return as a normal(default user)
    `exit`
    2. Uninstall postgres packages **if present**
    `dpkg -l 'postgres*' | grep ^i`
    ```bash
    sudo apt-get remove postgresql-10 postgresql-client-10
    ```
    3. Remove the old postgresql directory **if present**
    `sudo rm -rf /etc/postgresql/10/`
    4. Login as postgres user
    `sudo su postgres`
    5. Go inside psql folder (`/var/lib/postgresql`)
    ```bash
    ./delete_old_cluster.sh # by the time this guide was written: equivalent to rm -rf '/var/lib/postgresql/10/main'
    rm -rf 10 && rm -rf delete_old_cluster.sh
    ```
    2. Option 2 (Automatic)
    1. Use `pg_dropcluster`
    ```bash
    sudo pg_dropcluster 10 main --stop
    ```
    If everything works well in 2-3, we dont have to apply the backup as we have already migrated the data from the older version to the newer version, the backup is just in case if anything goes wrong.
    ### Sources
    - [StackOverflow: how-to-upgrade-postgresql-database-from-10-to-12-without-losing-data](https://stackoverflow.com/questions/60409585/how-to-upgrade-postgresql-database-from-10-to-12-without-losing-data-for-openpro)
    - [cloud.google.com/setting-up-postgres](https://cloud.google.com/community/tutorials/setting-up-postgres)