Getting Started with PostgreSQL in Docker

Vishal Pallerla•August 24, 2023

In this step-by-step guide, we will walk you through the setup process of a PostgreSQL database using Docker with and without a DevZero environment.

Learn more about What is Docker, What is it Used For, and How Does it Work?

Moreover, we'll show you how to interact and perform CRUD operations using a straightforward Python script and the easy-to-use management tool, pgAdmin. By the end of this tutorial, you will have a running PostgreSQL instance in a Docker container and will be able to interact with it using your preferred method.

Postgres is an accepted alias for the PostgreSQL project. Learn more about the differences here

Based on your preference, you have two options to proceed with setting up PostgreSQL on Docker: configuring everything on your local machine or starting right away with a DevZero machine that is pre-configured with all the necessary requirements. Choose the section below that aligns with your preferred approach:

With DevZero #

If you prefer to start right away with a DevZero machine that is pre-configured with all the necessary requirements, follow the steps provided in this section. This option allows you to skip the local configuration process and quickly begin working with PostgreSQL.

1. Sign up for a DevZero account with our Getting Started flow.

2. Once signed in to DevZero Console, Create a copy of Postgres Docker template using this link or from the dashboard and click launch.

Launch Postgres on Docker with DevZero

3. Once your environment is ready, click OPEN IN VS CODE to open the project in your local VS Code IDE. This option allows you to seamlessly transition from the DevZero console to your favorite local IDE VS Code. If this is your first time using the VS Code option, we recommend checking out our user onboarding guide.

Additionally, you may find our VS Code shortcuts blog post useful. It features 10 useful shortcuts that can significantly enhance your coding experience in VS Code.

4. Next, simply follow the instructions provided in the PostgreSQL in Docker section to get started efficiently and effortlessly.

Flowchart showing differences in launching postgres on docker with and without devzero

Without DevZero #

If you prefer to configure everything on your local machine, follow the steps provided in this section. This option allows you to have full control over the setup process and customize it according to your specific requirements.

Install Docker #

Before we begin, make sure you have Docker installed on your machine. If you don't have Docker installed, follow the steps outlined in Docker's official documentation to install Docker on your platform:

• Windows
• macOS
• Linux

Install Git #

Download and install Git from the official website

Install Python #

To enable interaction with the PostgreSQL database that we will create in future steps, you can utilize Python scripts for seamless communication. Ensure that you have Python installed on your machine. If Python is not installed, you can download and install it from the official Python website. This will provide you with the necessary Python runtime environment to execute the scripts and interact with the PostgreSQL database effectively.

Additionally, you should have some basic knowledge of SQL and database concepts.

Install SQLAlchemy and psycopg2-binary #

First, you need to install SQLAlchemy and psycopg2-binary, which is a precompiled version of psycopg2. Run the following command:

pip install sqlalchemy psycopg2-binary

Clone the Repository #

To proceed with the tutorial, you can clone the repository containing the sample python script to interact with the postgres database that we will create in subsequent steps. Follow the instructions below to clone the repository:

• Open a terminal or command prompt.
• Navigate to the directory where you want to clone the repository.
• Execute the following command to clone the repository:

git clone https://github.com/devzero-examples/postgres-docker.git

This command will clone the repository and download all the files to your local machine.

Pull the PostgreSQL Docker Image #

Since Docker images are the building blocks of containers, we first need to pull the official PostgreSQL image from Docker Hub. Open a terminal and run the following command:

docker pull postgres

This command will download the latest PostgreSQL image to your local machine.

Pull the pgAdmin Docker Image #

If you prefer to interact with the PostgreSQL database using pgAdmin, we can obtain the official pgAdmin Docker image from Docker Hub. To do this, follow the steps below:

docker pull dpage/pgadmin4

By running this command, the latest pgAdmin image will be downloaded and stored on your local machine. This image will be utilized to set up and utilize pgAdmin for managing and interacting with the PostgreSQL database in subsequent steps.

PostgreSQL in Docker #

By following these steps, you will be able to proceed with the tutorial regardless of whether you opted for the DevZero environment or configuring everything on your local machine. Navigate to the project directory where you cloned the repository and follow these steps

