How to Install and Configure PostgreSQL on Ubuntu?


Ubuntu

This tutorial guides you through step by step instructions to install PostgreSQL on Ubuntu, including system preparation, secure installation, basic configuration, and accessing the database, with practical examples.

PostgreSQL, an advanced open-source database system, is renowned for its robustness, flexibility, and performance. It supports both SQL (relational) and JSON (non-relational) querying, making it highly versatile for various data storage needs. Renowned for its stability and scalability, PostgreSQL is ideal for handling large datasets and complex queries, which makes it a preferred choice for many businesses and large-scale applications. This tutorial will walk you through the process of installing PostgreSQL on Ubuntu, ensuring you are set up for a smooth and secure database experience.

 To install PostgreSQL on Ubuntu using the apt package manager and configure it properly, follow these steps:

 

1. Update Packages

Before you begin, it’s a good idea to update your system’s package index. Open a terminal and run following command using sudo privilege:

sudo apt update

 

2. Listing Available Versions

To list the available versions of PostgreSQL in the repository, you can use the apt-cache command. This allows you to see all the versions of PostgreSQL that you can install. To do this, run:

apt-cache showpkg postgresql

Look for the section labeled "Versions". Under this section, you will find entries that represent the available versions of PostgreSQL

For example:

Package: postgresql
Versions: 
14+238

If you wish to continue with this version then you can skip the next step, otherwise we can add the official postgresql repository to get access to latest package:

 

3. Add PostgreSQL Apt Repository (Optional)

Ubuntu's default repositories include PostgreSQL, making it easy to install with just a few commands. However, these repositories might not always contain the latest version or might lack specific versions needed for certain applications. To address this, you can add the official PostgreSQL repository to your Ubuntu system, which offers a wider range of versions and more frequent updates.

Install software properties common, which provides an easier way to add new repositories.

sudo apt install software-properties-common

PostgreSQL signs its repositories with a GPG key. Import this key to your system using wget command to ensure the authenticity of the repository and convert the GPG key to a keyring format that apt can understand.

NOTE:
We could have also used apt-key add to add the GPG key but then apt-key is deprecated. This is part of a broader effort to transition away from using apt-key for managing repository keys in Debian and Ubuntu systems. So we chose to manually download and add the GPG key.
wget -qO - https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor -o postgres.gpg

Now, move this newly created postgres.gpg (which is in keyring format) to the /etc/apt/trusted.gpg.d/ directory.

sudo mv postgres.gpg /etc/apt/trusted.gpg.d/

Add the PostgreSQL repository to your system. Replace $(lsb_release -cs) with your Ubuntu version code name if you encounter issues.

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

Update the package lists:

sudo apt update -y

In my case this command was giving following message while performing apt update:

Skipping acquire of configured file 'main/binary-i386/Packages' as repository 'http://apt.postgresql.org/pub/repos/apt jammy-pgdg InRelease' doesn't support architecture 'i386

The message about skipping the acquisition of certain packages is not an error but an informational note indicating that the PostgreSQL repository does not provide packages for the 'i386' architecture. This is normal for a 64-bit system, and it shouldn't impede the installation and use of PostgreSQL.

Next let's re-list available postgresql versions on our Ubuntu server:

apt-cache showpkg postgresql

Now we can see two different versions available for installation:

Versions: 
16+256.pgdg22.04+1 (/var/lib/apt/lists/apt.postgresql.org_pub_repos_apt_dists_jammy-pgdg_main_binary-amd64_Packages)

14+238 (/var/lib/apt/lists/in.archive.ubuntu.com_ubuntu_dists_jammy_main_binary-amd64_Packages) (/var/lib/apt/lists/in.archive.ubuntu.com_ubuntu_dists_jammy_main_binary-i386_Packages)

 

4. Install PostgreSQL

Now we can use apt package manager to install the latest available postgresql package:

sudo apt install postgresql postgresql-contrib
How to Install and Configure PostgreSQL on Ubuntu?

This command installs PostgreSQL along with a set of additional utilities and functionalities (postgresql-contrib).

 

5. Start and Enable PostgreSQL Service

After installation, the PostgreSQL service should start automatically. To verify its status, run:

sudo systemctl status postgresql.service
How to Install and Configure PostgreSQL on Ubuntu?

If it's not running, start it with:

sudo systemctl start postgresql.service

