How to Install and Use PostgreSQL on Ubuntu

PostgreSQL is a powerful, open-source object-relational database system that is widely used for various types of applications. This guide will help you install PostgreSQL on Ubuntu and get started with basic usage.

← Back

Step 1: Update Your System

Before installing PostgreSQL, update your package index to ensure your installation is up to date:

sudo apt update

Step 2: Install PostgreSQL

Next, install the PostgreSQL package using the following command:

sudo apt install postgresql postgresql-contrib

The postgresql-contrib package contains additional utilities and features for PostgreSQL.

Step 3: Verify Installation

After the installation completes, you can verify that PostgreSQL is installed and running by checking its service status:

sudo systemctl status postgresql

This should show you that the PostgreSQL service is active and running.

Step 4: Access PostgreSQL

PostgreSQL uses a concept called "roles" for authentication. By default, a system user called postgres is created. To access the PostgreSQL database, switch to the postgres user:

sudo -i -u postgres

Once you're logged in as the postgres user, you can access the PostgreSQL prompt with the following command:

psql

Step 5: Create a New Database

To create a new database, use the following SQL command within the PostgreSQL prompt:

CREATE DATABASE your_database_name;

Replace your_database_name with the desired name of your database.

Step 6: Create a New Role

To create a new user (role) with a password, use the following SQL command:

CREATE ROLE your_role_name WITH LOGIN PASSWORD 'your_password';

Replace your_role_name and your_password with the desired role name and password.

Step 7: Grant Privileges to the User

To allow the new role to create databases, run this command:

ALTER ROLE your_role_name CREATEDB;

Step 8: Exit the PostgreSQL Prompt

Once you're done, you can exit the PostgreSQL prompt by typing:

\q

Step 9: Connect to PostgreSQL Using Your Role

You can now connect to PostgreSQL with the new role you created by running the following command:

psql -d your_database_name -U your_role_name

Replace your_database_name and your_role_name with the appropriate values. You'll be prompted for the password you set for the role.

Step 10: Stop and Restart PostgreSQL

To stop PostgreSQL, run:

sudo systemctl stop postgresql

To restart PostgreSQL, use:

sudo systemctl restart postgresql

Conclusion

Congratulations! You’ve successfully installed PostgreSQL on Ubuntu, created a new database, user, and granted privileges. PostgreSQL is now ready to use on your system for database management. Make sure to follow best practices for securing your PostgreSQL installation.

← Back