Services · Package

PostgreSQL

winter-service-postgres is a ready-made PostgreSQL service. The application brings the database up before everything else, waits until it answers, keeps checking it while it runs, restarts it after a crash or a hang, and stops it last — with a fast shutdown.

Package flytachi/winter-service-postgresClass PostgresVersions 14–18

What it is and why

What it is. A Postgres class implementing Service: it knows how to run PostgreSQL in the application’s container, how to tell it is ready, how to stop it and what it needs in the image.

The problem. A database of your own in the container means initdb on the first start, checking the version of the data, a role of the application’s own with its database and password, the right owners for the directories, pg_isready without false FATALs in the log, the right stop signal. Every project puts this together anew and trips over the same things.

The solution. All of it is in the package, tried on a real server. One line is left in the project.

The package runs the server. You connect to it as usual — through PDO or winter-ppa, see Connecting.

Installation

bash
composer require flytachi/winter-service-postgres

It needs PHP 8.4+, flytachi/winter-kernel 4.5+ and the flytachi/winter image (Alpine): the server itself is installed from apk, and call service docker adds it to the image.

Quick start

1. Declare it:

bootstrap.php
use Flytachi\Winter\Service\Postgres\Postgres;

protected static function components(Components $components): void
{
  $components->service(new Postgres(version: 18, database: 'app'));
}

2. Add it to the image:

bash
php call service docker

The Dockerfile gets apk add postgresql18 postgresql18-client postgresql18-contrib and the directories owned by winter. More in What the image needs.

The command does not touch compose — it prints what to add. Put into docker-compose.yml what suits you:

docker-compose.yml
services:
server:
  stop_grace_period: 70s
  volumes:
    - postgres-data:/var/lib/postgresql/data
volumes:
postgres-data:
  • stop_grace_period — always needed (why is below).
  • The volume — if the database must outlive a recreated container. For tests or a throwaway stand it can go. The data directory is already inside a volume of yours (data: '/var/lib/app/postgresql')? No separate volume is needed.

A stop needs 70 seconds

With PostgreSQL, the container needs up to 70 seconds to stop — stop_grace_period: 70s. Docker’s default is 10 seconds: after that it kills the container, and the database has no time to stop cleanly. On Kubernetes, set the same 70 seconds. Where the number comes from is in Stopping.

bash
docker compose up -d --build

3. Start it. On the first start the log says:

text
[2026-11-15 02:10:00] [INFO ] -sys- [1]: Application starting High: postgres
[2026-11-15 02:10:01] [INFO ] -postgres- [9]: initialised PostgreSQL 18 data in /var/lib/postgresql/data.
[2026-11-15 02:10:01] [INFO ] -postgres- [9]: created role winter.
[2026-11-15 02:10:02] [INFO ] -postgres- [9]: created database app.
[2026-11-15 02:10:02] [INFO ] -postgres- [9]: database system is ready to accept connections
[2026-11-15 02:10:02] [INFO ] -sys- [1]: postgres is up.
[2026-11-15 02:10:02] [INFO ] -sys- [1]: Application starting Normal: Http

PostgreSQL is up before everything else: HTTP takes its first request with the database already answering. The database’s lines go into the common log under the -postgres- channel — more in Log.

Parameters

The server

Parameter Default What it sets
version 18 The major version: the image installs postgresql{version}. Data of another version is not opened — see Changing the major version
data /var/lib/postgresql/data The data directory — keep it in a volume
socket /run/postgresql The directory of the Unix socket
host null null — the socket only, nothing outside the container connects. 127.0.0.1 — TCP inside the container too. 0.0.0.0 — other containers as well. A host needs a password
port 5432 The TCP port; the socket’s name carries it too (.s.PGSQL.5432)
user 'winter' The application’s role: a login without superuser rights, the owner of its database. Created when it is missing. See Roles
password null The application role’s password. Asked over TCP (scram-sha-256), never through the socket. Applied on every start: changed in the code — changed in the database, removed — removed
database null The application’s database; created when it is missing — on any start, not only the first. null — one named after the role (winter)
encoding 'UTF8' The cluster’s encoding
locale 'C' The cluster’s locale; Alpine’s musl has few others
timezone null The server’s time zone (timezone, log_timezone), set on every start: what now()::date and date_trunc('day', …) count days by. null — the application’s, TIME_ZONE (UTC without it). An IANA name, Asia/Tashkent; not through settings
contrib true Whether the image gets postgresql{version}-contrib — the pgcrypto, pg_trgm, citext extensions and the rest. See Extensions
settings [] Server settings, each passed as -c key=value: ['shared_buffers' => '256MB', 'max_connections' => 200]; true/false become on/off
args [] Further postgres arguments, as they are
shutdown Shutdown::Fast How it is asked to stop — see Stopping
logs true Whether PostgreSQL’s log goes into the application’s — see Log. false — as PostgreSQL writes it

