PostgreSQL: running in Docker, psql and a first table

docker volume create shop-pgdata

docker run -d --rm --name shop-db \
  -e POSTGRES_PASSWORD=change-me-locally \
  -p 127.0.0.1:5432:5432 \
  -v shop-pgdata:/var/lib/postgresql/data \
  postgres:17-alpine

docker logs shop-db

$ docker logs shop-db 2>&1 | tail -1
2026-10-12 01:02:45.235 UTC [1] LOG:  database system is ready to accept connections

$ docker run -d --rm --name pg-wrong -e POSTGRES_PASSWORD=change-me-locally -v pg-wrong:/var/lib/postgresql postgres:17-alpine
$ docker exec pg-wrong psql -U postgres -c "CREATE DATABASE shop"
CREATE DATABASE
$ docker inspect pg-wrong --format '{{range .Mounts}}{{.Name}} -> {{.Destination}}{{println}}{{end}}'
pg-wrong -> /var/lib/postgresql
6ad9b1ca6430d46b5b43f74b3cc4757853465c4e675bff418b1636558222dc07 -> /var/lib/postgresql/data

$ docker stop pg-wrong
$ docker run -d --rm --name pg-wrong -e POSTGRES_PASSWORD=change-me-locally -v pg-wrong:/var/lib/postgresql postgres:17-alpine
$ docker exec pg-wrong psql -U postgres -c '\l shop'
                                          List of databases
 Name | Owner | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules | Access privileges
------+-------+----------+-----------------+---------+-------+--------+-----------+-------------------
(0 rows)

$ docker exec -it shop-db psql -U postgres
postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
postgres=# SELECT version();
                                         version
------------------------------------------------------------------------------------------
 PostgreSQL 17.11 on x86_64-pc-linux-musl, compiled by gcc (Alpine 15.2.0) 15.2.0, 64-bit
(1 row)

postgres=# \q

-- Run once by the postgres superuser
CREATE ROLE shop_app LOGIN PASSWORD 'change-me-app';
CREATE ROLE shop_readonly LOGIN PASSWORD 'change-me-readonly';

-- The database owner is the application user, not the superuser
CREATE DATABASE shop OWNER shop_app;

-- Run by the owner shop_app in the shop database
GRANT SELECT ON ALL TABLES IN SCHEMA public TO shop_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO shop_readonly;

docker cp db shop-db:/db

docker exec shop-db psql -U postgres -v ON_ERROR_STOP=1 -f /db/roles.sql

docker exec shop-db psql -U shop_app -d shop -v ON_ERROR_STOP=1 \
  -f /db/schema.sql -f /db/seed.sql -f /db/grants.sql

$ docker exec shop-db psql -U postgres -v ON_ERROR_STOP=1 -f /db/roles.sql
CREATE ROLE
CREATE ROLE
CREATE DATABASE
$ docker exec shop-db psql -U shop_app -d shop -v ON_ERROR_STOP=1 -f /db/schema.sql -f /db/seed.sql -f /db/grants.sql
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 3
INSERT 0 3
INSERT 0 3
INSERT 0 4
GRANT
ALTER DEFAULT PRIVILEGES
$ docker exec shop-db psql -U postgres -c '\l shop' -c '\du'
                                                List of databases
 Name |  Owner   | Encoding | Locale Provider |  Collate   |   Ctype    | Locale | ICU Rules | Access privileges
------+----------+----------+-----------------+------------+------------+--------+-----------+-------------------
 shop | shop_app | UTF8     | libc            | en_US.utf8 | en_US.utf8 |        |           |
(1 row)

                               List of roles
   Role name   |                         Attributes
---------------+------------------------------------------------------------
 postgres      | Superuser, Create role, Create DB, Replication, Bypass RLS
 shop_app      |
 shop_readonly | 

$ docker exec shop-db psql -U shop_readonly -d shop -c "SELECT name, price FROM products ORDER BY id"
            name            |  price
