PostgreSQL Development Introduction

Building PostgreSQL from source

Start by compiling and installing PostgreSQL yourself. Clone postgresql.git from git.postgresql.org; the build system discovers dependencies on disk and toggles features accordingly. Versions 16 and newer are built with Meson, while 15 and older use GNU Autoconf. The commands differ between the two; the installation docs cover both, and there is a quick Meson and Autoconf command reference on the wiki.

Stack traces will be a routine part of your work, so build with -Og -g to retain debug symbols without over-optimizing: meson setup -debug=true -Dbuildtype=debugoptimized .... These are the defaults, but know what they do.

To keep several major versions around at once, create one git worktree per major version. Worktrees let you hold multiple branches simultaneously without committing, stashing, resolving conflicts, or losing build artifacts when you switch.

Install under $HOME rather than /usr so you never need sudo. Compile and install every currently supported major version and satisfy any missing dependencies. Then walk the tree: from the repository root, run tree -d -L 5 | less, and inspect git tag and git branch --remote. Find out through your own searches how to run the unit tests and where the full compilation output lands. Read the versioning policy — five major versions are supported at any time.

Running multiple versions side by side

An installation holding several major versions looks like this:

├── 13
│   ├── bin
│   ├── include
│   ├── lib
│   └── share
├── 15
│   ├── bin
│   ├── include
│   ├── lib
│   └── share
└── 16
    ├── bin
    ├── include
    ├── lib64
    └── share

Binaries are only callable by name once the matching bin directory is on $PATH, and during development you will want to switch between them constantly instead of editing export PATH="$PATH:/whatever/path. This shell function handles the switch:

# Choose PostgreSQL bin directory and append it to your $PATH.
pgenv() {
	# Major version
	_pg_ver="$1"
	# Full PostgreSQL bin directory path.
	_pg_path="$HOME/.local/usr/psql/$_pg_ver/bin"

	# Freak out if such a directory does not exist.
	if [ ! -d "$_pg_path" ]; then
		echo "$_pg_path does not exist"
		return 1
	fi

	# Remove all paths from $PATH that have psql directory in them.
	_clean_path="$(echo -n "$PATH" | tr ':' '\n' | grep -v psql | tr '\n' ':')"
        _clean_path="${_clean_path%?}"
	# Export new bin directory.
	export PATH="${_pg_path}:${_clean_path}"
}
# Call immediately to set a default version.
pgenv 16

Append it to ~/.bashrc or ~/.zshrc and reload your shell configuration. Running pgenv 17 selects PostgreSQL 17; call it again with another number to change versions. which pg_config confirms which binary will execute.

The usual way to start the server is pg_ctl -D /path/to/cluster/dir start. To keep it in the foreground with logs visible, start the binary directly as postgres -D /path/to/cluster/dir. Because repeating -D and naming a database for psql gets tedious, define PGDATA, PGDATABASE and the other environment variables. Mine live inside the pgenv function body:

# Set environment for PostgreSQL binaries.
export PGHOST="localhost"
export PGPORT="5432"
export PGDATA="$HOME/.local/share/psql/$_pg_ver/main"
export PGUSER="$USER"
export PGDATABASE="postgres"

After that, postgres replaces postgres -D /path/to/cluster, and psql replaces psql database_name.

A first extension in C

Writing extensions in C is a gentler entry point than contributing to core. As a exercise, build one that defines a function returning a string:

postgres=# select postgres_func();
      postgres_func
--------------------------
 Hello from my extension!
(1 row)

postgres=#

Four artifacts are required:

• The C source code of the extension.
• The SQL glue code.
• A control file with extension metadata.
• A Makefile.

The C source

Save as my_extension.c:

/* Supplied with PostgreSQL, contain the following macro definitions. */
#include "postgres.h"
#include "fmgr.h"

/* Linker magic, each PostgreSQL extension must have it. */
PG_MODULE_MAGIC;

/* C function prototypes. */
static const char *c_func(void);
Datum postgres_func(PG_FUNCTION_ARGS);

/* Internal C function, declare them as usual. */
const char *
c_func(void)
{
	const char *msg = "Hello from my extension!";
	return msg;
}

PG_FUNCTION_INFO_V1(postgres_func);
/* Functions defined like the one below can be called from PostgreSQL shell
 * and must be preceded by the above magic linker call. */
