Working with Postgres Databases and Platform.sh

Sharpen your tools!
Requirements
- An app setup on Platform.sh using a postgres database.
- A database management application. I will use PSequel in this post.
User Story
If you need to import a new clean database to a Platform.sh environment (not master). In my case I was running migrations and needed to ensure that the order of operations was going to apply cleanly to the production copy of the database.
Commands
Make a backups and restore points of
master
andtest
environments.- Create a restore point through the Platform.sh dashboard
Navigate to your app's Platform.sh dashboard get into the environment you want to replace the DB for, in my case test
. Clicking on the snapshots
icon in the top right of the dashboard will create a snapshot restorable backup point for your app in case something goes horribly wrong ;).
Here are the platform
cli commands to make a backup of the databases of the environments in question. First I take a DB dump of the master
environment to refresh the data from and then a backup of the test
environment in case I need to get back to it.
platfrom cli commands:
platform --environment=master --relationship=pgdatabase db:dump
platform --environment=test --relationship=pgdatabase db:dump
Drop the
main
database tables (to start with a clean slate):platform --environment=test --relationship=pgdatabase sql
- This command will drop you into a
psql
shell so you can examine and manipulate the databases.
- This command will drop you into a
\c postgres
(just connect to any database besides main)drop owned by main
(this will drop the tables not the DB)- NOTE: Dropping the DB will leave you in a bad state; as your user does not
have permission to recreate the
main
DB. If you do get into this state use the restore point you created in the Platform.sh dashboard.
- NOTE: Dropping the DB will leave you in a bad state; as your user does not
have permission to recreate the
Import the backup or fresh DB you need.
platform --environment=test --relationship=pgdatabase sql < {YOUR_PROJECT_ID--master--dump}.sql
- Be sure to replace
YOUR_PROJECT_ID
with your Platform apps ID. This should already be in the filename if you are following along!
- Be sure to replace
Conclusion
Get to know your tools to save time! A little exploring of your toolsets everyday goes a long long way.