How to Provision a Free PostgreSQL Database

IMPORTANT: Make sure you set your pool_size in prod.exs to 2 beforehand. The free tier database only allows limited connections.

The following command will provision a free database for you and set your DATABASE_URL environment variable appropriately.

gigalixir pg:create --free

List databases by running

gigalixir pg

Delete by running

gigalixir pg:destroy -d $DATABASE_ID

You can only have one database per app because otherwise managing your DATABASE_URL variable would be trickier.

In the free tier, the database is a free multi-tenant postgres database cluster with shared CPU, memory, and disk, but it is not suitable for production use.

The free tier database is limited to 2 connections, 10,000 rows, and no backups. Idle connections are terminated after 5 minutes.

If you want to upgrade your database, you’ll have to migrate the data yourself. For a complete feature comparison see tiers.

For information on upgrading your account, see upgrade account.

How to Provision a Standard PostgreSQL database

The following command will provision a database for you and set your DATABASE_URL environment variable appropriately.

gigalixir pg:create --size=0.6

Give it a moment, it takes a few minutes ⏲️ to provision.

You can check the status by running

gigalixir pg

You may also want to adjust your pool_size. We recommend setting the pool size to (M-6)/(n+1) where M is the max connections and n is the num app replicas.

We subtract 6 because Cloud SQL will sometimes - but rarely - use 6 for maintenance purposes.

We use n+1 because rolling deploys will temporarily have an extra replica during the transition.

For example, if you are running a size 0.6 database with 1 app replica, the pool size should be (25-6)/(1+1)=9.

You can only have one database per app because otherwise managing your DATABASE_URL variable would be tricky.

Under the hood, we use Google’s Cloud SQL which provides reliability, security, and automatic backups. For more information, see Google Cloud SQL for PostgreSQL Documentation.

How to Upgrade / Migrate a Free DB to a Standard DB

If you started out with a free tier database and then upgraded to the standard tier, we highly recommend you migrate to a standard tier database.

The standard tier databases support encryption, backups, extensions, functions, triggers, and dedicated cpu, memory, & disk. There are no row limits, connection limits, and they are automatically scalable.

Unfortunately, we can’t automatically migrate your free tier db to a standard tier db. You’ll have to

  1. pg_dump the free database.
  2. Delete the free database with gigalixir pg:destroy --help.
    • Note: postgres may make you scale down to 0 app replicas to do this step, so you’ll have some downtime.
  3. Create the standard tier database with gigalixir pg:create.
  4. Restore the data with psql or pg_restore. You can find the url to use with gigalixir pg once the standard tier database is created.

Please don’t hesitate to Contact Us if you need help with this migration process.

How to Scale a Database

To change the size of your database, run

gigalixir pg:scale -d $DATABASE_ID --size=1.7

You can find your database ID by running:

gigalixir pg

Supported sizes include 0.6, 1.7, 4, 8, 16, 32, 48, 64, and 128.

For more information about databases sizes, see Database Sizes & Pricing.

How to Dump the Database to a File

We recommend pg_dump.

You can find all the connection parameters you need from gigalixir pg.

This should dump the database contents as a sql file which you can load back in with psql.

If you dump a binary file, then you can use pg_restore.

How to Restore a Database Backup

We use Cloud SQL under the hood - which takes automatic backups every day and keeps seven backups available. For more, see https://cloud.google.com/sql/docs/postgres/backup-recovery/backups

First, get your database ID by running

gigalixir pg

View what backups you have available by running

gigalixir pg:backups -d $DATABASE_ID

Note

We required the database_id even though we could probably detect it automatically because these are sensitive operations and we prefer to be explicit.

Find the backup ID you want and run with:

gigalixir pg:backups:restore -d $DATABASE_ID -b $BACKUP_ID

This can take a while. Sometimes over ten minutes. To check the status, run:

gigalixir pg

How to Restart a Database

Contact us and we’ll help you out. Only standard tier databases can be restarted.

How to Delete a Database

Warning
Deleting a database also deletes all of its backups. Please make sure you backup your data first.

To delete a database, run:

gigalixir pg:destroy -d $DATABASE_ID

How to Install a Postgres Extension

Note

Free Databases do not support extensions - except for citext - which is preinstalled. For more information, see tiers.

First, make sure Google Cloud SQL supports your extension by checking their list of extensions.

If your Postgres extension is supported, find your database URL by running:

gigalixir pg

Then, get a psql console into your database:

psql $DATABASE_URL

Then, install your extension:

CREATE EXTENSION foo;

How to Connect a Database

If you followed the Getting Started Guide, then your database should already be connected. If not, connecting to a database is done no differently from apps running outside Gigalixir.

We recommend you set a DATABASE_URL config and configure your database adapter accordingly to read from that variable. In short, you’ll want to add something like this to your prod exs file.