Create a Docker Container for PostgreSQL #

Create a new Docker container using the PostgreSQL image. Run the following command with your preferred values for the container name, password, and other parameters. Here, I am setting the container name to my_postgres:

docker run --name my_postgres -e POSTGRES_PASSWORD=mysecretpassword -p 5432:5432 -d postgres

This command creates a new container with the following settings:

--name my_postgres: Sets the container name to "my_postgres".

-e POSTGRES_PASSWORD=mysecretpassword: Sets the PostgreSQL password to "mysecretpassword".

-p 5432:5432: Maps the container's port 5432 to the host's port 5432.

-d postgres: Specifies the Docker image to use (in this case, the PostgreSQL image).

To verify the creation of the PostgreSQL container, you can use the docker ps command.

docker ps

Verifying postgres container creation

Look for the container with the name my_postgres in the output. Ensure that the container has the PostgreSQL image postgres and the specified container name.

Interact with PostgreSQL using a Python Script #

If you prefer to interact with the PostgreSQL database using a Python script, follow the steps below:

Navigate to the postgres-docker directory:

cd postgres-docker

Update the config.ini file with the following values:

This configuration file stores the PostgreSQL connection details, including the password, in the [postgresql] section.

[postgresql]

host=localhost

port=5432 #YOUR PORT NUMBER

dbname=postgres #DATABASE NAME YOU GAVE WHILE CREATING POSTGRES DOCKER CONTAINER

user=postgres #USERNAME 

password=mysecretpassword #PASSWORD

By storing the PostgreSQL password in a separate configuration file, you can keep sensitive information out of your Python script. This makes it easier to manage and secure your application's configuration.

You can use the configparser library to read the configuration file in your python script.

Note: The repository you cloned contains the complete python script for basic CRUD operations. However, for your reference, here is the code:

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import sessionmaker, declarative_base
import configparser

# Read the configuration file
config = configparser.ConfigParser()
config.read("config.ini")

# Get the PostgreSQL connection details
host = config.get("postgresql", "host")
port = config.get("postgresql", "port")
dbname = config.get("postgresql", "dbname")
user = config.get("postgresql", "user")
password = config.get("postgresql", "password")

# Define the database connection
engine = create_engine(f"postgresql://{user}:{password}@{host}:{port}/{dbname}")

# Define the ORM model
Base = declarative_base()

class Test(Base):
    __tablename__ = "test"
    id = Column(Integer, primary_key=True)
    name = Column(String)

# Create the table
Base.metadata.create_all(engine)

# Create a session
Session = sessionmaker(bind=engine)
session = Session()

# Create
new_entry1 = Test(name="John Doe")
new_entry2 = Test(name="Bob")
session.add(new_entry1)
session.add(new_entry2)
session.commit()

# Read
rows = session.query(Test).all()
print("Read:")
for row in rows:
    print(row.id, row.name)

# Update
update_entry = session.query(Test).filter_by(name="John Doe").first()
if update_entry:
    update_entry.name = "Jane Smith"
    session.commit()
    print("Update: Entry updated successfully.")

# Read after update
rows = session.query(Test).all()
print("Read after update:")
for row in rows:
    print(row.id, row.name)

# Delete
delete_entry = session.query(Test).filter_by(name="Jane Smith").first()
if delete_entry:
    session.delete(delete_entry)
    session.commit()
    print("Delete: Entry deleted successfully.")

# Read after delete
rows = session.query(Test).all()
print("Read after delete:")
for row in rows:
    print(row.id, row.name)

# Close the session
session.close()

This script demonstrates basic CRUD operations (Create, Read, Update, Delete) using SQLAlchemy with PostgreSQL. It connects to the PostgreSQL database using the provided configuration details from config.ini file, creates a table named test, inserts a new entry, performs read operations, updates an entry, deletes an entry, and performs additional read operations to showcase the changes.

Finally, run the Python script using the following command:

python postgres_sqlalchemy.py

Output from python script showcasing basic CRUD operations

Interacting with PostgreSQL using pgAdmin #

