Setting Up PostgreSQL for Development Without Fighting It

PostgreSQL is easy to install and surprisingly easy to get wrong on the first day. The package installs, the service starts, and then the first command says Peer authentication failed for user "postgres". A password set during installation is not the password the prompt wants. The container you started last week has no data in it. A tool cannot find the server on 5432. Each of these has a plain explanation, and none is a sign that PostgreSQL is hard. They come from the fact that the server has its own idea of users, authentication and files, separate from your operating system's, and that idea needs learning once. This guide covers how to set up a development database that behaves, from the install route and the version to the first user, the connection, the config files and the errors you will meet. The spokes linked throughout go into each part. If you only need a database running in the next five minutes, running PostgreSQL in Docker with a persistent volume is the fastest path.

Decide how you will run it

There are two sensible routes for a development machine, and a third that suits some teams.

Native packages. Install PostgreSQL with your system's package manager (Homebrew on macOS, apt or dnf on Linux) or the installer from the project's download page on Windows. The server runs as a service, starts at boot and uses the machine's filesystem. Advantages: no extra layer, easy access to tools, fast. Costs: one version at a time unless you manage several clusters, and the install's quirks differ per platform.

Docker. Run the official postgres image in a container, with a named volume for the data. Advantages: any version, several at once, the same on every machine, disposable. Costs: a container runtime to run and a habit of mapping ports and volumes correctly.

A managed or shared dev database. A cloud instance shared by the team. Advantages: matches production closely and needs nothing local. Costs: latency, shared state and breaking each other's data. Most teams do better with a local database for development and a shared one for integration.

Pick one local route and write it into the project's README. The worst setup is the one where each developer did something different and nobody can reproduce another's error.

Choose a version on purpose

PostgreSQL releases a major version once a year, and each major is supported for five years, with minor releases (bug and security fixes) several times a year. The versioning policy lists the schedule. For development, three rules cover most cases.

Match production. If production runs 16, develop on 16. A feature that exists in 18 and not in 16 will pass on your laptop and fail after deployment. Differences between majors are usually small and always inconvenient at the wrong time.

For a new project with no production yet, start on a recent supported major, the current release or the one before it, and plan to upgrade within the support window (which PostgreSQL version to start a new project on goes through the choice).

Pin the major in code. In Docker, use postgres:17, not postgres:latest; latest changes under you, and a data directory created by one major will not start under another without an upgrade. In packages, install postgresql@17 explicitly where the platform offers it.

Minor versions within a major are safe to update freely; they never change the on-disk format.

Install it

The details vary by platform. In outline:

On macOS with Homebrew, brew install postgresql@17 installs the server, and brew services start postgresql@17 runs it. Homebrew's service runs as your user, and the installer creates a superuser role named after your macOS user, with no password, which is why psql postgres just works there. On Debian and Ubuntu, sudo apt install postgresql installs the distribution's default major and starts a cluster; the project's own apt repository provides other majors. The installer creates an operating system user named postgres and a database superuser of the same name, and local connections as that user use peer authentication. On Red Hat family systems, the package installs the binaries and you initialize the cluster with postgresql-setup --initdb, then enable the service. On Windows, the EDB installer asks for a superuser password, a port (5432 by default) and a locale, and installs the service and optional tools.

Installing PostgreSQL on macOS, Linux and Windows walks through each, and running PostgreSQL in Docker with a persistent volume covers the container route.

Make the data survive

A database that loses its data when you restart something is the first surprise for people moving to Docker. The container's filesystem is disposable. Data must live in a volume.

docker run --name pg17 -d \
  -e POSTGRES_PASSWORD=devpassword \
  -p 5432:5432 \
  -v pg17data:/var/lib/postgresql/data \
  postgres:17

The -v pg17data:/var/lib/postgresql/data mounts a named volume at the data directory, so removing and recreating the container keeps the data. Without it, docker rm deletes the database. The POSTGRES_PASSWORD variable is read only when the data directory is empty, on first start; changing it later does nothing, which confuses people (resetting a forgotten postgres password). Newer image versions changed where the data directory lives by default, so read the image documentation for the version you run before choosing the mount path.

With a native install, the data directory is on disk and survives restarts. It still deserves a thought: if you uninstall the package, check whether the data goes with it.

Create a role and a database per project

The postgres account is a superuser. Using it for application work is like running everything as root: it works, and it hides permission mistakes that will bite in production. For each project, create a role and a database.

CREATE ROLE shop LOGIN PASSWORD 'dev-only-password';
CREATE DATABASE shop OWNER shop;

