Managing Your Scalingo for PostgreSQL® Addon

Scaling (Changing Plan)

You can change your database plan whenever you want. This operation happens instantly, no manual input is required. When you change the plan, the instances of your database are stopped and then restarted with the characteristics of the chosen plan. According to the type of plan you are originally using, the impact on your application differs:

  • Starter Plans: Since these plans are not highly available, expect some unavailability during which the scale operations are conducted. The duration highly depends on the database size (from seconds to several hours).
  • Business Plans: Thanks to high availability, only a failover occurs during the operation. Current connections are stopped and the application has to reconnect. It can be transparent or lead to a ~2-5 seconds interruption, depending on the driver used by your application and its configuration.

In both cases, once the operation is finished, the application is restarted to ensure it creates new healthy connections to the database.

Using the Dashboard

  1. From your web browser, open your dashboard
  2. Click on the application for which you want to scale the Scalingo for PostgreSQL® addon
  3. Click on the Resources tab
  4. Locate the Addons block and click on the button
  5. From the dropdown menu, select Change plan
  6. Select the new plan
  7. Click the Finish button
  8. Validate by clicking the Confirm plan change button

Using the Command Line

  1. Make sure you have correctly setup the Scalingo command line tool
  2. From the command line, list the plans available for postgresql:
    scalingo addons-plans postgresql
    

    The output should look like this:

    +----------------------------+---------------+
    |             ID             |     NAME      |
    +----------------------------+---------------+
    | postgresql-sandbox         | Sandbox       |
    | postgresql-starter-512     | Starter 512M  |
    | postgresql-starter-1024    | Starter 1G    |
    ...
    
  3. Locate the ID corresponding to the plan you want to scale to (for example postgresql-business-1024)
  4. 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 |
    ...
    
  5. Locate the ID corresponding to the addon you want to scale
  6. Change plan using the addons-upgrade sub-command:
    scalingo --app my-app addons-upgrade <addon_ID> <plan_ID>
    

    The output should look like this:

    -----> Addon ad-d0aa540a-5ed2-41f8-8bbe-91e3aff6623b has been upgraded
           Message from addon provider: Database plan is being changed
    

Using the Terraform Provider

  1. Update the plan property of the corresponding Resource block in your Terraform file to scale the addon:
    resource "scalingo_addon" "my-db" {
      provider_id = "postgresql"
      plan = "postgresql-business-1024"
      app = "${scalingo_app.my-app.id}"
    }
    

    In this example, we switch the my-db resource attached to the my-app application to a PostgreSQL Business 1024 addon.

  2. Run terraform plan and check if the result looks good
  3. If so, run terraform apply

Upgrading

Upgrading your PostgreSQL® addon consists in changing the database version for a newer one.

When the database vendor releases a new version of your database engine, we take some time to study it and test it thoroughly before making it available. Upgrading to this new version is still your choice. We don’t do it automatically.

Your database needs to be upgraded to the latest minor version before having access to the next major version. For instance, let’s imagine that your version is 2.3.x and you want to upgrade to 3.1.x. If there is a 2.5.x version available, you first need to upgrade your database to the 2.5.x version before upgrading to the 3.1.x version.

During the upgrade, a downtime can unfortunately happen, depending on the Plan you are using and the upgrade:

  • Starter Plans: In all cases, we have to stop the node to upgrade it, causing an inevitable downtime.
  • Business Plans: We are able to achieve zero-downtime upgrade of minor version. In the case of major version upgrade, we need to completely stop the nodes, hence causing an inevitable downtime.

In both cases, once the operation is finished, the application is restarted.

Using the Database Dashboard

  1. From your web browser, open your database dashboard
  2. Click the Overview tab
  3. Locate the Database Upgrade block
  4. If an upgrade is available, the text in the block explains what will be done.
  5. To launch the upgrade, click the Upgrade to … button

Details About Actions During a Major Upgrade

This section details the actions executed during a major upgrade (e.g. from PostgreSQL® 14 to PostgreSQL® 15):

  1. The entire cluster is stopped. The database is unreachable.
  2. pg_upgrade is executed on the primary node. A pessimistic estimation of the duration of this operation is 1 minute per 10 GiB.
  3. The primary node is restarted. The database is reachable again. The application can normally use the database.
  4. SQL query ANALYZE in three stages is executed against your database to build up PostgreSQL statistics and ensure the query planner is making the right choice of indices.
    • First stage should finish in a matter of seconds according to your data size and should allow most requests from your applications to be running at nominal performance.
    • Second and third stages are incrementally longer but allow PostgreSQL to build up again full internal statistics about the database data and its structure, ensuring a full recovery of performance from before the upgrade.
  5. A base backup is asynchronously done to make point-in-time recovery possible again.
  6. The follower is built from scratch, based on the primary node data (database lives in degraded replication mode until its full recovery).

Managing Users

By default, Scalingo creates a user with read and write permissions on your database with the following queries:

