How To Install PostgreSQL on Ubuntu

PostgreSQL, often referred to as Postgres, is an open-source object-relational database management system. It has earned a strong reputation for its reliability, feature robustness, and adherence to SQL standards. Key features include reliable transactions (ACID compliance) and high concurrency without read locks, making it a popular choice for complex, data-driven applications.

This quickstart guide focuses on the essential steps to get a PostgreSQL server up and running on an Ubuntu system. We will walk through installing the necessary packages, using the default “roles” system for authentication, and creating a new role and database. For a more in-depth guide on configuration, security, and management, please refer to the full tutorial on How To Install and Use PostgreSQL on Ubuntu 22.04.

To follow along with this tutorial, you will need one Ubuntu server that has been configured by following our Initial Server Setup for Ubuntu guide. After completing this prerequisite tutorial, your server should have a non-root user with sudo permissions and a basic firewall.

To install PostgreSQL, first refresh your server’s local package index:

sudo apt update

Then, install the postgres package along with a -contrib package that adds some additional utilities and functionality:

sudo apt install postgresql postgresql-contrib

You can check the version by running the following command:

psql --version

Ensure that the service is started:

sudo systemctl start postgresql.service

By default, Postgres uses a concept called “roles” to handle authentication and authorization. These are, in some ways, similar to regular Unix-style users and groups.

Upon installation, Postgres is set up to use ident authentication, meaning that it associates Postgres roles with a matching Unix/Linux system account. If a role exists within Postgres, a Unix/Linux username with the same name is able to sign in as that role.

The installation procedure created a user account called postgres that is associated with the default Postgres role. There are a few ways to utilize this account to access Postgres. One way is to switch over to the postgres account on your server by running the following command:

sudo -i -u postgres

Then you can access the Postgres prompt by running:

psql

This will log you into the PostgreSQL prompt, and from here you are free to interact with the database management system right away.

To exit out of the PostgreSQL prompt, run the following:

\q

This will bring you back to the postgres Linux command prompt. To return to your regular system user, run the exit command:

exit

Another way to connect to the Postgres prompt is to run the psql command as the postgres account directly with sudo:

sudo -u postgres psql

This will log you directly into Postgres without the intermediary bash shell in between.

Again, you can exit the interactive Postgres session by running the following:

\q

If you are logged in as the postgres account, you can create a new role by running the following command:

createuser --interactive

If, instead, you prefer to use sudo for each command without switching from your normal account, run:

sudo -u postgres createuser --interactive

Either way, the script will prompt you with some choices and, based on your responses, execute the correct Postgres commands to create a user to your specifications.

Output
Enter name of role to add: sammy
Shall the new role be a superuser? (y/n) y

Another assumption that the Postgres authentication system makes by default is that for any role used to log in, that role will have a database with the same name which it can access.

This means that if the user you created in the last section is called sammy, that role will attempt to connect to a database which is also called “sammy” by default. You can create the appropriate database with the createdb command.

If you are logged in as the postgres account, you would type something like the following:

createdb sammy

If, instead, you prefer to use sudo for each command without switching from your normal account, you would run:

sudo -u postgres createdb sammy

The createuser and createdb shell commands are convenient helpers. However, for more control, you can perform these same actions directly within PostgreSQL using SQL commands. This approach is often clearer when setting passwords or granting specific permissions from the start.

1. First, connect as the administrative postgres user:

   sudo -u postgres psql
   
   

   Once at the PostgreSQL prompt, you can use CREATE ROLE and CREATE DATABASE to set up your new user and database.

2. Create a New Role (User): While the createuser shell command is interactive, the CREATE ROLE command lets you define everything in one statement. To create a user named sammy that can log in (LOGIN) and has a password, run:

   CREATE ROLE sammy WITH LOGIN PASSWORD 'your_strong_password';
   
   

   If this user will also need to create databases, you can grant that permission at the same time:

   CREATE ROLE sammy WITH LOGIN PASSWORD 'your_strong_password' CREATEDB;
   
   

   This is equivalent to answering “yes” to the superuser question in the interactive helper, though SUPERUSER is a much broader and more dangerous permission. Granting CREATEDB is safer if that’s all the user needs.

3. Create a New Database: Next, create the database. It’s good practice to assign ownership of the new database to the new role you just created.

   CREATE DATABASE sammydb OWNER sammy;
   
   

   If you are already connected to the sammy database and want to grant all privileges on it to the sammy user, you would run:

   GRANT ALL PRIVILEGES ON DATABASE sammydb TO sammy;
   
   

4. Exit the postgres session: You can now exit the psql prompt for the postgres user.

   \q
   
   

Because you set a password, this new user can authenticate using methods other than ident, which is necessary for remote connections.

