How to Install and Configuration PostgreSQL on Ubuntu

Install and configuration PostgreSQL on Ubuntu is the process of setting up PostgreSQL by updating your system, running sudo apt install postgresql, and creating users and databases.

🤖AI Overview:

Install and configuration PostgreSQL on Ubuntu refers to the steps needed to set up the PostgreSQL database system on Ubuntu. The process involves updating the system, installing PostgreSQL with sudo apt install postgresql, and then creating users and databases to manage your data. This setup allows you to store and organize information securely on your Ubuntu server.

Quick Steps to Install and Configuration PostgreSQL on Ubuntu

1. Update your system with sudo apt update
2. Install PostgreSQL with sudo apt install postgresql postgresql-contrib
3. Start PostgreSQL using sudo systemctl start postgresql.service
4. Access PostgreSQL with sudo -u postgres psql
5. Create a new role using createuser --interactive
6. Add a database with createdb your_db_name
7. Manage tables and data using SQL commands in the PostgreSQL shell

How to Install and Configuration PostgreSQL on Ubuntu

As an Intermediate Linux Administrator at OperaVPS, I know that having a reliable database is important for beginners. PostgreSQL is one of the most widely used and practical relational database systems. In this guide, I will show you how to install and configuration PostgreSQL on Ubuntu 22.04 step by step so you can work with your own database environment easily and safely.

Why Choose PostgreSQL?

PostgreSQL, also called Postgres, is a trusted relational database system that is known for quality and long-term development. It supports advanced features such as secure transactions and concurrent processing. With strong community support, PostgreSQL meets the reliability needs of many users.

Prerequisite

Before you begin to install and configuration PostgreSQL on Ubuntu, you need access to a Linux VPS running Ubuntu 22.04. For system security and smooth operation, use a firewall and a user account with sudo privileges.

Steps to Install and Configuration PostgreSQL on Ubuntu

Step 1: Updating System Packages

Start by updating your system packages with:

sudo apt update

Why this matters: Updating ensures you install the latest and most secure versions, which helps avoid problems.

Step 2: Installing PostgreSQL

Install PostgreSQL and additional useful features by running:

sudo apt install postgresql postgresql-contrib

This step makes sure you have the database and its core tools ready for use.

Step 3: Starting the PostgreSQL Service

Activate the database system with:

sudo systemctl start postgresql.service

This command starts PostgreSQL so you can begin using it.

Configure PostgreSQL

PostgreSQL uses roles to manage access and authentication. These roles are similar to system users and help keep your data safe.

After installation, peer authentication is used by default. This means a Linux system user must match a PostgreSQL role to access the database. The default role is postgres.

Step 1: Accessing PostgreSQL

• First method: Switch to the postgres account and open the shell

sudo -i -u postgres

Then start PostgreSQL:

psql

• Second method: Use one command as the postgres user

sudo -u postgres psql

To exit the shell, use:

\q

Step 2: Creating a New Role

Create a new role with the right permissions:

createuser --interactive

Or use sudo directly:

sudo -u postgres createuser --interactive

This step is important for controlling who can access and manage your databases.

Step 3: Creating a New Database

Link each role to a new database:

createdb opera

Or with sudo:

sudo -u postgres createdb opera

This gives your new user a place to manage data.

Step 4: Accessing the Database with the New Role

Create a matching Linux user:

sudo adduser opera

Switch to that user and enter the PostgreSQL shell:

sudo -i -u opera

psql

Or connect directly:

sudo -u opera psql

Check your connection with:

\conninfo

This confirms you are using the correct user and database.

Step 5: Creating and Deleting Tables

Create a table with:

CREATE TABLE table_name (

    column_name1 col_type(field_length) column_constraints,

    column_name2 col_type(field_length),

    column_name3 col_type(field_length)

);

Show all tables:

\d

Show only tables:

\dt

Setting up tables defines how your data is stored and managed.

Step 6: Adding, Deleting Data, and Querying Tables

Add data to a table:

INSERT INTO table_name (color, location, install_date) VALUES ('blue', 'south', '2017-04-28');

INSERT INTO table_name (color, location, install_date) VALUES ('yellow', 'northwest', '2018-08-16');

Query data:

SELECT * FROM table_name;

Delete rows:

DELETE FROM table_name WHERE column_name = 'value';

Regular checks and updates keep your database accurate.

Step 7: Adding and Removing Columns

Add a column:

ALTER TABLE table_name ADD column_name type;

Check the table:

SELECT * FROM table_name;

Remove a column:

ALTER TABLE table_name DROP column_name;

This helps keep your table organized as your data changes.

Step 8: Updating Data in Tables

Update row data:

UPDATE table_name SET column_name = 'new_value' WHERE column_name = 'old_value';

Updating records helps keep your information current.

How to Uninstall PostgreSQL Database in Ubuntu 22.04

To remove PostgreSQL, use:

sudo apt remove postgresql postgresql-contrib

This clears PostgreSQL and its tools from your server.

FAQ

Conclusion:

By following these steps to install and configuration PostgreSQL on Ubuntu, you will have a ready-to-use and secure database server.

Each step builds your skills and helps you learn best practices for managing PostgreSQL as a beginner.

If you have any issues, remember that you can ask for help any time.