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.
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.
Click on the Add New Server button and the server creation dialog will appear.
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:
The second is by selecting servers in the browser panel and then clicking the object item from the top navigation 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.
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:
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.
If the connection is successful, you should now see a new server in the servers section of the browser.
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.
If you un-collapse the server tree, you'll see three high-level options for administering the server:
Login / Group Roles, and
Databases section is where you'll spend most of your time working in pgAdmin4, so let's discuss the first two before diving into
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.
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.
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.
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...
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.
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.
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.
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...
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:
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.
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:
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.
Finally, you can delete the column by right-clicking on the column in the browser and selecting Delete/Drop from the 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.
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.
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
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.
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.
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.
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.
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 firstname.lastname@example.org. We're happy to hear from you.
attribution Alex McIl