To log in with ident based authentication, you’ll need a Linux user with the same name as your Postgres role and database.

If you don’t have a matching Linux user available, you can create one with the adduser command. You will have to do this from your non-root account with sudo privileges (meaning, not logged in as the postgres user):

sudo adduser sammy

Once this new account is available, you can either switch over and connect to the database by running the following:

sudo -i -u sammy
psql

Or, you can do this inline:

sudo -u sammy psql

This command will log you in automatically, assuming that all of the components have been properly configured.

If you want your user to connect to a different database, you can do so by specifying the database like the following:

psql -d postgres

Once logged in, you can check your current connection information by running:

\conninfo

Output
You are connected to database "sammy" as user "sammy" via socket in "/var/run/postgresql" at port "5432".

By default, PostgreSQL only listens for connections from the local machine (localhost). To allow other computers to connect to your database, you must perform three steps:

1. Configure PostgreSQL to listen on its public IP address.
2. Add a host-based authentication (HBA) rule to allow remote connections for your user.
3. Open port 5432 in your server’s firewall.

Step 1: Edit postgresql.conf

First, find your main PostgreSQL configuration file. On Ubuntu, this is typically located at /etc/postgresql/17/main/postgresql.conf. The version number (e.g., 17) may vary.

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

Inside this file, find the listen_addresses line. By default, it is set to localhost.

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = 'localhost'    # what IP address(es) to listen on;

Change localhost to * to listen on all available IP addresses, or set it to your server’s public IP address for more specificity.

listen_addresses = '*'

Save and close the file.

Step 2: Edit pg_hba.conf

Next, you must configure the “host-based authentication” file, pg_hba.conf, to tell PostgreSQL how to authenticate remote users.

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

At the bottom of this file, add a new line to allow connections for your user from a specific IP address. The ident method will not work for remote TCP/IP connections. You must use a password-based method like scram-sha-256 or md5.

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# Example for remote access:
host    sammydb         sammy           198.51.100.5/32         scram-sha-256

Let’s break down this line:

• host: Specifies a connection using a TCP/IP socket (a network connection).
• sammydb: The database name.
• sammy: The role (user) name.
• 198.51.100.5/32: The IP address of the remote machine. /32 means this rule applies only to this single IP. Use 198.51.100.0/24 to allow all IPs in that subnet. Never use 0.0.0.0/0 in production.
• scram-sha-256: A secure, modern password-based authentication method. This requires that your user has a password, which you can set using the SQL method (CREATE ROLE ... PASSWORD ... or ALTER ROLE sammy WITH PASSWORD 'new_password';).

Warning: Never hardcode passwords in scripts or documentation. The 'new_password' value above is a placeholder—always use a strong, unique password and manage it securely (for example, using a password manager or environment variables). Avoid sharing or storing plaintext passwords in version control or public places.

Step 3: Open Firewall and Restart Service

With the configuration files updated, allow traffic on port 5432 through UFW:

sudo ufw allow 5432/tcp

Finally, restart the PostgreSQL service to apply all changes:

sudo systemctl restart postgresql

You can now test the connection from your remote machine (e.g., 198.51.100.5):

psql -h your_server_ip -U sammy -d sammydb

You will be prompted for the password you set for the sammy role.

A regular backup strategy is non-negotiable for any database. PostgreSQL provides two main methods for backups: logical and physical.

Logical Backups with pg_dump

The pg_dump utility creates a “logical” backup by generating a file containing SQL commands. When this file is run, it recreates the database, tables, and data.

• Pros: Flexible, human-readable (as plain text), can be restored onto different machine architectures or PostgreSQL major versions. Ideal for single databases.
• Cons: Can be slow for very large databases.

To back up a single database: The custom format (-F c) is compressed and is the recommended format for most use cases.

pg_dump -U sammy -W -F c -f sammydb.dump sammydb

• -U sammy: Connect as the sammy user.
• -W: Prompt for the user’s password.
• -F c: Output in the custom (compressed) format.
• -f sammydb.dump: Write the output to a file named sammydb.dump.
• sammydb: The name of the database to back up.

To restore from a custom-format dump: You must use the pg_restore utility. This command will not work if the database newdb already exists and has tables in it.

• First, create a new, empty database:

  createdb -U sammy newdb
  
  

• Then, restore the dump into it:

  pg_restore -U sammy -W -d newdb sammydb.dump
  
  

• -d newdb: Restore into the database named newdb.

Physical Backups and Point-in-Time Recovery (PITR)

A physical backup is a binary-level copy of the entire database cluster’s data files. This method is used in conjunction with Write-Ahead Logs (WALs) to enable Point-in-Time Recovery (PITR).

PITR allows you to restore your database to any specific moment since your last base backup (for example, to five minutes before a user accidentally dropped a major table).

