Install PostgreSQL on Ubuntu and Set Up pgAdmin Step by Step

PostgreSQL is not the flashiest tool in a developer’s stack, but it is often the most important one. When data integrity, performance under load, and long-term reliability matter, it tends to be the database teams reach for. Ubuntu, on the other hand, is the operating system that powers a significant share of the servers those databases run on, making the two a natural pairing. 

This guide covers the full setup process from start to finish. You will learn how to install PostgreSQL on Ubuntu using either the official APT repository or the default Ubuntu package archive, how to create users and databases, how to get pgAdmin running as a graphical management interface, and which commands are worth knowing before you start working with your data. 

What Is PostgreSQL

PostgreSQL, often called Postgres, is an open-source object-relational database system that’s been in active development for over 35 years. It supports both SQL for relational queries and JSON for non-relational data, making it a flexible choice for a wide range of applications. The PostgreSQL database engine is known for its standards compliance, extensibility, and robustness under heavy workloads. 

The current stable PostgreSQL version at the time of writing is PostgreSQL 18.3. Each major version introduces performance improvements, new SQL features, and enhanced developer tooling. Staying on a supported version matters for both security patches and access to new capabilities. 

PostgreSQL vs. Other SQL Databases

When comparing the PostgreSQL database to alternatives like MySQL or MariaDB, a few differences stand out. PostgreSQL is fully ACID-compliant and supports advanced data types such as arrays, hstore, and JSONB natively. It handles complex queries and concurrent write operations more gracefully than many of its counterparts. 

MySQL tends to be simpler to configure for basic use cases and has a large ecosystem around it, particularly in shared hosting environments. PostgreSQL offers more sophisticated features for applications that need fine-grained control over data integrity, custom functions, or advanced indexing strategies. PostgreSQL commands also tend to follow the SQL standard more closely, which is useful when working across multiple database systems. 

For teams building data-intensive applications, analytics platforms, or systems that require strong transactional guarantees, PostgreSQL is frequently the better fit. 

Prerequisites Before You Install PostgreSQL

Before running any installation commands, a few things need to be in place. You’ll need a machine running Ubuntu with a user account that has sudo privileges. A stable internet connection is required if you’re pulling packages from an external repository. You should also have basic familiarity with the Linux command line, since the setup process involves running shell commands and editing configuration files. 

Supported Ubuntu Versions and VPS Requirements

PostgreSQL supports all current Ubuntu LTS releases. The most commonly used versions are Ubuntu 20.04 LTS (Focal Fossa), Ubuntu 22.04 LTS (Jammy Jellyfish), and Ubuntu 24.04 LTS (Noble Numbat). If you’re working on a VPS, the minimum recommended specifications are 1 GB of RAM and at least 10 GB of disk space, though production workloads will typically require significantly more depending on the size of your dataset and query volume. 

Most major cloud providers, including DigitalOcean, Linode, Vultr, and AWS, offer Ubuntu LTS images that are fully compatible with both installation methods covered below. If your VPS requirements include running PostgreSQL alongside a web server or application runtime, factor in additional memory accordingly. 

How to Install PostgreSQL on Ubuntu

There are two main ways to install PostgreSQL on Ubuntu. The first uses the official PostgreSQL APT repository, which gives you access to the latest versions. The second installs the version that ships with Ubuntu’s default package archive, which is slightly easier to set up but may not be the most current release. 

Option 1: Install via APT Repository

Using the official PostgreSQL APT repository is the recommended approach when you need a specific or up-to-date PostgreSQL version. This method adds the PostgreSQL Global Development Group’s repository to your system sources and installs directly from there. 

Start by installing the prerequisites needed to add the repository securely: 

Next, create the directory for storing repository signing keys if it doesn’t already exist, then download and store the PostgreSQL signing key: 

sudo install -d /usr/share/postgresql-common/pgdg

sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc 

Now add the official PostgreSQL APT repository to your system’s source list. The following command automatically detects your Ubuntu version and configures the correct repository entry: 

sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' 

Update your package index and install PostgreSQL:

sudo apt update
sudo apt install -y postgresql

Once the installation completes, the PostgreSQL service starts automatically. You can confirm this with: 

sudo systemctl status postgresql

Option 2: Install from Local Ubuntu Repo

If you don’t need the absolute latest PostgreSQL version and prefer to keep things simple, you can install directly from Ubuntu’s default package repository. This method requires fewer steps and is perfectly suitable for development environments or situations where version specificity isn’t a concern. 

Update your package index first: 

sudo apt update

Then install PostgreSQL along with the contrib package, which adds several useful utilities and extensions: 

sudo apt install -y postgresql postgresql-contrib

As with the APT repository method, the service will start automatically after installation. The version installed this way is tied to your Ubuntu release. For example, Ubuntu 22.04 ships with PostgreSQL 14 by default. 

Verify the PostgreSQL Installation:

Once installed, confirm that PostgreSQL is running and check which version is active on your system. To check the PostgreSQL version, run the following PostgreSQL command: 

psql --version

You should see output similar to: 

psql (PostgreSQL) 16.3

To verify the service status and ensure it’s enabled to start on boot: 

sudo systemctl status postgresql
sudo systemctl enable postgresql

To access the PostgreSQL interactive terminal directly, switch to the default postgres system user and open the psql shell: 

sudo -i -u postgres
psql

Once inside, you can run \l to list all databases, or \q to exit. A successful connection to the psql prompt confirms the installation is working correctly. 

How to Create a PostgreSQL User and Database

By default, PostgreSQL creates a system user called postgres and a corresponding database role with the same name. For most real-world setups, you’ll want to create a dedicated PostgreSQL user and database rather than using the default account for application connections. 

Creating a New Role and Database

Switch to the postgres system user to begin managing roles: 

sudo -i -u postgres

Once in the postgres session, open the psql shell: 

psql

To create a new PostgreSQL user (referred to as a role in PostgreSQL terminology), run: 

CREATE ROLE appuser WITH LOGIN PASSWORD 'yourpassword'; 

Replace appuser with your desired username and yourpassword with a strong password. The LOGIN option allows the role to connect to the server. Without it, the role exists but can’t authenticate. 

To create a new PostgreSQL database and assign it an owner: 

CREATE DATABASE appdb OWNER appuser;

You can verify the new database exists by listing all databases: 

 \l

To check which roles have been created: 

\du

Granting Privileges to a Database User

Creating a role and a database doesn’t automatically give the role full access to every object within that database. You need to explicitly grant the necessary PostgreSQL privileges depending on what the user needs to do. 

To grant all privileges on a specific database: 

GRANT ALL PRIVILEGES ON DATABASE appdb TO appuser;

If your application needs to interact with tables within a particular schema, connect to the database first and then grant schema-level privileges: 

\c appdb
GRANT ALL ON SCHEMA public TO appuser; 
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO appuser;

For tighter access control, you can grant more granular permissions, such as SELECT only for read-only users: 

GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

Always follow the principle of least privilege. Grant only the permissions an application or user actually needs, rather than blanket superuser access. 

How to Install and Configure pgAdmin

pgAdmin is the most widely used graphical administration tool for PostgreSQL. It provides a browser-based interface for managing databases, running queries, monitoring server activity, and handling user permissions without needing to use the command line for every task. 

Installing pgAdmin on Ubuntu

The pgAdmin team maintains its own APT repository for Ubuntu, which provides the most up-to-date release. Start by installing the repository setup script: 

curl -fsS https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo gpg --dearmor -o /usr/share/keyrings/packages-pgadmin-org.gpg

sudo sh -c 'echo "deb [signed-by=/usr/share/keyrings/packages-pgadmin-org.gpg] https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list'

Update your package index and install pgAdmin in web mode: 

sudo apt update
sudo apt install -y pgadmin4-web

After the package installs, run the included setup script to configure the web server and create an initial administrator account: 

sudo /usr/pgadmin4/bin/setup-web.sh

The script will prompt you for an email address and password, which you’ll use to log in to the pgAdmin interface. Once complete, pgAdmin is accessible in your browser at: 

