How to Install and Use PostgreSQL on Ubuntu 24.04

Introduction

Unlock the power of robust data management on your Ubuntu server with PostgreSQL! As a leading Linux database, PostgreSQL stands out as an open-source, advanced relational-document database system renowned for its performance, stability, and extensibility. Whether you’re building enterprise-level applications or managing complex data volumes, PostgreSQL delivers unmatched reliability and data integrity. This comprehensive guide will walk you through installing PostgreSQL 18 on Ubuntu 24.04 LTS, setting up the indispensable pgAdmin 4, and mastering essential commands for efficient database administration. Dive in to harness the full potential of this powerful open-source RDBMS.

Setting Up PostgreSQL 18 on Ubuntu 24.04 LTS

While Ubuntu typically includes a specific “snapshot” of PostgreSQL in its default repositories, accessing the latest features often requires a direct approach. For PostgreSQL 18 on Ubuntu 24.04, we’ll configure the official PostgreSQL Apt repository to ensure you get the most up-to-date version.

Installing PostgreSQL 18

First, enable the PostgreSQL Apt repository, which provides automated updates for newer versions:

sudo apt install -y postgresql-common ca-certificates
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

Now, update your package list and proceed with the installation of PostgreSQL 18:

sudo apt update
sudo apt install postgresql-18 postgresql-contrib-18

Verifying PostgreSQL Service Status

Once the installation is complete, the PostgreSQL service should start automatically. Confirm its operational status and ensure it’s configured to launch on system boot:

sudo systemctl status postgresql
sudo systemctl enable postgresql

Streamlining PostgreSQL Administration with pgAdmin 4

For those who prefer a graphical interface for database management, pgAdmin 4 is the official web-based tool. It simplifies tasks like managing databases, users, and executing queries.

Adding the pgAdmin 4 Repository

pgAdmin 4 is not in Ubuntu’s default repositories. Let’s add its official repository:

sudo apt install -y curl ca-certificates gnupg
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://apt.pgadmin.org/apt/ $(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'

Installing pgAdmin 4 (Desktop or Web)

You can choose to install pgAdmin 4 for desktop use, web access, or both:

# Install for both desktop and web modes
sudo apt install pgadmin4

# Install for desktop mode only
sudo apt install pgadmin4-desktop

# Install for web mode only
sudo apt install pgadmin4-web

# Configure the webserver, if you installed pgadmin4-web:
sudo /usr/pgadmin4/bin/setup-web.sh

After installing the web version, access pgAdmin 4 by navigating to your server’s IP address or hostname in your web browser (usually on port 80 or 443, configured by the setup script).

Mastering PostgreSQL Roles and Database Management

Effective database administration hinges on understanding how PostgreSQL manages access through roles and databases.

Understanding PostgreSQL’s Authentication & Roles

PostgreSQL uses roles to manage access permissions. A role can function as a database user, a group of users, or both. By default, client authentication for local connections is often set to “peer” method in /etc/postgresql/18/main/pg_hba.conf, which links database users to operating system users. The default administrative user is postgres, created without a password during installation.

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

Roles are powerful and can:

• Own database objects (tables, views, functions, etc.).
• Assign privileges on objects to other roles.
• Grant membership in a role to another role (role inheritance).

Configuring Encrypted Passwords for Roles

For enhanced security, especially for non-postgres users, configure roles to use encrypted passwords. Edit pg_hba.conf to change the authentication method from peer to scram-sha-256 (recommended for modern security) or md5:

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

Locate lines similar to:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     peer

And modify them, for example, to require scram-sha-256 for all non-postgres local users:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer
local   all             all                                     scram-sha-256

After making changes, always restart the PostgreSQL service to apply them:

sudo systemctl restart postgresql

Creating and Managing PostgreSQL Roles

You can interact with PostgreSQL through its command-line client, psql. Access it as the postgres system user:

sudo -i -u postgres
psql # to launch the postgres shell program

Alternatively, launch psql directly:

sudo -u postgres psql

Exit the psql shell by typing \q or pressing Ctrl+D.

Here’s how to create and manage roles:

• • Create a basic role:

CREATE ROLE tecmint;

• • Create a login role (user):

CREATE ROLE tecmint LOGIN;
-- Or simply:
CREATE USER tecmint;

• • Create a role with a password:

CREATE ROLE tecmint WITH LOGIN PASSWORD 'secure_password_here';
-- Or:
CREATE USER tecmint WITH PASSWORD 'secure_password_here';

• • Create a role with superuser privileges (use with caution):

CREATE ROLE admin WITH LOGIN PASSWORD 'admin_password' SUPERUSER;

• • Create a role that can create databases:

CREATE ROLE developer WITH LOGIN PASSWORD 'dev_password' CREATEDB;

• • List existing roles:

\du              -- shows actual users with details
SELECT rolname FROM pg_roles;
\du+             -- for more detailed information

• • Change a role’s password:

ALTER ROLE tecmint WITH PASSWORD 'new_password';

• • Grant/revoke superuser privileges:

ALTER ROLE tecmint WITH SUPERUSER;
ALTER ROLE tecmint WITH NOSUPERUSER;

• • Drop a role: (Note: A role cannot be dropped if it owns database objects.)

DROP ROLE tecmint;

Database Creation and Ownership

Once you have roles, you can create databases and assign ownership:

• • Create a database:

CREATE DATABASE tecmint;

• • Create a database owned by a specific role:

CREATE DATABASE tecmint OWNER tecmint;

• • Create a database with specific encoding and collation:

CREATE DATABASE tecmint ENCODING 'UTF8' LC_COLLATE='en_US.UTF-8' LC_CTYPE='en_US.UTF-8' OWNER tecmint;