Pgadmin is an open-source administration and management tool for PostgreSQL, offering an intuitive graphical interface for managing databases, users, and SQL queries.

If you prefer to interact with the PostgreSQL database using pgAdmin, follow the steps below. We will use the official pgAdmin Docker image and configure it to connect to the PostgreSQL container created earlier in the tutorial.

Create a Docker Container for pgAdmin #

Create a new Docker container using the pgAdmin image. Run the following command with your preferred values. Here, created a container named my_pgadmin:

docker run --name my_pgadmin -p 5051:80 -e "PGADMIN_DEFAULT_EMAIL=admin@example.com" -e "PGADMIN_DEFAULT_PASSWORD=admin" -d dpage/pgadmin4

This command creates a new container with the following settings:

--name my_pgadmin: Sets the container name to "my_pgadmin".

-p 5051:80: Maps the container's port 80 to the host's port 5051.

-e "PGADMIN_DEFAULT_EMAIL=admin@example.com": Sets the default email address for the pgAdmin user.

-e "PGADMIN_DEFAULT_PASSWORD=admin": Sets the default password for the pgAdmin user.

-d dpage/pgadmin4: Specifies the Docker image to use (in this case, the pgAdmin image).

To verify the creation of the PostgreSQL container, you can use the docker ps command.

Verifying pgadmin container creation

To find the IP address of the Docker host network, you can use the following command in the terminal:

docker inspect -f '{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' my_postgres

This command should return the IP address of the PostgreSQL container. Take note of this IP address, and use it as the Hostname/address when adding the PostgreSQL server to pgAdmin.

Forwarding Port 5051 in VS Code for pgAdmin Access #

Add the port 5051 in the VS Code Ports tab to establish a connection between the pgAdmin web interface running in a Docker container and your local machine.

To add the port 5051 in the VS Code Ports tab, follow these steps:

1. Click on the "Ports" tab.
2. In the "Forwarded Ports" section, click on the "+" button to add a new forwarded port.
3. Enter "5051" as the port.
4. Save the configuration.

Adding port 5051 in vs code

Once you have added the port, you can access the pgAdmin web interface by navigating to http://localhost:5051 in your web browser. This will open the pgAdmin interface, where you can log in and manage your PostgreSQL database.

pgAdmin Login

Log in using the email address and password specified in the docker run command (in this case, admin@example.com and admin).

Add the PostgreSQL Server to pgAdmin #

After logging in to pgAdmin, you need to add the PostgreSQL server to manage it. Follow these steps:

• Click on Add New Server in the Quick Links pane.
• In the General tab, enter a name for the server (e.g., "My PostgreSQL Server").
• Switch to the Connection tab and enter the following details:
  • Hostname/address: <IP_ADDRESS> (Replace <IP_ADDRESS> with the IP address of the PostgreSQL container obtained using the docker inspect command)
  • Port: 5432 (The port number mapped to the PostgreSQL container)
  • Maintenance database: postgres
  • Username: postgres
  • Password: mysecretpassword (The password specified when creating the PostgreSQL container)
• Click Save to add the server.

pgAdmin add server

Once you have added the PostgreSQL server in pgAdmin, you have the flexibility to perform various operations, including querying the initially created test database using a Python script or creating new databases, tables, and more. Here are the steps you can follow:

1. In the left pane, expand the Servers group and locate the PostgreSQL server you added.
2. Expand the server to view the available databases.
3. To create a new database, right-click on the Databases group under the PostgreSQL server and select Create > Database. Provide the desired name for the new database and configure any additional settings as needed.
4. To create new tables within a database, expand the desired database, navigate to Schemas > Tables and right-click on the Tables group, and select Create > Table. Define the table structure, columns, and any constraints required.
5. You can also perform other operations such as creating views, indexes, and executing SQL queries directly within pgAdmin.

pgAdmin explore and querying

By following this tutorial, you have successfully set up a PostgreSQL container using Docker and interacted with it using either a Python script or pgAdmin in a DevZero environment or your local environment. You can now use this setup to develop and test your applications that require a PostgreSQL database.

Vishal Pallerla

Developer Advocate, DevZero