http://your-server-ip/pgadmin4

If you’re running this on a local machine, you can use http://localhost/pgadmin4 instead. 

Connecting pgAdmin to Your PostgreSQL Server

After logging in to pgAdmin, you need to register your PostgreSQL server so the tool knows where to connect. In the left-hand browser panel, right-click on Servers and select Register, then Server. 

In the General tab, give your connection a name, such as Local PostgreSQL or the name of your project. Switch to the Connection tab and fill in the following fields: 

Host name/address: 127.0.0.1 (or your server's IP address if connecting remotely) 

Port: 5432 

Maintenance database: postgres 

Username: the PostgreSQL user you created earlier (or postgres for the default) 

Password: the password for that user

Click Save to establish the connection. If the credentials are correct and the PostgreSQL service is running, the server will appear in the browser panel and you can start exploring your databases, running queries through the Query Tool, and managing roles through the graphical interface. 

If you’re connecting to a remote PostgreSQL instance, make sure the server’s pg_hba.conf file is configured to accept connections from your IP address, and that port 5432 is open in your firewall. 

Essential PostgreSQL Commands to Know

Once PostgreSQL is installed and running, a working knowledge of the most common PostgreSQL commands will save you significant time. Below is a reference of the commands used most frequently in day-to-day database administration. 

Meta-Commands

Inside the psql shell, meta-commands begin with a backslash: 

\l – list all databases

\c dbname – connect to a specific database 

\dt – list all tables in the current database 

\du – list all roles and users 

\d tablename – describe the structure of a table 

\q – quit the psql shell 

SQL Options

For SQL operations, the following are the building blocks you’ll use most often: 

To create a table:

CREATE TABLE users (id SERIAL PRIMARY KEY, name VARCHAR(100), email TEXT UNIQUE NOT NULL);

To insert a record:

INSERT INTO users (name, email) VALUES ('Alice', '[email protected]');

To query records:

SELECT * FROM users;

To update a record:

UPDATE users SET name = 'Alice Smith' WHERE id = 1;

To delete a record:

DELETE FROM users WHERE id = 1;

To drop a table entirely:

DROP TABLE users;

To back up a database from the shell (outside psql):

pg_dump -U appuser -d appdb > appdb_backup.sql

To restore from a backup:

psql -U appuser -d appdb < appdb_backup.sql

These cover the majority of tasks you’ll encounter when managing a PostgreSQL setup, whether you’re developing locally or maintaining a production database. 

PostgreSQL Troubleshooting Common Issues

Even with a straightforward installation process, you may encounter issues the first time you set up PostgreSQL. Most problems fall into a small number of categories and have well-established solutions. 

 Connection Refused Errors and Fixes

A “connection refused” error when trying to connect to PostgreSQL usually points to one of three causes: the service isn’t running, the wrong host or port is being used, or the pg_hba.conf file is blocking the connection. 

First, confirm the service is running: 

sudo systemctl start postgresql

Then check that you’re connecting to the correct host and port. By default, PostgreSQL listens on 127.0.0.1 port 5432. If you need to allow external connections, open the postgresql.conf file and update the listen_addresses parameter: 

sudo nano /etc/postgresql/16/main/postgresql.conf

Find the line: 

#listen_addresses = 'localhost'

Change it to: 

listen_addresses = '*'

Next, edit pg_hba.conf to allow connections from the appropriate IP ranges: 

sudo nano /etc/postgresql/16/main/pg_hba.conf

Add a line like the following at the bottom to allow a specific IP address to connect with password authentication: 

host    all             all             192.168.1.0/24          scram-sha-256 

After saving changes to either file, restart PostgreSQL for them to take effect: 

sudo systemctl restart postgresql 

If you’re using a firewall such as UFW, make sure port 5432 is open: 

sudo ufw allow 5432/tcp 

For connections that are still being refused after these steps, double-check that the PostgreSQL user you’re authenticating with actually has the LOGIN privilege and that the password is correct.  

FAQ: PostgreSQL on Ubuntu