Datum
postgres_func(PG_FUNCTION_ARGS)
{
	/* Yes, I could have supplied the string literal
	 * directly to that macro call. */
	PG_RETURN_CSTRING(c_func());
}

SQL glue and control file

Save as my_extension--1.0.sql:

-- It is a dummy SQL function that invokes our real C function
-- with that same name.
CREATE FUNCTION postgres_func()
RETURNS cstring -- data type of the return value.
AS 'MODULE_PATHNAME' -- expands to module_pathname value in control file.
LANGUAGE C;

-- You can insert arbitraty SQL code in here,
-- views, table definitions, whatever.

Save as my_extension.control:

comment = 'Briefly describe your extension here'
# This number corresponds to the above SQL file name.
default_version = '1.0'
# $libdir expands to lib directory path
# under your PostgreSQL install (--prefix) directory.
module_pathname = '$libdir/my_extension'

All accepted values are documented here.

The Makefile

Run the pg_config binary and look at its output:

$ pg_config
BINDIR = /home/username/.local/usr/psql/16/bin
DOCDIR = /home/username/.local/usr/psql/16/share/doc/postgresql
HTMLDIR = /home/username/.local/usr/psql/16/share/doc/postgresql/html
INCLUDEDIR = /home/username/.local/usr/psql/16/include
PKGINCLUDEDIR = /home/username/.local/usr/psql/16/include/postgresql
INCLUDEDIR-SERVER = /home/username/.local/usr/psql/16/include/postgresql/server
LIBDIR = /home/username/.local/usr/psql/16/lib64
PKGLIBDIR = /home/username/.local/usr/psql/16/lib64/postgresql
LOCALEDIR = /home/username/.local/usr/psql/16/share/locale
MANDIR = /home/username/.local/usr/psql/16/share/man
SHAREDIR = /home/username/.local/usr/psql/16/share/postgresql
SYSCONFDIR = /home/username/.local/usr/psql/16/etc/postgresql
PGXS = /home/username/.local/usr/psql/16/lib64/postgresql/pgxs/src/makefiles/pgxs.mk
CONFIGURE = 
CC = ccache cc
CPPFLAGS = -D_GNU_SOURCE
CFLAGS = -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wmissing-prototypes -Wpointer-arith -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -Wdeclaration-after-statement -Wno-format-truncation -Wno-stringop-truncation
CFLAGS_SL = -fPIC
LDFLAGS = -Wl,--as-needed
LDFLAGS_EX = 
LDFLAGS_SL = 
LIBS = 
VERSION = PostgreSQL 16.1

It knows everything about your PostgreSQL installation, and you can request individual values:

$ pg_config --libdir --bindir
/home/username/.local/usr/psql/16/lib64
/home/username/.local/usr/psql/16/bin

The --libdir value is what $libdir expands to inside the control file. The --pgxs value points at a Makefile; open it and read the opening lines:

# This file contains generic rules to build many kinds of simple
# extension modules.  You only need to set a few variables and include
# this file, the rest will be done here.

That tells us we need not write build recipes for an extension. We define variables and include this file instead — look further down for how:

# Use the following layout for your Makefile:
#
#   [variable assignments, see below]
#
#   PG_CONFIG = pg_config
#   PGXS := $(shell $(PG_CONFIG) --pgxs)
#   include $(PGXS)
#
#   [custom rules, rarely necessary]

In effect, it asks pg_config where --pgxs lives and includes it; PG_CONFIG exists so a custom path to the pg_config binary can be supplied. Further down still are the variables you may define in your own Makefile:

# Set one of these three variables to specify what is built:
#
#   MODULES -- list of shared-library objects to be built from source files
#     with same stem (do not include library suffixes in this list)
#   MODULE_big -- a shared library to build from multiple source files
#     (list object files in OBJS)
#   PROGRAM -- an executable program to build (list object files in OBJS)
#
# The following variables can also be set:
#
#   EXTENSION -- name of extension (there must be a $EXTENSION.control file)
#   MODULEDIR -- subdirectory of $PREFIX/share into which DATA and DOCS files
#     should be installed (if not set, default is "extension" if EXTENSION
#     is set, or "contrib" if not)
#   DATA -- random files to install into $PREFIX/share/$MODULEDIR
...

