Docker - How can run the psql command in the postgres container?

Docker

PostgreSQL

psql command

container

database management

Docker - How can run the psql command in the postgres container?

Master System Design with Codemia

Enhance your system design skills with over 120 practice problems, detailed solutions, and hands-on exercises.

Start Practicing Learn More

Introduction

To run psql inside a Docker PostgreSQL container, use docker exec -it <container> psql -U postgres. This opens an interactive PostgreSQL shell connected to the database running inside the container. You can also run one-off SQL commands, connect from outside the container via exposed ports, or use docker-compose exec for Compose-managed containers.

Start a PostgreSQL Container

bash

1docker run --name my-postgres \
2    -e POSTGRES_PASSWORD=mysecret \
3    -e POSTGRES_USER=admin \
4    -e POSTGRES_DB=myapp \
5    -p 5432:5432 \
6    -d postgres:16

--name my-postgres: gives the container a name for easy reference
-e POSTGRES_PASSWORD: required, sets the superuser password
-e POSTGRES_USER: creates a user (defaults to postgres if omitted)
-e POSTGRES_DB: creates a database (defaults to the username if omitted)
-p 5432:5432: maps container port to host for external access
-d: runs in detached mode

Connect with docker exec

bash

1# Interactive psql session
2docker exec -it my-postgres psql -U admin -d myapp
3
4# Using the default postgres user
5docker exec -it my-postgres psql -U postgres

-it: interactive mode with a pseudo-TTY (needed for the psql prompt)
psql -U admin: connect as user admin
-d myapp: connect to database myapp

You get a full psql prompt:

1myapp=# SELECT version();
2                          version
3------------------------------------------------------------
4 PostgreSQL 16.1 on x86_64-pc-linux-gnu, compiled by gcc...
5(1 row)
6
7myapp=# \dt
8         List of relations
9 Schema | Name | Type  | Owner
10--------+------+-------+-------
11(0 rows)
12
13myapp=# \q

Run a Single SQL Command

bash

1# Execute SQL inline and exit
2docker exec -it my-postgres psql -U admin -d myapp -c "SELECT NOW();"
3
4# Multiple commands
5docker exec -it my-postgres psql -U admin -d myapp -c "
6    CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT);
7    INSERT INTO users (name) VALUES ('Alice'), ('Bob');
8    SELECT * FROM users;
9"
10
11# Run a SQL file
12docker exec -i my-postgres psql -U admin -d myapp < setup.sql

Note: Use -i (not -it) when piping input from a file. The -t flag expects a terminal and conflicts with piped input.

Docker Compose

yaml

1# docker-compose.yml
2services:
3  db:
4    image: postgres:16
5    environment:
6      POSTGRES_USER: admin
7      POSTGRES_PASSWORD: mysecret
8      POSTGRES_DB: myapp
9    ports:
10      - "5432:5432"
11    volumes:
12      - pgdata:/var/lib/postgresql/data
13
14volumes:
15  pgdata:

bash

1# Start the services
2docker compose up -d
3
4# Connect to psql
5docker compose exec db psql -U admin -d myapp
6
7# Run a command
8docker compose exec db psql -U admin -d myapp -c "SELECT 1;"

docker compose exec uses the service name (db) instead of the container name.

Connect from Host Machine

If the port is exposed (-p 5432:5432), connect directly from your host:

bash

1# Using psql installed on the host
2psql -h localhost -p 5432 -U admin -d myapp
3
4# Or with a connection string
5psql "postgresql://admin:mysecret@localhost:5432/myapp"

This requires psql to be installed on your host machine (via brew install postgresql on macOS or apt install postgresql-client on Ubuntu).

Common psql Commands

sql

1-- List databases
2\l
3
4-- Connect to a different database
5\c other_db
6
7-- List tables
8\dt
9
10-- Describe a table
11\d users
12
13-- List users/roles
14\du
15
16-- Run a SQL file
17\i /path/to/script.sql
18
19-- Enable expanded output (vertical format)
20\x
21
22-- Quit
23\q

Troubleshooting

bash

1# Check if the container is running
2docker ps | grep postgres
3
4# View container logs (useful if psql can't connect)
5docker logs my-postgres
6
7# Check if PostgreSQL is ready
8docker exec my-postgres pg_isready -U admin
9# /var/run/postgresql:5432 - accepting connections
10
11# If the container is not running, start it
12docker start my-postgres

Importing and Exporting Data

bash

1# Export a database dump
2docker exec my-postgres pg_dump -U admin myapp > backup.sql
3
4# Import a dump
5docker exec -i my-postgres psql -U admin -d myapp < backup.sql
6
7# Export in custom format (compressed)
8docker exec my-postgres pg_dump -U admin -Fc myapp > backup.dump
9
10# Restore custom format
11docker exec -i my-postgres pg_restore -U admin -d myapp < backup.dump

Initialization Scripts

Mount SQL files into /docker-entrypoint-initdb.d/ to run them on first start:

bash

1docker run --name my-postgres \
2    -e POSTGRES_PASSWORD=mysecret \
3    -v ./init.sql:/docker-entrypoint-initdb.d/init.sql \
4    -d postgres:16

Files in this directory run alphabetically on first container start (when the data directory is empty). Supports .sql, .sql.gz, and .sh files.

Common Pitfalls

Forgetting -it flags: docker exec my-postgres psql -U postgres without -it gives no interactive prompt. Always use -it for interactive sessions.
Wrong user: The default superuser is postgres, not root. If you set POSTGRES_USER, use that username with -U.
Container not ready: PostgreSQL takes a few seconds to initialize. If psql fails immediately after docker run, wait or use pg_isready to check.
Using -it with piped input: docker exec -it container psql < file.sql fails because -t conflicts with pipe input. Use -i only (no -t) for file input.
Port conflict: If port 5432 is already in use on the host (local PostgreSQL), map to a different port: -p 5433:5432, then connect with -p 5433.

Summary

Use docker exec -it <container> psql -U <user> for interactive sessions
Use -c "SQL" to run single commands without entering the psql shell
Use docker compose exec <service> psql for Compose-managed containers
Expose port 5432 to connect from the host with a local psql client
Mount init scripts to /docker-entrypoint-initdb.d/ for first-run setup
Use pg_dump and pg_restore via docker exec for backups