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 by using the following command
apt update apt install postgresql postgresql-contrib
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
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
postgres@crown:~$ psql psql (14.5 (Ubuntu 14.5-1ubuntu1)) Type "help" for help. postgres=#
To exit the postgres shell, use the below command,
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,
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.
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
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 .
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
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:
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.