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
pg_dump
the free database.- 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.
- Create the standard tier database with
gigalixir pg:create
. - Restore the data with
psql
orpg_restore
. You can find the url to use withgigalixir pg
once the standard tier database is created.
Please don’t hesitate to Contact Us if you need help with this migration process.
- Databases still have connection limits based on Google Cloud SQL limits. See https://cloud.google.com/sql/docs/postgres/quotas#fixed-limits
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
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
To delete a database, run:
gigalixir pg:destroy -d $DATABASE_ID
How to Install a Postgres Extension
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
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)