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

From your web browser, open your dashboard
Click on the application for which you want to scale the Scalingo for PostgreSQL® addon
Click on the Resources tab
Locate the Addons block
Click the “…“ button
From the dropdown menu, select Change plan
Select the new plan
Click the Finish button
Validate by clicking the Confirm plan change button

Using the Command Line

Make sure you have correctly setup the Scalingo command line tool

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    |
...

Locate the ID corresponding to the plan you want to scale to (for example postgresql-business-1024)

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 |
...

Locate the ID corresponding to the addon you want to scale

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

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.
Run terraform plan and check if the result looks good
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 must be upgraded to the latest minor version before you can access the next major version. Each following major version will be suggested until you reach the latest version available on the platform.

Let’s assume your database is currently on PostgreSQL® 13.5 and you want to upgrade to PostgreSQL® 15. First, upgrade your database to the latest version of the 13.x branch. Then, proceed to the latest version of the 14.x branch. Finally, upgrade to the latest version of the 15.x branch.

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

From your web browser, open your database dashboard
Select the Settings tab
In the Settings submenu, select the General tab
Locate the Database Version block
If an upgrade is available, a button allows you to trigger the upgrade
Click the button to launch the upgrade process

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):

The entire cluster is stopped. The database is unreachable.
pg_upgrade is executed on the primary node. A pessimistic estimation of the duration of this operation is 1 minute per 10 GiB.
The primary node is restarted. The database is reachable again. The application can normally use the database.
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.
A base backup is asynchronously done to make point-in-time recovery possible again.
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

From your web browser, open your database dashboard
Click the Settings tab
In the Settings submenu, select Users
The list of available users is displayed in the User Management block

Using the Command Line

Make sure you have correctly setup the Scalingo command line tool

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 24 and 64 characters long.
- Must not contain the character " or '

Using the Database Dashboard

From your web browser, open your database dashboard
Click the Settings tab
In the Settings submenu, select Users
Fill the Add a user form:
- Fill a username
- (optional) To allow the new user to have writings abilities, make sure to check the Write (optional) checkbox
Validate by clicking the Add this user button

Using the Command Line

Make sure you have correctly setup the Scalingo command line tool

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>

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

Make sure you have correctly setup the Scalingo command line tool

Update the user password with the following command:

scalingo --app my-app --addon postgresql database-users-update-password <username>

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

From your web browser, open your database dashboard
Click the Settings tab
In the Settings submenu, select Users
Locate the user you want to remove
Click the … button next to the user
From the popup menu, select Delete user
In the popup window, confirm the deletion by typing the name of the user
Validate by clicking the Confirm button

Using the Command Line

Make sure you have correctly setup the Scalingo command line tool

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.

Viewing Enabled Extensions

Using the Database Dashboard

From your web browser, open your database dashboard
Click the Settings tab
In the Settings submenu, select Configuration
The list of enabled extensions is displayed in the PostgreSQL Extensions block

Enabling an Extension

Using the Command Line

Access your database using the Interactive Remote Console
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

Access your database using the Interactive Remote Console
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=>
```

Last update: 21 Oct 2024 Suggest edits