How to migrate database changes between environments

Goal

Import changes to a database in a child environment to its parent using the Platform.sh CLI.

Assumptions

This guide assumes:

  • an active application on Platform.sh configured to a database
  • A local repository with the Platform.sh project as git remote
  • The Platform.sh CLI installed locally
  • An SSH key configured on the project account
  • admin role granted for the project

Problems

Changes to a database in master will be visible to its child environments when they are synchronized, however changes made to a development environment - such as adding an admin user (alan) to a PostgreSQL database - do not run in the opposite direction and will not be available in master.

In a Django application, admin user alan is visible from the dev branch.

$ platform db:sql
psql (11.1 (Debian 11.1-3.pgdg90+1), server 9.6.11)
Type "help" for help.

main=> SELECT * FROM auth_user;
 id |          password         |          last_login           | is_superuser | username | first_name | last_name | email | is_staff | is_active |          date_joined          
----+---------------------------+-------------------------------+--------------+----------+------------+-----------+-------+----------+-----------+-------------------------------
  2 |      <password hash>      | 2019-03-12 15:52:38.379839+00 | t            | chad     |            |           |       | t        | t         | 2019-03-07 16:02:26.583956+00
  6 |      <password hash>      | 2019-03-12 15:48:02+00        | t            | alan     |            |           |       | t        | t         | 2019-03-12 15:47:19+00
(2 rows)

alan is not visible in master, however.

$ platform db:sql
psql (11.1 (Debian 11.1-3.pgdg90+1), server 9.6.11)
Type "help" for help.

main=> SELECT * FROM auth_user; 
id |          password         |          last_login           | is_superuser | username | first_name | last_name | email | is_staff | is_active |          date_joined          
----+---------------------------+-------------------------------+--------------+----------+------------+-----------+-------+----------+-----------+-------------------------------
 2 |      <password hash>      | 2019-03-11 14:40:56.401026+00 | t            | chad     |            |           |       | t        | t         | 2019-03-07 16:02:26.583956+00
(1 row)

Note: Importing a database into an active environment is a destructive operation.
This guide assumes that the only change made between master and dev is the addition of a new admin user in dev. Synchronize dev if there is data in master you do not want to be overwritten.

$ git checkout dev    
$ platform environment:synchronize    

Platform.sh also strongly recommends that you take a backup of the target environment before executing.

$ git checkout master
$ platform backup:create

Migrating changes between environments can be done in two ways:

  1. Restoring a backup to the target environment
  2. Manually dumping and importing the database to the target environment

Steps (Restoring a backup to the target environment)

1. Create a backup

Create a backup of the dev environment that contains the database changes

$ git checkout dev   
Switched to branch 'dev'    
$ platform backup:create    
Creating a backup of dev   
Waiting for the backup to complete...   
Waiting for the activity m3qerwnkyblje (User created a backup of dev):
    Backing up dev
    Backup name is <backup name>   
[============================] 14 secs (complete)   
A backup of environment dev has been created   
backup name: <backup name>    

2. Restore the backup from dev to master

While still checked out as dev and using the <backup name> from above:

$ platform backup:restore --target=master <backup name>

Additional information about creating and restoring backups can be found in the Platform.sh documentation and in How to create and restore backups using the CLI.

Steps (Manually dump to the target environment)

1. Dump the database

For this example, the relationship database was defined in .platform.app.yaml with:

relationships:
     database: "postgresqldb:postgresql"    

From dev dump the PostrgreSQL database according to its relationship name (database). The --relationship tag is useful if there are multiple database relationships present, but if only one is used platform db:dump will work without it.

See the documentation for more information.

$ git checkout dev    
$ platform db:dump --relationship database    
Creating SQL dump file: <local directory>/<project id>--dev-54ta5gq--postgresqldb--main--dump.sql    

2. Pipe the SQL dump to the target environment

```bash
$ git checkout master
$ platform sql --relationship database -e master < <project id>--dev-54ta5gq--postgresqldb--main--dump.sql
```

Verify

Verify that the new admin user alan is now visible from master.

$ platform db:sql
psql (11.1 (Debian 11.1-3.pgdg90+1), server 9.6.11)
Type "help" for help.
   
main=> SELECT * FROM auth_user;
id |          password         |          last_login           | is_superuser | username | first_name | last_name | email | is_staff | is_active |          date_joined          
----+---------------------------+-------------------------------+--------------+----------+------------+-----------+-------+----------+-----------+-------------------------------
 2 |      <password hash>      | 2019-03-12 15:52:38.379839+00 | t            | chad     |            |           |       | t        | t         | 2019-03-07 16:02:26.583956+00
 6 |      <password hash>      | 2019-03-12 15:48:02+00        | t            | alan     |            |           |       | t        | t         | 2019-03-12 15:47:19+00
(2 rows)

Conclusion

Child environments inherit service visibility from their parents, but to ensure that development changes to not affect a production environment, updates and synchronizations do not occur in the opposite direction.

If a change is made in a development environment that is desired on master, the Platform CLI can be used to migrate those changes by either creating and restoring a backup to the target environment or by manually dumping a database piping the dump file into the target environment.