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 and click on 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 examplepostgresql-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 themy-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
- Click the Overview tab
- Locate the Database Upgrade block
- If an upgrade is available, the text in the block explains what will be done.
- 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):
- 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 Users tab
- The list of available users is displayed under the User Management section
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 Users tab
- Fill the Add a new user form:
- Fill a username
- Fill the corresponding password twice
- (optional) If you want to restrict this user to read only abilities, make sure to check the Read only checkbox
- Validate by clicking the Add 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 Users tab
- Locate the user you want to remove
- Click the Delete button next to the user
- Confirm the removal
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.
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=>