PostgreSQL

Last Updated May 2015

PostgreSQL is a powerful, open source object-relational database with more than 15 years of active development and a proven architecture. Creating and using PostgreSQL databases on Pagoda Box is extremely simple, but there are some things to take note of. After reading this doc, you will be familiar with:

  • How to create a PostgreSQL database
  • How to connect to your database
  • Things to know when using PostgreSQL on Pagoda Box

Creating a PostgreSQL Database

Creating a PostgreSQL database can be done either in your dashboard or from your Boxfile.

Through the Dashboard

To create a PostgreSQL database through your dashboard, click on "New Data Service," then click the PostgreSQL icon.

Creating a PostgreSQL Database in the Dashboard

You're given the option to name your database and select which PostgreSQL version you'd like use. If no name is provided, we will create one for you. You're also give a choice of "topology", which defines the architecture of your PostgreSQL service. More on this below and in the Service Topologies doc.

Click "Create Service" to begin provisioning your PostgreSQL database.

Through the Boxfile

To create a PostgreSQL database through the Boxfile, include a database service with the type specified as postgresql. You can also specify a topology, but it isn't required. More on topologies below.

PostgreSQL Database in the Boxfile YAML

  database1:
    type: postgresql
    topology: single
/Boxfile

On your next deploy, the database will be created.

Configuring Your PostgreSQL Database

All environment configuration is handled in the Boxfile. The PostgreSQL Settings in the Boxfile doc walks through all of the configurable PostgreSQL options.

Connecting to Your PostgreSQL Database

Connecting services in your application to a PostgreSQL database isn't any different on Pagoda Box than any other host. Each database has specified connection credentials used to connect your codebase to your database. You can get the credentials in the App Dashboard. Simply click on your database service and go to the "Connection" tab.

PostgreSQL Connection Credentials

Whenever you create a PostgreSQL database on Pagoda Box, we automatically generate environment variables for each connection credential:

DATABASE1_NAME
DATABASE1_USER
DATABASE1_PASS
DATABASE1_HOST
DATABASE1_PORT

Note: The service ID (DATABASE1) changes depending on which database you're connecting to.

Including environment variables in place of connection credentials in your codebase allows you to avoid storing the actual credentials in your repo and sets connection details before the database is even created. If you launch a database from your Boxfile with the credential environment variables in place, on the next deploy, your database will be created and your app will automatically be able to connect to it.

Check out the Environment Variables doc for more information.

Hard-Coding Credentials

You can hard-code your database connection credentials into your codebase, but there are some drawbacks to this approach:

  1. Connection credentials are viewable in your repo - On Pagoda Box, this doesn't compromise the security of your database.

  2. You have to create your database before you can get the credentials - To hard-code your database credentials, you first need to know what they are and you can't do that until you create the database. This isn't a critical problem. It's more of a workflow issue.

Managing Your PostgreSQL Database

You have a few options when it comes to managing the data inside your PostgreSQL database. The following docs walk through each:

Through a Pagoda Tunnel
Through an SSH Connection

Making PostgreSQL Redundant

Data stored in your database is incredibly important and ideally will always be available. Adding redundancy to your PostgreSQL service is the best way to ensure your data is always available. To make your PostgreSQL database redundant, use the "redundant" topology when creating your database.

Making PostgreSQL Redundant

Topologies can also be changed in your dashboard after your database is created.

With the Redundant topology, a second PostgreSQL instance is provisioned and master-master replication between the two instances. If the primary instance every fails or becomes unresponsive, the second will act as a hot failover and take over handling requests until the primary instance can be repaired and/or restarted.

If you have any questions, suggestions, or corrections, let us know.