Connect as that role and work there. Create a schema if you want one (the default public schema works), and set the role's search path. From PostgreSQL 15, ordinary users no longer have the right to create objects in the public schema by default, which is a deliberate change; a role that owns its database can create what it needs, and a role that does not will see permission denied for schema public. Creating your first database user and schema in PostgreSQL covers the statements and the permission model.

Keep the superuser for administration: creating roles, extensions, restoring dumps. Keep the project role for the application and for migrations. In production, the migration role and the application role are often separate, and rehearsing that locally avoids a surprise.

Connect

The client you will use most is psql. A connection needs a host, a port, a database, a user and a password, and there are three ways to give them: command line flags, environment variables, or a connection string.

psql -h localhost -p 5432 -U shop -d shop
psql "postgresql://shop@localhost:5432/shop"
PGHOST=localhost PGUSER=shop PGDATABASE=shop psql

All three do the same. The URI form is the one applications and frameworks take, usually in an environment variable such as DATABASE_URL. Special characters in a password must be percent-encoded in a URI. Connecting to PostgreSQL from the command line with psql and connection strings and URI formats for PostgreSQL clients cover the options, including SSL mode and socket connections.

Do not put passwords in files that go into the repository. Use a .pgpass file in your home directory (hostname:port:database:username:password, permissions 0600) so psql and many tools find the password without a prompt, and keep DATABASE_URL for application runs in an untracked .env (using pgpass to stop typing passwords in psql and tools).

The errors everyone meets

Four failures account for most first-week questions.

Peer authentication failed for user "postgres". The server is set to trust the operating system's identity for local socket connections, and your OS user is not postgres. Run sudo -u postgres psql, or connect over TCP with -h localhost, which uses password authentication. The cause is in pg_hba.conf (fixing peer authentication failed for user postgres).

Connection refused on port 5432. Nothing is listening where the client looks: the server is not running, it listens on a different port or interface, or a container's port is not published (fixing connection refused on port 5432 in PostgreSQL).

A forgotten or unknown postgres password. Reset it through a local superuser connection, or briefly relax pg_hba.conf (resetting a forgotten postgres password without data loss).

"I changed the config and nothing happened." You edited a file that is not the one the server reads, or you did not reload. Ask the server: SHOW config_file; (where PostgreSQL keeps its config files and data directory).

Knowing these four by name makes the rest of the setup unremarkable.

Know where things are

PostgreSQL keeps three kinds of files you will touch: the data directory (the database itself), the configuration files (postgresql.conf, pg_hba.conf, pg_ident.conf, sometimes in the data directory and sometimes under /etc) and the logs. Their locations depend on the platform: Debian puts configs in /etc/postgresql/<version>/main/, Red Hat family in the data directory under /var/lib/pgsql/, Homebrew under its prefix and Docker in the container. Rather than memorize paths, ask the server: SHOW data_directory;, SHOW config_file;, SHOW hba_file;. These return the files the running server uses, which is the only truth.

Do not edit files in the data directory by hand unless you know the file, and never copy a running data directory as a backup. Use pg_dump or pg_basebackup.

Rebuild from scratch, often

The healthiest development database is one you can throw away. Write the setup as scripts: create role, create database, run migrations, load seed data. Keep them in the repository. Then recovering from a mess is a single command, a new teammate is productive in minutes and tests can start from a known state.

#!/usr/bin/env bash
set -euo pipefail
psql -U postgres -h localhost -v ON_ERROR_STOP=1 <<'SQL'
DROP DATABASE IF EXISTS shop;
DROP ROLE IF EXISTS shop;
CREATE ROLE shop LOGIN PASSWORD 'dev-only-password';
CREATE DATABASE shop OWNER shop;
SQL
psql "postgresql://shop:dev-only-password@localhost:5432/shop" -v ON_ERROR_STOP=1 -f db/schema.sql -f db/seed.sql

ON_ERROR_STOP makes psql stop at the first error instead of carrying on with a half-built database. Test the script on a clean machine or a fresh container from time to time; scripts that nobody runs stop working.

Seed data should be small, deterministic and realistic enough to exercise the code: a few customers, a few hundred orders, the awkward cases (a customer with no orders, a refund, a name with an apostrophe). Do not copy production data into development without masking; it brings privacy obligations with it.

Extensions and settings worth knowing

A few extensions come up in nearly every project. pg_stat_statements records query statistics. pgcrypto and the built-in gen_random_uuid() generate IDs and hashes. citext offers case-insensitive text. pg_trgm supports fuzzy text search. They ship with the server (in the contrib package on some platforms) and are enabled per database with CREATE EXTENSION, which needs superuser or trusted-extension rights.