This is an advanced strategy with two main components:

1. Base Backup: A full physical copy of the database, taken using a tool like pg_basebackup.
2. WAL Archiving: The database is configured to continuously copy its transaction logs (WAL files) to a separate storage location.

To enable this, you must edit postgresql.conf:

wal_level = replica       # Minimum level for WAL archiving
archive_mode = on         # Enables archiving
archive_command = 'cp %p /path/to/wal/archive/%f'  # A command to copy WAL files

The archive_command is a simple example; production setups use more reliable tools like wal-g or pgBackRest to manage this process.

Beyond the secure remote access settings discussed earlier, a production database requires several additional layers of security.

Enforcing SSL/TLS Connections

If you transmit sensitive data over a network, you must encrypt the connection.

1. Enable SSL in postgresql.conf: Uncomment the ssl line and set it to on. You must also provide paths to your certificate and private key files.

   # - SSL -
   ssl = on
   ssl_cert_file = '/etc/ssl/certs/your_server_cert.pem'
   ssl_key_file = '/etc/ssl/private/your_server_key.key'
   

   For testing, you can generate a self-signed certificate, but for production, you should use a certificate from a trusted authority.

2. Enforce SSL in pg_hba.conf: To require that remote connections use SSL, change the host record type to hostssl. A connection using host can use SSL, but it is not mandatory. A hostssl connection will be rejected if it does not use SSL.

   # TYPE     DATABASE        USER            ADDRESS                 METHOD
   
   # Reject if not using SSL
   hostssl    sammydb         sammy           198.51.100.5/32         scram-sha-256
   
   # Allow local connections without SSL (optional)
   host       all             all             127.0.0.1/32            scram-sha-256
   

Principle of Least Privilege

Never use the postgres superuser role for your application. This role can bypass all permissions and drop the entire database cluster.

Instead, create specific roles for each application or user, granting only the permissions they need.

Example: Create a read-only user for an analytics application.

1. Create the role. This role cannot log in.

   CREATE ROLE analytics_user;
   
   

2. Grant it CONNECT access to your database.

   GRANT CONNECT ON DATABASE sammydb TO analytics_user;
   
   

3. Grant it USAGE on the schema (e.g., the public schema).

   GRANT USAGE ON SCHEMA public TO analytics_user;
   
   

4. Grant it SELECT permissions on specific tables.

   GRANT SELECT ON my_table, another_table TO analytics_user;
   
   

Auditing

You can log database activity by setting parameters in postgresql.conf. This is useful for tracking changes or investigating issues.

For more detailed auditing, consider the pgaudit extension, which provides fine-grained logging of session and object-level activities.

While the installation is straightforward, new users often encounter issues related to PostgreSQL’s distinct authentication model. Here are the most common pitfalls and their solutions.

Pitfall 1: FATAL: Peer authentication failed for user "sammy"

This is the most common error. It occurs when you try to connect as a user (e.g., psql -U sammy) but your current Linux username does not match the requested PostgreSQL role.

• Cause: By default, PostgreSQL is set up to use ident authentication, which is aliased as peer in the pg_hba.conf file. This method requires your operating system username to match the PostgreSQL role name for local connections.

• Solution: Do not connect as your normal user. Instead, use sudo to run the command as the matching OS user.

  1. First, ensure a PostgreSQL role named sammy exists (Step 3).
  2. Second, ensure a Linux user named sammy exists (Step 5).
  3. Finally, connect using sudo -u sammy psql. This command logs you in automatically because the OS user “sammy” matches the Postgres role “sammy”.

Pitfall 2: FATAL: role "sammy" does not exist

This is a simpler problem. It means you are trying to log in with a role that hasn’t been created inside PostgreSQL.

• Cause: You may have created the Linux user adduser sammy but forgot to create the corresponding PostgreSQL role createuser sammy.
• Solution: Log in as the default postgres user and create the role as shown in Step 3.

Pitfall 3: Firewall Blocks Connection

If you have a firewall enabled, as recommended in the prerequisites, it may block connections.

• Cause: The UFW (Uncomplicated Firewall) is active but not configured to allow PostgreSQL traffic.

• Solution: This only affects remote connections. Local connections over the default Unix socket are not blocked by UFW. If you intend to enable remote access, you must explicitly allow traffic on PostgreSQL’s default port, 5432.

  sudo ufw allow 5432/tcp
  
  

  Only run this command if you are intentionally and securely enabling remote access.

While both are relational databases, their initial setup and authentication models on Ubuntu differ significantly.

The main takeaway is that PostgreSQL’s setup is heavily tied to local Unix users and permissions out of the box, making it very secure locally but requiring more steps for remote network access. MySQL’s model is more centralized around its root user and password authentication from the start.

