Database Management

Instructions for managing your application databases in the Flynn hosting environment used by your Satorix Hosting Cluster.

Database resource details

Your Satorix Hosting Cluster has database resources built in to allow the easy deployment of your application. This is currently PostgreSQL 11.0, MySQL (MariaDB 10.1), MongoDB 3.2, and Redis 3.0.

PostgreSQL

If you have selected a multi-host cluster for your Satorix Hosting Environment then the PostgreSQL 11.0 database is in a highly-available configuration that fails over to a synchronous replica if the primary goes down. The PostgreSQL appliance is configured to provide full consistency and partition tolerance for operations committed to the write-ahead log (WAL).

Currently the built in PostgreSQL database of your Satorix Hosting Cluster has no support for tuning so we do not recommend using this database for applications that have a high throughput or an excessive amount of records.

MySQL

If you have selected a multi-host cluster for your Satorix Hosting Environment then the MariaDB 10.1 database is in a highly-available configuration that fails over to a synchronous replica if the primary goes down.

Currently the built in MySQL database of your Satorix Hosting Cluster has no support for tuning so we do not recommend using this database for applications that have a high throughput or an excessive amount of records.

MongoDB

If you have selected a multi-host cluster for your Satorix Hosting Environment then the MongoDB 3.2 database is in a highly-available configuration with automatic provisioning. The MongoDB replication is implemented using the MongoDB replica set feature.

Since the MongoDB database uses a replica set for replication it is unsafe by default, all operations are replicated asynchronously.

To ensure your writes to the database the majority write concern should be used. This will make sure that writes are replicated before being acknowledged by the client. If you need read consistency/isolation then a majority read concern must also be used. This is to ensure writes can only be read if they have been committed to all the cluster members.

Redis

The Redis 3.2 database currently installed on your Satorix Hosting Cluster is in a single process configuration. The data stored is ephemeral and is intended for caching or development use.

There are no safety or availability configurations in place for the Redis database. Data loss and inconsistency is likely. You should treat any data stored in the database as ephemeral and prone to loss at any time.

Importing an existing Postgresql database

flynn -a projectname pg restore -f postgres-db.dump

Note: If the database exists already and you want to make sure the import is perfectly clean do the following.

  • First scale down your application: 
    flynn -a projectname scale web=0
  • Then remove all existing tables and data from the database: 
    flynn -a projectname pg psql -- -c "
    DO \$\$ DECLARE r RECORD;
      BEGIN
        FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = current_schema()) LOOP
          EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(r.tablename) || ' CASCADE';
        END LOOP;
      END \$\$;
"
    THE ABOVE PSQL COMMAND WILL DESTROY ALL EXISTING DATA!

Importing an existing MySQL database

flynn -a projectname mysql restore -f mysql-db.dump