You are currently viewing A Comprehensive Guide to Installing PostgreSQL on Ubuntu

A Comprehensive Guide to Installing PostgreSQL on Ubuntu

PostgreSQL is a powerful, open-source relational database management system known for its flexibility, reliability, and robust features. It offers features like ACID compliance, transactions, indexing, and extensibility. This guide will walk you through installing PostgreSQL on Ubuntu system.

How To Install and Configure PostgreSQL on Ubuntu

Preparing Your Ubuntu System

It is the best practice to ensure that your system is up to date and has the latest package information. Open the terminal and run the following commands to update your packages.

sudo apt update
sudo apt upgrade

Installing PostgreSQL on Ubuntu

After your packages are updated, install PostgreSQL using the package manager (apt):

sudo apt install postgresql postgresql-contrib

This command will install PostgreSQL and some additional contrib packages for extended functionality.

Configuring PostgreSQL

We have installed and started PostgreSQL, but we need to perform some basic configuration to enhance security and usability.

sudo systemctl start postgresql   #To start PostgreSQL service:: 
service postgresql start  #If systemctl is not configured, use this
sudo systemctl enable postgresql    #To ensure PostgreSQL starts automatically on system boot 
service postgresql enable   #if systemctl is not configured.

Accessing PostgreSQL

By default, PostgreSQL creates a user named postgres during installation. To access PostgreSQL and interact with databases, use this user. 

sudo -i -u postgres   #Switch to the postgres user
#If sudo is not configured; use : su postgres
psql #Access the PostgreSQL prompt:

It’s now time to run some commands on our Postgresql command line.

Creating a Database and User

CREATE DATABASE <database_name>;   #To create a new database
CREATE USER <username> WITH PASSWORD '<password>';   #create a database user.
GRANT ALL PRIVILEGES ON DATABASE <database_name> TO <username>; #Grant privileges to the user for a specific database

#Configuring the user and the database to some web applications, may require some more privileges. If required, ensure that the user has the appropriate permissions on the public schema:
\c databasename   #switch to the database you want to make updates on
##GRANT USAGE, CREATE ON SCHEMA public TO your_db_user;
GRANT ALL PRIVILEGES ON SCHEMA public TO your_db_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO your_db_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO your_db_user;

Once you create the database and grant permission to a database user, they can switch to this database and query it.

psql -h HostIP -U db-user -d db-name  
#where HOSTIP is the host ip for the db or the hostname 
#db-user is the database user created previously
#db-name is the database name as created.

Once connected, you can now run the SQL commands. 

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255)
);
#To create a table named users with columns id, name and email 
INSERT INTO users (name, email) VALUES ('John Doe', '[email protected]');  #insert data into users table

SELECT * FROM users;
 #get a list of all data in the users table.

Make a donation to support us

Web Hosting and email hosting Packages

Hosting In Kenya
Hosting In Nigeria
Other Countries

For web development services, SEO services, Digital marketing strategies, website set up services, web hosting and domain registration; contact Dynamic Technologies.

Related content:

Tags: ,