The list is longer than shown — read it yourself. With that knowledge, the Makefile for our extension is:

# my_extension.control must exist.
EXTENSION = my_extension
# We want a single shared library.
MODULE_big = my_extension
# A single shared library from the following object files.
# As we have a single source file, we have a single object file.
OBJS = my_extension.o
# Files to install along with the extension.
DATA = my_extension--1.0.sql

PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

Save all four files, run make to build and make install to install. Restart the server, connect with psql, run CREATE EXTENSION my_extension;, and the function is callable via SELECT postgres_func();.

Now explore: try to break things, read the errors, fix them. Study other extensions, install and use them, and note that some require more than CREATE EXTENSION ...;. Break those too, and see what happens when the installation requirements are not followed to the letter. This extension template is worth downloading and studying; it contains material this article does not cover.

Contributing to core

No procedure or precise prerequisite list exists; only a growing body of advice. Reviewing code during commit fests is a good way to begin. Then read the PostgreSQL Wiki pages:

• Developer FAQ
• Development information
• So, you want to be a developer?

Where to get help

Read this book first — it is the best overview of PostgreSQL internals available, brief, to the point, and closer to a series of articles than a textbook.

Next, read the Git commit messages behind the code you are working on; they are usually the best source of information. Then come the README files scattered across the source tree, which you can locate with find:

$ find /path/to/postgresql/repository -iname readme
./src/backend/utils/mb/README
./src/backend/utils/misc/README
./src/backend/access/gist/README
./src/backend/access/brin/README
./src/backend/access/rmgrdesc/README
./src/backend/access/transam/README
./src/backend/access/hash/README
./src/backend/access/gin/README
./src/backend/access/spgist/README
./src/backend/access/nbtree/README
./src/backend/regex/README
./src/backend/snowball/README
./src/backend/optimizer/README
./src/backend/optimizer/plan/README
...

None of that replaces asking people. Join the mailing lists, attend conferences, and connect with other developers by whatever means suit you. The community is civilized — hostility is not something you should expect — so do not hold back questions.

Getting around the source

The following is a set of suggestions, not a fixed procedure; adapt it to your environment.

Git

Core work means navigating a large repository. git bisect helps locate the commit that introduced a snippet, though it does not always work. git log -S'code_line' /path/to/file can also identify commits. git blame reveals the latest commit touching a given line. git cherry-pick re-applies a patch — a series of commits — onto another branch; it is a favorite because it never fails outright, asking you to resolve conflicts manually instead of aborting.

Grep

Look at a few random function bodies in the project:

Datum
hashhandler(PG_FUNCTION_ARGS)
{
    /* ... */
}

IndexBuildResult *
hashbuild(Relation heap, Relation index, IndexInfo *indexInfo)
{
    /* ... */
}

void
hashbuildempty(Relation index)
{
    /* ... */
}

