Setting up a local PostgreSQL database

Introduction

A working PostgreSQL setup has two parts: the PostgreSQL server (postgres), which stores your data and accepts connections, and a client that talks to it. This guide uses psql, PostgreSQL's command-line client, which every installation method below includes.

There are two broad ways to run the server on your own computer:

• In a container. The official postgres Docker image is the quickest option on any operating system. The server is isolated from the rest of your system, you can run several versions side by side, and removing it leaves nothing behind. For most development work, this is the option we recommend.
• As a native installation. PostgreSQL's package repositories, your Linux distribution, Homebrew, Postgres.app and the EDB installer install PostgreSQL directly on your operating system, usually as a service that starts with your computer. Choose this if you can't or don't want to use Docker.

After the installation, the guide shows how to lock the server down, create a database and a role for your application, manage the service, and remove PostgreSQL again. Jump to the part you need:

• Choosing a PostgreSQL version
• Running PostgreSQL in Docker
• Installing PostgreSQL on Linux
• Installing PostgreSQL on macOS
• Installing PostgreSQL on Windows
• Securing the installation
• Creating a role and a database for your application
• Managing the server and finding its files
• Removing PostgreSQL

Choosing a PostgreSQL version

The PostgreSQL project releases a new major version about once a year and supports each one for five years. Within a major version, minor releases with bug and security fixes come out at least every three months, and the project recommends always running the current minor release. These major versions are supported:

PostgreSQL 13 reached its end of life on November 13, 2025, and PostgreSQL 12 on November 21, 2024.

This guide installs PostgreSQL 18, the newest major version, which gives a new project the longest support window. At the time of writing, its current release is 18.6. If you are matching an existing server, install the major version it runs instead; the sections below point out where the version number goes.

Always name the major version when you install PostgreSQL. Names without a version, such as the Docker tag postgres:latest, Homebrew's postgresql and the postgresql package in PostgreSQL's APT repository, stand for the newest major version. That is 18 at the time of writing, but PostgreSQL 19 is in beta testing, and once it's released, a routine update can move you to it. A new major version can't simply take over an older version's data directory: moving your databases to it takes a dump and restore or pg_upgrade.

Running PostgreSQL in Docker

You need Docker installed: Docker Desktop on macOS and Windows, or Docker Engine on Linux. The commands below are for a Unix shell such as bash or zsh (on Windows, use WSL).

First, generate a random password for PostgreSQL's administrative postgres role and store it in a file that only you can read:

(umask 077; openssl rand -base64 24 > postgres-password.txt)

The umask in the subshell makes sure the file is created readable only by you, without a moment where others could read it. Keep this file out of version control. Then start the server:

docker run --name postgres -d \
  -p 127.0.0.1:5432:5432 \
  -v postgres-data:/var/lib/postgresql \
  -v "$PWD/postgres-password.txt:/run/secrets/postgres-password:ro" \
  -e POSTGRES_PASSWORD_FILE=/run/secrets/postgres-password \
  postgres:18

Each option has a job:

• -p 127.0.0.1:5432:5432 makes the server reachable on port 5432 of your computer, but only from your computer. Without the 127.0.0.1 part, Docker publishes the port on all of the host's addresses, which makes your database reachable from the network. (Before Docker Engine 28, other hosts on the same local network segment could reach even ports published to 127.0.0.1; Docker's page has the details.) If port 5432 is already in use, for example by a PostgreSQL server installed on your computer, pick another host port, such as 127.0.0.1:5433:5432, and connect to that port.
• -v postgres-data:/var/lib/postgresql keeps the data in a named volume, so your databases survive when you remove or recreate the container. The PostgreSQL 18 image changed where it keeps its data: the volume belongs on /var/lib/postgresql, and the data directory is /var/lib/postgresql/18/docker inside it. Guides written for older versions mount the volume on /var/lib/postgresql/data instead. The postgres:18 container then stops right away with an error that starts with Error: in 18+, these Docker images are configured to store database data in a format which is compatible with "pg_ctlcluster".
• POSTGRES_PASSWORD_FILE tells the image to read the password for the postgres superuser from the mounted file. Unlike -e POSTGRES_PASSWORD=..., this keeps the password out of your shell history and out of the output of docker inspect. The image only uses the password to initialize an empty data directory; after that, it is stored in the database, and changing the file has no effect.
• postgres:18 follows PostgreSQL 18: pulling it again gets you the latest 18.x release, never a new major version. Use postgres:17 for PostgreSQL 17.

