Tool Up: pgAdmin4

Published

ToolUp is a series of articles where we take a deeper dive into the tools that make database developers' lives easier. In this article, we'll take a look at pgAdmin4 for PostgreSQL.

When you're searching for a PostgreSQL admin tool, there's no better place to start than pgAdmin. pgAdmin began its life in 1998 as a Java application called pgManager, and the latest pgAdmin4 is a complete rewrite of the application using web technologies and the Electron framework.

pgAdmin4 has a wide array of tools for administering PostgreSQL, and in this article, we'll walk through some of the key features of the tool by performing some common administrative tasks.

Installing pgAdmin4

pgAdmin4 can be installed as a stand-alone application for various platforms or as a web-application from source. Visit the download page for pgAdmin4 and select the platform of your choice. We won't cover running pgAdmin4 as a web application from source in this article, but the source code comes bundled with a Python web server and straightforward build instructions.

Connecting to Compose for PostgreSQL

To connect to Compose for PostgreSQL, we'll first need to create a server. Servers are the abstraction that pgAdmin4 uses to represent a connection to a remote PostgreSQL database. pgAdmin4 also allows you to create server groups, which can make managing collections of databases easier. Let's take a look at a few of the ways we can create new servers in pgAdmin.

We can create a server directly from the welcome dashboard, which is the first screen that appears when you launch the application.

pgAdmin Dashboard

Click on the Add New Server button and the server creation dialog will appear.

New Server Dialog

pgAdmin4 also has two different context-sensitive menus that you can use to create new servers. The first can be reached by right-clicking on servers in the browser panel and selecting create and server from the context menu:

Create New Server Context Menu

The second is by selecting servers in the browser panel and then clicking the object item from the top navigation menu.

Create New Server from Main Menu

Keep in mind that the object menu is context-sensitive, meaning the options will change depending on what you have selected. We'll explore that a little later when we create other objects such as Users, Tables, and Views.

Now that we know how to get to the interface that allows us to add a new server, let's create one. First, create a new Compose for PostgreSQL instance from the Compose dashboard and snag your connection string from the Connection Info section of the dashboard. We'll use those credentials to connect.

Connection String Info

Next, open the Create - Server dialog using one of the methods we discussed above. Make sure to give your server a name in the General tab:

Create Server Dialog General Tab

Then, navigate to the Connection tab and enter the connection info for your Compose for PostgreSQL deployment. Make sure to leave the SSL Mode option as Require. Then, click on the Save button.

Create Server Dialog Connection Tab

If the connection is successful, you should now see a new server in the servers section of the browser.

Collapsed view of Servers in Browser Panel

Walkthrough of the pgAdmin4 Browser

Once you've created a server, most of your navigation will take place in the browser tab on the left side of the user interface.

Highlight of Browser Panel

If you un-collapse the server tree, you'll see three high-level options for administering the server: Databases, Login / Group Roles, and Tablespaces. The Databases section is where you'll spend most of your time working in pgAdmin4, so let's discuss the first two before diving into Databases.

Creating a Database User

The first administration menu we'll take a look at is the Login / Group Roles section. This section allows us to create new users, update existing users, and delete users we no longer need. Compose for PostgreSQL defaults to a user with the name admin, and clicking on the admin user in the Login / Group Roles section allows us to inspect that default user.

Database User Info

While viewing this information is safe, modifying any of the information of the admin user can make your Compose for PostgreSQL database inaccessible. As such, it's very strongly recommended that you don't modify or delete this user. You can, however, change the password.

You can, however, safely create new users with the same or lesser permissions as the admin user. To create a new user, right-click on any item in the Login / Group Roles section to open the context menu. Select the Create menu item and select Login/Group Role.

Create new Login / Group Role Context Menu

This brings up the Create - Login/Group Role dialog. When creating a new user through the dialog, you'll need at least a name for the user. You can also modify the permissions for the new user in the Privileges tab, although the user you create can only have privileges available to the admin user. Clicking Save will save that user to the database.

Edit User Privileges

Once you've created a new user, you can update that user by right-clicking on their username to bring up the context menu, then clicking Properties...

Edit User Properties context menu

This brings up a dialog that has the same options as the create dialog, so you can modify any of the properties of a user after creation.

A Short Note on Tablespaces

