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.
https://www.pgadmin.org/docs/pgadmin4/4.28/index.html
This post will describe how to install and interact with pgAdmin, the management tool for PostgreSQL and derivative relational databases. pgAdmin 4 can be run as either a web or desktop application. I will briefly go over:
- Creating a database
- Creating users and granting roles
- Creating Tables
- Importing Data
I will be connecting to the database created and populated from my previous Criminal Analysis: Data Storage posts.
Installation
PostgresSQL
Although, not necessary to install first, I would suggest installing PostgreSQL first. Whats the point of installing an graphic interface to a database if you don’t have the database? For instructions on installing, please refer to my install guide here.
To confirm that PostgreSQL service is active, running and is enabled, run the following from the terminal.
$ sudo systemctl is-active postgresql
$ sudo systemctl is-enabled postgresql
$ sudo systemctl status postgresql
# Check if your service is ready for connections and accepting
$ sudo pg_isreadypgAdmin
From the pgAdmin download page you can select your specific installation configuration.
On a Linux machine, getting pgAdmin installed and running, via APT, is pretty easy and straight forward. Just follow the simple APT instructions from the pgAdmin website (here). I have copy/pasted their code instructions below for ease and simplicity. The following did work on my Linux/Ubuntu 20.04 Computer.
The following platforms are supported with these installation instructions:
- Debian 9 (Stretch), 10 (Buster)
- Ubuntu 16.04 (Xenial), 18.04 (Bionic), 19.10 (Eoan), 20.04 (Focal)
# ********************************************
# Install it from the pgAdmin4 APT repository
# ********************************************
# Install the public key for the repository (if not done previously):
curl https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo apt-key add
# Create the repository configuration file:
sudo sh -c 'echo "deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'
# ********************************************
# Choose whichever application mode you desire
# ********************************************
# Install for both desktop and web modes:
sudo apt install pgadmin4
# Install for desktop mode only:
sudo apt install pgadmin4-desktop
# Install for web mode only:
sudo apt install pgadmin4-web
# Configure the webserver, if you installed pgadmin4-web:
sudo /usr/pgadmin4/bin/setup-web.shMy default installation through my Linux/Ubuntu 20.04 was version 13.1
Configuring the Webserver
If you installed pgadmin4 or pgadmin4-web, the you can run the following to configure your webserver:
sudo /usr/pgadmin4/bin/setup-web.shThe shell script configures the database and authentication. You will need to enter an email address and password. After providing that information the application initializes, creating the storage and log directories. It will ask a couple of questions, which you will need to respond to. The following screenshot demonstrates the process I went through during my configuration.
Web Mode
Make sure the PostgreSQL server is running. You can use the following to start the service.
sudo systemctl start postgresql@13-mainFollowing the configuration, it provided me with the URL http://127.0.0.1/pgadmin4 to access pgAdmin 4 in web mode. You can also access it with http://localhost/pgadmin4 if running on localhost. If you have a predefined IP address, simply replace the 127.0.0.1 in the URL above.
Going the URL should bring you to the following. Enter your username and password to login.
You should get the following display if your login was successful.
Add New Server
In this example, I will be configuring the new server to the database, I created for my Criminal Analysis project (reference post).
The “Create -Server” menu has several tabs. For my initial configuration, I will just enter information on “General” and “Connection”. The other tabs can be configured for your specific needs.
General Information
The following screenshot shows the information I have provided to describe my server.
Connection Information
The connection information is what I have been using on my PostgreSQL database created for my Criminal Analysis project.
View Server
Once you have saved the information on the “Create – Server” menu, you should be able to see the composition of your connected database server like the image below. You can explore the server tree of information Notice the Databases and Login/Group Roles. You should explore the nested information to understand what you have access to and what information can be provided.
Dashboard View
The dashboard view provides streaming metrics and information about your database.
Other Menu Tabs
The other menu tables adjacent to the “Dashboard” can be explored to provide information for each selected object in the database.
- Properties
- SQL
- Statistics
- Dependencies
- Dependents
Example Properties
Here we can see what the Properties tab looks like for the selected Table object “dc_polygon” looks like. You can modify the information by clicking the “Edit” button in the upper right corner.
Example SQL
Here we can see what the SQL tab looks like for the selected Table object “dc_polygon”. This table shows up the SQL statements for this particular object.
Example Statistics
Here we can see what the Statistics tab looks like for the selected Table object “dc_polygon”.
Example Dependencies
Here we can see what the Dependencies tab looks like for the selected Table object “dc_polygon”. This tells us the various objects the current object depends on.
Since this particular table does not have any Dependents there is no information on that tab.
Creating a Database
In the images below you can see the results of creating an example database through pgAdmin 4 (web mode). First I right-click on the “Database” object, then navigate to “Create” and “Database”.
This brings up a menu window to work through the options to creat the database. IN the following images you can see what I enter into the “General”, “Definition” and “Security” tabs.
After clicking “Save”, I can see the database in my object browser pane.
Please reference the official documentation (https://www.pgadmin.org/docs/pgadmin4/latest/managing_database_objects.html) for creating or modifying database objects. The documentation will provide more details on explanation for those interested.
Creating a User/Group
Now we will demonstrate creating a user. First right-click the “Login/Group Roles” object in the Browser pane. Then navigate to “Create” and “Login/Group Role”.
That should bring up the window to create the new user/group role. Go through each tab to configure the user/group as desired.
General Info Definition Privileges Membership assignment Parameters Security
When you click “Save”, you will see your new user/group in the object browser pane.
Please reference the official documentation (https://www.pgadmin.org/docs/pgadmin4/4.28/user_management.html) for creating or modifying a user/group. The documentation will provide more details on explanation for those interested.
Creating Tables
The following screenshots will demonstrate creating an example table.
This will open up a window to begin creating and defining your new table object. You can go through each of the window tabs to specify the configuration. In the pictures below, I just define the “General” and “Columns” tabs to get the table initially created.
After you click “Save”, you can see your newly created table in the tables portion of your database.
Please reference the official documentation (https://www.pgadmin.org/docs/pgadmin4/latest/modifying_tables.html) for creating or modifying a table. The documentation will provide more details on explanation for those interested.
Importing Data
We can also import data into our database. Sometimes you will have a spreadsheet or existing database file you would like to just import into your database. The following will show you how to do that.
Define Columns If You Have Not Already
First, you need to define the table and columns of the table. In the previous section, I showed how to create table. During that creation, we did not define the columns. If you are going to import the data, the columns need to be defined. The following image shows our columns and datatypes using the SQL table while the desired table is selected.
Import Tool
You can right-click on the table then click on the “Import/Export” option. Next we toggle the Import/Export switch to display “Import”. For the filename, I had to drag and drop the desired file into the importer before I could select the file to import. Then I configured the encoding field, delimiter, quote and escape fields. I toggled the “Header” option as well since my file contains a header. If the file contained an Object ID (OID), you could toggle that as well.
Before hitting “OK”, I checked out the “Columns” tab to check to make sure I didnt miss anything. Fill the the appropriate fields as needed. Make sure the right columns are in the “Columns to import” field too.
Once you click “OK”, you should see a window pop up in the lower right-hand corner giving you the status.
Verify Import, View Data
Now I’ll check to see if the data was imported as I assumed it would. To do this, right-click on the table, then select the “View/Edit data” option. It sbhould bring up a viewing pane as seen in the image below.
Please reference the official documentation (https://www.pgadmin.org/docs/pgadmin4/latest/import_export_data.html) for creating or modifying a table. The documentation will provide more details on explanation for those interested.
Remove Table
To remove a table in pgAdmin select the table, right-click then navigate to the “Delete/Drop” option. A dialog window will pop up asking you to confirm deletion. Once the table is deleted, it will no longer appear in your table list.
Conclusion
pgAdmin looks to be a great graphical interface to the PostgreSQL/PostGIS database. This would certainly be an application for DBAs and Data Engineers but likely not for Data Scientists or Data Analysts. For those that are not DBAs or Data Engineers, interacting with the database can be done easily from within R or Python.
I would say that creating a table and importing data is much easier in R or Python, as can be seen in both my Data Storage posts for my Criminal Analysis project. Its good to be exposed to what is available out there regardless of your position.
What’s next?
Now that we have the basics of creating and modifying a database for a data analysis project using the pgAdmin application, there are a couple different paths for you.
- You can certain dig into the database administration and management side of things.
- Design
- Security
- Admin/Management of Data
- Services
- As a Data Scientist, you may not ever need to dabble in this particular area of skills. Typically a Data Engineer or DBA will handle the database stuff.
- You can always learn more about SQL to advance your fluency and capabilities
- Querying the structures
- Modifying data
- Joins
- Procedures
- Advance your fluency and complexity
In future posts, I may go over a little more about database stuff like design, security, advanced SQL queries, functions, trigger functions and procedures.
SQL and Database Resources
If you have not learned much SQL and Databases, I would highly recommend setting up an account at HackerRank.com. It is free and provides good problems to solve, which gives you experience. Work through their SQL and Database problems. SQL is a very valuable skill to have and often expected for Data Scientists to know. If you are interested in Database problems, they have those too. The image below shows you all the skills available to learn from their platform.
For referencing or looking up commands, syntax, and examples use W3Schools.com and tutorialspoint.com.
Database Resources
For official information and documentation on PostgreSQL check out their website and documentation at https://www.postgresql.org/docs/13/index.html.
For the official pgAdmin documentation, please refer to this: https://www.pgadmin.org/docs/pgadmin4/4.28/index.html
There are all sorts of online resources and lots of books out there to help educate you on more database skills.
