Postgres Add-on

Patrick Espake

February 2015

In this article by Patrick Espake, author of the book Learning Heroku Postgres, you will learn how to install and set up PostgreSQL and how to create an app using Postgres.

(For more resources related to this topic, see here.)

Local setup

You need to install PostgreSQL on your computer; this installation is recommended because some commands of the Postgres add-on require PostgreSQL to be installed. Besides that, it's a good idea for your development database to be similar to your production database; this avoids problems between these environments.

Next, you will learn how to set up PostgreSQL on Mac OS X, Windows, and Linux. In addition to pgAdmin, this is the most popular and rich feature in PostgreSQL's administration and development platform.

The versions recommended for installation are PostgreSQL 9.4.0 and pgAdmin 1.20.0, or the latest available versions.

Setting up PostgreSQL on Mac OS X

The Postgres.app application is the simplest way to get started with PostgreSQL on Mac OS X, it contains many features in a single installation package:

  • PostgreSQL 9.4.0
  • PostGIS 2.1.4
  • Procedural languages: PL/pgSQL, PL/Perl, PL/Python, and PLV8 (JavaScript)
  • Popular extensions such as hstore, uuid-ossp, and others
  • Many command-line tools for managing PostgreSQL and convenient tools for GIS

The following screenshot displays the postgresapp website:Learning Heroku Postgres

For installation, visit the address http://postgresapp.com/, carry out the appropriate download, drag it to the applications directory, and then double-click to open.

The other alternatives for installing PostgreSQL are to use the default graphic installer, Fink, MacPorts, or Homebrew. All of them are available at http://www.postgresql.org/download/macosx.

To install pgAdmin, you should visit http://www.pgadmin.org/download/macosx.php, download the latest available version, and follow the installer instructions.

Setting up PostgreSQL on Windows

PostgreSQL on Windows is provided using a graphical installer that includes the PostgreSQL server, pgAdmin, and the package manager that is used to download and install additional applications and drivers for PostgreSQL.

To install PostgreSQL, visit http://www.postgresql.org/download/windows, click on the download link, and select the the appropriate Windows version: 32 bit or 64 bit. Follow the instructions provided by the installer.Learning Heroku Postgres

After installing PostgreSQL on Windows, you need to set the PATH environment variable so that the psql, pg_dump and pg_restore commands can work through the Command Prompt. Perform the following steps:

  1. Open My Computer.
  2. Right-click on My Computer and select Properties.
  3. Click on Advanced System Settings.
  4. Click on the Environment Variables button.
  5. From the System variables box, select the Path variable.
  6. Click on Edit.
  7. At the end of the line, add the bin directory of PostgreSQL: c:\Program Files\PostgreSQL\9.4\bin;c:\Program Files\PostgreSQL\9.4\lib.
  8. Click on the OK button to save.

The directory follows the pattern c:\Program Files\PostgreSQL\VERSION\..., check your PostgreSQL version.

Setting up PostgreSQL on Linux

The great majority of Linux distributions already have PostgreSQL in their package manager. You can search the appropriate package for your distribution and install it. If your distribution is Debian or Ubuntu, you can install it with the following command:

$ sudo apt-get install postgresql

If your Linux distribution is Fedora, Red Hat, CentOS, Scientific Linux, or Oracle Enterprise Linux, you can use the YUM package manager to install PostgreSQL:

$ sudo yum install postgresql94-server
$ sudo service postgresql-9.4 initdb
$ sudo chkconfig postgresql-9.4 on
$ sudo service postgresql-9.4 start

If your Linux distribution doesn't have PostgreSQL in your package manager, you can install it using the Linux installer. Just visit the website http://www.postgresql.org/download/linux, choose the appropriate installer, 32-bit or 64-bits, and follow the install instructions.

You can install pgAdmin through the package manager of your Linux distribution; for Debian or Ubuntu you can use the following command:

$ sudo apt-get install pgadmin3

For Linux distributions that use the YUM package manager, you can install through the following command:

$ sudo yum install pgadmin3

If your Linux distribution doesn't have pgAdmin in its package manager, you can download and install it following the instructions provided at http://www.pgadmin.org/download/.

Creating a local database

For the examples in this article, you will need to have a local database created. You will create a new database called my_local_database through pgAdmin.

To create the new database, perform the following steps:

  1. Open pgAdmin.
  2. Connect to the database server through the access credentials that you chose in the installation process.
  3. Click on the Databases item in the tree view.
  4. Click on the menu Edit -> New Object -> New database.
  5. Type the name my_local_database for the database.
  6. Click on the OK button to save.Learning Heroku Postgres

Creating a new local database called my_local_database

Creating a new app

Many features in Heroku can be implemented in two different ways; the first is via the Heroku client, which is installed through the Heroku Toolbelt, and the other is through the web Heroku dashboard.

In this section, you will learn how to use both of them.

Via the Heroku dashboard

Access the website https://dashboard.heroku.com and login. After that, click on the plus sign at the top of the dashboard to create a new app and the following screen will be shown:Learning Heroku Postgres

Creating an app

In this step, you should provide the name of your application. In the preceding example, it's learning-heroku-postgres-app. You can choose a name you prefer. Select which region you want to host it on; two options are available: United States or Europe.

Heroku doesn't allow duplicated names for applications; each application name supplied is global and, after it has been used once, it will not be available for another person. It can happen that you choose a name that is already being used. In this case, you should choose another name.

Choose the best option for you, it is usually recommended you select the region that is closest to you to decrease server response time. Click on the Create App button.

Then Heroku will provide some information to perform the first deploy of your application. The website URL and Git repository are created using the following addresses: http://your-app-name.herokuapp.com and git@heroku.com/your-app-name.git.Learning Heroku Postgres

learning-heroku-postgres-app created

Next you will create a directory in your computer and link it with Heroku to perform future deployments of your source code. Open your terminal and type the following commands:

$ mkdir your-app-name
$ cd your-app-name
$ git init
$ heroku git:remote -a your-app-name
Git remote heroku added

Finally, you are able to deploy your source code at any time through these commands:

$ git add .
$ git commit –am "My updates"
$ git push heroku master

Via the Heroku client

Creating a new application via the Heroku client is very simple. The first step is to create the application directory on your computer. For that, open the Terminal and type the following commands:

$ mkdir your-app-name
$ cd your-app-name
$ git init

After that you need to create a new Heroku application through the command:

$ heroku apps:create your-app-name
Creating your-app-name... done, stack is cedar-14
https://your-app-name.herokuapp.com/ | HYPERLINK "https://git.heroku.
com/your-app-name.git" https://git.heroku.com/your-app-name.git
Git remote heroku added

Finally, you are able to deploy your source code at any time through these commands:

$ git add .
$ git commit –am "My updates"
$ git push heroku master

Another very common case is when you already have a Git repository on your computer with the application's source code and you want to deploy it on Heroku. In this case, you must run the heroku apps:create your-app-name command inside the application directory and the link with Heroku will be created.

Summary

In this article, you learned how to configure your local environment to work with PostgreSQL and pgAdmin. Besides that, you have also understood how to install Heroku Postgres in your application.

In addition, you have understood that the first database is created automatically when the Heroku Postgres add-on is installed in your application and there are several PostgreSQL databases as well. You also learned that the great majority of tasks can be performed in two ways: via the Heroku Client and via the Heroku dashboard.

Resources for Article:


Further resources on this subject:


You've been reading an excerpt of:

Learning Heroku Postgres

Explore Title