Backing Up Your Scalingo for PostgreSQL® Addon

Starter and Business plans of Scalingo for PostgreSQL® include automated and managed backups so you don’t have to worry about them.

We use two kinds of mechanisms to create these automated backups: Point-in-Time Recovery backups and On-Demand backups. Manual backups are also available for these plans.

Understanding Point-in-Time Recovery Backups

Point-in-time recovery (PiTR) allows you to ask for the restoration of your data at a specific date. We achieve this by making a full PiTR backup of the database weekly. Between two full PiTR backups, we keep track of the write-ahead logs (WAL). The WAL contains all the modification instructions. By replaying the WAL from a full PiTR backup to a specific date, we are able to rebuild the state of the database at that particular date.

You have nothing to do to be able to use the PiTR mechanism. Be aware that you can only use the PiTR on the period between now and now minus seven days.

Retention Policy for PiTR Backups

Plan PiTR Backup Retained
Sandbox N/A
Starter 1
Business 1

Understanding On-Demand Backups

On-Demand backups are done on a daily basis. They consist in dumping your database in an archive that we keep during a certain amount of time.

For Business plans, the backup is done on the secondary node to avoid any impact on your primary node.

Retention Policy for On-Demand Backups

We keep a limited amount of backups depending on your database plan:

  • A daily backup is retained for the last 7 days, which means that we will keep a maximum of 7 daily backups (one for each of the last 7 days).
  • A weekly backup means that only one backup is saved over a 7 days period.
  • A monthly backup means that only one backup is saved over the course of a month.
Plan Weekly Backups Retained Monthly Backups Retained
Sandbox N/A N/A
Starter 4 weeks 0 month
Business 8 weeks 12 months

Configuring On-Demand Backups

By default, On-Demand backups are done around 1:00 AM Central European Time (CET or UTC+0100). This time can be modified.

Using the Database Dashboard

  1. From your web browser, open your database dashboard
  2. Click the Backups tab
  3. Locate the Backup Settings block
  4. Make sure the Periodic backups toggle is set to enabled
  5. Chose the time and select the timezone
  6. Click the Set button to validate

Using the Command Line

  1. Make sure you have correctly setup the Scalingo command line tool
  2. From the command line, list the addons attached to your application:
    scalingo --app my-app addons
    

    The output should look like this:

    +------------+-----------------------------------------+------------------------+---------+
    |   ADDON    |                   ID                    |          PLAN          | STATUS  |
    +------------+-----------------------------------------+------------------------+---------+
    | PostgreSQL | ad-871546ad-943a-4929-9770-ec7c839d65f5 | postgresql-starter-512 | running |
    ...
    
  3. Locate the ID corresponding to the addon for which you want to configure the backup schedule
  4. Configure the time of backup:
    • By setting an hour:
      scalingo --app my-app addon <addon_ID> backups-config --schedule-at 3
      

      In this example, we ask the platform to create the backups at ~03:00 CET.

    • By setting an hour and a timezone:
      scalingo --app my-app addon <addon_ID> backups-config --schedule-at "4:00 UTC"
      

      In this example, we ask the platform to create the backup at ~04:00 UTC.

    The output should look like this:

    -----> Periodic backups will be done daily at 6:00 CET
    

Downloading an On-Demand Backup

Using the Database Dashboard

  1. From your web browser, open your database dashboard
  2. Click the Backups tab
  3. Locate the Backups block
  4. Locate the On-Demand backup you are interested in
  5. Click the corresponding Download button

