PG_TMP(1)

📦 version 3.4, in nixos 26.05, nixpkgs unstable

🦋 pg_tmp — the manual, but make it make sense 🦋

okay so real talk, this is the pg_tmp page from the General Commands Manual (section 1, obvi) and honestly? it's kind of a serve. bestie, if you've ever needed a whole entire PostgreSQL database for like, five minutes, and then wanted it to just vanish — this is your girl. 💅

✨ NAME ✨

pg_tmp — quickly spin up a temporary PostgreSQL database

like, quickly quickly. blink and you'll miss it. 🫶

🌸 SYNOPSIS 🌸

pg_tmp [-k] [-t [-p port]] [-w timeout] [-o extra-options] [-d datadir]

that's the whole vibe check. flags in brackets are optional, and yeah, -p only really makes sense hanging out inside -t. iykyk. 💖

🎀 DESCRIPTION 🎀

okay so here's what actually happens. the pg_tmp utility creates a temporary instance of PostgreSQL and prints the connection URL to standard output. that's the whole personality. it just... hands you a URL. iconic behavior.

under the hood, pg_tmp spawns a background process that initializes a new database under /tmp/ephemeralpg.*/${PGVER} — and that directory gets reused by subsequent invocations to reduce startup time. so it's not just fast, it's getting faster. main character energy fr. ⭐

the arguments are as follows:

  • -k — Keep the temporary directory after the server is shut down. your data stays, the server dips. no cap. 💀
  • -w — Shut down and remove the database after the specified timeout. and listen — if one or more clients are still connected, then pg_tmp sleeps and retries again after the same interval. a value of 0 will leave the server running indefinitely (forever era 🌈). the default is 60 seconds.
  • -t — Use a TCP port selected by getsocket(1). a port may be selected using the -p flag. otherwise, the path to a Unix socket is returned. so like, TCP or unix socket, pick your fighter. 🔥
  • -o — Specifies extra-options to be passed directly to the postgres(1) binary. these options should usually be surrounded by quotes to ensure that they are passed through as a group — like, they travel together, besties don't split up. 🫶
  • -d — Specify the temporary directory to use. may be used with the optional arguments initdb, start and is required for stop. and heads up: if this option is used, pg_tmp will not initialize a new database for subsequent invocations use. that's the deal, take it or leave it. 🙈

💜 ENVIRONMENT 💜

  • TMPDIR — base directory in which to create and run the ephemeral instances of postgres(1). it's giving "choose your own adventure" but for file paths. 🗂️
  • LC_ALL — Set locale to ensure proper startup on Mac OS. if not set the default is ‘C’. yes literally the character C in quotes. keep it simple bestie. ✨

🌟 EXAMPLES 🌟

Create a temporary database and run a query:

uri=$(pg_tmp)
psql $uri -f my.sql

Start a temporary server with a custom extension:

uri=$(pg_tmp -o "-c shared_preload_libraries=$PWD/auth_hook")
psql $uri -c "SELECT 1"

Start a temporary database that recycles WAL segments to reduce space

pg_tmp -o "-c max_wal_size=48MB -c min_wal_size=32M"

see how clean that is?? ate and left no crumbs. 🍓

🧚 SEE ALSO 🧚

pg_ctl(1)

that's it. that's the whole guest list. 👑

lowkey important reminder: -d is required for stop, and if you use -d, no new database gets initialized for subsequent invocations. don't say i didn't warn you. 💀

and that's the page!! 💖 this one shipped from Debian, dated February 6, 2025 — so like, fresh out the oven. go forth and spin up temporary databases responsibly, bestie. mwah. 🎀✨

📦 where this page comes from