---
title: Upgrading your Database for PostgreSQL to the latest version
description: This page shows how to upgrade your Database for PostgreSQL to the latest version
tags: storage PostgreSQL Database rdb
dates:
  validation: 2025-06-02
  validation_frequency: 12
products:
  - postgresql-and-mysql
difficulty: beginner
usecase:
  - migration
ecosystem:
  - scaleway-only
---
import image from './assets/scaleway-database-information.webp'
import image2 from './assets/scaleway-database-restored.webp'

import Requirements from '@macros/iam/requirements.mdx'



[PostgreSQL](https://www.postgresql.org/) is a powerful, open-source object-relational database system with more than 30 years of active development. As major PostgreSQL releases regularly add new features, the layout of the system tables may change. However, the internal data storage format itself rarely changes. This allows you to upgrade a PostgreSQL database system without data loss using the backup and restore functions of the Database API.

In this tutorial, you will learn how to migrate your PostgreSQL databases from version 9.6 to 13. This can be achieved in two ways:

- Manually, using the console and the API. This could be a good option if you only have one database to migrate.
- Automatically, using a bash script based on the Scaleway CLIv2. This could be a good option if you have multiple databases to migrate, and/or are concerned about errors during a manual restore.

## Manual migration via the Scaleway console and API

<Message type="important">
  This migration method is not suitable for Database Instances with extensions installed. In such cases, we recommend using the [automatic migration method with our script](#automatic-migration-via-a-bash-script-using-the-scaleway-cli-v2) or doing a manual dump/restore.
</Message>

There are three steps to completing a manual migration: creating a new PostgreSQL 13 instance, backing up your existing database using the Scaleway API, and finally restoring your backup on your new PostgreSQL 13 Instance. We walk you through these three steps below.

<Requirements />

- A Scaleway account logged into the [console](https://console.scaleway.com)
- [Owner](/iam/concepts/#owner) status or [IAM permissions](/iam/concepts/#permission) allowing you to perform actions in the intended Organization
- A valid [API key](/iam/how-to/create-api-keys/)
- A PostgreSQL database running on version 9.6
- Installed `curl` on your local computer

### Creating a PostgreSQL 13 Instance

<Message type="note">
  This tutorial works with all versions of PostgreSQL. If you prefer another version than PostgreSQL 13, select your preferred version during the Instance creation.
</Message>

1. Open the [Scaleway console](https://console.scaleway.com) in a web browser and click **Managed Databases** in the **Storage** section of the side menu. The list of your databases displays.
2. Click **+ Create a Database Instance**. The [Database Instance creation wizard](https://console.scaleway.com/rdb/instances/create) displays.
3. Select **PostgreSQL-13** from the drop-down list and fill in the [required information](/managed-databases-for-postgresql-and-mysql/how-to/create-a-database/) for your new database Instance. Then click **Create an Instance**. Your database is created, and you are redirected to the database dashboard.
    <Message type="note">
      Ensure your new Database Instance is located in the same geographical region as your old one.
    </Message>
4. Click the **Users** tab and replicate the users of your existing Database Instance.

### Backing up your existing database using the Scaleway API

<Requirements />

- Generated your [API key](/iam/how-to/create-api-keys/)
- Installed `curl` on your local computer

1. Retrieve the database ID of your **old** Database Instance. You can find it on the **Database Instance Information** page of your Instance:
    <Lightbox image={image} alt="" />
2. Make a backup of your logical database(s) using the API:
    ```bash
    curl -X POST -H "Content-Type: application/json" \
    -H "X-Auth-Token: $SECRET_KEY" https://api.scaleway.com/rdb/v1/<region>/fr-par/backups -d '{
    "instance_id": "<database_instance_id>",
    "database_name": "<my_database>",
    "name": "<backup_name>",
    "expires_at": "<expiry_date>"
    }'
    ```
3. Replace the following information in the command above:
    * `$SECRET_KEY`: Your Scaleway API [secret key](/iam/concepts/#api-key)
    * `<region>`: The region of your Database Instance. It can either be `fr-par`, `nl-ams`, or `pl-waw`.
    * `<database_instance_id>`: The ID of your **old** Database Instance, for example: `ad085d32-16e0-4ce6-862c-8e70c56b9ee7`.
    * `<my_database>`: The name of your PostgreSQL database, for example, `customer_data`.
    * `<backup_name>`: A common name for your database backup, for example, `customer_data_backup`.
    * `<expiry_date>`: The expiration date for your backup in [ISO8601](https://www.iso.org/iso-8601-date-and-time-format.html) format, for example: `2021-06-26T13:00:00Z`.

    You will receive a [JSON formatted](https://www.json.org/json-en.html) output with information about the backup:

    ```json
    {
        "created_at": "2021-06-24T12:07:17.368668Z",
        "database_name": "customer_data",
        "download_url": null,
        "download_url_expires_at": null,
        "expires_at": "2021-06-26T13:00:00Z",
        "id": "7feaab67-0f22-4170-96cc-6fbb11c57256",
        "instance_id": "ad085d32-16e0-4ce6-862c-8e70c56b9ee7",
        "instance_name": "rdb-nostalgic-dubinsky",
        "name": "customer_data_backup",
        "region": "fr-par",
        "size": null,
        "status": "creating",
        "updated_at": null
    }
    ```
    <Message type="note">
      If you want to migrate several logical databases, repeat the command above for each of them.
    </Message>

### Restoring your backup on your new Database Instance

<Message type="important">
  Ensure that you have replicated your database users and permissions on your new Instance before continuing with the following steps.
</Message>

1. Retrieve the database ID of your **new** Database Instance. You can find it on the **Database Instance Information** page of your Instance.
2. Restore the backup of your logical database(s) using the API:
    ```bash
    curl -X POST -H "Content-Type: application/json" \
    -H "X-Auth-Token: $SECRET_KEY" https://api.scaleway.com/rdb/v1/regions/<region>/backups/<backup_id>/restore -d '{
    "database_name": "<my_database>",
    "instance_id": "<new_database_instance_id>"
    }'
    ```
    Replace the following information in the command above:

    * `$SECRET_KEY`: Your Scaleway API [secret key](/iam/concepts/#api-key)
    * `<region>`: The region of your Database Instance. It can either be `fr-par`, `nl-ams`, or `pl-waw`.
    * `<backup_id>`: The ID of your database backup, for example: `7feaab67-0f22-4170-96cc-6fbb11c57256`.
    * `<my_database>`: The name of your PostgreSQL database, for example: `customer_data`.
    * `<database_instance_id>`: The ID of your **new** Database Instance, for example: `d401ff10-350d-4707-9571-c861677f0031`.

    You will receive a [JSON formatted](https://www.json.org/json-en.html) output with information about the backup. The **status** should be `restoring`:

    ```json
    {
        "created_at": "2021-06-24T12:07:17.368668Z",
        "database_name": "dvdrental",
        "download_url": "null",
        "expires_at": "2021-06-26T13:00:00Z",
        "id": "7feaab67-0f22-4170-96cc-6fbb11c57256",
        "instance_id": "ad085d32-16e0-4ce6-862c-8e70c56b9ee7",
        "instance_name": "rdb-nostalgic-dubinsky",
        "name": "customer_data_backup",
        "region": "fr-par",
        "size": 661389,
        "status": "restoring",
        "updated_at": "2021-06-24T12:07:21.775783Z"
    }
    ```

    If you want to restore several logical databases, repeat the command above for each of them.

    <Message type="note">
      Depending on the size of your backup, restoring might take a moment.
    </Message>
3. The database appears in the **Managed Databases** tab of your new Database Instance, once the backup has been imported:
    <Lightbox image={image2} alt="" />

    <Message type="tip">
      Optionally, you can verify the status of your database with a tool like [pgAdmin 4](/tutorials/manage-database-instance-pgadmin4/).
    </Message>
4. Update your applications with the endpoint (IP address and port) of the new Database Instance.
5. Once the migration is complete, [delete](/managed-databases-for-postgresql-and-mysql/how-to/delete-a-database/) the old Database Instance from your account.

## Automatic migration via a bash script using the Scaleway CLI v2

This solution enables you to migrate your database(s) automatically, with a bash script using the Scaleway CLI v2. The script will walk you through the migration process.

<Requirements />

- A PostgreSQL database running on version 9.6
- Installed the [Scaleway CLI v2](https://github.com/scaleway/scaleway-cli) on your machine and the relevant [PostgreSQL binaries](https://www.postgresql.org/download/) on your machine

1. Copy and paste the following script into a text file, and save it as `db_mig_script.sh`.
    ```bash
        #!/bin/bash -l

        colred='\033[0;31m' # Red
        colgrn='\033[0;32m' # Green
        colylw='\033[0;33m' # Yellow
        colblu='\033[0;34m' # Blue
        colrst='\033[0m'    # Text Reset

        verbosity=5 # default to show info
        silent_lvl=0
        crt_lvl=1
        err_lvl=2
        wrn_lvl=3
        inf_lvl=4
        dbg_lvl=5

        log_notify() { log $silent_lvl "NOTE: $1"; } # Always prints
        log_critical() { log $crt_lvl "${colred}CRITICAL:${colrst} ---  $1"; }
        log_error() { log $err_lvl "${colred}ERROR${colrst} --- $1"; }
        log_warn() { log $wrn_lvl "${colylw}WARNING${colrst} - $1"; }
        log_info() { log $inf_lvl "${colgrn}INFO${colrst} ---- $1"; }
        log_debug() { log $dbg_lvl "${colblu}DEBUG${colrst} --- $1"; }
        log() {
            if [ $verbosity -ge "$1" ]; then
                datestring=$(date +'%Y-%m-%d %H:%M:%S')
                echo -e "$datestring - $2"
            fi
        }

        # ----------------------------------------------------------------------------------------------------------------------------------------------

        # This script is designed to migrate a Scaleway RDB instance to a new PostgreSQL major version
        # It requires Scaleway CLI v2 as well as PostgreSQL binaries

        MIGRATION_USER_PASSWORD=$(openssl rand -base64 14)
        MIGRATION_USER="rdb_migration"
        REGION="fr-par"
        CLI_COMMAND="scw rdb"

        # Wrapper to call rdb cli with json output including error
        run_rdb_cli() {
            $CLI_COMMAND "$@" region=$REGION -o json 2>&1
        }

        # Get the instance major version
        instance_major_version () {
            run_rdb_cli instance get "$1" | jq -r .engine | awk -F'-' '{print $2}' | awk -F'.' '{print $1}'
        }

        # Create a new migration user
        create_migration_user () {
            run_rdb_cli user create name="$MIGRATION_USER" password="$MIGRATION_USER_PASSWORD" instance-id="$1"
        }

        # Build and return PostgreSQL constring
        get_conn_string () {
            endpoint=$(run_rdb_cli instance get "$1" | jq -r '.endpoint')
            port=$(echo "$endpoint" | jq .port)
            ip=$(echo "$endpoint" | jq -r .ip)
            echo "-h $ip --port $port -U $MIGRATION_USER"
        }

        # Helper to validate or exit on user input
        validate_action () {
            while true; do
                read -rp "Do you want to continue? [y/n] " yn
                case $yn in
                    [Yy]* ) break;;
                    [Nn]* ) exit;;
                    * ) echo "Please answer yes or no.";;
                esac
            done
        }

        # Helper to get the Scaleway region to use
        get_region () {
            echo ""
            while true; do
                read -rp "Specify your Scaleway Zone. Default fr-par [fr-par/nl-ams/pl-waw] " region
                echo "$region"
                case $region in
                    [fr-par|nl-ams|pl-waw]* ) REGION=$region; break;;
                    '') break;;
                    * ) echo "Please answer in [fr-par/nl-ams/pl-waw] ";;
                esac
            done
        }

        # Check if instance_id exists and is reachable with current cli configuration
        check_instance () {
            if [ "$1" = "" ]; then
                log_error "You must specify an instance_id"
                exit 1;
            fi
            res=$(run_rdb_cli instance get "$1")
            if [ "$( jq 'has("message")' <<< "$res" )" == "true" ]; then
                err=$(jq -r .message <<< "$res")
                log_error "Error while getting database $1: $err"
                exit 1;
            fi
        }

        echo -e "Welcome to Scaleway RDB PostgreSQL Migration tool\nThis tool is designed to migrate your Databases from one major PostgreSQL version to another\n"
        echo -e "Please make sure, you got Scaleway V2 CLI (https://github.com/scaleway/scaleway-cli) as well as PostgreSQL binaries\n"

        scw version > /dev/null
        if [ $? -ne 0 ]; then
            log_error "The Scaleway CLI must be installed see https://github.com/scaleway/scaleway-cli"
            exit 1
        fi

        jq --version > /dev/null
        if [ $? -ne 0 ]; then
            log_error "jq tool must be installed"
            exit 1
        fi

        get_region
        log_info "Selected $REGION region"

        # List RDB Instances in the selected region
        echo "$CLI_COMMAND instance list region=$REGION"
        $CLI_COMMAND instance list region="$REGION"
        read -rp "Which is the source database instance? " old_instance
        check_instance "$old_instance"
        read -rp "Which is the destination database instance? " new_instance
        check_instance "$new_instance"

        # Check instance versions
        old_major_version=$(instance_major_version "$old_instance")
        new_major_version=$(instance_major_version "$new_instance")
        if [ "$old_major_version" -ge "$new_major_version" ]; then
            log_error "New instance major version must be superior to the old one";
            exit 1
        fi

        binaries_version=$(pg_restore --version | awk '{print $3}' | awk -F'.' '{print $1}')
        if [ $? -ne 0 ]; then
            "You need to install the PostgreSQL binaries"
            exit 1
        fi
        if [ "$binaries_version" != "$new_major_version" ]; then
            log_info "PostgreSQL binaries installed $binaries_version"
            log_error "You need to install $new_major_version PostgreSQL binaries version";
            exit 1
        fi

        # Get the source logical databases
        instance_dbs=$(run_rdb_cli database list instance-id="$old_instance")
        managed_dbs=$(jq -c '.[] | select( .managed == true )' <<< "$instance_dbs" | jq -r .name)
        not_managed_dbs=$(jq -c '.[] | select( .managed == false )' <<< "$instance_dbs" | jq -r .name)

        if [ -n "$managed_dbs" ]; then
            log_info "This script is able to migrate following databases:"
            for db in $managed_dbs; do
                echo "- $db"
            done;
        fi

        if [ -n "$not_managed_dbs" ]; then
            log_warn "The following databases are not managed by RDB and won't be migrated:"
            for db in $not_managed_dbs; do
                echo "- $db"
            done;
            validate_action
        fi

        # Create a migration user to read and write data on both Instances
        log_info "Create migration users"
        for instance in $old_instance $new_instance; do
            log_info "Create migration user on instance $instance";
            res=$(create_migration_user "$instance")

            # Error management
            if [ "$( jq 'has("hint")' <<< "$res" )" == "true" ]; then
                hint=$(jq -r .hint <<< "$res")
                if [ ! "$hint" = "User already exists" ]; then
                    log_error "Unable to create user $MIGRATION_USER on $instance instance: $hint"
                    exit 1;
                else
                    log_warn "User $USER_MIGRATION already exist on instance $instance. Update its password?"
                    validate_action
                    run_rdb_cli user update instance-id="$instance" name=$MIGRATION_USER password="$MIGRATION_USER_PASSWORD" is-admin=true > /dev/null;
                fi;
            fi;
        done

        # Dump and restore the databases
        log_info "Migrate databases"
        for db in $managed_dbs; do
            if [ "$db" != "rdb" ]; then

                log_info "Create $db on instance $new_instance"
                res=$(run_rdb_cli database create instance-id="$new_instance" name="$db")

                # Error management
                if [ "$( jq 'has("hint")' <<< "$res" )" == "true" ]; then
                    hint=$(jq -r .hint <<< "$res")
                    if [ ! "$hint" = "Database already exists" ]; then
                        log_error "Unable to create database $db on new instance: $hint"
                        exit 1;
                    else
                        log_warn "Database $db already exists, it won't be recreated"
                    fi;
                fi;
            fi;

            log_info "Set ALL privilege on $db to $MIGRATION_USER"
            run_rdb_cli privilege set instance-id="$new_instance" database-name="$db" user-name=$MIGRATION_USER permission=all > /dev/null

            # Dump and restore the database from old_instance to new instance
            old_instance_connstring=$(get_conn_string "$old_instance")
            new_instance_connstring=$(get_conn_string "$new_instance")

            log_info "Dump and restore database $db"
            log_debug "pg_dump $old_instance_connstring -Fc --create $db | pg_restore $new_instance_connstring --dbname $db --no-privileges --no-owner"
            PGPASSWORD=$MIGRATION_USER_PASSWORD pg_dump $old_instance_connstring -Fc --create "$db" | PGPASSWORD=$MIGRATION_USER_PASSWORD  pg_restore $new_instance_connstring --dbname "$db" --no-privileges --no-owner
            log_info "Database $db migrated to $new_instance"
        done;
    ```
2. Make the script executable using the `chmod` command:
    ```
    chmod +x db_mig_script.sh
    ```
3. Run the script from your terminal by typing `./db_mig_script.sh`

    The script will guide you through the migration process, asking you to complete the necessary information for your migration as required.


You now have successfully migrated your managed PostgreSQL database(s) to version 13 of the database engine. For more information about all new features and improvements in this version refer to the [PostgreSQL release notes](https://www.postgresql.org/docs/release/) and the [official documentation](https://www.postgresql.org/docs/). To learn more about Scaleway's powerful REST-API, refer to our [developers' documentation](https://www.scaleway.com/en/developers/api/managed-databases-for-postgresql-and-mysql/).