Cloud-Hosted PostgreSQL: Heroku Postgres

Share this article

Key Takeaways

  • Heroku Postgres is a reliable and robust database-as-a-service based on PostgreSQL, allowing developers to focus more on writing their application’s SQL rather than learning how to be a DBA.
  • Heroku Postgres offers a tiered service with the lowest tier priced at $200 a month. It provides easy database creation, scalability according to needs, 99.99% uptime, and supports large datasets.
  • Setting up a database instance on Heroku Postgres is straightforward. After signing up and verifying the account, users can select their desired plan and have their database instance ready within minutes.
  • Heroku Postgres can easily be integrated with PHP using the PDO_PGSQL driver, allowing users to connect to the database and send queries in the same manner as if they had connected to a local-running instance.
PostgreSQL is a mature, stable, feature-rich database server. Unlike its fellow open-source contenders MySQL and Firebird, PostgreSQL has a steeper learning curve when you’re first getting started, but you can avoid most of the installation, configuration, and maintenance hassles if you use a cloud-hosted database service. Heroku Postgres is a reliable and powerful database-as-a-service based on PostgreSQL provided by Heroku. Hosting your data with Heroku lets you focus more on writing your application’s SQL rather than learning how to be a DBA. In this article I’ll show you how to get started with Heroku Postgres. I’ll share some of the benefits of using Heroku, show you how to sign up, and show you how easy it is to set up a hosted PostgreSQL database instance and connect to it from PHP using PDO.

Why Heroku?

When faced with a serious choice, it’s a good idea to weigh the pros and cons before making a final decision. At the core of almost any web-based application nowadays is its data, and so choosing the right database strategy is probably more important than most people realize. Heroku Postgres may not be right for you, but how do you know? Heroku Postgres is a tiered offering, the lowest of which is priced at $200 a month. If you’re a developer looking just to tinker around and learn something new, this might be a deal-breaker for you. (It would be nice if Heroku could offer some sort of limited free developer trial in the future so more people could learn to work with their offerings.) On the flip side of the coin, $200 is just a drop in a bucket for a successful web-based application. It’s definitely cheaper than running your own hardware and the personnel expense of maintaining the database and ensuring compliance with any relevant regulations. It’s incredibly easy to create a database instance and you’re free to upgrade or downgrade your tier as your needs dictate. In fact, the ease of database creation with Heroku Postgres alleviates one of the oft-heard complaints about PostgreSQL — that it’s difficult to install. Heroku claims to be the largest PostgreSQL database-as-a-service provider. Given this scale, it makes sense to let the experts manage your database. Heroku promises 99.99% uptime, and has done some impressive things to achieve that. Transactions are continually archived to a geographically distributed data store, and automated health-checks are performed every 30 seconds to ensure your database is available. You type one command, and Heroku literally takes care of the rest. Heroku allows almost unlimited data, so large datasets and data warehousing is no problem. Their plans are priced according to hot data set, the portion of your data that is accessed the most and stays resident in RAM for performance reasons. The lowest tier supports 1.7GB of hot data and the highest supports 68GB.

Setting Up a Database Instance

Signing up for the Heroku Postgres service is easy. Just go to postgres.heroku.com in your browser and click the Sign Up Now button in the middle of the page. If you already have an existing Heroku account you can simply provide the email address and password you’ve used for it and you will be logged in. Otherwise, you can create a new account now.

login screen

Of course, you’ll need to provide your credit card details to pay for the Heroku Postgres service. If you’ve just created a new account, you’ll be taken to the Verify Account page where you can provide the necessary details.

verify account screen

Once you’ve logged in and verified, and if you’re not looking at it already, go to the Your Databases page accessible from the Your DBs entry in the top menu. This page is the dashboard from which you can monitor and manage all of your database instances. Click the + pointed to by the arrow to add a database; you’ll be presented with the list of plans from which you can pick. Select your desired plan and then the Add Database button to confirm the selection.

your databases screen

In just a few minutes you’ll have a nice, new, shiny provisioned database instance ready for your data. When it’s ready, click on it to drill down and see its connection information.

connection information screen

Interacting from PHP