On the first start, the container initializes the data directory and sets the password, runs a temporary server for any setup scripts, and then starts the real server. Wait until docker logs postgres shows database system is ready to accept connections after PostgreSQL init process complete:

docker logs postgres

...
PostgreSQL init process complete; ready for start up.

2026-09-30 12:38:21.719 UTC [1] LOG:  starting PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
2026-09-30 12:38:21.719 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2026-09-30 12:38:21.719 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2026-09-30 12:38:21.731 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2026-09-30 12:38:21.739 UTC [73] LOG:  database system was shut down at 2026-09-30 12:38:21 UTC
2026-09-30 12:38:21.745 UTC [1] LOG:  database system is ready to accept connections

The addresses 0.0.0.0 and :: belong to the container's own network interfaces. Which of your computer's addresses can reach the server is decided by the -p option.

Now connect with psql inside the container:

docker exec -it postgres psql -U postgres

psql (18.6 (Debian 18.6-1.pgdg13+2))
Type "help" for help.

postgres=#

psql doesn't ask for a password here, because the image trusts connections made inside the container. That's only open to someone who can run docker exec, and they can do anything in the container anyway.

If you have psql installed on your computer, you can also connect through the published port, and enter the password from postgres-password.txt when you're asked for it:

psql -h 127.0.0.1 -p 5432 -U postgres

The -h option matters: without it, psql looks for a server socket file on your computer instead of connecting over TCP, and the container's socket file only exists inside the container. Connections through the published port always need the password: the image requires scram-sha-256 password authentication for connections from outside the container, and your computer's connections reach the container from the address of Docker's network gateway.

The image can also create a role and a database on the first start, through the POSTGRES_USER and POSTGRES_DB variables, but the role it creates is a superuser. This guide creates them by hand instead, in Creating a role and a database for your application, so that you can see and choose the privileges. The image's documentation lists all of its options.

Installing PostgreSQL on Linux

The PostgreSQL project publishes its own package repositories for Debian and Ubuntu (APT) and for Red Hat Enterprise Linux and compatible distributions (Yum), with packages for every supported major version. Distributions also ship their own packages, but each distribution release has one default major version for its whole life. Here is what postgresql-server (postgresql on Debian and Ubuntu) gets you from some current distributions' own repositories:

sudo apt-get install postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

Types: deb
URIs: https://apt.postgresql.org/pub/repos/apt
Suites: noble-pgdg
Components: main
Signed-By: /usr/share/postgresql-common/pgdg/apt.postgresql.org.gpg

sudo apt-get install postgresql-18

...
Creating new PostgreSQL cluster 18/main ...
/usr/lib/postgresql/18/bin/initdb -D /var/lib/postgresql/18/main --auth-local peer --auth-host scram-sha-256 --no-instructions
...

pg_lsclusters

Ver Cluster Port Status Owner    Data directory              Log file
18  main    5432 online postgres /var/lib/postgresql/18/main /var/log/postgresql/postgresql-18-main.log

sudo -u postgres psql

psql (18.6 (Ubuntu 18.6-1.pgdg24.04+2))
Type "help" for help.

postgres=#

sudo apt-get update
sudo apt-get install postgresql-18

sudo dnf install https://download.postgresql.org/pub/repos/yum/reporpms/EL-$(rpm -E %{rhel})-$(uname -m)/pgdg-redhat-repo-latest.noarch.rpm

