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.
You will need to switch back to root user or a sudo user with required privileges .
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.