Now that you have a cloud-hosted PostgreSQL database running, let’s try interacting with it from PHP. Connect to the database using PostgreSQL’s interactive terminal client. If you are running PostgreSQL locally then you will already have the client installed; otherwise you’ll have to turn to your platform’s package management suite (e.g. on Ubuntu run sudo apt-get install postgresql-client) or go to postgresql.org, where you’ll find available for download pre-built binary packages suitable for FreeBSD, Linux, Mac, Solaris and Windows, as well as other installation options. To connect, call psql
with the -h option to specify the server’s hostname, -U to specify the username, and then the database name:
timothy@zarkov:~$ psql -h ec2-184-73-194-179.compute-1.amazonaws.com -U dj1wcxb3x9fy3x5 ul28zxpr39no1rr
Password for user ul28zxpr39no1rr: ********
psql (8.4.9, server 9.0.5)
WARNING: psql version 8.4, server version 9.0.
         Some psql features might not work.
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

dj1wcxb3x9fy3x5=>
Create a table to store some employee information using the following SQL:
CREATE TABLE employees (
    employee_id SERIAL,
    last_name VARCHAR(30),
    first_name VARCHAR(30),
    title VARCHAR(50)
);
employee_id is a SERIAL field and will be the employee table’s primary key. last_name, first_name, and title are VARCHAR fields which will store the individual’s name and job title. Then, insert some records into the table so there will be some data to work with:
INSERT INTO employees (last_name, first_name, title) VALUES
    ('Abreu', 'Mark', 'Project Coordinator'),
    ('Nyman', 'Larry', 'Security Engineer'),
    ('Simmons', 'Iris', 'Software Architect'),
    ('Miller', 'Anthony', 'Marketing Manager'),
    ('Leigh', 'Stephen', 'UI Developer'),
    ('Lee', 'Sonia', 'Business Analyst');
As with the terminal client, most developers will probably be running a version of PHP installed using package management tools. If that’s the case, the PostgreSQL extensions can be enabled in PHP by installing PostgreSQL support (e.g. sudo apt-get install php5-pgsql
). There are actually two possible PHP extensions you can use to work with PostgreSQL – the PostgreSQL extension which is essentially a wrapper for the C client library libpq, and PDO using the PDO_PGSQL driver. Both are generally installed together, but here I’ll be using PDO since it offers a consistent API to work with a variety of databases and is generally regarded as the appropriate interface for working with databases in modern, professional code. The connection is established by creating a new PDO object, passing in a DSN (Data Source Name) string at the time of instantiation. The string contains the information displayed in the Connection Settings section for the database.
<?php
$dsn = "pgsql:"
    . "host=ec2-184-73-194-179.compute-1.amazonaws.com;"
    . "dbname=ul28zxpr39no1rr;"
    . "user=dj1wcxb3x9fy3x5;"
    . "port=5432;"
    . "sslmode=require;"
    . "password=p28xwd9pjcrzyzp6mf74m99cze";

$db = new PDO($dsn);
The DSN starts with “pgsql” to specify use of the PDO_PGSQL driver, and then each piece of information is presented as a key=value pair separated from one another by semicolons. Now, with a connection established to the database, you can send queries in the same manner as if you had connected to a local-running instance.
<html>
 <head>
  <title>Employees</title>
 </head>
 <body>
  <table>
   <thead>
    <tr>
     <th>Employee ID</th>
     <th>Last Name</th>
     <th>First Name</th>
     <th>Title</th>
    </tr>
   </thead>
   <tbody>
<?php
$query = "SELECT employee_id, last_name, first_name, title "
    . "FROM employees ORDER BY last_name ASC, first_name ASC";
$result = $db->query($query);
while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
    echo "<tr>";
    echo "<td>" . $row["employee_id"] . "</td>";
    echo "<td>" . htmlspecialchars($row["last_name"]) . "</td>";
    echo "<td>" . htmlspecialchars($row["first_name"]) . "</td>";
    echo "<td>" . htmlspecialchars($row["title"]) . "</td>";
    echo "</tr>";
}
$result->closeCursor();
?>
   </tbody>
  </table>
 </body>
</html>
Here the code assumes the instantiation of $db has taken place and submits a SELECT query to retrieve the employee information from the remote PostgreSQL database – the employee id, last name, first name, and job title. Bookended by the appropriate HTML markup to render a table, the code iterates through the retrieved records and outputs each field. Finally, the results cursor is closed to free the connection to the server. The output of the script resembles the following:

script output screen

Summary

More and more developers are realizing that it makes sense to outsource their database hosting to cloud-service provides like Heroku. This lets you focus more on writing your application’s code and a handful of SQL queries rather than learning how to be a DBA. In this article, you saw how easy it is to sign up and get started using Heroku Postgres. The whole process literally takes minutes from start to finish, and you can connect to the database from PHP using PDO like any other PostgreSQL database. If your web application uses PostgreSQL, then you definitely should investigate Heroku’s offerings. You may find they’re exactly right for you. Image via Jozsef Bagota / Shutterstock

Frequently Asked Questions (FAQs) about Heroku PostgreSQL

What is the difference between Heroku PostgreSQL and regular PostgreSQL?

Heroku PostgreSQL is a cloud-based service that provides a fully managed database solution. It is built on top of the open-source PostgreSQL database but offers additional features such as automated backups, easy scalability, and high availability. On the other hand, regular PostgreSQL is an open-source object-relational database system that you need to install, configure, and manage on your own.

How can I scale my Heroku PostgreSQL database?

Heroku PostgreSQL allows you to scale your database easily. You can increase the number of database connections by upgrading your database plan. Heroku also provides the ability to scale horizontally by adding more database servers, known as followers. These followers can be promoted to a primary server if needed, providing high availability and redundancy.

How secure is Heroku PostgreSQL?

Heroku PostgreSQL provides several security features to protect your data. It includes automated backups, data encryption at rest and in transit, and compliance with various security standards. Additionally, Heroku’s infrastructure is designed to be resilient against attacks and failures, ensuring the availability and integrity of your data.

Can I migrate my existing PostgreSQL database to Heroku PostgreSQL?

Yes, you can migrate your existing PostgreSQL database to Heroku PostgreSQL. Heroku provides several tools and guides to help you with the migration process. You can use the pg:backups command to create a backup of your existing database and then restore it to your Heroku PostgreSQL database.

How does Heroku PostgreSQL handle backups?

Heroku PostgreSQL automatically creates daily backups of your database. You can also manually create backups at any time using the pg:backups command. These backups are stored securely and can be used to restore your database in case of data loss.

What are the limitations of Heroku PostgreSQL?

While Heroku PostgreSQL provides many benefits, it also has some limitations. For example, it does not support all PostgreSQL extensions, and some features may not be available in all regions. Additionally, the cost can be higher compared to managing your own PostgreSQL database, especially for larger databases.

How can I monitor the performance of my Heroku PostgreSQL database?

Heroku provides several tools to monitor the performance of your PostgreSQL database. You can use the Heroku Dashboard to view real-time performance metrics, and the pg:stats command to get detailed statistics about your database.

Can I use Heroku PostgreSQL with other programming languages?

Yes, Heroku PostgreSQL can be used with any programming language that supports PostgreSQL. This includes popular languages like Python, Ruby, Java, and Node.js. Heroku provides libraries and guides to help you integrate PostgreSQL with your application.

How does Heroku PostgreSQL handle data replication?

Heroku PostgreSQL provides built-in data replication. It uses a primary/standby architecture, where the primary server handles all write operations and the standby server replicates the data from the primary server. This ensures high availability and data consistency.

What is the pricing for Heroku PostgreSQL?

The pricing for Heroku PostgreSQL depends on the plan you choose. Heroku offers several plans, ranging from free to premium. Each plan provides different features and resources, so you can choose the one that best fits your needs and budget.

Timothy BoronczykTimothy Boronczyk
View Author

Timothy Boronczyk is a native of Syracuse, New York, where he lives with no wife and no cats. He has a degree in Software Application Programming, is a Zend Certified Engineer, and a Certified Scrum Master. By day, Timothy works as a developer at ShoreGroup, Inc. By night, he freelances as a writer and editor. Timothy enjoys spending what little spare time he has left visiting friends, dabbling with Esperanto, and sleeping with his feet off the end of his bed.

Intermediate
Share this article
Read Next
Get the freshest news and resources for developers, designers and digital creators in your inbox each week