Skip to main content

PostgreSQL Installation Guide

This guide provides instructions for installing PostgreSQL and pgAdmin, both using native package managers and Docker.

Installing pgAdmin​

Using apt (Debian/Ubuntu)​

These instructions install pgAdmin 4, the web-based administration tool.

sudo curl https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo apt-key add
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'
sudo apt install pgadmin4
sudo /usr/pgadmin4/bin/setup-web.sh  # This configures pgAdmin to run as a web application. Follow the prompts.

Explanation:

  1. curl ... | sudo apt-key add: Downloads the pgAdmin public key and adds it to your system's trusted keys. This allows apt to verify the authenticity of the pgAdmin packages.

  2. echo ... > /etc/apt/sources.list.d/pgadmin4.list: Adds the pgAdmin repository to your system's list of software sources. $(lsb_release -cs) dynamically determines your Debian/Ubuntu release codename (e.g., focal, jammy). apt update refreshes the package lists using the information from this new file.

  3. sudo apt install pgadmin4: Installs the pgAdmin 4 package and its dependencies.

  4. sudo /usr/pgadmin4/bin/setup-web.sh: Runs a script to configure pgAdmin to run as a web application. This script will ask you to provide an email address and password for the initial pgAdmin user.

Installing pgAdmin with Docker​

This method uses Docker to run pgAdmin in a container, providing a self-contained environment.

docker pull dpage/pgadmin4

Explanation:

  • docker pull dpage/pgadmin4: Downloads the official pgAdmin 4 Docker image from Docker Hub.

Installing PostgreSQL with Docker​

This is a quick way to get a PostgreSQL database up and running.

docker pull postgres

Explanation:

  • docker pull postgres: Downloads the official PostgreSQL Docker image from Docker Hub.

Using Docker Compose provides coordinated configuration of your PostgreSQL and PgAdmin containers. Create a docker-compose.yml file with the following content:

version: "3.9"
services:
  db:
    image: postgres:latest
    restart: always
    ports:
      - "5432:5432"
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: password
      POSTGRES_DB: mydatabase
    volumes:
      - db_data:/var/lib/postgresql/data

  pgadmin:
    image: dpage/pgadmin4:latest
    restart: always
    ports:
      - "8080:80" # Access pgAdmin via http://localhost:8080
    environment:
      PGADMIN_DEFAULT_EMAIL: [email protected] # Change this
      PGADMIN_DEFAULT_PASSWORD: password # Change this!
    depends_on:
      - db
    volumes:
      - pgadmin_data:/var/lib/pgadmin

volumes:
  db_data:
  pgadmin_data:

Then to launch:

docker-compose up -d

Explanation:

  1. version: "3.9": Specifies the Docker Compose file version.

  2. services:: Defines the services that will be run. In this case, db (PostgreSQL) and pgadmin.

  3. db:: Configuration for the PostgreSQL service:

    • image: postgres:latest: Specifies the image to use.
    • restart: always: Restarts the container automatically if it stops.
    • ports: - "5432:5432": Maps port 5432 on the host to port 5432 in the container (the standard Postgres port).
    • environment:: Sets environment variables for the PostgreSQL container:
      • POSTGRES_USER: Sets the default username.
      • POSTGRES_PASSWORD: Sets the password for the default user. Important: Change this in a real environment.
      • POSTGRES_DB: Sets the default database name.
    • volumes: Persists data even if the container is stopped or removed.

  4. pgadmin:: Configuration for the pgAdmin service:

    • image: dpage/pgadmin4:latest: Specifies the image to use.
    • restart: always: Restarts the container automatically if it stops.
    • ports: - "8080:80": Maps port 8080 on the host to port 80 in the container (the standard HTTP port). Access pgAdmin at http://localhost:8080.
    • environment:: Sets environment variables for the pgAdmin container:
      • PGADMIN_DEFAULT_EMAIL: Sets the default email address for the pgAdmin user (used for initial login). Important: Change this!
      • PGADMIN_DEFAULT_PASSWORD: Sets the default email password for the pgAdmin user. Important: Change this!
    • depends_on: - db: Ensure that the database start first.
      • volumes: Persist the application configuration

  5. volumes:: Defines named volumes for data persistence.

  • Connecting pgAdmin to the PostgreSQL Database:

    • Open pgAdmin from your browser.
    • Use the PGADMIN_DEFAULT_EMAIL and PGADMIN_DEFAULT_PASSWORD that were set in the docker-compose.yaml. Change these credentials immediately after logging in!
    • Add a new server connection.
    • Use db as the hostname when adding the server config.
    • User postgres as the User name, and password as username and password.

While Docker Compose is preferred, here are the individual commands if you want that.

Running Container pgAdmin (Standalone)​

docker run --name my-pgadmin -p 8080:80 \
  -e '[email protected]' \
  -e 'PGADMIN_DEFAULT_PASSWORD=password' \
  -d dpage/pgadmin4

Important notes about the above command:

  • It's recommended to map to port 8080 because 5432 may be in use if postgres is also running locally.
  • Ensure you change the default email and password IMMEDIATELY after your first login.
  • This runs pgAdmin only. You'll need to have a PostgreSQL server running elsewhere (e.g., locally or on another container) to connect to.
  • Access pgAdmin at: http://localhost:8080.

Accessing the PostgreSQL CLI inside a Docker Container​

These commands let you work with the PostgreSQL command-line interface (psql) within the Docker container.

Accessing the Container's Shell​

docker exec -it <container_name> bash

Replace <container_name> with the actual name or ID of your PostgreSQL container. You can find the container name using docker ps. If using docker-compose, the container name is [project_name]_db_1.

Accessing as the postgres User​

Once inside the container's shell:

su - postgres

This switches the current user to the postgres user, which has superuser privileges within the PostgreSQL database.

Accessing PostgreSQL using psql​

After switching to the postgres user:

psql

This command launches the psql interactive terminal, allowing you to execute SQL commands directly. By default, it connects to the postgres database.

Listing All Databases​

While connected to psql:

SELECT datname FROM pg_database;

This SQL query retrieves a list of all databases within the PostgreSQL instance.

Important Considerations:

  • Security: The default usernames and passwords used in these examples are highly insecure. You must change them to strong, unique passwords in any production or development environment. Never expose default configurations to the internet.

  • Data Persistence: Without configuring volumes, data stored within the Docker containers will be lost when the containers are stopped or removed. Use Docker volumes to persist data.

  • Networking: When running pgAdmin and PostgreSQL in separate Docker containers, ensure they can communicate with each other. Docker Compose automatically handles networking between services. When not using Compose, you will need to create a Docker network and connect the containers to that network or expose the PostgreSQL port and connect using host port and IP. The Docker Compose example is preferrable.

  • Docker Compose: Docker Compose is the recommended way to manage multi-container applications like PostgreSQL and pgAdmin. It simplifies configuration, networking, and deployment.