DEV Community

Cover image for How to Install PostgreSQL and Get Started (Beginner's Guide)
Peter Gatitu Mwangi
Peter Gatitu Mwangi

Posted on

How to Install PostgreSQL and Get Started (Beginner's Guide)

Learn to set up PostgreSQL step by step - no confusing tech jargon

PostgreSQL is a free database that stores your app's data. Think of it like a super organized filing cabinet for your website or app. Today, I'll show you exactly how to install it and start using it.

Why PostgreSQL?

Most apps need to save data somewhere. PostgreSQL is:

  • Free (saves you money)
  • Reliable (won't lose your data)
  • Popular (lots of help online)

Big companies like Instagram and Spotify use it, so you know it works.


Step 1: Install PostgreSQL

If you use Linux (Ubuntu):

sudo apt update
Enter fullscreen mode Exit fullscreen mode

This updates your computer's software list (like checking for new apps in an app store)

sudo apt install postgresql postgresql-contrib
Enter fullscreen mode Exit fullscreen mode

This installs PostgreSQL and some helpful tools

sudo service postgresql start
Enter fullscreen mode Exit fullscreen mode

This starts PostgreSQL running on your computer

Check if it worked:

psql --version
Enter fullscreen mode Exit fullscreen mode

You should see something like "psql 14.9" - that's the version number

If you use Mac:

First, install Homebrew (it's like an app store for your terminal):

/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"
Enter fullscreen mode Exit fullscreen mode

Then install PostgreSQL:

brew install postgresql
brew services start postgresql
Enter fullscreen mode Exit fullscreen mode

If you use Windows:

  1. Go to postgresql.org
  2. Click "Download"
  3. Download the Windows installer
  4. Run it and click "Next" until done

Step 2: Connect to Your Database

Think of this like logging into your filing cabinet.

On Linux:

sudo -i -u postgres
psql
Enter fullscreen mode Exit fullscreen mode

PostgreSQL creates a special user account called "postgres" - we switch to that user first

On Mac/Windows:

psql postgres
Enter fullscreen mode Exit fullscreen mode

You'll see something like postgres=# - that means you're in!


Step 3: Create Your First User

Right now, only the "postgres" user can access your database. Let's create your own account.

CREATE USER john WITH PASSWORD 'mypassword123';
Enter fullscreen mode Exit fullscreen mode

Replace "john" with your name and use a strong password

Why do this? It's like giving someone their own key to the filing cabinet instead of sharing the master key.

Now create a database:

CREATE DATABASE my_first_app;
Enter fullscreen mode Exit fullscreen mode

Give your user access to it:

GRANT ALL PRIVILEGES ON DATABASE my_first_app TO john;
Enter fullscreen mode Exit fullscreen mode

This lets "john" read, write, and modify the "my_first_app" database

Connect to your new database:

\c my_first_app
Enter fullscreen mode Exit fullscreen mode

Step 4: Give Schema Permissions

Schemas are like folders inside your database. PostgreSQL creates a default schema called "public" for every database.

Why this matters: Your user needs permission to create tables and store data in the schema.

Give your user schema permissions:

GRANT ALL ON SCHEMA public TO john;
Enter fullscreen mode Exit fullscreen mode

This lets "john" create tables and manage data in the "public" schema

Give permissions to future tables:

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO john;
Enter fullscreen mode Exit fullscreen mode

This automatically gives "john" access to any new tables created in the future

Why do both? The first command gives access to existing stuff. The second gives access to new stuff you create later.

Real example: Think of it like this - you give someone keys to a building (schema permission), then you also give them keys to any new rooms that get built (future table permissions).

Check your permissions:

\dn+
Enter fullscreen mode Exit fullscreen mode

Shows all schemas and who has access to them


Step 5: Basic Configuration (Optional)

PostgreSQL has two important settings files. Think of them as the rule books.

PostgreSQL has two important settings files. Think of them as the rule books.

Find your settings files:

SHOW config_file;
SHOW hba_file;
Enter fullscreen mode Exit fullscreen mode

This shows you where the files are located

Change basic settings:

Open the main config file:

sudo nano /path/to/postgresql.conf
Enter fullscreen mode Exit fullscreen mode

Common changes:

port = 5433
Enter fullscreen mode Exit fullscreen mode

Changes the "door number" PostgreSQL uses (default is 5432)

max_connections = 100
Enter fullscreen mode Exit fullscreen mode

How many people can connect at once (like seats in a restaurant)

Control who can connect:

Open the access file:

sudo nano /path/to/pg_hba.conf
Enter fullscreen mode Exit fullscreen mode

Change this line:

local   all             all                                     md5
Enter fullscreen mode Exit fullscreen mode

This makes people enter a password to connect (more secure)

Apply changes:

sudo service postgresql restart
Enter fullscreen mode Exit fullscreen mode

Like restarting your computer after installing something new


Quick Commands to Remember

\l          -- Show all databases (like seeing all your folders)
\c dbname   -- Switch to a database (like opening a folder)
\dt         -- Show tables (like seeing files in a folder)
\dn+        -- Show schemas and permissions (like seeing folder permissions)
\q          -- Quit (like closing the program)
Enter fullscreen mode Exit fullscreen mode

Common Problems and Fixes

"Can't connect to server"

  • PostgreSQL isn't running
  • Fix: sudo service postgresql start

"Authentication failed"

  • Wrong username/password
  • Fix: Check your username and password

"Permission denied for schema"

  • Your user can't create tables
  • Fix: GRANT ALL ON SCHEMA public TO yourusername;

"Port already in use"

  • Something else is using port 5432
  • Fix: Change the port number in settings

What You Just Learned

You now have:

  • PostgreSQL installed and running
  • Your own user account
  • A database ready for your app
  • Basic knowledge of how to configure it

Next Steps

  1. Learn basic SQL commands (SELECT, INSERT, UPDATE)
  2. Connect PostgreSQL to a simple app
  3. Learn about database design

PostgreSQL might seem scary at first, but you just completed the hardest part - getting it set up! Now you can start building apps that save data.


Did this help you? Hit the clap button and follow for more beginner-friendly tech tutorials!

Top comments (0)