----------------------------+----------
 Espresso Blend 1 kg        | 12900.00
 Ethiopia Yirgacheffe 250 g |  5600.00
 Paper filters V60          |  2500.00
(3 rows)

$ docker exec shop-db psql -U shop_readonly -d shop -c "DELETE FROM orders"
ERROR:  permission denied for table orders
$ docker exec shop-db psql -U shop_readonly -d shop -c "CREATE TABLE notes (id int)"
ERROR:  permission denied for schema public
LINE 1: CREATE TABLE notes (id int)
                     ^
$ docker exec shop-db psql -U shop_app -d shop -c '\dn+ public'
                                       List of schemas
  Name  |       Owner       |           Access privileges            |      Description
--------+-------------------+----------------------------------------+------------------------
 public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
        |                   | =U/pg_database_owner                   |
(1 row)

shop=> SELECT 0.1::double precision + 0.2::double precision AS float_sum,
shop->        0.1::numeric + 0.2::numeric                   AS numeric_sum;
      float_sum      | numeric_sum
---------------------+-------------
 0.30000000000000004 |         0.3
(1 row)

shop=> SELECT 2147483647::integer + 1;
ERROR:  integer out of range
shop=> SELECT id, paid_at FROM orders WHERE id = 1;
 id |        paid_at
----+------------------------
  1 | 2026-09-02 05:15:00+00
(1 row)

shop=> SET TimeZone = 'Asia/Almaty';
SET
shop=> SELECT id, paid_at FROM orders WHERE id = 1;
 id |        paid_at
----+------------------------
  1 | 2026-09-02 10:15:00+05
(1 row)

shop=> SELECT name, attrs ->> 'roast' AS roast
shop-> FROM products
shop-> WHERE attrs ? 'roast';
            name            | roast
----------------------------+-------
 Espresso Blend 1 kg        | dark
 Ethiopia Yirgacheffe 250 g | light
(2 rows)

-- Coffee shop training schema: clients, products, orders, order items
CREATE TABLE clients (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       text NOT NULL CHECK (name <> ''),
  city       text,
  email      text UNIQUE,
  created_on date NOT NULL DEFAULT CURRENT_DATE
);

CREATE TABLE products (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name      text          NOT NULL,
  category  text          NOT NULL,
  price     numeric(10,2) NOT NULL CHECK (price > 0),
  weight_g  integer       CHECK (weight_g > 0),
  is_active boolean       NOT NULL DEFAULT true,
  attrs     jsonb         NOT NULL DEFAULT '{}'
);

CREATE TABLE orders (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  public_id  uuid NOT NULL UNIQUE DEFAULT gen_random_uuid(),
  client_id  bigint NOT NULL REFERENCES clients (id),
  ordered_on date NOT NULL DEFAULT CURRENT_DATE,
  status     text NOT NULL DEFAULT 'new'
             CHECK (status IN ('new', 'paid', 'shipped', 'cancelled')),
  promo_code text,
  paid_at    timestamptz
);

CREATE TABLE order_items (
  order_id   bigint        NOT NULL REFERENCES orders (id) ON DELETE CASCADE,
  product_id bigint        NOT NULL REFERENCES products (id),
  quantity   integer       NOT NULL CHECK (quantity > 0),
  price      numeric(10,2) NOT NULL CHECK (price > 0),
  PRIMARY KEY (order_id, product_id)
);

-- Fictional data: no real customers or contacts
INSERT INTO clients (name, city, email, created_on) VALUES
  ('Aigerim Seitova',  'Almaty', 'aigerim@example.com', '2026-06-02'),
  ('Daniyar Akhmetov', 'Astana', NULL,                  '2026-06-15'),
  ('Madina Omarova',   'Almaty', 'madina@example.com',  '2026-07-01');

INSERT INTO products (name, category, price, weight_g, attrs) VALUES
  ('Espresso Blend 1 kg',        'coffee', 12900.00, 1000, '{"roast": "dark"}'),
  ('Ethiopia Yirgacheffe 250 g', 'coffee',  5600.00,  250, '{"roast": "light"}'),
  ('Paper filters V60',          'gear',    2500.00, NULL, '{"pack": 100}');