Development defaults are conservative about memory. For a laptop, the defaults are fine. Turn on useful logging while you learn: log_min_duration_statement = 200 logs queries slower than 200 milliseconds, and log_statement = 'ddl' records schema changes. Change settings with ALTER SYSTEM or by editing postgresql.conf and reloading; some need a restart, and SELECT name, context FROM pg_settings WHERE name = 'shared_buffers'; tells you which.

Tools around the server

psql is worth learning well; it is installed with the server and available everywhere (getting fast with psql and habits that save hours a week is the companion guide). A graphical client such as pgAdmin, DBeaver or your IDE's database panel helps for browsing and diagrams. Use the same connection details for all, and remember that GUI tools often hold the password in their own store.

Add pg_isready to your toolbox: pg_isready -h localhost -p 5432 says whether the server accepts connections, which is the first thing to test when something fails. And keep pg_dump and pg_restore in mind for copying databases between machines.

Working in a team

Agree the basics once, and write them down: which major version, how to start the database, the names of roles and databases, where the connection string lives, how to rebuild. A docker-compose.yml that starts the pinned version with a named volume and a health check is a few lines and removes most "works on my machine" problems.

services:
  db:
    image: postgres:17
    environment:
      POSTGRES_USER: shop
      POSTGRES_PASSWORD: dev-only-password
      POSTGRES_DB: shop
    ports: ["5432:5432"]
    volumes: ["pgdata:/var/lib/postgresql/data"]
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U shop -d shop"]
      interval: 5s
      retries: 10
volumes:
  pgdata:

With POSTGRES_USER, POSTGRES_PASSWORD and POSTGRES_DB, the image creates the role and database on first start, which also covers the "first user" step. The health check lets other services wait until the database is ready.

Encoding, locale and time zone: the choices that are hard to change

A few settings are fixed when a cluster or a database is created, and changing them later is painful. Get them right at the start.

Encoding. Use UTF8. It is the default on almost every modern install and the right answer; a database in a single-byte encoding cannot hold arbitrary text, and converting later means a dump and restore. Check with SHOW server_encoding; or \l.

Locale and collation. The collation decides how text sorts and compares. A locale such as en_US.UTF-8 sorts according to language rules and depends on the operating system's locale library, so an index built on one system can become subtly invalid after an operating system upgrade changes the library's rules. The C or C.UTF-8 collation sorts by byte value, is stable and fast and gives results that look wrong to humans ("Z" before "a"). Recent PostgreSQL versions also offer a built-in locale provider. The default is fine for development; for production, think about it deliberately, and use the same setting in development so the sort order of your tests matches reality.

Time zone. The server has a time zone setting that affects how timestamptz values are displayed and how now() is shown. Store instants as timestamptz and set the session time zone in the application, or use UTC throughout. A developer machine in one zone and a server in another produces test failures that depend on the time of day.

Data checksums. Checksums on data pages detect storage corruption and, in some versions, are off by default for new clusters and on in others. Check the documentation for your version. For development they matter little, and for production they matter more.

These settings are chosen by initdb when a native cluster is created, or by the Docker image's environment variables (POSTGRES_INITDB_ARGS). Check them before loading data you care about.

Working with more than one version

Sooner or later you maintain two projects on two majors, or you need to test an upgrade. Native installs can run several clusters side by side, each on its own port. On Debian and Ubuntu, pg_lsclusters lists them and pg_createcluster 18 test --port 5433 makes another; Homebrew installs versioned formulas (postgresql@17, postgresql@18) that you start on different ports. Docker is simpler: postgres:17 on port 5432 and postgres:18 on 5433, each with its own named volume.

Name volumes and containers for their version (pg17data, pg18data), so a mix-up cannot start the wrong data directory under the wrong binary. A data directory created by one major will not start under another, and the error message ("database files are incompatible with server") is clear. Keep the port numbers in the project's documentation and in DATABASE_URL.

Backing up a development database

Development databases are disposable until the day they hold a week of hand-built test data. A quick dump takes seconds.

pg_dump -h localhost -U shop -Fc shop > shop-2026-10-29.dump
pg_restore -h localhost -U shop -d shop_copy --no-owner shop-2026-10-29.dump

-Fc writes the custom compressed format, which pg_restore can read selectively and in parallel. Restore into a new database (createdb shop_copy first) rather than over the original. For plain SQL output that is easy to read and diff, use pg_dump -Fp. Keep the schema in migration files and the data reproducible from seeds, so that dumps are a convenience and not the only copy of something.

Copy databases between machines the same way. Do not copy the data directory of a running server: the files are not consistent while it runs, and the copy may not start.

Upgrading a local major version

