Installing PostgreSQL and Basic Settings

01What Is PostgreSQL and Why Choose It?

PostgreSQL is a free, open-source relational database system with a history of nearly forty years. Thanks to transaction safety (ACID), powerful data types, JSONB support and advanced indexing options, it is used everywhere from small projects to high-traffic applications.

PostgreSQL is a comfortable choice for data where consistency is critical, such as reservations, inventory, orders or listings. That is why transaction safety matters in systems like a hotel software platform or an e-commerce platform, where many people touch the same record at the same time. If you just want to try PHP and a database locally in a hurry, installing XAMPP is also a handy starting point. MySQL is also widely used for similar work; if you want to compare the two, take a look at our MySQL installation guide.

In this article we cover everything you will need on day one after installation: creating roles and databases, remote connection settings, backups and the error messages you are most likely to meet.

02What You Need Before Installing

Settling a few decisions before you start reduces problems later. On a production server in particular, decide up front which major version you will use; moving between major versions requires a dump and restore, or pg_upgrade.

The installation method matters too. On a development machine, Docker is the easiest way to run different versions side by side across projects without conflicts. On a production server, a service installed with the operating system's package manager gives you a more predictable setup for receiving security updates, monitoring logs and managing automatic startup.

• A user with administrator rights (Windows) or sudo (macOS, Linux)
• The default port 5432 not being used by another application
• The current supported major version, listed on the official site: postgresql.org
• A strong password for the superuser, saved somewhere safe
• A working Docker installation, if you will install with Docker

03Installation by Operating System

Each of the methods below installs from official or officially recommended sources. Replace the version placeholder in the commands with the major version you chose.

Windows

Download the installer prepared by EDB from the Windows page of postgresql.org. The wizard installs the server, pgAdmin, the command-line tools and Stack Builder together, and asks you for the postgres user's password, the port and the locale.

After installation, add the bin folder to the PATH variable so you can use the psql command in any terminal. The SQL Shell (psql) shortcut in the Start menu does the same job.

# The folder name changes with the major version you installed
$env:Path += ";C:\Program Files\PostgreSQL\<version>\bin"
psql --version
psql -U postgres -h localhost

macOS (Homebrew)

On Homebrew, PostgreSQL comes as version-numbered packages. First list the available packages, then install the major version you want and start it as a service. If you prefer a graphical interface, Postgres.app is also a popular alternative.

brew search postgresql
brew install postgresql@<version>
brew services start postgresql@<version>
# Versioned packages may not be added to PATH automatically;
# apply the echo 'export PATH=...' line from the installation output
psql --version
psql postgres

Ubuntu and Debian

The package in the distribution's own repository is enough for most projects. If you need a newer major version, you can add the PGDG repository described on postgresql.org and install from there.

sudo apt update
sudo apt install postgresql postgresql-contrib
sudo systemctl enable --now postgresql
sudo systemctl status postgresql
sudo -u postgres psql -c "SELECT version();"

Docker

For a development environment, the official postgres image is the cleanest route; you can start and stop the database without installing a permanent service on your computer. Always mount a named volume so your data is not lost.

• In official images version 18 and later, the volume is mounted at /var/lib/postgresql.
• In version 17 and earlier images, mount the volume at /var/lib/postgresql/data; check the note on the image's Docker Hub page.

docker run -d --name pg \
  -e POSTGRES_PASSWORD=GucluBirParola \
  -p 5432:5432 \
  -v pgdata:/var/lib/postgresql \
  postgres:<version>

docker exec -it pg psql -U postgres

04Creating a User and a Database

The postgres role that comes with the installation is a superuser; connecting your application with it is not safe. For each project, create a separate role with privileges only on its own database.

In PostgreSQL, a user and a role are the same concept: a role with the LOGIN privilege behaves like a user. A role you make the owner of a database can create tables in that database's public schema.

CREATE ROLE uygulama WITH LOGIN PASSWORD 'BurayaGucluParola';
CREATE DATABASE magaza OWNER uygulama ENCODING 'UTF8';

-- Read-only reporting user
CREATE ROLE rapor WITH LOGIN PASSWORD 'BaskaGucluParola';
GRANT CONNECT ON DATABASE magaza TO rapor;
\c magaza
GRANT USAGE ON SCHEMA public TO rapor;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO rapor;
ALTER DEFAULT PRIVILEGES FOR ROLE uygulama IN SCHEMA public
  GRANT SELECT ON TABLES TO rapor;

05The Most Useful psql Commands

psql is PostgreSQL's command-line client. Meta-commands that start with a backslash are not sent to the server as SQL; the client itself interprets them, and they speed up navigation a great deal.

psql -h localhost -U uygulama -d magaza
# With a connection string
psql "postgresql://uygulama@localhost:5432/magaza"

06Managing PostgreSQL with pgAdmin

pgAdmin is the official, free administration tool for PostgreSQL. It ships with the Windows installer; on macOS and Linux it is installed separately from pgadmin.org or run from its Docker image.

On first launch, define a new server with Register > Server: on the Connection tab enter localhost as the host, port 5432, and your username and password. Once connected, you can browse tables, run queries with the Query Tool, and perform backup and restore operations from the menu.

If you are connecting to a remote server, it is safer to use pgAdmin's SSH tunnel option than to expose the database port to the internet.

07Remote Access and Core Settings

By default PostgreSQL listens only for local connections. Because the location of the configuration files varies by operating system and version, first find out where they are from inside psql.

The listen_addresses value in postgresql.conf sets the addresses to listen on, while pg_hba.conf decides which client is authenticated and by which method. For password connections use scram-sha-256, and keep the IP range you allow as narrow as possible.

• shared_buffers: the official documentation recommends roughly a quarter of memory as a starting point on a dedicated server.
• max_connections: instead of setting it high, use connection pooling on the application side.
• To find slow queries, enable the log_min_duration_statement setting and analyze them with EXPLAIN ANALYZE.

SHOW config_file;
SHOW hba_file;
SHOW data_directory;

# TYPE  DATABASE  USER       ADDRESS          METHOD
host    magaza    uygulama   203.0.113.10/32  scram-sha-256

# A reload is enough for a pg_hba.conf change
sudo systemctl reload postgresql
# A listen_addresses change requires a restart
sudo systemctl restart postgresql

08Backups and Common Errors

Schedule automatic backups as soon as installation is done. pg_dump takes a consistent copy of a single database; a custom-format backup can be restored selectively with pg_restore. Store the backup in a different location, not on the same server.

Database performance directly affects the overall speed of an application. Results come when query times, indexes and server response are handled together; we apply this holistic approach in our speed and performance optimization work.

pg_dump -h localhost -U uygulama -F c -f magaza.dump magaza
pg_restore -h localhost -U uygulama -d magaza --clean magaza.dump