INSERT INTO orders (client_id, ordered_on, status, paid_at) VALUES
  (1, '2026-09-02', 'shipped', '2026-09-02 10:15:00+05'),
  (2, '2026-09-05', 'paid',    '2026-09-05 18:40:00+05'),
  (1, '2026-09-14', 'new',     NULL);

INSERT INTO order_items (order_id, product_id, quantity, price) VALUES
  (1, 1, 1, 12900.00),
  (1, 3, 2,  2500.00),
  (2, 2, 2,  5600.00),
  (3, 2, 1,  5600.00);

shop=> \dt
            List of relations
 Schema |    Name     | Type  |  Owner
--------+-------------+-------+----------
 public | clients     | table | shop_app
 public | order_items | table | shop_app
 public | orders      | table | shop_app
 public | products    | table | shop_app
(4 rows)

shop=> \d orders
                                    Table "public.orders"
   Column   |           Type           | Collation | Nullable |           Default
------------+--------------------------+-----------+----------+------------------------------
 id         | bigint                   |           | not null | generated always as identity
 public_id  | uuid                     |           | not null | gen_random_uuid()
 client_id  | bigint                   |           | not null |
 ordered_on | date                     |           | not null | CURRENT_DATE
 status     | text                     |           | not null | 'new'::text
 promo_code | text                     |           |          |
 paid_at    | timestamp with time zone |           |          |
Indexes:
    "orders_pkey" PRIMARY KEY, btree (id)
    "orders_public_id_key" UNIQUE CONSTRAINT, btree (public_id)
Check constraints:
    "orders_status_check" CHECK (status = ANY (ARRAY['new'::text, 'paid'::text, 'shipped'::text, 'cancelled'::text]))
Foreign-key constraints:
    "orders_client_id_fkey" FOREIGN KEY (client_id) REFERENCES clients(id)
Referenced by:
    TABLE "order_items" CONSTRAINT "order_items_order_id_fkey" FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE

shop=> INSERT INTO clients (name) VALUES (NULL);
ERROR:  null value in column "name" of relation "clients" violates not-null constraint
DETAIL:  Failing row contains (4, null, null, null, 2026-10-12).
shop=> INSERT INTO clients (name) VALUES ('');
ERROR:  new row for relation "clients" violates check constraint "clients_name_check"
DETAIL:  Failing row contains (5, , null, null, 2026-10-12).
shop=> INSERT INTO clients (name, email) VALUES ('Duplicate', 'aigerim@example.com');
ERROR:  duplicate key value violates unique constraint "clients_email_key"
DETAIL:  Key (email)=(aigerim@example.com) already exists.
shop=> INSERT INTO products (name, category, price) VALUES ('Free sample', 'coffee', 0);
ERROR:  new row for relation "products" violates check constraint "products_price_check"
DETAIL:  Failing row contains (4, Free sample, coffee, 0.00, null, t, {}).
shop=> INSERT INTO orders (client_id, status) VALUES (1, 'done');
ERROR:  new row for relation "orders" violates check constraint "orders_status_check"
DETAIL:  Failing row contains (4, 782914a2-f487-46e0-a5e7-6655ab194e20, 1, 2026-10-12, done, null, null).
shop=> INSERT INTO orders (client_id) VALUES (999);
ERROR:  insert or update on table "orders" violates foreign key constraint "orders_client_id_fkey"
DETAIL:  Key (client_id)=(999) is not present in table "clients".
shop=> INSERT INTO clients (id, name) VALUES (100, 'Manual id');
ERROR:  cannot insert a non-DEFAULT value into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.
shop=> INSERT INTO clients (name) VALUES ('No email 1'), ('No email 2') RETURNING id, email;
 id | email
----+-------
  7 |
  8 |
(2 rows)

INSERT 0 2

shop=> INSERT INTO clients (name, city)
shop-> VALUES ('Timur Bekov', 'Shymkent')
shop-> RETURNING id, created_on;
 id | created_on