When a new major arrives and you want it locally, you have two routes. The simple one is a dump and restore: dump from the old server, start the new one and restore. It is slow for large databases and safe for development-sized ones. The faster one, pg_upgrade, rewrites the cluster in place and needs both versions' binaries installed; its documentation is thorough and its preconditions (matching locale and encoding, extensions compatible) are worth reading before you start. For most development databases, dump and restore is enough, and it exercises the same path as a restore from backup, which is worth practicing.

After an upgrade, run your test suite and look at the query plans of the important queries. Planner changes between majors are usually improvements and occasionally regressions for one query, and development is the place to find that out.

Keeping a development server private

A development database is still a server. Out of the box, PostgreSQL listens on the loopback interface only (listen_addresses = 'localhost'), which is right. Docker is where people expose it accidentally: -p 5432:5432 publishes the port on all interfaces of the host, which on a laptop in a café means other devices on the network can reach it, protected only by a password such as postgres. Publish to loopback only with -p 127.0.0.1:5432:5432. Use a password that is not trivially guessed even in development, since the habit carries into places that matter.

Never reuse a production password locally, and never point a local tool at production by accident: name development roles and databases differently (shop_dev), and show the database name in the psql prompt so you always know where you are (setting up a psqlrc that makes daily work easier shows how).

A checklist for the first hour

On a new machine, the sequence that works:

1. Install the pinned major version, or pull the pinned image, and start it with a persistent data location.

2. Check that it is up with pg_isready and connect as the superuser.

3. Create the project role and database, owned by the role.

4. Connect as the project role with a URI, and store the password in a pgpass file.

5. Run the schema and seed scripts; check the row counts.

6. Note the output of SHOW config_file; and SHOW data_directory; in the team's setup notes.

7. Take a first dump, so you know the backup route works.

8. Write down anything that surprised you, and fix the README.

If a step fails, the error messages in the sections above name the cause in most cases. The habit of reading the first error line carefully, including the host and port that the client tried, solves more problems than any tool.

Connecting your application

The application reads one connection string, and the way it reads it should be the same in development and production, with only the value changing. Put it in an environment variable named in the documentation (DATABASE_URL is the common convention), load it from an untracked .env file in development, and have the code fail clearly when it is missing. Do not build connection strings from scattered fragments in several places; one function that reads the variable and opens the connection is easier to change.

Think about pooling early. Each PostgreSQL connection is a process on the server, and a web application that opens a connection per request will run out of connections quickly. Use the pool built into your driver or framework in development, with a small size, so you meet the limits before production does. Set application_name in the connection string so that queries show up with a recognizable label in pg_stat_activity, which makes a stuck query or a leaked connection easy to find in a shared server.

Check connectivity from the same environment the application runs in. A container on a Docker network reaches the database by service name (db), not localhost; an application on the host reaches a containerized database on the published port; a process inside a virtual machine reaches the host's database by the host's address. Most "connection refused" reports in a first week come from using localhost from inside a container where it means the container itself.

Migrations from the start

Even in a first prototype, put schema changes in files that run in order. Whether you use a framework's migration tool or plain numbered SQL files, the discipline is the same: the database's structure is described by code in the repository, applied in sequence, and recorded in a table that says which have run. A new developer, a test run and a production deployment then all build the same schema from the same starting point.

Keep migrations small and reversible where you can, test them on a copy of realistic data and never edit a migration that has run anywhere shared; add a new one instead. The script that rebuilds your development database should run the migrations, not a hand-maintained dump, so that the path to production is exercised every time.

When a local PostgreSQL is not the right development database

A local server is the wrong choice when the project depends on managed-service features you cannot reproduce locally (specific extensions, proprietary storage layers, serverless branching), in which case a development instance of the managed service is closer to reality. It is wrong when the machine is too constrained to run it, or when security policy forbids data on laptops. And it is unnecessary for a quick experiment that SQLite or DuckDB could answer in a file. For anything that will run on PostgreSQL in production, though, developing against PostgreSQL, the same major version, catches a large class of problems early.

Where to go next

Start with the install route that matches your machine: installing PostgreSQL on macOS, Linux and Windows or running PostgreSQL in Docker with a persistent volume. Then read creating your first database user and schema in PostgreSQL, connecting to PostgreSQL from the command line with psql and connection strings and URI formats for PostgreSQL clients. Keep the two error articles close: fixing peer authentication failed for user postgres and fixing connection refused on port 5432 in PostgreSQL. Finish with where PostgreSQL keeps its config files and data directory, resetting a forgotten postgres password without data loss and which PostgreSQL version to start a new project on.

The authoritative references are the PostgreSQL documentation, the download page for platform packages and the official Docker image page for container options. The site's tools convert small samples between CSV and JSON in your browser.