The component

Parameter Default What it sets
name 'postgres' Its name in logs and app status; the volume in the compose hint is {name}-data
priority Priority::High Starts before the rest, stops after them
critical true When it cannot run, the application stops
retries 3 Restarts after a crash within 30 minutes
grace 30.0 Seconds to stop before SIGKILL
startTimeout 60 Seconds to become ready; a first start runs initdb
alive true true — pg_isready every 10 s, 5 s for an answer, 6 misses in a row mean “hung”. A Probe of your own. null or false — not checked

Everything is checked when the object is made: a version the image cannot install, a relative path, a port out of range, a host without a password, a user or database name not of letters, digits and _, user: 'postgres' (that is the superuser), database: 'postgres' (the cluster’s own), an unknown time zone, a setting on two lines — each is an InvalidArgumentException explaining what is wrong.

php
new Postgres(
  version: 18,
  database: 'app',
  settings: ['shared_buffers' => '256MB', 'max_connections' => 200],
  alive: Probe::command(['pg_isready', '-q', '-h', '/run/postgresql', '-d', 'app'],
      every: 15, failures: 8),                // more patience under heavy load
  shutdown: Shutdown::Smart,                   // wait for every session to finish
);

What a start does

A script of the package runs before the server and then becomes the server itself (exec), so the stop signal reaches PostgreSQL directly:

  1. No data yet → initdb with the superuser postgres, encoding and locale. A directory holding files but no PostgreSQL data is not initialised over — the start stops.
  2. Data there → its version (PG_VERSION) is checked against version. A mismatch stops the start with what to do.
  3. The role user missing → it is created: a login, without superuser rights.
  4. The password follows the code: set — applied, removed — removed. It reaches the script in an environment variable, not on a command line every process in the container can read.
  5. The database missing → it is created, owned by user. There, but someone else’s → handed to user.
  6. pg_hba.conf is written whole: through the socket, anyone; over TCP, only user, only to its database, only with its password. When host is not the loopback, from other addresses too.
  7. The server starts: postgres -D … -k <socket> -p <port> -c listen_addresses=<host> …

Everything before the server starts runs in PostgreSQL’s single-user mode — the server listens on neither the socket nor the port yet.

Roles

The cluster has two roles:

Role Who How it signs in
postgres the superuser: the start script and an administrator through the socket only, inside the container, no password
user (winter) the application: a plain role, the owner of its database through the socket, no password; over TCP with its password, to its database only

The application’s role can do all the application needs: tables, indexes, ALTER in its database, the extensions of contrib.

Why a second role. For an SQL injection. Whoever finds one in the application gets the rights of the application’s connection — and no more:

Through an injection superuser the application’s role
read and change its database’s data yes yes
COPY … TO PROGRAM — a command in the container yes no
pg_read_file() — reading files, .env for one yes no
ALTER SYSTEM, creating roles yes no

With the superuser, an injection becomes the container’s takeover; with the application’s role, it stays inside one database. A second line of defence: the first is queries with parameters, as winter-ppa builds them.

An administrator — the superuser, through the socket:

bash
docker exec -it app psql -h /run/postgresql -U postgres

Data in which the application's role is a superuser

If the cluster was made so that user in it is a superuser, the server starts as usual, and the log warns that the application connects with every right, and how to change it. The script does not lower the rights itself.

Extensions

contrib (on by default) puts the extensions that come with PostgreSQL into the image: pgcrypto, pg_trgm, citext, hstore, unaccent, ltree, uuid-ossp and more. The service only installs them; CREATE EXTENSION enables one in the database. In winter-ppa that is #[Extension] on the configuration class:

