PostgreSQL for Beginners: A Step-by-Step Guide to Database Management

If you have learned the basics of SQL and want a real database to practice on, PostgreSQL is one of the best places to start. It is free, open source, and trusted by startups and large companies alike. It also follows the SQL standard closely, so what you learn here carries over to other databases.

PostgreSQL (often called “Postgres”) is a relational database management system. That means it stores data in tables made of rows and columns, links those tables together, and lets you ask questions about the data with SQL. A database management system, or DBMS, is the software that handles the hard parts for you: saving data safely, controlling who can see it, and answering queries quickly.

This guide walks through the beginner workflow for managing a database. You will install PostgreSQL, connect to it, create a database and tables, add some data, set up a limited user, and back everything up and restore it. Every example uses one small bookstore database, so each step builds on the last.

By the end, you will have:

• A working PostgreSQL server on your computer
• A bookstore database with two linked tables and sample data
• A limited user that an application could safely connect with
• A backup file, and proof that you can restore it

Why Use PostgreSQL?

PostgreSQL is a strong choice for a first serious database because:

• It is free and open source. There are no license fees, and a large community maintains it.
• It follows the SQL standard. The skills you build here also help with MySQL, SQL Server, and others.
• It protects your data. It supports full transactions, so a group of changes either all succeed or all fail.
• It handles real workloads. It powers everything from small web apps to large analytics systems.
• It has rich features. Advanced data types such as JSON, arrays, and dates with time zones are built in, so you can grow into them later.

What You Need Before You Start

• A computer running Windows, macOS, or Linux
• A terminal (Command Prompt, PowerShell, Terminal, or any shell)
• About 45 minutes

You do not need to know how to code. If SQL is new to you, it helps to read SQL JOINs Explained: A Beginner’s Guide alongside this one.

How PostgreSQL Is Organized

Before you install anything, it helps to know how the pieces fit together. Beginners often mix these up:

So the full name of a table is really database.schema.table, though you normally only type the table name. Users (called roles) belong to the whole server, not to a single database, which is why you grant them access to each database separately.

Step 1: Install PostgreSQL

Pick the option that matches your computer. PostgreSQL releases a new major version every year, and older versions stop receiving fixes after five years. The steps below work on any current version. You can check which versions are supported on the PostgreSQL versioning page.

Windows

1. Download the installer from the official PostgreSQL download page.
2. Run it and keep the default components. This includes the server, pgAdmin (a graphical tool), and the command-line tools.
3. When asked, set a password for the postgres user. This is the main administrator account, so write the password down.
4. Keep the default port, 5432.

When it finishes, search the Start menu for SQL Shell (psql). If psql is not recognized in a regular terminal later, see the troubleshooting table near the end of this guide.

macOS

With Homebrew, install a current version and start it as a background service:

brew install postgresql@17
brew services start postgresql@17

Important for Mac users: Homebrew creates the administrator account with your Mac username, not postgres, and it does not set a password. This means there is no user called postgres on your machine. That is normal. In the steps below, use the macOS commands shown instead of the ones with -U postgres.

Ubuntu or Debian Linux

sudo apt update
sudo apt install postgresql

On Ubuntu, the installer creates the postgres account and starts the service for you. That account has no password by default. You reach it with sudo, as shown in the next step.

With Docker

If you prefer not to install anything on your computer, you can run PostgreSQL in a container. My guide to Docker for beginners explains the basics, and the steps are similar to my guide on running MySQL in Docker:

docker run --name my-postgres \
  -e POSTGRES_PASSWORD=change-me \
  -p 5432:5432 \
  -v pgdata:/var/lib/postgresql/data \
  -d postgres:17

The -v pgdata:... line stores your data in a Docker volume, so it survives if you remove the container.

Check the Installation

For a native install, confirm the tools are available:

psql --version

You should see a version number such as psql (PostgreSQL) 17.x.

Step 2: Connect with psql

psql is PostgreSQL’s command-line client. It is installed with the server. Because every system sets up the administrator account a little differently, use the row that matches your setup:

On Windows, you will be asked for the password you set during installation.

You will see a prompt that looks like postgres=# (or yourname=# on a Mac). The # means you are connected as a superuser. Commands that start with a backslash, such as \l, are psql shortcuts and not SQL. Every SQL statement must end with a semicolon.

Here are the shortcuts you will use most:

Step 3: Create a Database

A single PostgreSQL server can hold many databases. Create one for the bookstore and connect to it:

CREATE DATABASE bookstore;
\c bookstore

The prompt now reads bookstore=#, so everything you run next happens inside this database.

Step 4: Create Tables

Tables hold your data. Before you write any SQL, decide what to store and how the pieces relate. If you want to go deeper on this, read Database Design: A Beginner’s Guide to Planning It Right.

Our bookstore has authors and books. One author can write many books, so each book points back to its author.

CREATE TABLE authors (
    author_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name      TEXT NOT NULL,
    country   TEXT
);