sudo dnf -qy module disable postgresql

sudo dnf install postgresql18-server
sudo /usr/pgsql-18/bin/postgresql-18-setup initdb
sudo systemctl enable --now postgresql-18

sudo -u postgres psql

sudo dnf install postgresql-server
sudo postgresql-setup --initdb
sudo systemctl enable --now postgresql

psql: error: connection to server at "localhost" (::1), port 5432 failed: FATAL:  Ident authentication failed for user "app"

sudo sed -i 's/ident$/scram-sha-256/' /var/lib/pgsql/data/pg_hba.conf
sudo systemctl reload postgresql

Installing PostgreSQL on macOS

The PostgreSQL project lists several options for macOS. This section covers three: the Homebrew package manager, Postgres.app and the installer by EDB. Pick one: all of them run a server on port 5432, and they don't know about each other.

brew install postgresql@18
brew services start postgresql@18

echo 'export PATH="$(brew --prefix postgresql@18)/bin:$PATH"' >> ~/.zshrc

sudo mkdir -p /etc/paths.d && echo /Applications/Postgres.app/Contents/Versions/latest/bin | sudo tee /etc/paths.d/postgresapp

echo 'export PATH="/Library/PostgreSQL/18/bin:$PATH"' >> ~/.zshrc

psql -U postgres

Installing PostgreSQL on Windows

On Windows, PostgreSQL's download page links to the installer by EDB. EDB's download page has Windows installers for x86-64 only. On an ARM-based Windows PC, consider running PostgreSQL in Docker instead: the official image includes an arm64 variant, and Docker Desktop for Windows on Arm is in early access at the time of writing.

1. On PostgreSQL's Windows download page, select Download the installer. On EDB's download page, download PostgreSQL 18 for Windows x86-64.
2. Run the installer. The choices that matter for a local database are:
   • Select Components: you need PostgreSQL Server and Command Line Tools. pgAdmin 4 is a graphical client that you can leave out, and you don't need Stack Builder, a tool for installing add-ons.
   • Password: set the password for the postgres superuser role.
   • Keep the defaults for the installation directory (C:\Program Files\PostgreSQL\18), the data directory (C:\Program Files\PostgreSQL\18\data), the port (5432) and the locale.
3. At the end, clear the option to launch Stack Builder.

PostgreSQL runs as a Windows service named postgresql-x64-18. To connect, open SQL Shell (psql) from the Start menu, which asks for the connection details (press Enter to accept the defaults in square brackets) and then for your password. Or call psql by its full path in PowerShell:

& "C:\Program Files\PostgreSQL\18\bin\psql.exe" -U postgres

As on macOS, according to the installer's source, it configures the server to listen on all of your computer's network addresses. Continue with Listening only on your own machine. EDB's documentation describes the installation steps in more detail.

Securing the installation

A local database often ends up holding copies of real data, so it's worth a few minutes to make sure only you can get into it.

psql -U postgres

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  Peer authentication failed for user "postgres"

SELECT type, database, user_name, address, auth_method FROM pg_hba_file_rules;

 type  |   database    | user_name  |  address  |  auth_method
-------+---------------+------------+-----------+---------------
 local | {all}         | {postgres} |           | peer
 local | {all}         | {all}      |           | peer
 host  | {all}         | {all}      | 127.0.0.1 | scram-sha-256
 host  | {all}         | {all}      | ::1       | scram-sha-256
 local | {replication} | {all}      |           | peer
 host  | {replication} | {all}      | 127.0.0.1 | scram-sha-256
 host  | {replication} | {all}      | ::1       | scram-sha-256
(7 rows)

\password app

Enter new password for user "app":
Enter it again:

WARNING:  setting an MD5-encrypted password
DETAIL:  MD5 password support is deprecated and will be removed in a future release of PostgreSQL.
HINT:  Refer to the PostgreSQL documentation for details about migrating to another password type.

