GuidesAPI ReferenceSupportArticles

PostgreSQL on Compose

Suggest Edits

PostgreSQL is an open-source object-relational database with a reputation for being reliable. It's built to support both small applications and large enterprise ecosystems and everything in between.

PostgreSQL for All is a general introduction to PostgreSQL on Compose.

Are you wondering what you'll get, or can get, with a Compose PostgreSQL deployment? Want to know what you'll need to do to manage it? Check out some of the implementation features and details down in PostgreSQL for Ops and Admins.

Just deployed PostgreSQL and want to get coding with it? Developing an application or want to try a new stack? Then see the PostgreSQL for Developers section for resources on how to connect from different languages, command line tools and more information to get you started.

PostgreSQL for All

When deployed on Compose, PostgreSQL comes with these standard Compose features.

Compose deployments of PostgreSQL also come with a number of PostgreSQL specific features:

  • Data Browser For PostgreSQL.
  • An optional add-on for real-time log access.
  • The ability to forward rich metrics to services with an optional add-on.
  • Access to the standard contrib modules.
  • No limit on database or schema creation - you have CREATEDB and CREATEROLE access.
  • Prices start from $17.50 for a 1GB deployment and $12 per extra gigabyte.

Compose Configurations

All PostgreSQL Deployments are high-availability clusters with two data nodes, three etcd arbiter capsules, and two HAProxy capsules for managing connections and fail-over. Each data node contains 1GB storage and 102MB of memory. The HAProxy capsules each have 64MB of memory.

Expansion Options

On standard Compose Hosted deployments, automatic vertical scaling occurs as your data set grows. You can also manually scale up your portals and PostgreSQL deployment from the deployment's Resources panel. A 10:1 ratio of disk to RAM is maintained, so by increasing the disk allocated to the deployment, you increase the RAM allocated.

You may add additional TCP portals from the Security panel in the Compose UI, as well as scale them with more memory should you need it.

For more information, see the PostgreSQL Resources and Scaling page.

Datacenter Availability

See Compose Datacenter Availability for current location availability.

Billing and Costs

Compose deployments are billed based on disk usage and your PostgreSQL deployment usage is measured hourly. It is then grouped into a single monthly billing cycle. This means that any scaling or add-on usage will be charged from when the new resource was provisioned; not just for the month. You can see your deployment's usage in the Overview panel, under Current Usage.

Initial deployment set up is $17.50/month. Depending on which scaling and add-ons options you choose, the cost will increase. For example, if you wish to add extra storage and RAM, it is billed at an additional $12/month per unit of 1GB storage/100MB memory, so an additional 2GB storage/204MB memory will cost an additional $24/month.

For Ops and Admins

High-availability and Failover Details

PostgreSQL clusters consist of a leader and a follower member on separate machines in separate zones. High-availability is managed with Patroni, where the etcd arbiters maintain cluster state, manage replication, and handle failover should the leader become unavailable.

Each PostgreSQL deployment also has two HAProxy portals, either of which will connect to the current cluster leader. Should one portal become unavailable, applications can failover to the other portal without requiring you to change your connection configuration.

Backups

Compose PostgreSQL backups use pg_basebackup on your running database cluster to backup your entire deployment. You can download any of the backups of your deployment, and also restore them locally. More information is on the Backups for PostgreSQL page.

General information on backup schedules, downloading, and restoring can be found on the Backups page.

Restore to a Local Database

Since your PostgreSQL backups are available for you to download, you may get a copy of your deployment running locally. To do so, you will need a local installation of PostgreSQL and the downloaded backup of your deployment. For using the backup, we include a README file which also provides instructions for using the backup file locally. Open the README file with any text editor.

The file structure of the backup allows for multiple backups to be stored in the same directory; the top few levels are data --> backup --> datestamp directory. Inside the datestamped directory is the snapshot and the WAL archive. You will want to start up your local PostgreSQL inside the snapshot directory with the command postgres -D conf. You can then connect to the db by running: psql postgres -U focker.

For Developers

CLI Tools

psql is the interactive terminal for PostgreSQL. You can install it locally and then use the connection strings and credentials from Compose to interact with your deployment. To install psql:

  • Ubuntu: Run sudo apt-get install postgresql-client to install the psql command.
  • Mac OS X:
  • You will need to install PostgreSQL, the database, to get the tools. Consult the PostgreSQL Mac OS X page for details of the various install options.
  • There are also stand-alone GUI tools for the Mac such as PSequel.
  • Other platforms: Consult the PostgreSQL download page for details of the various install options.

Connecting to PostgreSQL

The Overview panel of the Compose UI provides the basic information you need to get connected to your databases. In the section Connection info you will find Credentials and Connection Strings for your application, including a psql command for connecting. For more information, including examples in many languages, see the Connecting to PostgreSQL page.

Additional Resources and Related Articles

The full list of documentation for PostgreSQL is in the side-bar, in addition to all things Compose.

For more than just help docs, check out Compose Articles and our curated collection of PostgreSQL related topics for more how-to's and information on PostgreSQL on Compose.

Still Need Help?

If this article didn't solve things, summon a human and get some help!

Updated over 3 years ago