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.
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
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.
Using Automated Environment Variables (Recommended)
Whenever you create a PostgreSQL database on Pagoda Box, we automatically generate environment variables for each connection credential:
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.
You can hard-code your database connection credentials into your codebase, but there are some drawbacks to this approach:
Connection credentials are viewable in your repo - On Pagoda Box, this doesn't compromise the security of your database.
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:
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.
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.
Table of Contents
If you have any questions, suggestions, or corrections, let us know.