config :gigalixir_getting_started, GigalixirGettingStarted.Repo,
  adapter: Ecto.Adapters.Postgres,
  url: {:system, "DATABASE_URL"},
  database: "",
  ssl: true,
  pool_size: 2

Replace :gigalixir_getting_started and GigalixirGettingStarted with your app name. Then, be sure to set your DATABASE_URL config.

For more information on setting configs, see How to Configure an App. If you provisioned your database using, How to Provision a Standard PostgreSQL database, then DATABASE_URL should be set for you automatically once the database is provisioned.

Otherwise, do the following:

gigalixir config:set DATABASE_URL="ecto://user:pass@host:port/db"

If you need to provision a database, Gigalixir provides Databases-as-a-Service. See How to Provision a Standard PostgreSQL database.

How to Manually set up a Google Cloud SQL PostgreSQL Database

Note

You can also use Amazon RDS, but we do not have instructions provided yet.

How to Run Migrations

If you deployed your app without Distillery or Elixir Releases, meaning you are in Mix mode, you can run migrations as a job in a new container with

gigalixir run mix ecto.migrate

If you deployed your app as a Distillery release or Elixir release, Mix isn’t available. We try to make it easy by providing a special command, but the command runs on your existing app container, so you’ll need to make sure your app is running first and set up your SSH keys.

gigalixir account:ssh_keys:add "$(cat ~/.ssh/id_rsa.pub)"

Then run:

gigalixir ps:migrate

This command runs your migrations in a Remote Console directly on your production node. It makes some assumptions about your project so if it does not work, please contact us for help.

If you are running an Umbrella app, you will probably need to specify which "inner app" within your Umbrella to migrate. Do this by passing the --migration_app_name flag like so

gigalixir ps:migrate --migration_app_name=$MIGRATION_APP_NAME

When running gigalixir ps:migrate, sometimes the migration doesn’t do exactly what you want. If you need to tweak the migration command to fit your situation, it helps to know that all gigalixir ps:migrate is doing is dropping into a remote_console and running the following:

For information on how to open a Remote Console, see How to Drop into a Remote Console.

repo = List.first(Application.get_env(:gigalixir_getting_started, :ecto_repos))
app_dir = Application.app_dir(:gigalixir_getting_started, "priv/repo/migrations")
Ecto.Migrator.run(repo, app_dir, :up, all: true)

For example if you have more than one app, you may not want to use List.first to find the app that contains the migrations.

If you have a chicken-and-egg problem where your app will not start without migrations run, and migrations won’t run without an app running, you can try the following workaround on your local development machine. This will run migrations on your production database from your local machine using your local code.

MIX_ENV=prod DATABASE_URL="$YOUR_PRODUCTION_DATABASE_URL" mix ecto.migrate

How to Run Migrations on Startup

If you are using Distillery, we suggest using a Distillery pre-start boot hook by following https://github.com/bitwalker/distillery/blob/master/docs/guides/running_migrations.md and https://github.com/bitwalker/distillery/blob/master/docs/extensibility/boot_hooks.md

If you are using Elixir Releases, we suggest creating a custom Procfile and overlaying it into your release tarball.

To do this create a file rel/overlays/Procfile with something like this:

web: /app/bin/$GIGALIXIR_APP_NAME eval "MyApp.Release.migrate" && /app/bin/$GIGALIXIR_APP_NAME $GIGALIXIR_COMMAND

You have to implement the MyApp.Release.migrate function with something like https://hexdocs.pm/phoenix/releases.html#ecto-migrations-and-custom-commands. You might also be interested in reading https://elixirforum.com/t/equivalent-to-distillerys-boot-hooks-in-mix-release-elixir-1-9/23431

If you aren’t running Distillery or Elixir releases, meaning you are in Mix mode, you can try modifying your Procfile to something like this

web: mix ecto.migrate && elixir --name $MY_NODE_NAME --cookie $MY_COOKIE -S mix phx.server

For more details, see Can I Use a Custom Procfile?

How to Reset the Database?

First, drop into a Remote Console and run this to "down" migrate. You may have to tweak the command depending on what your app is named and if you’re running an umbrella app.

Ecto.Migrator.run(MyApp.Repo, Application.app_dir(:my_app, "priv/repo/migrations"), :down, [all: true])

Then run this to "up" migrate.

Ecto.Migrator.run(MyApp.Repo, Application.app_dir(:my_app, "priv/repo/migrations"), :up, [all: true])

How to Run Seeds?

If you are in Mix mode (not using Distillery or Elixir releases) and have a seeds.exs file, you can just run

gigalixir run -- mix run priv/repo/seeds.exs

Otherwise, you’ll need to drop into a Remote Console and run commands manually. If you have a seeds.exs file, you can follow the Distillery Migration Guide and run something like this in your Remote Console.

seed_script = Path.join(["#{:code.priv_dir(:myapp)}", "repo", "seeds.exs"])
Code.eval_file(seed_script)
NEXT ⟶