PostgreSQL Lab: PostgreSQL 18 in Docker, psql, pgAdmin and Porting Bookstore

Version note: The examples in this post were run with PostgreSQL 18.7 (the official postgres:18 Docker image), psql 18, .NET 10, Npgsql 10.0.3 and Npgsql.EntityFrameworkCore.PostgreSQL 10.0.3.

Guess First

A teammate runs PostgreSQL 17 in the development environment of Bookstore, the online bookstore used as the shared example across this site's series. The volume is mounted where every example on the internet has put it for years: /var/lib/postgresql/data. It holds a few tables and some test data. One day they bump the image tag to 18 and restart the container with the same volume:

# Yesterday: PostgreSQL 17, data in this volume
docker run -d --name bookstore-pg -e POSTGRES_PASSWORD=$env:PG_SUPERUSER_PASSWORD `
  -v bookstore-pgdata:/var/lib/postgresql/data postgres:17

# Today: same volume, image 18
docker rm -f bookstore-pg
docker run -d --name bookstore-pg -e POSTGRES_PASSWORD=$env:PG_SUPERUSER_PASSWORD `
  -v bookstore-pgdata:/var/lib/postgresql/data postgres:18

What happens? Does PostgreSQL 18 find the old data and upgrade it automatically, start with an empty database, or not start at all?

docker logs bookstore-pg

Error: in 18+, these Docker images are configured to store database data in a
       format which is compatible with "pg_ctlcluster" (specifically, using
       major-version-specific directory names).  This better reflects how
       PostgreSQL itself works, and how upgrades are to be performed.

       See also https://github.com/docker-library/postgres/pull/1259

       Counter to that, there appears to be PostgreSQL data in:
         /var/lib/postgresql/data

       This is usually the result of upgrading the Docker image without
       upgrading the underlying database using "pg_upgrade" (which requires both
       versions).

The container never started and stopped with exit code 1. That is a good outcome: the image notices data at the old path and refuses to run. A major version change requires pg_upgrade or a dump and restore because the data file format changes; the Docker image won't do it for you. If you "fix" the error by mounting a new, empty volume at the new path, the container starts happily and your application sees an empty database. The data isn't lost, it is still in the old volume; it just feels that way at that moment. In this post we build a lab the right way: the image and the volume path, roles, the PostgreSQL version of the Bookstore schema, psql and the configuration files.

The postgres:18 Image: Volume Path, PGDATA and Password

With PostgreSQL 18 the layout of the official image changed. The volume the image declares is now /var/lib/postgresql, and the data lives in a version-specific subdirectory: PGDATA=/var/lib/postgresql/18/docker. The goal is to follow the layout of the Debian packages and to make major upgrades easier. pg_upgrade --link needs the old and new data directories on the same file system; with 17/docker and 18/docker side by side under one mount, that becomes possible. For 17 and earlier the right place is still /var/lib/postgresql/data, so the path depends on the image version. We set up the lab in PowerShell like this:

# The superuser password is only used on first start, while the data directory is empty
$env:PG_SUPERUSER_PASSWORD = "<strong-password>"
docker volume create bookstore-pgdata
docker run -d --name bookstore-pg `
  -e POSTGRES_PASSWORD=$env:PG_SUPERUSER_PASSWORD `
  -p 5432:5432 `
  -v bookstore-pgdata:/var/lib/postgresql `
  -v "${PWD}\db:/lab:ro" `
  postgres:18

docker logs bookstore-pg

The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are enabled.
fixing permissions on existing directory /var/lib/postgresql/18/docker ... ok
...
initdb: warning: enabling "trust" authentication for local connections
...
PostgreSQL init process complete; ready for start up.
LOG:  listening on IPv4 address "0.0.0.0", port 5432
LOG:  database system is ready to accept connections

Three lines in the log matter. The first is Data page checksums are enabled: from PostgreSQL 18 on, initdb turns data checksums on by default, so reading a page that got corrupted on disk raises an error instead of silently returning wrong data. The second is the locale: the cluster was created with en_US.utf8; Turkish sorting and the letter İ are the subject of post 03. The third is the trust warning: connections made from inside the container don't ask for a password. We'll see what that means when we look at pg_hba.conf below.

The second -v mounts the repository's db folder read-only into the container. We run the schema and experiment scripts from /lab/... with docker exec, so most readers on Windows don't need to install psql separately.

Choosing the image tag is a decision too. postgres:latest changes major version one day and you end up exactly in the "Guess First" situation. postgres:18 follows the latest minor release of 18; since minor releases only bring bug and security fixes, that is the right choice for most environments. If you want environments to be identical, pin a full version such as postgres:18.7 and update deliberately. On the password side, POSTGRES_PASSWORD is only read while the data directory is empty, on first start; changing it later has no effect. In production-like environments it is better to pass the password from a Docker secret file with POSTGRES_PASSWORD_FILE.

Separating Roles: Owner, Application and Superuser

The postgres role the image creates is a superuser. Like sa in SQL Server it can do everything: read every table, change every role, run commands on the server with COPY ... FROM PROGRAM. Letting the application connect with this role means a SQL injection bug in the application spreads to the whole database server. In the lab we use three roles from the start: postgres for administration, bookstore_owner which owns the schema and runs migrations, and bookstore_app which the application connects with. PostgreSQL has no separate login and user; both are roles, and the LOGIN attribute is enough to connect.

CREATE ROLE bookstore_owner LOGIN;
CREATE ROLE bookstore_app LOGIN;
\password bookstore_owner
\password bookstore_app

CREATE DATABASE bookstore OWNER bookstore_owner;
REVOKE ALL ON DATABASE bookstore FROM PUBLIC;
GRANT CONNECT, TEMPORARY ON DATABASE bookstore TO bookstore_app;

\connect bookstore
/* tables and sequences bookstore_owner creates later are open to the application role */
ALTER DEFAULT PRIVILEGES FOR ROLE bookstore_owner IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO bookstore_app;
ALTER DEFAULT PRIVILEGES FOR ROLE bookstore_owner IN SCHEMA public
    GRANT USAGE, SELECT ON SEQUENCES TO bookstore_app;

docker exec -it bookstore-pg psql -U postgres -f /lab/00-roles.sql

\password asks for the password twice without echoing it and sends the server a SCRAM-SHA-256 hash; the password never ends up in the script or the command history. In SQL Server a schema-level grant such as GRANT SELECT ON SCHEMA::dbo also covers tables added to the schema later. In PostgreSQL GRANT ... ON ALL TABLES IN SCHEMA only covers the tables that exist at that moment; for future ones you need ALTER DEFAULT PRIVILEGES. With it, whenever the owner creates a new table, the application role automatically gets read and write rights (SELECT, INSERT, UPDATE, DELETE) on it, and nobody has to write a GRANT after every migration. Since PostgreSQL 15 not everyone may create tables in the public schema; the schema belongs to the database owner. That's why the application role can't create a table:

docker exec -it bookstore-pg psql -U bookstore_app -d bookstore -c "CREATE TABLE wishlist (book_id int)"

ERROR:  permission denied for schema public
LINE 1: CREATE TABLE wishlist (book_id int);
                     ^

That is exactly what we want: the application reads and writes data but can't change the schema. The details of the permission model, such as schema-level privileges, predefined roles like pg_read_all_data and row-level security, come in post 14.

Porting the Bookstore Schema to PostgreSQL

We ported the Bookstore schema from the SQL Server Derinlemesine series to PostgreSQL: the same eight tables, the same row counts, the same deterministic data generation. There is no random(); every value comes from a hash derived from the row number, so two loads produce identical data and you see exactly the numbers in the posts. Two of the tables:

CREATE TABLE books
(
    book_id        int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    isbn           varchar(13)   NOT NULL,
    title          text          NOT NULL,
    author_id      int           NOT NULL,
    category       text          NOT NULL,
    price          numeric(10,2) NOT NULL,
    published_year smallint      NOT NULL
);

CREATE TABLE orders
(
    order_id     int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_number varchar(20)   NOT NULL,
    customer_id  int           NOT NULL,
    status       text          NOT NULL,
    created_at   timestamptz   NOT NULL,
    total_amount numeric(12,2) NOT NULL,
    currency     char(3)       NOT NULL
);

These are the type mapping decisions we made:

The last row is the most important one; we'll see why in the pitfalls section. For a general summary of dialect differences see the "One Query, Four Dialects" post of the SQL Temelleri series. Choosing GENERATED ALWAYS is deliberate as well: it stops the application from supplying id values by hand.

INSERT INTO books (book_id, isbn, title, author_id, category, price, published_year)
VALUES (40001, '9780000040001', 'Dune #40001', 1, 'SciFi', 19.90, 1965);

ERROR:  cannot insert a non-DEFAULT value into column "book_id"
DETAIL:  Column "book_id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

That's why the loading script uses OVERRIDING SYSTEM VALUE and at the end moves the identity sequences to the largest id with setval; together they are the counterpart of SQL Server's SET IDENTITY_INSERT ON and DBCC CHECKIDENT. We run the script as the owner role:

docker exec -it bookstore-pg psql -U bookstore_owner -d bookstore -f /lab/schema.sql

 table_name  |  rows   | total_size
-------------+---------+------------
 authors     |    5000 | 432 kB
 books       |   40000 | 6144 kB
 customers   |  100000 | 16 MB
 order_items | 2500496 | 234 MB
 orders      | 1000000 | 141 MB
 payments    | 1000000 | 118 MB
 shipments   |  954755 | 111 MB
 stock_items |   40000 | 2968 kB
(8 rows)

The load took 11 seconds in this lab. About 630 MB in total; one million orders and 2.5 million order lines are enough for realistic plans in the index and EXPLAIN experiments later. The script is idempotent: it drops and recreates the tables, so running the same command again brings the lab back to a clean state after an experiment.

Everyday psql Commands

psql is the counterpart of sqlcmd on the SQL Server side, and a lot more. Meta-commands starting with a backslash don't go to the server; psql runs them itself, and most are ready-made queries over the catalog. Let's connect as the application role and look at the most common ones:

docker exec -it bookstore-pg psql -U bookstore_app -d bookstore

\conninfo
\dt
\d+ books

           Connection Information
      Parameter       |        Value
----------------------+---------------------
 Database             | bookstore
 Client User          | bookstore_app
 Socket Directory     | /var/run/postgresql
 Server Port          | 5432
 Password Used        | false
 Backend PID          | 135
 SSL Connection       | false
 Superuser            | off

                 List of tables
 Schema |    Name     | Type  |      Owner
--------+-------------+-------+-----------------
 public | authors     | table | bookstore_owner
 public | books       | table | bookstore_owner
 public | customers   | table | bookstore_owner
 public | order_items | table | bookstore_owner
 public | orders      | table | bookstore_owner
 public | payments    | table | bookstore_owner
 public | shipments   | table | bookstore_owner
 public | stock_items | table | bookstore_owner
(8 rows)

                         Table "public.books"
     Column     |         Type          | Nullable |           Default
----------------+-----------------------+----------+------------------------------
 book_id        | integer               | not null | generated always as identity
 isbn           | character varying(13) | not null |
 title          | text                  | not null |
 author_id      | integer               | not null |
 category       | text                  | not null |
 price          | numeric(10,2)         | not null |
 published_year | smallint              | not null |
Indexes:
    "books_pkey" PRIMARY KEY, btree (book_id)
    "ix_books_author_id" btree (author_id)
    "ux_books_isbn" UNIQUE, btree (isbn)
Foreign-key constraints:
    "fk_books_authors" FOREIGN KEY (author_id) REFERENCES authors(author_id)
Referenced by:
    TABLE "order_items" CONSTRAINT "fk_order_items_books" FOREIGN KEY (book_id) REFERENCES books(book_id)
    TABLE "stock_items" CONSTRAINT "fk_stock_items_books" FOREIGN KEY (book_id) REFERENCES books(book_id)
Not-null constraints:
    "books_book_id_not_null" NOT NULL "book_id"
    "books_isbn_not_null" NOT NULL "isbn"
    ...
Access method: heap

We shortened the output (\d+ also shows storage and statistics columns). Two details stand out. Password Used is false: we connected over the Unix socket from inside the container and the trust rule didn't ask for a password. The Not-null constraints section is new in PostgreSQL 18; NOT NULL is now kept in the catalog as a real, named constraint. \d+ is a more readable sp_help: indexes, foreign keys and references from other tables in one place.

Other everyday commands: \l lists databases, \dn schemas, \du roles. \x shows wide rows vertically, and \x auto does it only when a row doesn't fit on screen. \timing on prints the duration of every query. \e opens the last query in an editor and runs it when you save and exit; editors are limited inside the container, so this one is more comfortable with psql installed on your own machine. \gset stores a query result in psql variables, and \watch 2 repeats the last query every two seconds; we used \gset for the WAL measurement in post 01. To avoid typing the same settings for every connection we prepared a psqlrc file and put it in the repository's db folder:

\set QUIET 1
\pset null '(null)'
\set COMP_KEYWORD_CASE upper
\set HISTCONTROL ignoredups
\x auto
\timing on
\set PROMPT1 '%n@%/%R%# '
\unset QUIET

# psql reads ~/.psqlrc by default; PSQLRC points it at our file
docker exec -it -e PSQLRC=/lab/psqlrc bookstore-pg psql -U bookstore_app -d bookstore

bookstore_app@bookstore=> SELECT book_id, title, price FROM books WHERE book_id <= 2;
 book_id |     title     | price
---------+---------------+-------
       1 | Emma #1       | 32.77
       2 | The Hobbit #2 | 39.00
(2 rows)

Time: 0.578 ms

Telling NULL apart from an empty string ((null)), completing keywords in upper case and seeing in the prompt which role is connected to which database all reduce the risk of mistakes, especially when you are connected to the lab and production at the same time.

postgresql.conf, pg_hba.conf and ALTER SYSTEM

PostgreSQL's settings live in two main files. postgresql.conf holds server parameters (memory, logging, connection count), and pg_hba.conf holds who may connect from where, to which database, and with which method. In SQL Server these jobs are spread across sp_configure and login and endpoint settings. You can see where the files are and the parsed contents of pg_hba.conf from SQL:

SHOW hba_file;
SELECT line_number, type, database, user_name, address, auth_method FROM pg_hba_file_rules;

                 hba_file
-------------------------------------------
 /var/lib/postgresql/18/docker/pg_hba.conf

 line_number | type  |   database    | user_name |  address  |  auth_method
-------------+-------+---------------+-----------+-----------+---------------
         117 | local | {all}         | {all}     |           | trust
         119 | host  | {all}         | {all}     | 127.0.0.1 | trust
         121 | host  | {all}         | {all}     | ::1       | trust
         124 | local | {replication} | {all}     |           | trust
         125 | host  | {replication} | {all}     | 127.0.0.1 | trust
         126 | host  | {replication} | {all}     | ::1       | trust
         128 | host  | {all}         | {all}     | all       | scram-sha-256
(7 rows)

Rules are read top to bottom and the first match wins. Connections from inside the container, over the Unix socket and 127.0.0.1, are trust, meaning no password. A connection from outside, for example from your application on Windows through port 5432, falls through to the last line and must authenticate with a password using SCRAM-SHA-256. That's reasonable for a lab; in production there should be no trust line. Details of pg_hba.conf and encrypted connections come in post 14.

Instead of editing the file by hand you can change parameters with ALTER SYSTEM. The value is written to postgresql.auto.conf, which is read after postgresql.conf and therefore overrides it:

ALTER SYSTEM SET log_min_duration_statement = '250ms';
ALTER SYSTEM SET shared_buffers = '512MB';
SELECT pg_reload_conf();

SELECT name, setting, unit, pending_restart FROM pg_settings
WHERE name IN ('log_min_duration_statement', 'shared_buffers');

            name            | setting | unit | pending_restart
----------------------------+---------+------+-----------------
 log_min_duration_statement | 250     | ms   | f
 shared_buffers             | 16384   | 8kB  | t

The two parameters behave differently. log_min_duration_statement took effect immediately on reload: every query longer than 250 ms now goes to the log. shared_buffers still has the old value (16384 × 8 kB = 128MB) and says pending_restart = t; shared memory is allocated only when the server starts, so it needs a restart. The context column of pg_settings tells you when each parameter can be changed (postmaster restart, sighup reload, user within a session). One more note: pg_reload_conf() only sends a signal and returns immediately; if you read the setting right after it in the same command line you may still see the old value. After docker restart bookstore-pg, SHOW shared_buffers returned 512MB. So that the lab runs with defaults in the next posts, we reverted the settings with ALTER SYSTEM RESET ALL after the experiment.

Connection String and GUI Tools

On the .NET side the Npgsql connection string looks like SQL Server's but the keys differ:

Host=localhost;Port=5432;Database=bookstore;Username=bookstore_app;Password=<app-password>

Host instead of Server, Username instead of User Id; as a counterpart of Windows authentication there is Kerberos/GSSAPI support, but in the lab we use passwords. Keep the password out of the repository, in user secrets or an environment variable during development. To run the experiments from post 01 against the lab with the application role, just set the BOOKSTORE_PG environment variable to this string.

Three graphical tools stand out. pgAdmin 4 is the PostgreSQL community's official administration tool (9.19 at the time of writing); it runs as a desktop application or as a container and has PostgreSQL-specific screens such as plan visualization and role management. Microsoft's PostgreSQL extension for VS Code became generally available in November 2025; if you use the mssql extension in VS Code on the SQL Server side, this is the most familiar experience. DBeaver is a good general tool for people who work with several databases at once. Whichever you pick, don't set up the connection with bookstore_app; use a separate read role or the owner, and tell environments apart by color or name when connecting to production.

In Production

This lab is a development environment. On the way to production also consider the following: pin the image tag at least to the major version and apply minor updates on a schedule; back up the data separately from the volume (a volume backup is not the same as a consistent database backup; pg_dump and pg_basebackup come in post 14). Size shared_buffers, work_mem and max_connections for the memory limit you give the container; PostgreSQL isn't told about the container's memory limit, and when the limit is exceeded the kernel kills a process. Don't leave trust in pg_hba.conf, and allow the application role only into the database it needs. Running PostgreSQL on Kubernetes is a separate topic; in the storage and stateful applications post of the Kubernetes series we discussed whether to run the database inside or outside the cluster.

Common Pitfalls

Quoted names. What does EF Core produce on PostgreSQL from a model written for SQL Server? This is the script the Npgsql provider generates for Book and Author entities with default settings:

public sealed class BookstoreContext(DbContextOptions<BookstoreContext> options) : DbContext(options)
{
    public DbSet<Book> Books => Set<Book>();
    public DbSet<Author> Authors => Set<Author>();
}

DbContextOptions<BookstoreContext> options = new DbContextOptionsBuilder<BookstoreContext>()
    .UseNpgsql(connectionString)
    .Options;
using BookstoreContext db = new(options);
Console.WriteLine(db.Database.GenerateCreateScript());

CREATE TABLE "Books" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    "Isbn" text NOT NULL,
    "Title" text NOT NULL,
    "Price" numeric NOT NULL,
    "PublishedAt" timestamp with time zone NOT NULL,
    "AuthorId" integer NOT NULL,
    CONSTRAINT "PK_Books" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_Books_Authors_AuthorId" FOREIGN KEY ("AuthorId") REFERENCES "Authors" ("Id") ON DELETE CASCADE
);

Every name is in double quotes and in PascalCase. PostgreSQL folds unquoted names to lower case and keeps quoted names as they are, so "Books" and books are two different tables. See what happens when we create this table next to the lab's books:

SELECT count(*) FROM Books;
SELECT count(*) FROM "Books";
SELECT "Title" FROM Books LIMIT 1;

 count
-------
 40000

 count
-------
     0

ERROR:  column "Title" does not exist
LINE 1: SELECT "Title" FROM Books LIMIT 1;
               ^
HINT:  Perhaps you meant to reference the column "books.title".

The unquoted Books silently went to books and returned 40,000, while EF Core's table is empty. Forgetting the quotes around "Books" in hand-written reporting SQL means reading from the wrong table without even getting an error. Also, the Price column came out as numeric without precision (the model doesn't specify one) and DateTime was mapped to timestamp with time zone. We cover switching names to snake_case (EFCore.NamingConventions) and Npgsql's type mapping in posts 11 and 12; this is why the Bookstore schema uses snake_case from the start.

Connecting the application as postgres. Tempting for a quick start, but a superuser also uses the 3 connections reserved for superusers that we saw in post 01; an application that leaks connections then also eats the slot an administrator needs in an emergency. A superuser also bypasses row-level security and can read files and run commands on the server. Use a separate role from day one.

A container without a volume. If you don't pass -v, the volume the image declares is created anonymously, and it is deleted together with the container by docker rm -v or --rm. Use a named volume even in the lab.

Changing POSTGRES_PASSWORD and assuming the password changed: if the data directory isn't empty, the image doesn't read this variable. Change the password with \password postgres.

A Small Experiment

★☆☆ Look inside the volume with docker exec bookstore-pg ls /var/lib/postgresql /var/lib/postgresql/18/docker. Where do postgresql.auto.conf, pg_hba.conf and pg_wal live?

★★☆ Install psql on your own machine (in the Windows installer of PostgreSQL, selecting only the "Command Line Tools" component is enough) and connect with psql -h localhost -U bookstore_app -d bookstore. Explain why it asks for a password this time by finding the matching line in the pg_hba_file_rules output.

★★★ Create a new role, bookstore_readonly, that can only SELECT and automatically gets rights on tables the owner creates later. Then rerun schema.sql (it drops and recreates the tables) and verify that this role can still read the new tables.

The lab is ready. In the next post we take a closer look at the types in these tables: text versus varchar, what timestamptz really stores, uuidv7(), enums, arrays, ranges and collation with Turkish characters.