The second high-level menu item in the database admin panel is called Tablespaces. The Tablespaces menu is used to define alternative file locations for storing PostgreSQL database artifacts, such as tables and indices. Compose for PostgreSQL does not allow these types of modification to take place, so the Tablespaces menu won't be covered in this article.

Databases Menu

Now that we've tackled the first two menus, it's time to look at the databases menu, which is where you'll spend most of your time. When you expand an item in the databases menu, you'll see all of the database objects you can administer.

Databases Browser Menu

You can read more about what the different objects do in the online help documentation for pgAdmin4. For the remainder of this article, we'll focus on items in the Schemas database object and, in particular, the Tables submenu.

Schema Menu

Creating, Updating, and Deleting Tables

Let's start adding data to our database by creating our first table. Right-click on the Tables menu item in the browser, mouse over the Create menu item and click on Table...

Create Table Contet Menu

This brings up the Create - Table dialog which has all of the options we'll need to create our new database table. As an example, let's create a table that we might see in a Student Information System, student. Enter student into the name field of the form:

Create Table Dialog

Then, click on the columns tab and click on the + button to add a new column to the table. Then, click on the "edit" icon to update the table column. Let's create a field called firstName with a type of character varying [] and a length of 255.

Add Column Dialog

You can also set the field as the primary key and apply a NOT NULL constraint by clicking on the toggle switches. Click the save button to save that column, and your table schema will now be updated. You can add more columns using the same interface.

If you already have a table created, you can update the columns in that table from the Browser by expanding the table, right-clicking on Columns, and selecting Column from the Create context menu:

Create Column Context Menu

You can view the details of an existing column by selecting it from the browser and navigating to the properties tab in the main panel. You can also edit the selected column by clicking on the edit icon at the top of the main panel.

Edit Column Properties Interface

Finally, you can delete the column by right-clicking on the column in the browser and selecting Delete/Drop from the context menu:

Delete Column Context Menu

This pattern is a theme throughout pgAdmin4, and we can use the same right-click context menu on other database objects as well. Under the student table, in addition to columns, you'll also see objects for Indexes, Constraints, Rules, and Triggers. You can add, update, and delete these using the same right-click context menu that you used to create a table.

Creating, Updating, and Deleting Records

Usually, you won't need to use pgAdmin for adding records to your database but it can be very helpful for viewing, updating, and deleting records in one-off scenarios. Let's start by adding a record to the student database we created earlier. Right-click on the student table in the browser and select All Rows from the View/Edit Data context menu.

View / Edit Data Context Menu

This will perform a query on the database and return all of the records in a table format. Since we haven't created any records yet, we won't see any data in this interface yet.

View / Edit Data Table

However, there's no way to create the data in this interface. That's because we haven't defined a primary key on our table yet. Tables without a primary key or OID field can't be edited in the View/Edit Data interface. The simple fix for this is to define a primary key in the table.

Right click on the Constraints menu in your student table in the browser, then click on Primary Key from the Create context menu

Create Primary Key Constraint

We'll name the primary key constraint, and then from the Definition tab, select the column that contains the primary key. Since we only have a firstName column here, we'll select that, but typically you'll want a primary key that's more robust. Click the save button to commit the change and add the primary key.

Create Primary Key Dialog

Once the primary key is defined, we can navigate back to the View/Edit Data interface. Notice how we now have an input box in the table beneath the firstName column header. Clicking on that box will open a text editor that you can use to add a new record.

Create / Update Rows

You can also edit records in this interface by double-clicking on an existing table cell to bring up the editor and clicking save when you've finished your edits.

Update Column Text Editor

You can also delete a record by selecting the record you'd like to delete on the table and clicking on the trash icon at the top of the interface.

Delete Row Illustration

Summing It Up

pgAdmin4 is a complex tool with many features to explore. It provides a convenient visualization of the many PostgreSQL database objects available, and would be an excellent addition to the database admin's toolbox. The interface can be tricky for new database administrators to navigate, but users are rewarded with easy access to some of the most powerful features of PostgreSQL through a simple and consistent visual interface. In our next article, we'll take a look at another PostgreSQL administration tool and see how it compares to pgAdmin.


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 Alex McIl

John O'Connor
John O'Connor is a code junky, educator, and amateur dad that loves letting the smoke out of gadgets, turning caffeine into code, and writing about it all. Love this article? Head over to John O'Connor’s author page to keep reading.

Conquer the Data Layer

Spend your time developing apps, not managing databases.