Create a Containerized PostgreSQL Server in Windows

This article will guide you through the process of setting up your own PostgreSQL instance on Windows using Docker.

Posted by Jason August 11, 2024

PostgreSQL is a powerful, open-source relational database system. Docker is a platform that allows you to run applications in isolated environments called containers. By using Docker, you can easily set up and manage PostgreSQL on your Windows machine without worrying about the underlying dependencies and configurations.

This tutorial will guide you through the steps necessary to start running PostgreSQL as a containerized instance on your computer.

Prerequisites

Before starting, make sure you have the following installed on your Windows machine:

Docker Desktop: Docker Desktop allows you to run Docker containers on Windows. After running the installation file you will be prompted to restart your machine.

Download Docker Desktop

Windows Subsystem for Linux (WSL) 2: Docker Desktop requires WSL 2 to run Linux containers on Windows. You can now install everything you need to run WSL with a single command. Open PowerShell or Windows Command Prompt in administrator mode by right-clicking and selecting “Run as administrator”, enter the wsl –install command, then restart your machine.

wsl --install

Set Up a PostgreSQL Container

Open a Terminal: You can use either PowerShell, Command Prompt, or a terminal from your WSL 2 distribution.

Pull the PostgreSQL Docker Image: Run the following command to pull the official PostgreSQL Docker image. This command downloads the latest PostgreSQL image from Docker Hub.

docker pull postgres

Create a Docker Network (Optional): You can create a dedicated Docker network for your PostgreSQL container if you plan to connect multiple containers.

docker network create pg-network

Run the PostgreSQL Container: Use the following command to start a new PostgreSQL container.

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

Explanation of the flags:

  • –name postgres-container: Assigns a name to the container.
  • -e POSTGRES_PASSWORD=mysecretpassword: Sets the password for the default postgres user.
  • -d: Runs the container in detached mode (in the background).
  • -p 5432:5432: Maps port 5432 on your host to port 5432 in the container.
  • postgres: Specifies the image to use.

Verify the Container is Running: Run the following command to check if the PostgreSQL container is running. You should see an entry for postgres-container in the list of running containers.

docker ps

Connect to the PostgreSQL Database

Install a PostgreSQL Client: You can use any PostgreSQL client to connect to your database. One popular choice is pgAdmin.

Connect Using pgAdmin:

  • Open pgAdmin and create a new connection.
  • In the connection details, use the following information:
    • Host: localhost
    • Port: 5432
    • Username: postgres
    • Password: mysecretpassword

Connect Using Command Line:

Alternatively, you can connect to PostgreSQL using the psql command-line tool. First, you need to run the tool inside the container. Enter the password mysecretpassword when prompted.

psql -U postgres

Create a New Database

After you have logged in as the root postgres user you can create a new database using the following command.

CREATE DATABASE your_database_name;

Create a New User

In order to create a new user (which we will assign to your database shortly) run the create user command.

CREATE USER your_username WITH PASSWORD 'your_password';

Grant Access to the Database

Give your user the correct privileges to perform SQL operations on your database. For now we will just grant all privileges, in a production database you would want to give only the minimum access necessary.

GRANT ALL PRIVILEGES ON DATABASE your_database_name TO your_username;

Connect to the New Database

Run the connect command to connect to your newly created database.

\c your_database_name;

Create a Schema within the Database

You may want to logically organize your tables, users, index, and so forth. A PostgreSQL schema is used for this purpose. Run the following command to create a new schema.

CREATE SCHEMA your_schema_name;

Grant Access to your Schema

We will need to give your user access to run commands against the schema.

GRANT ALL PRIVILEGES ON SCHEMA your_schema_name TO your_username;

Grant Schema Access to all Future Tables

In order prevent having to grant access to the schema ever time you create a new table, you can run the following alter command.

ALTER DEFAULT PRIVILEGES IN SCHEMA your_schema_name GRANT ALL ON TABLES TO your_username;

Managing the PostgreSQL Container

Stopping the Container: To stop the running container, use the following command.

docker stop postgres-container

Starting the Container: To start a stopped container, use the following command.

docker start postgres-container

Removing the Container: To remove the container when it’s no longer needed, run this command.

docker rm -f postgres-container

Troubleshooting Common Issues

Container Not Starting: If the container fails to start, check the logs. The logs can help identify issues such as incorrect environment variables or port conflicts.

docker logs postgres-container

Port 5432 Already in Use: If port 5432 is already in use, you can map PostgreSQL to a different port by changing -p 5432:5432 to something like -p 5433:5432.

Conclusion

You’ve successfully set up a PostgreSQL database on Windows using Docker. This setup allows you to manage your PostgreSQL instance efficiently while taking advantage of Docker’s containerization benefits. You can now use this PostgreSQL database for development, testing, or any other purpose you need.

Tags:

Table of Contents

→ Index