CREATE TABLE books (
    book_id        INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title          TEXT NOT NULL,
    author_id      INTEGER NOT NULL REFERENCES authors (author_id),
    price          NUMERIC(8, 2) NOT NULL CHECK (price >= 0),
    published_year SMALLINT,
    in_stock       BOOLEAN NOT NULL DEFAULT TRUE,
    added_at       TIMESTAMPTZ NOT NULL DEFAULT now()
);

Here is what each part does:

• GENERATED ALWAYS AS IDENTITY makes PostgreSQL number the rows for you (1, 2, 3, and so on).
• PRIMARY KEY uniquely identifies each row.
• NOT NULL means the column cannot be left empty.
• REFERENCES authors (author_id) is a foreign key. PostgreSQL will refuse a book whose author does not exist.
• CHECK (price >= 0) blocks negative prices.
• DEFAULT fills in a value when you do not provide one.

Common Data Types

Use NUMERIC for money, never FLOAT. Floating-point numbers can introduce small rounding errors.

Check your work with \dt and \d books.

Step 5: Add Data

Insert the authors first, because books refer to them:

INSERT INTO authors (name, country) VALUES
    ('Octavia Butler', 'United States'),
    ('Gabriel García Márquez', 'Colombia'),
    ('Chinua Achebe', 'Nigeria');

INSERT INTO books (title, author_id, price, published_year, in_stock) VALUES
    ('Kindred', 1, 14.99, 1979, TRUE),
    ('Parable of the Sower', 1, 16.50, 1993, TRUE),
    ('One Hundred Years of Solitude', 2, 18.00, 1967, TRUE),
    ('Things Fall Apart', 3, 12.95, 1958, FALSE);

The author_id values (1, 2, and 3) match the numbers PostgreSQL generated for the authors, in the order you inserted them.

Check that the data is there:

SELECT * FROM books;

You should see four rows. This guide focuses on managing the database, so it keeps queries short. When you are ready to write more advanced ones, see SQL JOINs Explained and Mastering SQL Subqueries.

Step 6: Manage Users and Permissions

Do not connect your applications as the administrator account. Create a limited user (PostgreSQL calls it a role) that can only do what the app needs:

CREATE ROLE app_user WITH LOGIN PASSWORD 'change-me';

GRANT CONNECT ON DATABASE bookstore TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

Run these while connected to the bookstore database. The last line only covers tables that exist right now, so repeat it after you add new tables. Use a strong password, and keep it in a .env file instead of your code.

List all users with \du. To check the new one works, connect as that user. This needs the psql tool on your own computer, so Docker users should install the client or run the command from the container:

psql -U app_user -h localhost -d bookstore

Enter the password when asked. Then try something the user is not allowed to do:

SELECT count(*) FROM books;
DROP TABLE books;

The first command works and the second fails with an error such as must be owner of table books. That error is the limited account doing its job. It can read and write the data, but it cannot destroy the table or create other users.

Your Connection String

Applications and tools usually ask for one connection string instead of separate settings. It has this shape:

postgresql://app_user:your-password@localhost:5432/bookstore

That is user:password@host:port/database. Store it in your .env file or your hosting provider’s secret settings, never in the code itself. If your password contains special characters such as @ or /, they must be URL-encoded.

Who Can Connect from Where?

By default, PostgreSQL only accepts connections from the same computer. Two settings control this: listen_addresses in postgresql.conf, and the rules in pg_hba.conf. Do not change them just to make something work. If you ever need remote access, use a firewall, require encrypted connections, and allow only the addresses you trust.

A Note About the Public Schema

In PostgreSQL 15 and later, ordinary users can no longer create objects in the public schema. That is why the GRANT USAGE ON SCHEMA public line matters, and why app_user can read the tables but not create new ones.

Limited accounts also reduce the damage from attacks such as SQL injection. To learn how that attack works and how to prevent it, read SQL Injection: How It Works and How to Prevent It.

Step 7: Back Up and Restore Your Database

Backups are the most important habit in database management. PostgreSQL ships with pg_dump for this. Run these commands from your normal terminal, not inside psql. Use the group that matches your setup.

Windows and macOS

On Windows, add -U postgres -h localhost to each command. On macOS, leave those out.

# Create a backup in a compact format
pg_dump -U postgres -h localhost -d bookstore -F c -f bookstore.dump

# Restore it into a new, empty database
createdb -U postgres -h localhost bookstore_copy
pg_restore -U postgres -h localhost -d bookstore_copy bookstore.dump

Ubuntu or Debian

The postgres account cannot write to your home folder, so send the output to your own file with > and read it back with <:

sudo -u postgres pg_dump -F c bookstore > bookstore.dump

sudo -u postgres createdb bookstore_copy
sudo -u postgres pg_restore -d bookstore_copy < bookstore.dump

Docker

The dump is created inside the container, so copy it out with docker cp:

docker exec my-postgres pg_dump -U postgres -F c -f /tmp/bookstore.dump bookstore
docker cp my-postgres:/tmp/bookstore.dump ./bookstore.dump

