Difficulty: intermediate
Estimated Time: 3 hours

Using PostgreSQL Cool Features in Application Development

This workshop is to get you hands on with using some of the features in PostgreSQL you may not know about. The idea is that these features will make it easier to get your job done.

Class Structure

The outline of this workshop is:

  1. Run PostgreSQL in containers
  2. Load SQL for table definitions and then load some data into the tables
  3. Work with spatial data
  4. Work with full text search
  5. Work with key-value columns
  6. Work with JSON data
  7. Use R embedded in PostgreSQL to do statistical functions

The overall goal is to have you feel comfortable using these features and exploring them more when you are finished. It is also to help you understand the PostgreSQL is the 80% in the 80/20 rule - you can use it for at least 80% of your application development needs. You can feel comfortable reaching for PostgreSQL as your favorite development datastore.

We will not be going in depth on each of these topics. Just enough to get you started.

Final Notes

The container used in this class is available in Dockerhub. As long as you have Docker on your machine you can use the same version of PostgreSQL as the workshop. All the data from the workshop was intentionally chosen from public domain or permissive licenses so that you can use it for commercial and non-commercial purposes. Feel free to download it and play some more at your own pace on your own machine.

The only way to truly learn this technology is to play/experiment/use it for a while. You have all the tools you need - do it!

And with that, we are finished.

Features in PostgreSQL for Application Development

Step 1 of 7

Running in Containers

In this exercise we will introduce containers (which you may know as Docker) and then spin up our PostgreSQL instance using containers.

A Little Background on Containers

Containers have quite a long history in computing before Docker. At a simplistic level, containers "package up" applications and their dependencies to run with everything that is needed above the kernel OS. This allows for a cleaner separation of dependencies as the container has all the things it needs to run except the kernel. Here is good introduction to Docker containers. Be aware that there are other container runtimes and specifications besides Docker.

Containers are spun up from a container image. In this workshop we will use container to denote the running container and image to denote the binary used to spin up the container.

Another advantage of images is that not only do they container the binaries for the application but they also are configured and ready to run. With a container you can skip most of the configuration and just do some version of "container run"

In this workshop we will be using a image that contains Postgresql, PostGIS, embedded R, and some other extensions. If you have ever tried to install all these pieces you know what a hassle it can be. Let's see how easy it can be with containers.

Running PostgreSQL in Containers

Simplest method

Let's start with the quickest and easiest way to start up PostgreSQL using a container.

docker run -e PG_PASSWORD=password thesteve0/postgres-appdev

If you click the little check mark in the box above it will execute the command in the terminal window. What you are doing is telling docker to run image thesteve0/postgres-appdev and pass in the environment variable for what you want the password to be for both the standard user and the postgres (DBAdmin) user.

  1. The default name for the primary database will be: mydb
  2. The default username is: rnduser2w3
  3. The default port will be: 5432
  4. And the postgres user password will be equal to the user password which you set in the command.

CONGRATULATIONS you just spun up a fully working PostgreSQL database with a bunch of functionality!

But this is a pretty simplistic way to start PostgreSQL - great if you wanna just "get going quickly".

Because we didn't run the container in "detached" mode we never got our prompt back. Detached mode allows the container to run in the background and give us back our prompt. To shut down the container click on tab titled "Terminal 2" and find out information on our running container:

docker ps


Please note either the name or the ID of your running container (highlighted in red above). Now in the same terminal type in the following command:

docker kill <id or name of your container>

If you go back to the first tab, "Terminal" you will see that you get your prompt back. Let's start PostgreSQL more appropriately for our workshop.

Better way to start the container

Let's set a new username, give the container a fixed (rather than random) name, expose port 5432 from the container into the VM we are running, and have it detach so we can get our prompt back.

docker run -d -p 5432:5432 -e PG_USER=groot -e PG_PASSWORD=password -e PG_DATABASE=workshop --name=pgsql thesteve0/postgres-appdev

And with that we have now spun up PostgreSQL with

  1. The ability to connect from our VM to the instance running in the container
  2. Username: groot
  3. Password: password
  4. A database named: workshop
  5. A container named: pgsql

Now that we have PostgreSQL running let's move on to adding some tables and data.