How to Install PostgreSQL on Ubuntu 23.04

---

PostgreSQL is a powerful, open-source object-relational database system with over 30 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance.

Install PostgreSQL

---

Install PostgreSQL on Ubuntu by using the following command

apt update

apt install postgresql postgresql-contrib

Output:

root@ubuntu:~# apt install postgresql postgresql-contrib
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following additional packages will be installed:
  libcommon-sense-perl libjson-perl libjson-xs-perl libllvm15 libpq5
  libsensors-config libsensors5 libtypes-serialiser-perl postgresql-15
  postgresql-client-15 postgresql-client-common postgresql-common ssl-cert
  sysstat
Suggested packages:
  lm-sensors postgresql-doc postgresql-doc-15 isag
The following NEW packages will be installed:
  libcommon-sense-perl libjson-perl libjson-xs-perl libllvm15 libpq5
  libsensors-config libsensors5 libtypes-serialiser-perl postgresql
  postgresql-15 postgresql-client-15 postgresql-client-common
  postgresql-common postgresql-contrib ssl-cert sysstat

PostgreSQL Roles and Databases

---

We'll switch to the Postgres user account for the next steps, to switch to the Postgres account, use the following command,

sudo -i -u postgres

You can access a PostgreSQL prompt using the psql utility

psql

Output:

postgres@ubuntu:~$ psql
psql (15.2 (Ubuntu 15.2-1))
Type "help" for help.

postgres=#

To exit the postgres shell, use the below command,

\q

Create PostgreSQL Role

Now let us see how we can create additional users that can interact with the Databases.

For this, you have to be a postgres user and then run the command as shown below,

createuser --interactive

Output:

postgres@ubuntu:~$ createuser --interactive
Enter name of role to add: sam
Shall the new role be a superuser? (y/n) y

Create a PostgreSQL Database

Creating a database is as simple as it gets. Run the below command as a postgres user account.

createdb database_name

Example:

postgres@ubuntu:~$ createdb my_db

Open a Postgres Prompt with the New Role

For this, we will create a new Linux system user by using adduser.

For simplicity purpose, we will be using the same name as which we created the postgres role with, adam.

adduser username

Example:

root@ubuntu:~# adduser sam
Adding user `sam' ...
Adding new group `sam' (1001) ...
Adding new user `sam' (1001) with group `sam (1001)' ...
Creating home directory `/home/sam' ...
Copying files from `/etc/skel' ...
New password:
Retype new password:
passwd: password updated successfully
...

Switch over to the newly added user and connect to the database.

su username

Open the postgres shell and connect to the new Database,

psql -d database_name

Example:

root@ubuntu:~#  sudo -i -u sam

sam@ubuntu:~$ psql -d my_db
psql (15.2 (Ubuntu 15.2-1))
Type "help" for help.

my_db=#

Once you're logged in as adam, check your current connection information:

\conninfo

Output:

my_db=# \conninfo
You are connected to database "my_db" as user "sam" via socket in "/var/run/postgresql" at port "5432".

This concludes the topic on installing PostgreSQL on Ubuntu 23.04.