To ensure PostgreSQL starts when your system boots, enable the service:

sudo systemctl enable postgresql.service

 

6. Securing and Configuring PostgreSQL

Securing your PostgreSQL database is crucial, especially when it involves setting a strong password for the default postgres user and securing remote connections.

 

6.1 Setting Up a Strong Password for the Postgres User

First, you need to access the PostgreSQL command line interface. You can do this by switching to the postgres user on your system:

sudo -u postgres psql

Once you're in the PostgreSQL CLI (you should see a postgres=# prompt), run the following command to set a new password:

ALTER USER postgres WITH PASSWORD 'new_strong_password';

Replace new_strong_password with a strong, secure password. Make sure to remember this password or store it securely, as it's essential for administrative access to your databases.

Example:

ALTER USER postgres WITH PASSWORD 'ZmqWu8gUbNY5QmQ';

 

6.2 Securing Remote Connections

If your PostgreSQL server is accessible over a network (especially the internet), securing remote connections is vital.

Open the postgresql.conf file located in the PostgreSQL directory (commonly found at /etc/postgresql/[version]/main/postgresql.conf). Use a text editor like nano or vim:

sudo nano /etc/postgresql/[version]/main/postgresql.conf

Look for the line #listen_addresses = 'localhost' and change it to:

listen_addresses = '*'

This change allows PostgreSQL to accept connections from any IP address. Be cautious with this setting; ensure your firewall and network settings are properly configured to prevent unauthorized access.

To secure PostgreSQL with a firewall, you can use either ufw (Uncomplicated Firewall) or firewalld. The specific commands for each depend on the firewall system you're using

# For UFW
sudo ufw allow 5432/tcp
sudo ufw status

# For Firewalld
sudo firewall-cmd --permanent --add-service=postgresql
sudo firewall-cmd --permanent --add-port=5432/tcp
sudo firewall-cmd --reload
sudo firewall-cmd --list-all

Next, edit the pg_hba.conf file, which is also in the PostgreSQL directory:

sudo nano /etc/postgresql/[version]/main/pg_hba.conf

Add a line to define which hosts are allowed to connect, the authentication method, and for which databases. For example:

# TYPE  DATABASE  USER  ADDRESS         METHOD
host    all       all   0.0.0.0/0       md5

This line allows all users (all) to connect to all databases (all) from any IP address (0.0.0.0/0) using MD5 password authentication.

Add the following line to allow remote_user to connect to remote_db from 192.168.1.10 using md5 authentication:

host    remote_db        remote_user     192.168.1.10/32         md5

To apply the changes, restart the PostgreSQL service:

sudo systemctl restart postgresql

 

7. Accessing the PostgreSQL Shell

Accessing and using PostgreSQL involves interacting with the database through its shell, commonly known as psql, PostgreSQL's command-line interface. Here's a guide on how to access the PostgreSQL shell and use basic commands to interact with your databases:

If you've installed PostgreSQL on a Unix/Linux system, you'll typically have a postgres user created by default. To access the PostgreSQL shell, you should switch to this user:

sudo -u postgres psql

This command launches the psql shell.

If you want to access a specific database, you can do so directly:

sudo -u postgres psql -d database_name

Replace database_name with the name of the database you want to access.

 

8. Basic and Advanced Commands for Interacting with the Database

8.1 Basic Commands - Viewing Information

Command Description
\l List all databases
\conninfo Show current database and user
\dt List all tables in the current database
\d table_name Describe a table (structure/schema)

 

8.2 Basic Commands - Data Manipulation

Command Description
INSERT INTO table_name (column1, column2) VALUES (value1, value2); Insert data into a table
UPDATE table_name SET column1 = value1 WHERE condition; Update data in a table
DELETE FROM table_name WHERE condition; Delete data from a table
SELECT * FROM table_name WHERE condition; Select data from a table

 

8.3 Basic Commands - Transaction Control

Command Description
BEGIN; Begin a transaction
COMMIT; Commit a transaction
ROLLBACK; Rollback a transaction

 

8.4 Advanced Commands - Indexing and Performance

Command Description
CREATE INDEX index_name ON table_name (column_name); Create an index on a table
EXPLAIN SELECT * FROM table_name WHERE column_name = value; Explain a query plan
VACUUM (VERBOSE, ANALYZE) table_name; Vacuum (clean and optimize) a database

 

