Using PostgreSQL with Docker and pgAdmin

Step 1: Set Up Docker

Create a docker-compose.yml file:

version: '3.8'

services:
  db:  # service 1st
    image: postgres:latest
    container_name: postgres_db  # if not provided docker will automatically assign one
    environment:  # env variables used for the connection
      POSTGRES_USER: your_username 
      POSTGRES_PASSWORD: your_password
      POSTGRES_DB: your_database
    ports:  # you can change the port as well (5432 is default port of postgres)
      - "5432:5432"
    networks:
      - postgres_network

  pgadmin:  # service 2nd
    image: dpage/pgadmin4
    container_name: pgadmin
    environment:
      PGADMIN_DEFAULT_EMAIL: admin@admin.com
      PGADMIN_DEFAULT_PASSWORD: admin
    ports:
      - "8080:80"
    networks:
      - postgres_network

networks:
  postgres_network:

Step 2: Start Containers

Run the following command:

docker-compose up -d

If Error: Port Already Allocated

If you encounter an error like this:

ERROR: for pgadmin Cannot start service pgadmin: driver failed programming external connectivity on endpoint pgadmin: Bind for 0.0.0.0:8080 failed: port is already allocated

Fix:

1. Check for Running Services on Port 8080:

    sudo lsof -i :8080
   

2. Stop the Conflicting Process:

    sudo kill <PID>
   

   Replace <PID> with the actual process ID using port 8080.

Step 3: Access pgAdmin

Open your browser and go to http://localhost:8080. Log in with:

• Email: admin@admin.com

• Password: admin

Step 4: Connect to PostgreSQL

1. In pgAdmin, right-click on "Servers" > "Create" > "Server..."

2. Fill in the details:

   • Host: postgres_db

   • Port: 5432

   • Database: your_database

   • Username: your_username

   • Password: your_password

3. Click Save.

Step 5: Create a Table

1. Open the Query Tool in pgAdmin.

2. Run the following SQL command:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE
);

Step 6: Insert Data

Run this command in the Query Tool:

INSERT INTO users (username, email) VALUES ('shivam', 'shivam@example.com');

Step 7: View Data

Right-click on the users table > View/Edit Data > All Rows.

Conclusion

Remember to stop your containers when done:

docker-compose down

Thanks!!