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 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
- 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
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 8 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=>