\password

sed -i '' 's/trust$/scram-sha-256/' "$(brew --prefix)/var/postgresql@18/pg_hba.conf"
brew services restart postgresql@18

ALTER SYSTEM SET listen_addresses = 'localhost';

SHOW listen_addresses;

 listen_addresses
------------------
 localhost
(1 row)

Creating a role and a database for your application

Your applications shouldn't connect as a superuser. Instead, give each application its own database and a role that can only work with that database. If the application's credentials leak, or a bug runs the wrong statement, the damage stays inside that one database.

In PostgreSQL, users and groups are both roles; a user is a role that is allowed to log in. Log in as the superuser with the method from your installation:

• Docker: docker exec -it postgres psql -U postgres
• Debian, Ubuntu, RHEL-compatible and Fedora packages: sudo -u postgres psql
• Homebrew and Postgres.app: psql postgres
• EDB installer: psql -U postgres on macOS, or SQL Shell (psql) on Windows

Create the role, give it a password, and create a database that it owns:

CREATE ROLE app LOGIN;
\password app
CREATE DATABASE app OWNER app;

postgres=# CREATE ROLE app LOGIN;
CREATE ROLE
postgres=# \password app
Enter new password for user "app":
Enter it again:
postgres=# CREATE DATABASE app OWNER app;
CREATE DATABASE

The statements do the following:

• CREATE ROLE app LOGIN creates a role named app that can log in. By default, a new role isn't a superuser and can't create databases or other roles.
• \password app sets its password, as described in Setting passwords safely.
• CREATE DATABASE app OWNER app creates an empty database named app and makes the app role its owner.

The owner matters because of how PostgreSQL handles the public schema, where tables go by default. Since PostgreSQL 15, only the database's owner (and superusers) can create objects in the public schema of a new database, where earlier versions let every role do that. If you create the database with the superuser as its owner and only grant the application privileges on it, for example with GRANT ALL PRIVILEGES ON DATABASE app TO app, the application can connect, but its first CREATE TABLE fails with ERROR: permission denied for schema public. The schema documentation describes the other ways to set this up, for example if a separate role runs your schema migrations.

You can check the new role's attributes with \du:

\du app

     List of roles
 Role name | Attributes
-----------+------------
 app       |

An empty Attributes column means that the role has none of the special attributes, such as Superuser or Create DB.

Now log in as the new role. How you connect depends on your installation:

• Debian, Ubuntu, RHEL-compatible and Fedora packages: psql -h localhost -U app app. The -h localhost option makes psql connect over TCP, where the server asks for the password. Without it, psql connects through the socket file, where peer authentication refuses the login with FATAL: Peer authentication failed for user "app", because your operating system user isn't called app.
• Docker: psql -h 127.0.0.1 -U app app from your computer, or docker exec -it postgres psql -U app app, which doesn't ask for the password.
• macOS: psql -h localhost -U app app. With Homebrew and Postgres.app, you're only asked for a password after you've switched to password authentication.
• Windows: & "C:\Program Files\PostgreSQL\18\bin\psql.exe" -h localhost -U app app in PowerShell. In SQL Shell (psql), enter app as the database and the username.

Create a table, and check that the role can use it, but can't read the stored password hashes, create databases or roles, or create tables in another database:

CREATE TABLE notes (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, body text NOT NULL);
INSERT INTO notes (body) VALUES ('hello');
SELECT * FROM notes;
SELECT rolname, rolpassword FROM pg_authid;
CREATE DATABASE other;
CREATE ROLE other;
\c postgres
CREATE TABLE notes (id int);

app=> CREATE TABLE notes (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, body text NOT NULL);
CREATE TABLE
app=> INSERT INTO notes (body) VALUES ('hello');
INSERT 0 1
app=> SELECT * FROM notes;
 id | body
----+-------
  1 | hello
(1 row)