docker cp ./bookstore.dump my-postgres:/tmp/bookstore.dump
docker exec my-postgres createdb -U postgres bookstore_copy
docker exec my-postgres pg_restore -U postgres -d bookstore_copy /tmp/bookstore.dump

If you leave off -F c, you get a plain SQL text file instead, which you can open in any editor and restore with psql -f. The compact -F c format is smaller and gives you more options when restoring.

Check the Restore

After restoring, check that the copy has your data. Connect to it with the same command you used in Step 2, adding the database name:

\dt
SELECT count(*) FROM books;

You should see the authors and books tables, and a count of 4. When you are done testing, you can remove the copy with DROP DATABASE bookstore_copy;. Run it from a different database, such as postgres, because you cannot drop the database you are connected to.

What a Backup Does and Does Not Include

• pg_dump backs up one database, including its tables, data, and permissions on those tables. It does not include users (roles), which belong to the whole server. To save them too, run pg_dumpall --globals-only.
• The file is saved in the folder you ran the command from. It does not stay inside the database, so copy it to another drive or cloud storage.
• Restore with the same or a newer version of PostgreSQL than the one that made the backup. Restoring a newer dump into an older server often fails.

Stop Typing the Password Every Time

On Windows, pg_dump asks for the postgres password every time. You can set it for the current terminal session:

# macOS, Linux, or Git Bash
export PGPASSWORD='your-password'

# Windows PowerShell
$env:PGPASSWORD = 'your-password'

For something permanent and safer, use a password file. On Linux and macOS it is ~/.pgpass (run chmod 600 ~/.pgpass), and on Windows it is %APPDATA%\postgresql\pgpass.conf. Each line has this format:

localhost:5432:bookstore:postgres:your-password

Never put real passwords in a script you share or commit.

Automate Your Backups

A backup you have to remember to run is a backup you will forget. Here is a nightly backup at 2 a.m. on Linux or macOS. Open the schedule with crontab -e and add:

0 2 * * * pg_dump -F c -f /home/you/backups/bookstore-$(date +\%F).dump bookstore

On Ubuntu, add it to the postgres account’s schedule instead (sudo crontab -u postgres -e) and write to a folder that account can use. On Windows, create a basic task in Task Scheduler that runs your pg_dump command every night. Keep a few recent copies, and store one on a different computer or in cloud storage.

A backup you have never restored is only a guess. Try a restore at least once, so you know the process works before you need it.

Troubleshooting Common Problems

Quick Reference

Best Practices for Beginners

• Name things clearly. Use lowercase names with underscores, such as published_year. Mixed-case names need double quotes everywhere, which gets annoying.
• Give every table a primary key.
• Use the right data types. NUMERIC for money, TIMESTAMPTZ for times, BOOLEAN for yes or no.
• Add constraints. NOT NULL, CHECK, and foreign keys stop bad data from getting in.
• Use a limited user for apps, never the administrator account.
• Back up before risky changes, automate regular backups, and test your restores.
• Keep PostgreSQL updated, since new minor versions include security and bug fixes.

Common Mistakes to Avoid

• Never testing a restore. A backup that fails to restore is no backup at all.
• Forgetting the semicolon. If psql shows bookstore-# instead of bookstore=#, it is waiting for you to finish the statement.
• Using FLOAT for money. Rounding errors add up.
• Committing passwords to Git. A password in a script or config file is readable by anyone with access to the repository, so keep it out of version control.
• Opening the database to the whole internet. By default PostgreSQL only listens on the local machine. Leave it that way unless you have a firewall and a specific reason.
• Keeping the only backup on the same computer. If the disk fails, you lose both the database and the backup.

Practice Exercises

Try these on your own. They use only what you learned above:

1. Add a fourth author and one of their books. Check the result with SELECT * FROM books;.
2. Create a read-only user called reader_user that can only SELECT. Connect as that user and prove that an INSERT fails.
3. Run the full disaster drill: back up bookstore, drop the database (from the postgres database), restore it, and confirm that the count of books is still correct.
4. Write down your own connection string for app_user and store it in a .env file.

Where to Go Next

Once you are comfortable with these basics, good next steps include:

• Learning to query your data with SQL JOINs and subqueries
• Speeding up slow queries with indexes (see why your SQL query is slow)
• Using transactions (BEGIN, COMMIT, ROLLBACK) so a group of changes succeeds or fails together
• Connecting PostgreSQL to a Python app with psycopg or SQLAlchemy
• Trying a graphical tool such as pgAdmin or DBeaver

Conclusion

PostgreSQL gives you a reliable, free, and standards-based database to learn on. In this guide you installed it, connected with psql, created a database and linked tables, added data, created a limited user, and made a backup that you restored and checked.

The best way to make these skills stick is to build something. Take a small idea you care about, such as a book list, a budget tracker, or the contacts for a client project, design two or three tables for it, and manage it the same way: create it, load it, back it up, and restore it. Each time you do, the commands will feel more natural, and you will have a real foundation for data analysis, web development, or database administration.