As we step into July 2023, I find myself on a new path after the termination of my contract, seeking a fresh role. While exploring job postings for database administrators, I’ve noticed a recurring trend – many positions now require knowledge of databases other than SQL Server. Though I’ve spent the majority of my career supporting Microsoft SQL Server, I recognize the importance of broadening my skillset by delving into the realm of open source offerings.
In this blog series, I’ll be documenting my journey of learning PostgreSQL, which I’ve chosen as my first step towards diversifying my expertise. If you’re also interested in an in-depth and comprehensive introduction to PostgreSQL, I recommend starting with resources like PostgreSQL Tutorial or similar ones. To facilitate my learning, I’ve gathered various resources, including LinkedIn Learning, interactive labs on O’Reilly, and thorough internet searches.
At various points I’ll switch between command line and GUI, so I’ll be using Windows Terminal for the command line, and either PGAdmin or dBeaver for the GUI.
Getting Started with Docker
Containerization has proven to be an excellent tool for accelerating the learning process when it comes to new software. Containers simplify repetitive configuration tasks by providing fully functional images of popular operating systems and software. As I embark on my PostgreSQL learning journey, I’ll be utilizing Docker on Windows extensively for practice.
Docker Desktop, part of the free Docker Personal offering, will be my go-to platform. However, before diving in, there is a prerequisite to install and configure the WSL 2 backend on Windows, and instructions can be found on the Desktop download page.
Once Docker Desktop is installed, Docker Hub becomes the source of container images. There are two methods to pull the PostgreSQL image into Docker Desktop:
After installing Docker Desktop, Docker Hub is the source of container images. There are two methods of pulling the image to Docker Desktop:
- Within Docker Desktop, use the search at the top to find ‘postgres’ and then click “Pull” on the line displaying the ‘Official Docker Image’ tag.
- Alternatively, from a command line, execute
docker pull postgres.
Creating and Managing Containers
While creating a container from an image is easily done via Docker Desktop, I’ve discovered that using the command line offers better control. To create a new container named ‘postgres’, run the following command:
docker run --name postgres -e POSTGRES_PASSWORD = <secret> -d -p 5432:5432 postgres
| Option | Description |
|---|---|
--name | Assign a name to the container instead of using randomly generated |
--env, -e | Set environment variable |
--detach, -d | Run container in background |
--publish, -p | Publish container’s port to the host |
With the ‘postgres’ container now running, I have two options for connecting to it – command line or GUI. Docker Desktop conveniently suggests installing the PGAdmin extension which creates another container to keep things tidy.
Using PGAdmin
PGAdmin is a standard GUI for PostgreSQL and is my tool of choice for connecting to the ‘postgres’ container. The first time PGAdmin opens, it prompts for a master password, which secures all user passwords for connecting to individual servers.
To connect from the PGAdmin container to the PostgreSQL container, a special host name, ‘host.docker.internal,’ is required. This ensures that PGAdmin connects to the port internal to Docker, rather than attempting to connect to the Windows port. If PostgreSQL Server were installed locally, the host name would be ‘localhost’ or ‘127.0.0.1’.
The default database connection is to the system database called ‘postgres,’ with the default username as ‘postgres,’ and the password supplied via the docker run environment variable ‘POSTGRES_PASSWORD.’ Once connected, the tree view will be displayed in the object browser.
Using psql
psql serves as the command line for PostgreSQL. To install it locally and run it from the command line, you’d need to run the PostgreSQL installer and deselect everything except ‘Command Line Tools.’ As I have also installed the server locally, I already have psql available.
Alternatively, another option is the Terminal connection of the ‘postgres’ container. In Docker Desktop, click on the container name and then select the Terminal tab. This will bring up the bash terminal, and you can use psql -U postgres (case-sensitive) to connect. The prompt will change to postgres=#. To exit psql, type \q.
Conclusion
With my learning environment now set up, I’m ready to delve into the basics of PostgreSQL. In my next post, I will focus on exploring fundamental psql commands and continue sharing my progress on this exciting learning journey. Stay tuned for more!