Compose takes care of the day to day operations of running a database, but you still need good tools to administer your database and herd your data. In this article, we’ll get you going with three administration tools you can use with PostgreSQL.

Over the next few weeks, we’ll be looking at PostgreSQL admin tools in a new weekly "Tool Up" series. To kick off, we’re going to give a quick rundown of the tools we’ll be looking at and how to get them connected to Compose so you can try them out too.

Let's get started...

pgAdmin

pgAdmin has been a staple of PostgreSQL tooling for almost 20 years, and in 2016 the open source administration tool was completely re-written using Electron, python, and other web technologies and given the name pgAdmin4. While some have looked to keep the now unsupported pgAdmin3 going, the forks we have found lack support for SSL which is needed to work with Compose PostgreSQL.

Let's take a look at the latest version, pgAdmin4.

Installing pgAdmin4

You can install pgAdmin4 on Linux, Mac OSX, and Windows machines by downloading the proper installer for your platform from the download site. Since it’s an open source tool, you can also download the source code, get it running on new platforms, and even enhance it.

Connecting to Compose PostgreSQL

The first screen you'll see when you open pgAdmin4 is the home screen.

Before we can do too much, we'll need to add a PostgreSQL connection. Click on the Add New Server button, which will bring up a modal dialog with configuration settings for the server.

You'll need to give the server a name on the General tab.

Next, we'll switch over to the connection tab and add our connection details from the connection info section of our Compose PostgreSQL database and save it.

Once you've connected up to your PostgreSQL deployment, you'll see the available databases in the tree view.

Running basic queries

To run a query on our database, we'll first need to access the query tool by right-clicking on the compose database and clicking on the Query Tool item in the context menu.

You can then start typing queries into the window, clicking the execute button that looks like a lightning bolt when your query is ready.

You can also access many other tools from the context menu depending on which item in the database you right-click on. For example, you can query the data in a table by drilling down into the Schemas menu and right-clicking on the table you want to query.

pgAdmin is a powerful tool that has a long history among Postgres developers, but the latest version has changed quite a bit and developers that are used to the previous version may not be comfortable with it. Let's take a look at another tool that takes a different approach.

PostgreSQL Studio

Next up in our roundup is PostgreSQL Studio, a web-based PostgreSQL management application. PostgreSQL Studio can be downloaded and run using any Java-based web server, such as Tomcat or JBoss. Let's experiment with running a local deployment.

Installing PostgreSQL Studio

PostgreSQL is a java web application, so the first step will be to ensure that you have the latest version of the Java Runtime Engine (JRE) installed on your system. You'll also need the application and a web server to run it on. The PGStudio download page has a version that comes pre-bundled with Apache Tomcat, a Java-based web server.

For simplicity's sake, that's the version we'll use here. If you already have a web server, or if you want to use a different server like JBoss, you can download the plain .war file to run within a web server directly from the site as well.

Once you've downloaded the Tomcat-bundled version, unzip it using your operating system's unzip utility and navigate to that folder with your terminal program.

$ cd <download_location>

The archive contains a file called RUNNING.txt which lists out instructions for running the application on various platforms. Ultimately, once setup is complete, the following command can be used to start up the web server from within the download directory:

Linux and Mac OSX:

$ ./bin/startup.sh

Windows:

C:\<download_directory>> bin/startup.bat  

Once you see a Tomcat started message, you can now access PostgreSQL Studio. Open a web browser and type http://localhost:8080 in the URL bar. You should see the application page load up.

Running with a Docker Container

If you're having trouble getting the configuration to work, or would rather run in a docker container, you can run it using the lukasmartinelli/pgstudio image on Dockerhub using the following command.

docker run --rm -p 8080:8080 -t lukasmartinelli/pgstudio  

Connecting to Compose PostgreSQL

Connecting to Compose PostgreSQL using PGStudio is a snap - simply enter your connection details from the connection info section of your PostgreSQL deployment and you're all set.

Running Basic Queries

Once you're connected up, you'll be dropped into the main querying interface.

Let's start by creating a table. Click on the Tables tab in the accordion menu and click the Plus button. This will bring up the Add Table interface.

From there, we can add and configure new columns with names, data types, and other properties.

Once you've created a table, double-clicking on it will open the table configuration panel. From this panel, you can configure your table in great detail including adding indices, constraints, triggers, rules, and more.

If you're looking for a fully-featured web-based interface for managing PostgreSQL, PostgreSQL Studio is a robust tool with many more features that are worth exploring.

psql

This list wouldn't be complete without a quick peek at the command line interface bundled with every installation of PostgreSQL. While using the command line may seem a bit archaic to some, there are quite a few good reasons why you may want to consider it. Aside from having full support for the entire range of features available in each version of Postgres, psql is an excellent way to automate administration tasks; since it's a command line application, developers can take advantage of features like input stream and output stream redirection to perform tasks from a script.

Let's take a peek at how we can use psql:

Installation

psql is bundled with every installation of PostgreSQL, so if you have a installed a local version of the database then chances are already pretty good you have a copy. If not, the best way to install the tool is to install a local version of the database from the downloads page.

If you're using Mac OSX, you can install PostgreSQL via the homebrew package manager:

brew update

brew install postgres  

Connecting to Compose PostgreSQL

Next, we'll connect up to our Compose deployment by opening a terminal and entering the following command to connect to our database. We'll automatically be prompted to enter our password once we execute the command:

$ psql -h aws-us-east-1-portal.27.dblayer.com -U admin -p 23012 -d compose

If all goes well, we should be dropped into the psql prompt:

Password for user admin:  
psql (9.6.5, server 9.6.3)  
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)  
Type "help" for help.

compose=>  

We can now start querying our database using PostgreSQL commands. For example, let's inspect the database to see the tables that are present.

compose-> \dt  
       List of relations
 Schema | Name  | Type  | Owner 
--------+-------+-------+-------
 public | Test1 | table | admin
(1 row)

The command line tool is the most minimal and powerful interface to PostgreSQL, and sometimes just typing in a command directly is the fastest way to get things done.

Summing it Up

There are many more great tools for managing your PostgreSQL deployments on Compose, and these represent just a small sampling of them. Over the next few weeks, we'll take a deeper dive into these as well as others and show you just how t

If you have any feedback about this or any other Compose article, drop the Compose Articles team a line at articles@compose.com. We're happy to hear from you.

attribution C Rayban