main/Configs/MainDbConfig.php
#[Extension('pg_trgm')]
#[Extension('citext')]
class MainDbConfig extends PgDbConfig implements PpaPoolConfigInterface
{
  public static function service(): Postgres
  {
      return new Postgres(version: 18, database: 'app');   // contrib: true
  }
  // setUp() — see Connecting
}

The application’s role enables the trusted ones itself — nearly all code needs: pgcrypto, uuid-ossp, citext, hstore, pg_trgm, unaccent, ltree, btree_gin, btree_gist, fuzzystrmatch, intarray, cube, isn, lo, seg, tablefunc.

Untrusted extensions

pg_stat_statements, postgres_fdw, dblink and the other maintenance ones only the superuser enables. CREATE EXTENSION from the application’s role stops on them: permission denied to create extension … Must be superuser. PostGIS and pgvector are not in contrib — they are apk packages of their own.

UUIDs need no extension: gen_random_uuid() is built into PostgreSQL 13+. No need for contrib — new Postgres(contrib: false), and the image is 4 MB smaller.

Connecting

The Postgres object knows where its server is:

php
$pg->dsn();       // pgsql:host=/run/postgresql;port=5432;dbname=app
$pg->user();      // winter — the application's role, not the superuser
$pg->database;    // app — without database: winter, the role's name

Through the socket, inside the container, no password is asked. Over TCP (host set) — pgsql:host=127.0.0.1;port=5432;dbname=app, with user and password.

So the way the database runs and the way it is reached are best kept in one place — one method that both the application and the code connecting to it call.

Through PDO

main/Db.php
namespace Main;

use Flytachi\Winter\Service\Postgres\Postgres;

final class Db
{
  public static function service(): Postgres
  {
      return new Postgres(version: 18, database: 'app');
  }

  public static function connect(): \PDO
  {
      $pg = self::service();
      return new \PDO($pg->dsn(), $pg->user(), $pg->password, [
          \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
      ]);
  }
}
bootstrap.php
protected static function components(Components $components): void
{
  $components->service(Db::service());
}
php
$notes = Db::connect()->query('SELECT id, text FROM notes')->fetchAll();

Through winter-ppa

The natural home is winter-ppa’s database configuration class: the same class runs the server and connects to it, so the socket, the port, the database and the user cannot drift apart.

main/Configs/MainDbConfig.php
namespace Main\Configs;

use Flytachi\Winter\Cdo\Config\PgDbConfig;
use Flytachi\Winter\Ppa\Pool\PpaPoolConfigInterface;
use Flytachi\Winter\Ppa\Pool\PpaPoolTrait;
use Flytachi\Winter\Service\Postgres\Postgres;

class MainDbConfig extends PgDbConfig implements PpaPoolConfigInterface
{
  use PpaPoolTrait;

  public int $poolMaxConnections = 10;
  public float $poolWaitTimeout = 5.0;

  /** What the application runs. */
  public static function service(): Postgres
  {
      return new Postgres(version: 18, database: 'app');
  }

  /** How winter-ppa connects to it. */
  public function setUp(): void
  {
      $pg = self::service();
      $this->host = $pg->socket;                     // a socket directory is a host for libpq
      $this->port = $pg->port;
      $this->database = $pg->database;
      $this->username = $pg->user;
      $this->password = $pg->password ?? '';         // through the socket none is asked
  }
}
bootstrap.php
protected static function components(Components $components): void
{
  $components->service(MainDbConfig::service());
}

Repositories point at it as at any configuration:

php
class NoteRepository extends Repository
{
  protected string $dbConfigClassName = MainDbConfig::class;
  protected string $entityClassName = Note::class;
  public static string $table = 'notes';
}

$notes = NoteRepository::instance()->orderBy('id')->findAll();
$id = new NoteRepository()->insert($note);

When the database is open over TCP (host set, other containers connecting), setUp() takes 127.0.0.1 — or the container’s name — for the host, and the password is required.

A client over TCP — name the application's database

Over TCP the application’s role is let into its database only. DBeaver, TablePlus and other clients with an empty “Database” field ask for the cluster’s own postgres, and get no pg_hba.conf entry … database "postgres". Name the database of database: — without it, winter, the role’s name: psql -h 127.0.0.1 -U winter -d winter.

How many connections

