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 on Ubuntu

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 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=#

Exit out of the PostgreSQL shell type

\q

Create PostgreSQL Role,

sudo -u postgres createuser --interactive

Output:

Enter name of role to add: john
Shall the new role be a superuser? (y/n) y

Create a PostgreSQL database,

sudo -u postgres createdb john

Open a Postgres Prompt with the New Role.

sudo adduser john

Switch over and connect to the database.

sudo -u john psql

Output:

root@crown:~# sudo -u john psql
could not change directory to "/root": Permission denied
psql (14.5 (Ubuntu 14.5-1ubuntu1))
Type "help" for help.

john=#

Once you logged in as john and check your current connection information.

\conninfo

Output:

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