Granting Role Privileges

Grant all necessary privileges to your new role for its assigned database:

GRANT ALL PRIVILEGES ON DATABASE tecmint TO tecmint;

Now, you can connect to and manage the tecmint database as the tecmint role:

psql -U tecmint -d tecmint

Essential PostgreSQL Operations: From Tables to Data

Let’s explore the fundamental operations for building and interacting with your database tables.

Creating and Populating Tables

Define your data structure by creating tables, then fill them with data:

• • Create a table (e.g., authors):

CREATE TABLE authors (
    code      SERIAL PRIMARY KEY,
    name      VARCHAR(40) NOT NULL,
    city      VARCHAR(40) NOT NULL,
    joined_on DATE NOT NULL
);

Using SERIAL automatically generates unique, sequential numbers for the primary key.

• • Insert data into the table:

INSERT INTO authors (name, city, joined_on) VALUES
    ('Ravi Saive', 'Mumbai', '2012-08-15'),
    ('Aaron Kili', 'Nairobi', '2014-03-20'),
    ('Matei Cezar', 'Bucharest', '2015-06-10');

Querying and Manipulating Data (SELECT, UPDATE, DELETE)

Interact with your data using standard SQL commands:

• • View all data:

SELECT * FROM authors;

• • Select specific columns:

SELECT name, city FROM authors;

• • Filter data:

SELECT * FROM authors WHERE city = 'Mumbai';

• • Order results:

SELECT * FROM authors ORDER BY joined_on DESC;

• • Update existing data:

UPDATE authors SET city = 'Delhi' WHERE name='Ravi Saive';

• • Delete specific rows:

DELETE FROM authors WHERE name='Ravi Saive';

Managing Tables and Their Structure

PostgreSQL offers robust commands to manage your tables:

• • List tables in the current database:

\dt
\dt+   -- for more detailed information

• • Describe table structure:

\d authors

• • Add a new column:

ALTER TABLE authors ADD COLUMN email VARCHAR(100);

• • Drop a column:

ALTER TABLE authors DROP COLUMN email;

• • Rename a column:

ALTER TABLE authors RENAME COLUMN code TO author_id;

• • Delete/Drop a table: (CASCADE removes dependent objects)

DROP TABLE authors;
DROP TABLE authors CASCADE;

Database Listing and Deletion

Manage your collection of databases efficiently:

• • List all databases:

SELECT datname FROM pg_database;
\list
\l

• • Delete/Drop a database: (Warning: This is permanent!)

DROP DATABASE tecmint;

Switching Between Databases

Easily navigate between databases within your psql session:

\connect database_name
\c database_name

Optimizing and Securing Your PostgreSQL Deployment

Beyond basic setup, proper configuration for remote access and performance is crucial for any production-ready PostgreSQL instance.

Enabling Remote Connections

By default, PostgreSQL restricts connections to localhost. To allow remote access, modify postgresql.conf:

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

Change:

#listen_addresses="localhost"

To:

listen_addresses="*"

Next, configure pg_hba.conf to specify which hosts can connect:

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

Add a line allowing connections from your network (replace 192.168.1.0/24 with your actual network subnet):

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             192.168.1.0/24          scram-sha-256

Or, less securely, allow from any IP (use with extreme caution):

host    all             all             0.0.0.0/0               scram-sha-256

Configuring Firewall Access

Ensure your server’s firewall permits incoming connections to PostgreSQL’s default port (5432/tcp):

sudo ufw allow 5432/tcp

Finally, restart PostgreSQL to apply all network configuration changes:

sudo systemctl restart postgresql

Basic Performance Tuning Parameters

For better performance on Ubuntu 24.04, consider adjusting these settings in postgresql.conf based on your server’s RAM and workload:

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

Recommended adjustments:

• shared_buffers = 256MB (typically 25% of RAM)
• effective_cache_size = 1GB (50-75% of RAM)
• maintenance_work_mem = 64MB
• checkpoint_completion_target = 0.9
• wal_buffers = 16MB
• default_statistics_target = 100
• random_page_cost = 1.1
• effective_io_concurrency = 200
• work_mem = 4MB

After modifying, restart PostgreSQL:

sudo systemctl restart postgresql

Unique Tip: To diagnose and optimize slow queries, use the EXPLAIN ANALYZE command before your SQL query. For instance, `EXPLAIN ANALYZE SELECT * FROM authors WHERE city = ‘Mumbai’;` This will not only show the query plan but also execute the query and provide actual runtime statistics, helping you pinpoint bottlenecks.

PostgreSQL Database Maintenance and Troubleshooting

Regular backups and a handy reference of commands are crucial for smooth database operations.

Backup and Restore Strategies

Safeguard your data with these essential backup and restore commands:

• • Backup a single database:

pg_dump -U postgres tecmint > tecmint_backup.sql

• • Backup all databases:

pg_dumpall -U postgres > all_databases_backup.sql

• • Restore a database:

psql -U postgres tecmint < tecmint_backup.sql

Quick Reference: Essential psql Commands

Here’s a quick list of commonly used psql meta-commands:

Conclusion

This guide has walked you through installing and utilizing PostgreSQL 18 on your PostgreSQL Ubuntu 24.04 LTS server. From initial setup and pgAdmin 4 integration to mastering roles, database operations, remote access, and performance tuning, you now have a solid foundation for managing this powerful database. PostgreSQL’s robust features make it an excellent choice for a wide range of applications. Feel free to share your questions or insights in the comments below, and explore the PostgreSQL 18 Official Documentation for deeper dives into specific topics.

Read the original article