----+------------
  9 | 2026-10-12
(1 row)

INSERT 0 1
shop=> UPDATE products
shop-> SET price = price * 1.10
shop-> WHERE category = 'coffee'
shop-> RETURNING id, name, price;
 id |            name            |  price
----+----------------------------+----------
  1 | Espresso Blend 1 kg        | 14190.00
  2 | Ethiopia Yirgacheffe 250 g |  6160.00
(2 rows)

UPDATE 2
shop=> DELETE FROM orders
shop-> WHERE status = 'new'
shop-> RETURNING id, public_id;
 id |              public_id
----+--------------------------------------
  3 | 6138bdcb-be64-4598-a3e1-5c2c2ef80458
(1 row)

DELETE 1
shop=> SELECT count(*) AS items_left FROM order_items;
 items_left
------------
          3
(1 row)

$ docker stop shop-db
shop-db
$ docker run -d --rm --name shop-db -e POSTGRES_PASSWORD=change-me-locally -p 127.0.0.1:5432:5432 -v shop-pgdata:/var/lib/postgresql/data postgres:17-alpine
$ docker logs shop-db 2>&1 | head -3

PostgreSQL Database directory appears to contain a database; Skipping initialization

$ docker exec shop-db psql -U shop_app -d shop -c "SELECT count(*) AS clients FROM clients"
 clients
---------
       6
(1 row)

# 1. Custom-format dump (-Fc): compressed, read by pg_restore
docker exec shop-db pg_dump -U shop_app -d shop -Fc -f /tmp/shop.dump
docker cp shop-db:/tmp/shop.dump ./shop.dump

# 2. Check: restore into a separate empty database
docker exec shop-db psql -U postgres -c "CREATE DATABASE shop_restore OWNER shop_app"
docker exec shop-db pg_restore -U shop_app -d shop_restore /tmp/shop.dump

$ docker exec shop-db pg_restore -U shop_app -d shop_restore /tmp/shop.dump; echo "exit $?"
exit 0
$ docker exec shop-db psql -U shop_app -d shop -c "SELECT (SELECT count(*) FROM clients) AS clients, (SELECT count(*) FROM products) AS products, (SELECT count(*) FROM orders) AS orders, (SELECT count(*) FROM order_items) AS items"
 clients | products | orders | items
---------+----------+--------+-------
       6 |        3 |      2 |     3
(1 row)

$ docker exec shop-db psql -U shop_app -d shop_restore -c "SELECT (SELECT count(*) FROM clients) AS clients, (SELECT count(*) FROM products) AS products, (SELECT count(*) FROM orders) AS orders, (SELECT count(*) FROM order_items) AS items"
 clients | products | orders | items
---------+----------+--------+-------
       6 |        3 |      2 |     3
(1 row)

$ docker exec shop-db pg_dump -U shop_app -d shop -f /tmp/shop.sql
$ docker exec shop-db head -8 /tmp/shop.sql
--
-- PostgreSQL database dump
--

\restrict AuNO2dNJw5UDhjrML04TbDxP98WIJkamUGNY5xdp8sLuCz1X2zVIZqKR8umAdei

-- Dumped from database version 17.11
-- Dumped by pg_dump version 17.11

DATABASE_URL=postgresql://shop_app:change-me-app@localhost:5432/shop

import pg from "pg";

const client = new pg.Client({ connectionString: process.env.DATABASE_URL });
await client.connect();

const who = await client.query("SELECT current_user, current_database()");
console.log(who.rows[0]);

// $1 is sent separately from the query text — no string concatenation
const cheap = await client.query(
  "SELECT name, price FROM products WHERE price < $1 ORDER BY price",
  [7000],
);
console.log(cheap.rows);

await client.end();

$ npm install pg
$ node --env-file=.env check-db.js
{ current_user: 'shop_app', current_database: 'shop' }
[
  { name: 'Paper filters V60', price: '2500.00' },
  { name: 'Ethiopia Yirgacheffe 250 g', price: '6160.00' }
]