PostgreSQL’s default is max_connections = 100, three of them kept for the superuser. winter-ppa’s pool holds its connections in every process, not for the whole application, so count how many can be open at once:

text
(HTTP workers + the processes, daemons, scheduler, WebSockets that use the database)
× $poolMaxConnections
+ ~5 — psql, migrations, checks

By default that is 1 worker × 5 = 5 connections, far from the limit. It is usually hit when the workers grow: 8 workers (SERVER_WORKERS=8) × a pool of 20 is 160 already, and at a peak the database starts answering too many clients already. Then make the pool smaller, or raise the limit:

php
new Postgres(database: 'app', settings: ['max_connections' => 200]);

Every PostgreSQL connection is a process with memory of its own: raise the limit with the container’s memory in mind, not “to be safe”.

Log

PostgreSQL’s log goes into the application’s: in the same format, under the -postgres- channel (blue in colour, not to be taken for the application’s lines), with the PID of the database process that wrote the line in the brackets.

text
[2026-11-15 02:11:23] [ERROR] -postgres- [64]: relation "usres" does not exist at character 15 {"statement":"SELECT * FROM usres"}

PostgreSQL’s levels become the log’s:

PostgreSQL Log
DEBUG1–DEBUG5 DEBUG
LOG, INFO INFO
NOTICE NOTIC
WARNING WARN
ERROR ERROR
FATAL CRIT
PANIC EMERG
  • LOG_LEVEL applies to the database too. LOG_LEVEL=warning drops its everyday messages (start, checkpoints) and keeps the warnings and errors.
  • DETAIL, HINT, STATEMENT, CONTEXT are not lines of their own: they join their error, in its context under detail, hint, statement, context.
  • JSON and the log file work as for the application’s lines. In JSON the database’s PID is in extra.program.
  • The start script’s lines (created role winter.) are in the -postgres- channel too; its warnings and errors at WARN and ERROR.

Log off — the database is silent too

PostgreSQL’s lines go through the application’s logger, so without LOG_LEVEL (the log off) they are not seen either — the reason the database did not start among them: only postgres failed: exit code 1 is left. To see it, set LOG_LEVEL=error — errors only — or logs: false: PostgreSQL then writes its output directly.

A log_line_prefix of your own in settings is not understood: such lines reach the log as they are, at INFO. Want PostgreSQL’s output untouched? logs: false.

A detailed query log

log_statement: 'all' or log_min_duration_statement: 0 give thousands of lines a second, all of them through the application’s process. For such a log, turn on PostgreSQL’s own collector — 'logging_collector' => true in settings: it writes files inside the data directory, past the application.

Stopping

shutdown Signal What PostgreSQL does
Shutdown::Smart SIGTERM Waits for every session to end. A client that stays — an administrator’s psql, say — holds the stop until grace runs out
Shutdown::Fast (default) SIGINT Ends the sessions, rolls their transactions back, writes a checkpoint: clean and quick
Shutdown::Immediate SIGQUIT Stops without a checkpoint; the next start recovers as after a crash

A hung server — its alive check failed 6 times in a row — is stopped step by step: its signal, SIGTERM after grace / 2, SIGKILL after grace; then restarted. How that works is in alive.

70 seconds to stop

PostgreSQL stops last, after HTTP, so their times add up:

What Seconds
HTTP finishes its requests 30
PostgreSQL (grace) 30
the application’s margin 5
the container’s margin 5
stop_grace_period 70

That is the number call service docker prints. A stop usually takes a fraction of a second: 70 is the limit, not a wait.

Changed grace (new Postgres(grace: 60.0))? Run call service docker again and update stop_grace_period in compose — 100 seconds here. On Kubernetes, the same number goes into terminationGracePeriodSeconds.

Changing the major version

PostgreSQL 19 cannot read the data of 18. Raising version without upgrading the data would give a server that does not start. So the package does not try:

text
[2026-11-15 02:10:01] [ERROR] -postgres- [9]: the data in /var/lib/postgresql/data is PostgreSQL 18, the server is 19: upgrade it with pg_upgrade, or set version: 18.

The data is moved with pg_upgrade (both versions in the image) or with a dump and a restore (pg_dumpall), and only then version goes up. Minor updates (18.6 → 18.7) need nothing: the image gets them with its next build.

Next