Difficulty: beginner
Estimated Time: 15 minutes

Basic Introduction to PGAdmin4

This class gives you a basic introduction to using PGAdmin4 - a GUI for working with your PostgreSQL database.

From the project site:

pgAdmin is the leading Open Source management tool for Postgres, the world’s most advanced Open Source database. pgAdmin 4 is designed to meet the needs of both novice and experienced Postgres users alike, providing a powerful graphical interface that simplifies the creation, maintenance and use of database objects.

In this scenario we will just get you started using the tool. We will cover topics such as :

  1. Connecting to a database
  2. Navigating in the interface
  3. Creating, editing, and executing some SQL
  4. Creating a table

We assume you understand basic database concepts such as creating a table with data definition langauge (DDL) or simple SQL queries. If not, there may be some terminology you do not understand in the class but you should still be able to complete the class without major problems.

We use a blockquote for commands that you should do in your sessions.

They look like this.

We have already have spun up a PostgreSQL instance with a database. Inside that database we have created a spatially enabled table for storm event locations in the United States.

Here are the details on the database we are connecting to:

  1. Username: groot
  2. Password: password (same password for the postgres user as well)
  3. A database named: workshop

The credentials for PgAdmin4 are:

  1. username: admin
  2. password: password

    Let's get started

Final Notes

I hope you feel more comfortable beginning to use PgAdmin4 to interact with your PostgreSQL database. If you are uncomfortable with the command line or perhaps you are debugging some complicated SQL, PgAdmin4 can really be your friend.

We really just showed a limited amount of the functionality in the GUI. As you probably noticed there is a lot you can do when right click on objects or click on the alternative tabs for an object. I STRONGLY recommend that you keep playing around.

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.

And with that, we are finished.

Intro. PgAdmin4

Step 1 of 4

Connect To Your Database

Connecting to our Database

If you look at the second tab in our learning interface it says PgAdmin4. Go ahead and click that tab which should spawn a new browser window or tab. You may have to wait a few seconds while the PGAdmin4 interface gets rendered. You should now be able to login using username admin and password password.

First screen

You should be seeing a screen that looks like this:

Home Screen

This is the home page for PgAdmin4. Before we can do anything meaningful we need to connect to a PostgreSQL server. There are two ways to do this:

  1. Click on the "Add New Server" butting in the middle of the screen.
  2. Right click on the Servers icon in the top left of the left navigation panel.

Let's start by doing the right click option because it will give us more flexibility.

Right click options

Create Server

When you right click on the icon you should see a list of options. Go ahead and mouse over create. This will then show server group and server. If you are going to manage and interact with a lot of PostgreSQL servers then you might want to create server groups such as testing vs production. Server groups is just a logical organization in the PgAdmin interface.

In our case since we are only managing one server:

Go ahead and click server to bring up the new create server dialog.

Create Server Dialog

While there are quite a few boxes and tabs in this dialog, only a few of them are actually required to create a new server in PgAdmin.

First Tab

First Tab

On the general tab, the only field required is Name. This name represents the name YOU want to use to identify this PostgreSQL server in the PgAdmin interface. You could call it monkey if you want - it doesn't require any relation to a name in the real world.

For today's exercise let's name it "Workshop"

In the comment field you can put in some information about this server connection. The information can be brought up in the interface later and can help you remember facts about this server.

Once you are done filling in those fields go ahead and click on the second tab titled Connection.

Second Tab

This Connection tab contains most of the important information about your server.

Second Tab

In the Host name/address field you put the IP address, DNS name, or network name PgAdmin should use to reach your PostgreSQL Server. For today's exercise we are running PgAdmin and PostgreSQL in containers with a host network so we are going to use the container name for our server, which is pgsql.

Put pgsql in the address box.

Note: you can also run PgAdmin4 on your local machine, which means you would use the address that you use to connect from your local machine to the DB server.

Port 5432 is the default port for PostgreSQL so we will leave that alone. By default, the name of the maintenance database is postgres so we leave that alone as well.

The next crucial piece of information is Username and Password. This is the username and password you use to connect to your PostgreSQL server. We are going to connect as a normal, unprivileged user:

  • username: groot
  • password: password

For convenience you can save the password on the PgAdmin server - check it if you want.

And now we are finished with all the information required to connect. Go ahead:

click the Save button on the bottom of the dialog.

The other tabs are for more advanced use cases not covered today.

What you should see now

After clicking the Save button, your left nav should now have a connection named Workshop.

New Connection

Wrap Up

You now have successfully connected PgAdmin4 to your PostgreSQL server - this is probably one of the more confusing aspects of PgAdmin usage - congrats! The rest of our steps will help you navigate and get work done in PgAdmin4.