Using the Command Line

  1. From the command line, list the addons attached to your application:
    scalingo --app my-app addons
    

    The output should look like this:

    +------------+-----------------------------------------+------------------------+---------+
    |   ADDON    |                   ID                    |          PLAN          | STATUS  |
    +------------+-----------------------------------------+------------------------+---------+
    | PostgreSQL | ad-871546ad-943a-4929-9770-ec7c839d65f5 | postgresql-starter-512 | running |
    ...
    
  2. Locate the ID of the database addon you are interested in
  3. Run the following command(s):
    • To download the latest backup available:
      scalingo --app my-app --addon <addon_ID> backups-download
      

      The output should look like this:

      -----> Selected the most recent successful backup
      139.37 KiB / 139.37 KiB [----------------------------------] 100.00% ? p/s
      ===> 20231207000608_my_app_4553.tar.gz
      

      The backup is downloaded in your current working directory.

    • To download a specific backup:
      1. List the backups available for this database addon:
        scalingo --app my-app --addon <addon_ID> backups
        

        The output should look like this:

        +--------------------------+--------------------------------+--------+--------+
        |            ID            |           CREATED AT           |  SIZE  | STATUS |
        +--------------------------+--------------------------------+--------+--------+
        | 65710b0a99c3cd23d455edee | Thu, 07 Dec 2023 01:00:10 CET  | 143 kB | done   |
        | 656fb98b99c3cd23d455d4e7 | Wed, 06 Dec 2023 01:00:11 CET  | 143 kB | done   |
        | 656e680a99c3cd23d455c1f0 | Tue, 05 Dec 2023 01:00:10 CET  | 143 kB | done   |
        ...
        
      2. Locate the ID of the backup you want to download
      3. Download the backup:
        scalingo --app my-app --addon <addon_ID> backups-download --backup <backup_ID>
        

        The output should look like this:

        79.10 KiB / 79.10 KiB [---------------------------------] 100.00% ? p/s
        ===> 20230305000044_my_app_4553.tar.gz
        

        The backup is downloaded in your current working directory.

Understanding Manual Backups

Manual backups use the exact same mechanism as On-Demand backups, except that they are not automated. As the name suggests, Manual backups are triggered manually, whenever you want.

Retention Policy for Manual Backups

We keep a limited amount of Manual backups depending on your database plan:

Plan Backups Retained
Sandbox N/A
Starter 10
Business 50

When a database is removed from an application, the retention policy remains untouched: backups are not instantly deleted.

Creating a Manual Backup

Using the Database Dashboard

  1. From your web browser, open your database dashboard
  2. Click the Backups tab
  3. Locate the Backup Settings block
  4. Click the Make Manual Backup button

Using the Command Line

  1. Make sure you have correctly setup the Scalingo command line tool
  2. From the command line, list the addons attached to your application:
    scalingo --app my-app addons
    

    The output should look like this:

    +------------+-----------------------------------------+------------------------+---------+
    |   ADDON    |                   ID                    |          PLAN          | STATUS  |
    +------------+-----------------------------------------+------------------------+---------+
    | PostgreSQL | ad-871546ad-943a-4929-9770-ec7c839d65f5 | postgresql-starter-512 | running |
    ...
    
  3. Locate the ID corresponding to the addon for which you want to configure the backup schedule
  4. Ask the platform to backup the database:
    scalingo --app my-app --addon <addon_ID> backups-create
    

    After a while, the output should look like this:

    -----> Backup successfully finished
    

Downloading a Manual Backup

Please refer to Downloading an On-Demand Backup section, as the process is exactly the same.

Dumping the Database

There are different ways to dump (and restore) a PostgreSQL® database. We generally advise to either:

From a One-Off Container

This method has two main advantages:

  • It doesn’t require to make your database reachable over Internet
  • You won’t have to tweak your connection URI
  1. Follow the procedure to access your PostgreSQL® database from a One-Off container
  2. From the One-Off command line, download the Scalingo Command Line Tool using our install-scalingo-cli helper tool:
    install-scalingo-cli
    
  3. Login to Scalingo:
    scalingo login
    
  4. Dump the database:
    pg_dump --clean --if-exists --format c --dbname "${SCALINGO_POSTGRESQL_URL}" --no-owner --no-privileges --no-comments --exclude-schema 'information_schema' --exclude-schema '^pg_*' --file dump.pgsql
    

From Your Workstation

  1. Open a DB tunnel so you can access your database from your workstation
  2. Adjust the connection URI:
    export SCALINGO_DATABASE_URL="postgresql://<user>:<password>@127.0.0.1:<port>/<dbname>"
    

    With user, password and dbname from your original connection URI and port depending on what you did (default is 10000)

  3. Dump the database using the pg_dump command (you may have to install it):
    pg_dump --clean --if-exists --format c --dbname "${SCALINGO_POSTGRESQL_URL}" --no-owner --no-privileges --no-comments --exclude-schema 'information_schema' --exclude-schema '^pg_*' --file dump.pgsql
    

Suggest edits

Backing Up Your Scalingo for PostgreSQL® Addon

©2024 Scalingo