Installing PostgreSQL on Ubuntu 22.10

Hello,

In this week’s feature highlight, we look at How to Install PostgreSQL on Ubuntu 22.10

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@crown:~# apt install postgresql postgresql-contrib
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following packages were automatically installed and are no longer required:
  galera-4 libconfig-inifiles-perl libdaxctl1 libdbd-mysql-perl libdbi-perl
  libmariadb3 libmysqlclient21 libndctl6 libpmem1 libsnappy1v5 liburing2
  mariadb-common socat
Use 'apt autoremove' to remove them.
The following additional packages will be installed:
  libcommon-sense-perl libjson-perl libjson-xs-perl libllvm14 libpq5
  libsensors-config libsensors5 libtypes-serialiser-perl postgresql-14
  postgresql-client-14 postgresql-client-common postgresql-common 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@crown:~$ psql
psql (14.5 (Ubuntu 14.5-1ubuntu1))
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@vps:~$ createuser --interactive
Enter name of role to add: adam
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@vps:~$ 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.

You will need to switch back to root user or a sudo user with required privileges .

adduser username

Example:

root@vps:~# adduser adam
Adding user `adam' ...
Adding new group `adam' (1000) ...
Adding new user `adam' (1000) with group `adam' ...
Creating home directory `/home/adam' ...
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.

sudo -u username

Open the postgres shell and connect to the new Database,

psql -d database_name

Example:

root@vps:~# sudo -i -u adam

adam@vps:~$ psql -d my_db
psql (14.5 (Ubuntu 14.5-1ubuntu1))
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 "adam" via socket in "/var/run/postgresql" at port "5432".

This concludes the topic on installing PostgreSQL on Ubuntu.