The style places each function name at the start of a line, followed by (, which means any function's body can be found by name across the whole tree:

$ grep -rni --include='*.c' ^hashbuild\(
src/backend/access/hash/hash.c:115:hashbuild(Relation heap, Relation index, IndexInfo *indexInfo)

The output gives a file path and line number, for any function. Neovim accepts a line number to jump to, so nvim +115 src/backend/access/bash/bash.c lands directly on the function. This can be scripted:

#!/bin/bash
# Find a given function witin a source tree and open the file at the exact
# location in $EDITOR.

# Terminate the script execution on any non-zero exit code.
set -e

_func_name=''

# There are function names with underscores, like init_custom_variable(...).
# I would rather type init custom variable than init_custom_variable, and
# then let the script substitute all space characters with underscores.
#
# Join arguments with '_'.
for _arg in "$@"
do
	_func_name="${_func_name}_${_arg}"
done
# Remove the leading '_'.
_func_name="${_func_name#_}"

# Find that function.
_location=$(grep -rni --include='*.c' "^$_func_name(" | head -n 1)
# Parse its location and line number from grep output.
IFS=':'
read -r -a _lparts <<< "$_location"
# Open the file.
$EDITOR +"${_lparts[1]}" "${_lparts[0]}"

Somewhere in the tree sits init_custom_variable(...). Typing ffunc init custom variable opens its body immediately: the ffunc script greps for the function, parses the location and hands it to Neovim.

LSP

Since comments above function definitions are so valuable, configure your editor to jump to them quickly:

/*
 * execCurrentOf
 *
 * Given a CURRENT OF expression and the OID of a table, determine which row
 * of the table is currently being scanned by the cursor named by CURRENT OF,
 * and return the row's TID into *current_tid.
 *
 * Returns true if a row was identified.  Returns false if the cursor is valid
 * for the table but is not currently scanning a row of the table (this is a
 * legal situation in inheritance cases).  Raises error if cursor is not a
 * valid updatable scan of the specified table.
 */
bool
execCurrentOf(CurrentOfExpr *cexpr,
			  ExprContext *econtext,
			  Oid table_oid,
			  ItemPointer current_tid)
{

Meson builds produce compile_commands.json — take a peek:

[
  {
    "directory": "/home/uninstance/dev/c/rel16/build",
    "command": "ccache cc -Isrc/port/libpgport_srv_crc.a.p -Isrc/include -I../src/include -fdiagnostics-color=always -D_FILE_OFFSET_BITS=64 -Wall -Winvalid-pch -O2 -g -fno-strict-aliasing -fwrapv -fexcess-precision=standard -D_GNU_SOURCE -Wmissing-prototypes -Wpointer-arith -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -Wdeclaration-after-statement -Wno-format-truncation -Wno-stringop-truncation -fPIC -DBUILDING_DLL -msse4.2 -MD -MQ src/port/libpgport_srv_crc.a.p/pg_crc32c_sse42.c.o -MF src/port/libpgport_srv_crc.a.p/pg_crc32c_sse42.c.o.d -o src/port/libpgport_srv_crc.a.p/pg_crc32c_sse42.c.o -c ../src/port/pg_crc32c_sse42.c",
    "file": "../src/port/pg_crc32c_sse42.c",
    "output": "src/port/libpgport_srv_crc.a.p/pg_crc32c_sse42.c.o"
  },
  {
    "directory": "/home/uninstance/dev/c/rel16/build",
    "command": "ccache cc -Isrc/port/libpgport_srv.a.p -Isrc/include -I../src/include -fdiagnostics-color=always -D_FILE_OFFSET_BITS=64 -Wall -Winvalid-pch -O2 -g -fno-strict-aliasing -fwrapv -fexcess-precision=standard -D_GNU_SOURCE -Wmissing-prototypes -Wpointer-arith -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -Wdeclaration-after-statement -Wno-format-truncation -Wno-stringop-truncation -fPIC -DBUILDING_DLL -MD -MQ src/port/libpgport_srv.a.p/bsearch_arg.c.o -MF src/port/libpgport_srv.a.p/bsearch_arg.c.o.d -o src/port/libpgport_srv.a.p/bsearch_arg.c.o -c ../src/port/bsearch_arg.c",
    "file": "../src/port/bsearch_arg.c",
    "output": "src/port/libpgport_srv.a.p/bsearch_arg.c.o"
  },
  {
    "directory": "/home/uninstance/dev/c/rel16/build",

It is the full sequence of compilation commands, and it is what lets an editor navigate the tree. Microsoft's Language Server Protocol is the standard that any editor implements — natively or through plugins — to get completion, code actions and go-to-definition in any language. Neovim, for instance, supports LSP natively. Clang ships clangd, the C/C++ language server, and it needs that compile_commands.json.

So, for completion and navigation you need:

• An editor with LSP support.
• clangd, the C/C++ language server.
• compile_commands.json, generated by Meson.

Meson generates this file for PostgreSQL builds, but it does not currently work with extensions, and it will not work for versions 15 and older, which do not use Meson. Bear can produce compile commands for arbitrary projects, extensions included — simply build with bear -- make.

In action, LSP lists every reference of the portal variable:

Tree-sitter

Tree-sitter, a generic language parser and syntax tree generator, is a great aid to code navigation. It integrates well with editors and, among other things, provides syntax highlighting derived from the syntax tree rather than regular expressions, plus project symbol navigation:

Where to stop

Being a good database administrator is not a prerequisite for being a good database developer, and no one — developer or administrator — knows all of PostgreSQL. Developers in particular tend to specialize in specific areas of the system rather than covering it end to end.