1. How do I check if PostgreSQL is running on Ubuntu?

You can check the status of the PostgreSQL service using the systemctl command.

Open your terminal and run the following command:

sudo systemctl status postgresql.service

If the service is active, the output will show active (running) in green. If it is inactive or has failed, it will provide details on its state.

● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (running) since Wed 2025-10-22 14:30:00 IST; 5min ago
   Main PID: 12345 (postgres)
      Tasks: 6 (limit: 4617)
     Memory: 150.0M
     CGroup: /system.slice/postgresql.service
             ├─12345 /usr/lib/postgresql/17/bin/postgres -D /var/lib/postgresql/17/main -c config_file=/etc/postgresql/17/main/postgresql.conf
...

2. How can I connect to the PostgreSQL shell?

You can connect using the psql command-line utility. By default, PostgreSQL uses ident authentication, which means it associates database roles with matching system user accounts.

The simplest way to connect is to first switch to the postgres Linux user account, which is created during installation and has superuser access to the database.

1. Switch to the postgres user:

   sudo -i -u postgres
   
   

2. Open the PostgreSQL shell:

   psql
   
   

You will be logged in and see the postgres=# prompt. To exit the shell, type \q and press Enter.

3. How do I enable remote connections securely?

Enabling remote connections requires two main configuration changes: updating the server’s listening address and setting an authentication rule for remote users.

1. Update postgresql.conf: First, find your configuration file. This is typically located at /etc/postgresql/<VERSION>/main/postgresql.conf. Open this file with a text editor:

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

   Inside the file, find the listen_addresses line. By default, it is set to localhost. To allow connections from all IP addresses, change it to '*':

   #------------------------------------------------------------------------------
   # CONNECTIONS AND AUTHENTICATION
   #------------------------------------------------------------------------------
   
   # - Connection Settings -
   
   listen_addresses = '*'        # what IP address(es) to listen on;
   

   Save and close the file.

2. Update pg_hba.conf: Next, edit the “host-based authentication” file, pg_hba.conf, located in the same directory:

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

   Add a new line to the bottom of the file to allow a specific user to connect from a specific IP address or range. Use a secure password method like scram-sha-256 (or md5 for older versions).

   # TYPE  DATABASE        USER            ADDRESS                 METHOD
   
   # Allow user 'sammy' to connect from '203.0.113.10' using a password
   host    all             sammy           203.0.113.10/32         scram-sha-256
   

3. Set a User Password: If your database user (like sammy) does not have a password, remote authentication will fail. Connect to psql and set one:

   ALTER USER sammy WITH PASSWORD 'your_secure_password';
   
   

4. Allow Traffic Through the Firewall: Ensure your firewall allows traffic on the default PostgreSQL port, 5432.

   sudo ufw allow 5432/tcp
   
   

5. Restart the Service: Finally, apply all changes by restarting PostgreSQL:

   sudo systemctl restart postgresql
   
   

4. What’s the default PostgreSQL port on Ubuntu?

The default TCP port for PostgreSQL is 5432.

This is the standard port the server “listens” on for new client connections. If a firewall is active on the server, you must open this port to allow connections, whether from the local machine (for some configurations) or from remote clients.

5. How do I uninstall PostgreSQL completely?

To completely remove PostgreSQL, including its packages, configuration files, and data, you must first uninstall the packages and then manually delete the directories.

1. Stop the PostgreSQL Service:

   sudo systemctl stop postgresql
   
   

2. Uninstall Packages: Use apt purge to remove the PostgreSQL packages and their system-wide configuration files.

   sudo apt purge postgresql postgresql-contrib postgresql-17
   
   

   Replace postgresql-17 with your specific version if needed.

3. Remove Data and Configuration Directories: The purge command does not remove the database cluster data or the main configuration directory. You must remove these manually:

   sudo rm -rf /etc/postgresql/
   sudo rm -rf /var/lib/postgresql/
   
   

After these steps, PostgreSQL will be completely removed from your system.

This guide has demonstrated the essential steps to install and configure PostgreSQL on an Ubuntu server. We covered the package installation, the use of ident authentication with roles, and the creation of a new user and database using both shell commands and direct SQL. We also explored the procedures for enabling remote access, securing connections, and implementing basic backup strategies.

With this setup complete, you now have a functional database server. While this quickstart covers the fundamentals, your next steps will likely involve designing your database schema or migrating existing data. For more in-depth learning on database management and SQL, you can refer to the following resources:

• How To Install and Use PostgreSQL on Ubuntu 20.04
• How To Move a PostgreSQL Data Directory to a New Location on Ubuntu 20.04
• How To Use PostgreSQL with your Django Application on Ubuntu 20.04
• A comparison of relational database management systems
• Practice running queries with SQL