app=> SELECT rolname, rolpassword FROM pg_authid;
ERROR:  permission denied for table pg_authid
app=> CREATE DATABASE other;
ERROR:  permission denied to create database
app=> CREATE ROLE other;
ERROR:  permission denied to create role
DETAIL:  Only roles with the CREATEROLE attribute may create roles.
app=> \c postgres
You are now connected to database "postgres" as user "app".
postgres=> CREATE TABLE notes (id int);
ERROR:  permission denied for schema public
LINE 1: CREATE TABLE notes (id int);
                     ^

The errors are what you want to see. The role can still connect to the postgres database, as every role can by default, but it can't create anything there.

Applications usually connect with a connection URI, which for this role looks like postgresql://app:YOUR_PASSWORD@localhost:5432/app. If your password contains characters such as /, @ or :, they must be percent-encoded; the guide to connection URIs explains how. To learn more about roles and privileges, read how to manage roles and how to manage privileges. To connect from other tools, see connecting to PostgreSQL databases.

Managing the server and finding its files

Where PostgreSQL keeps its configuration, data and log depends on how you installed it:

The configuration files are postgresql.conf for the server's settings and pg_hba.conf for its authentication rules. Wherever PostgreSQL came from, a superuser can ask the server where they are:

SHOW config_file;
SHOW hba_file;
SHOW data_directory;

                  config_file
-----------------------------------------------
 /var/lib/postgresql/18/docker/postgresql.conf
(1 row)

                 hba_file
-------------------------------------------
 /var/lib/postgresql/18/docker/pg_hba.conf
(1 row)

        data_directory
-------------------------------
 /var/lib/postgresql/18/docker
(1 row)

This output is from the Docker image. After you change pg_hba.conf, reload the server's configuration (for example, sudo systemctl reload postgresql). Some settings in postgresql.conf, including listen_addresses, only take effect when you restart the server.

On Linux, sudo systemctl disable postgresql (postgresql-18 with PostgreSQL's Yum repository) stops the server from starting at boot, if you'd rather start it only when you need it.

Removing PostgreSQL

Removing PostgreSQL's programs generally leaves your data in place, so you can reinstall without losing it. Delete the data directory yourself once you're sure you don't need it; if you might, export your data first.

• Docker: remove the container, then the volume. Removing the volume deletes all of your databases. Delete postgres-password.txt too.

  docker rm -f postgres
  docker volume rm postgres-data

• Debian and Ubuntu: remove the server and the packages that were installed with it. The cluster's data directory, /var/lib/postgresql/18/main, and its configuration in /etc/postgresql/18/main stay.

  sudo apt-get remove postgresql-18
  sudo apt-get autoremove

  sudo apt-get purge postgresql-18 asks whether to delete the cluster too. The default answer, Yes, deletes all of your databases along with their configuration and logs. To stop using PostgreSQL's repository, also delete /etc/apt/sources.list.d/pgdg.sources.

• RHEL and compatible: remove the server and the repository's release package. dnf also removes the client and libraries that were installed with the server. /var/lib/pgsql/18 stays.

  sudo dnf remove postgresql18-server pgdg-redhat-repo

• Fedora: remove the server. /var/lib/pgsql/data stays.

  sudo dnf remove postgresql-server

• Homebrew: stop the service and uninstall the formula. The data directory in $(brew --prefix)/var/postgresql@18 isn't part of the formula and stays.

  brew services stop postgresql@18
  brew uninstall postgresql@18

• Postgres.app: quit Postgres.app and move it to the Trash. Your data stays in ~/Library/Application Support/Postgres. The Postgres.app documentation lists what else to remove, including the /etc/paths.d/postgresapp file.

• EDB installer: on macOS, open /Library/PostgreSQL/18/uninstall-postgresql.app. On Windows, uninstall PostgreSQL 18 from the list of installed apps in Windows' settings. According to EDB's documentation, the uninstaller leaves the data directory in place, and on macOS also the postgres user it created.