GRANT CREATE ON SCHEMA public TO <username>
GRANT ALL PRIVILEGES ON DATABASE <database> TO <username>
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO <username>
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO <username>
ALTER DEFAULT PRIVILEGES FOR USER <database> IN SCHEMA public GRANT ALL ON TABLES TO <username>
ALTER DEFAULT PRIVILEGES FOR USER <database> IN SCHEMA public GRANT ALL ON SEQUENCES TO <username>

Listing Users

Using the Database Dashboard

  1. From your web browser, open your database dashboard
  2. Click the Users tab
  3. The list of available users is displayed under the User Management section

Using the Command Line

  1. Make sure you have correctly setup the Scalingo command line tool
  2. From the command line, run the following command to list the users:
    scalingo --app my-app --addon postgresql database-users-list
    

    The output should look like this:

    +----------------+-----------+-----------+---------------------+
    |    USERNAME    | READ-ONLY | PROTECTED | PASSWORD ENCRYPTION |
    +----------------+-----------+-----------+---------------------+
    | my_app_4553    | false     | true      | SCRAM-SHA-256       |
    | my_app_4553_rw | false     | false     | MD5                 |
    | my_app_4553_ro | true      | false     | MD5                 |
    +----------------+-----------+-----------+---------------------+
    

    In this example, we can see that the database has 3 users available. One has been created along with the addon and is protected, which means it can’t be removed. Among the two others, one can only read data from the database. We can also see the password encryption algorithm.

Creating a New User

The following restrictions apply when creating a new user, regardless of the method used:

  • Username:
    • Must be between 6 and 32 characters long
    • Can only contain alphanumerical characters and underscores (_)
    • Must start with a letter
  • Password:
    • Must be between 8 and 64 characters long.
    • Must not contain the character " or '

Using the Database Dashboard

  1. From your web browser, open your database dashboard
  2. Click the Users tab
  3. Fill the Add a new user form:
    • Fill a username
    • Fill the corresponding password twice
  4. (optional) If you want to restrict this user to read only abilities, make sure to check the Read only checkbox
  5. Validate by clicking the Add User button

Using the Command Line

  1. Make sure you have correctly setup the Scalingo command line tool
  2. From the command line, run the following command to create a new user:
    scalingo --app my-app --addon postgresql database-users-create <username>
    

    Optionally, if you want to restrict this user to read only abilities, use the --read-only flag:

    scalingo --app my-app --addon postgresql database-users-create --read-only <username>
    
  3. Set the user password:
    • Either chose a password and confirm it
    • Or let the platform generate a password by leaving the field empty

    The output should look like this:

    • If you set a password:
      User "my_user" created.
      
    • If you let the platform generate a password:
      User "my_user" created with password "YANs3y07m5_KJC2MSDGebh8tx1lliFWh2Yb239zVqGQvbElWDjIN7QWspVH92Ul8".
      

Updating a User Password

Using the Command Line

  1. Make sure you have correctly setup the Scalingo command line tool
  2. Update the user password with the following command:
    scalingo --app my-app --addon postgresql database-users-update-password <username>
    
  3. An interactive prompt asks you for a password you want to attribute to your user:
    • Either chose a password and confirm it
    • Or let the platform generate a password by leaving the field empty The output should look like this:
    • If you set a password:
      User "my_user" created.
      
    • If you let the platform generate a password:
      User "my_user" created with password "YANs3y07m5_KJC2MSDGebh8tx1lliFWh2Yb239zVqGQvbElWDjIN7QWspVH92Ul8".
      

Deleting a User

Using the Database Dashboard

  1. From your web browser, open your database dashboard
  2. Click the Users tab
  3. Locate the user you want to remove
  4. Click the Delete button next to the user
  5. Confirm the removal

Using the Command Line

  1. Make sure you have correctly setup the Scalingo command line tool
  2. Remove the user with the following command:
    scalingo --app my-app --addon postgresql database-users-delete <username>
    

Managing Extensions

PostgreSQL® is a database engine which is extensible thanks to a large set of extensions. A lot of them are installed alongside your PostgreSQL® instance, but you need to enable those manually according to your needs.

Please refer to the Overview page to get the list of available extensions.

Enabling an Extension

Using the Command Line

  1. Access your database using the Interactive Remote Console
  2. From the PostgreSQL® console, run the following command:
    CREATE EXTENSION IF NOT EXISTS <extension_name>;
    

    The output should look like this:

    CREATE EXTENSION
    my_app_4553=>
    

Disabling an Extension

Using the Command Line

  1. Access your database using the Interactive Remote Console
  2. From the PostgreSQL® console, run the following command:
    DROP EXTENSION IF EXISTS <extension_name>;
    

    The output should look like this:

    DROP EXTENSION
    my_app_4553=>
    

Suggest edits

Managing Your Scalingo for PostgreSQL® Addon

©2024 Scalingo