8.5 Advanced Commands - User and Access Management

Command Description
CREATE USER user_name WITH PASSWORD 'password'; Create a new user
GRANT ALL PRIVILEGES ON database_name TO user_name; Grant privileges to a user
REVOKE ALL PRIVILEGES ON database_name FROM user_name; Revoke privileges from a user
ALTER ROLE user_name SET default_transaction_read_only = on; Alter user roles and privileges

8.6 Advanced Commands - Querying

Command Description
WITH cte_name AS (SELECT * FROM table_name) SELECT * FROM cte_name; Use Common Table Expressions (CTE)
SELECT column_name, AVG(another_column) OVER (PARTITION BY column_name) FROM table_name; Use Window Functions

 

8.7 Advanced Commands - Database Administration

Command Description
pg_dump database_name > backup_file.sql Backup a database
pg_restore -d database_name backup_file.sql Restore a database
ALTER DATABASE database_name SET parameter TO value; Alter database settings

 

8.8 Advanced Commands - Working with JSON and Arrays

Command Description
SELECT * FROM table_name WHERE json_column->>'key' = 'value'; Query JSON data
SELECT * FROM table_name WHERE array_column @> ARRAY['value1','value2']; Work with array data

 

9. Loading Sample Database

Loading a sample database into PostgreSQL is a great way to familiarize yourself with its features and practice SQL queries. A commonly used sample database is the "DVD Rental" database. Below are the steps to download, unzip, restore it, and verify the sample data:

First, you need to download the sample database file. The DVD Rental database can typically be found as a .zip file.

 curl -O https://www.postgresqltutorial.com/wp-content/uploads/2019/05/dvdrental.zip

This command uses curl to download the dvdrental.zip file.

After downloading the file, you need to unzip it to extract the .tar file containing the actual database backup.

unzip dvdrental.zip
sudo mv dvdrental.tar /tmp

This command unzips the file, and you should now have a dvdrental.tar file in your /tmp/ directory.

Before restoring the database, make sure PostgreSQL is running. Then, create a new database to which you'll restore the sample database.

Create New Database: This creates a new database named dvdrental.

sudo -u postgres createdb dvdrental

Restore the Database: This command restores the database from the dvdrental.tar file into the dvdrental database.

sudo -u postgres pg_restore -C -d dvdrental /tmp/dvdrental.tar

After restoring the database, you can log into the PostgreSQL shell and explore the sample data.

sudo -u postgres psql dvdrental

This logs you into the PostgreSQL shell directly into the dvdrental database. List tables:

\dt
How to Install and Configure PostgreSQL on Ubuntu?

Select data from a table. This command selects the first 10 entries from the film table.

SELECT * FROM film LIMIT 10;

Use this command to exit the psql shell.

\q

 

10. Conclusion

In this guide, we've successfully navigated the installation and configuration of PostgreSQL on Ubuntu, covering the setup of a secure environment, enabling remote connections, and loading a sample database. The process involved steps like updating PostgreSQL configurations, modifying firewall settings, and using the psql shell for database interactions.

For further learning and more detailed information, the official PostgreSQL documentation is an invaluable resource. You can find it at PostgreSQL Documentation.

 

Deepak Prasad

Deepak Prasad

Deepak Prasad is the founder of GoLinuxCloud, bringing over a decade of expertise in Linux, Python, Go, Laravel, DevOps, Kubernetes, Git, Shell scripting, OpenShift, Networking, and Security. His extensive experience spans development, DevOps, networking, and security, ensuring robust and efficient solutions for diverse projects.

Certifications and Credentials:

  • Certified Kubernetes Application Developer (CKAD)
  • Go Developer Certification
  • Linux Foundation Certified System Administrator (LFCS)
  • Certified Ethical Hacker (CEH)
  • Python Institute PCAP (Certified Associate in Python Programming)
You can connect with him on his LinkedIn profile and join his Facebook and LinkedIn page.

Can't find what you're searching for? Let us assist you.

Enter your query below, and we'll provide instant results tailored to your needs.

If my articles on GoLinuxCloud has helped you, kindly consider buying me a coffee as a token of appreciation.

Buy GoLinuxCloud a Coffee

For any other feedbacks or questions you can send mail to admin@golinuxcloud.com

Thank You for your support!!