How to Install PostgreSQL Database Server on Ubuntu 22.04?
PostgreSQL, often called Postgres, is a sophisticated open-source object-relational database system (ORDBMS). The technology is well-regarded for its extensibility, dependability along with adherence to ACID principles, which makes certain data remain accurate. Significant attributes involve accommodation for tailored data types, improved indexing options along with exhaustive text searches. PostgreSQL sees common use within web applications, for data storage, in addition to within analytics because of its adjustable scale and quick operation.
This guide explains how to install PostgreSQL database on Ubuntu 22.04
Installation of the PostgreSQL DataBase
1. To install PostgreSQL on Ubuntu, use the below apt-get command:
sudo apt-get -y install postgresql-14
Sample output:
sudo apt-get -y install postgresql-14 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 libllvm14 libpq5 libsensors-config libsensors5 libtypes-serialiser-perl postgresql-client-14 postgresql-client-common postgresql-common ssl-cert sysstat Suggested packages: lm-sensors postgresql-doc-14 isag The following NEW 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 ssl-cert sysstat 0 upgraded, 14 newly installed, 0 to remove and 0 not upgraded. Need to get 42.4 MB of archives. After this operation, 161 MB of additional disk space will be used. Get:1 http://nova.clouds.archive.ubuntu.com/ubuntu jammy/main amd64 libcommon-sense-perl amd64 3.75-2build1 [21.1 kB] ... update-alternatives: using /usr/share/postgresql/14/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode Processing triggers for man-db (2.10.2-1) ... Processing triggers for libc-bin (2.35-0ubuntu3) ... Scanning processes... Scanning linux images... Running kernel seems to be up-to-date. No services need to be restarted. No containers need to be restarted. No user sessions are running outdated binaries. No VM guests are running outdated hypervisor (qemu) binaries on this host. Get:4 http://archive.ubuntu.com/ubuntu focal-updates/main amd64 postgresql-common all X.X.X [XX.X MB] Get:5 http://archive.ubuntu.com/ubuntu focal-updates/main amd64 postgresql-14 amd64 X.X.X [XX.X MB] Fetched XX.X MB in Xs (XX.X kB/s) Selecting previously unselected package libpq5:amd64. (Reading database ... XXXXXX files and directories currently installed.) Preparing to unpack .../libpq5_X.X.X_amd64.deb ... Unpacking libpq5:amd64 (X.X.X) ... Selecting previously unselected package postgresql-client-common. Preparing to unpack .../postgresql-client-common_X.X.X_all.deb ... Unpacking postgresql-client-common (X.X.X) ... Selecting previously unselected package postgresql-client-14. Preparing to unpack .../postgresql-client-14_X.X.X_amd64.deb ... Unpacking postgresql-client-14 (X.X.X) ... Selecting previously unselected package postgresql-common. Preparing to unpack .../postgresql-common_X.X.X_all.deb ... Unpacking postgresql-common (X.X.X) ... Selecting previously unselected package postgresql-14. Preparing to unpack .../postgresql-14_X.X.X_amd64.deb ... Unpacking postgresql-14 (X.X.X) ... Setting up libpq5:amd64 (X.X.X) ... Setting up postgresql-client-common (X.X.X) ... Setting up postgresql-client-14 (X.X.X) ... Setting up postgresql-common (X.X.X) ... Setting up postgresql-14 (X.X.X) ... Adding user `postgres' to group `ssl-cert' ... Creating PostgreSQL cluster 14/main ... Configuring postgresql.conf to use port 5432 ... Updating symlinks in /etc/postgresql/14/main ... Starting PostgreSQL 14 database server ... Processing triggers for man-db (X.X.X) ... Processing triggers for libc-bin (X.X.X) ...
2. Test the installation
sudo su - postgres postgres@ubuntu-machine:~$ psql
Sample output:
sudo su - postgres postgres@tutos-ugc:~$ psql psql (14.2 (Ubuntu 14.2-1ubuntu1)) Type "help" for help. postgres=# exit postgres@tutos-ugc:~$
3. Add the user to PostgreSQL
Postgres provides two ways to add a user. You can use SQL or a bash command. In this article the SQL method will be used exclusively. It is more detailed. To create a new account, you must use the default postgres account and the psql CLI.
Create a role using the below command
postgres=# CREATE USER foo WITH PASSWORD 'bar';
Sample Output:
postgres=# CREATE USER foo WITH PASSWORD 'bar'; CREATE ROLE postgres=#
Create a database
1. Set the owner when you create a database
postgres=# CREATE DATABASE example OWNER foo
Sample output:
postgres=# CREATE DATABASE example OWNER foo CREATE DATABASE postgres-#
2. After this, use the new user to create an equivalent linux user (root privilege are mandatory for this kind of command)
adduser foo
Sample output:
$ sudo su -
$ root@tutos-ugc:~# adduser foo
Adding user `foo' ...
Adding new group `foo' (1001) ...
Adding new user `foo' (1001) with group `foo' ...
Creating home directory `/home/foo' ...
Copying files from `/etc/skel' ...
New password:
Retype new password:
passwd: password updated successfully
Changing the user information for foo
Enter the new value, or press ENTER for the default
Full Name []: Foo Bar
Room Number []:
Work Phone []:
Home Phone []:
Other []:
Is the information correct? [Y/n] Y
$ root@tutos-ugc:~# exit
3. Test the account
sudo su - foo psql -d example
Sample Output:
ubuntu@tutos-ugc:~$ sudo su - foo foo@tutos-ugc:~$ psql -d example psql (14.2 (Ubuntu 14.2-1ubuntu1)) Type "help" for help. example=>
4. List the databases to verify your creation
postgres=# \l
Sample output:
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+---------+-----------------------
example | foo | UTF8 | C.UTF-8 | C.UTF-8 |
postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
Create a table
1. Use the below command to create a table
example=> CREATE TABLE first_table (id INT PRIMARY KEY NOT NULL, column1 CHAR(50));
Sample Output:
example=> CREATE TABLE first_table (id INT PRIMARY KEY NOT NULL, column1 CHAR(50)); CREATE TABLE example=>
2. Use the first_table in SQL queries
example=> CREATE TABLE first_table (id INT PRIMARY KEY NOT NULL, column1 CHAR(50)); CREATE TABLE example=>
Sample output
example=> INSERT INTO first_table (id, column1) VALUES (1, 'example'); INSERT 0 1 example=> SELECT * FROM first_table; id | column1 ----+---------------------------------------------------- 1 | example (1 row)
PostgreSQL database is successfully installed and configured on Ubuntu 22.04.
