PostgreSQL (sometimes known as Postgres) is a relational database management system that implements the SQL querying language. It complies with industry standards and has numerous advanced features such as dependable transactions and concurrency without read locks. The PostgreSQL project began in 1986 at the University of California’s Berkeley Computer Science Department. The project was previously known as POSTGRES, after the Ingres database, which was also created at Berkeley. The POSTGRES project’s purpose was to add the bare minimum of capabilities required to enable multiple data types. The POSTGRES project was renamed to PostgreSQL in 1996 to emphasize its SQL capabilities. Installing PostgreSQL is crucial to building dynamic apps and websites.
PostgreSQL is now usually referred to as Postgres. The PostgreSQL Global Development Group, a resolute community of contributors, has consistently provided the open-source and free database project since then. PostgreSQL was initially created with the intention of running on UNIX-like platforms. PostgreSQL was later developed to work on a variety of systems, including Windows, macOS, and Solaris. From installing PostgreSQL to creating a new user and database, this guide shows how to install PostgreSQL on Ubuntu 20.04 server database. You can find other guides here: How to Install Apache OpenOffice on Ubuntu /, How to install Apache Tomcat on Ubuntu/, How to install and configure Tripwire on Ubuntu/and How to install Node.js on Ubuntu/ also How to Install and Configure Nagios on Ubuntu/ and How to Install LAMP Stack on Ubuntu 18.04
Ubuntu Versions That Supports PostgreSQL Installation
You can install PostgreSQL on the following Ubuntu versions:
- impish (21.10)
- hirsute (21.04)
- focal (20.04)
- bionic (18.04)
Advantage: Postgres is free and open source. Reduce your expenses. PostgreSQL is a true open-source solution, hence there are no license payments Community-driven various businesses and individuals have contributed to the project, which has fueled innovation. Bugs are quickly fixed because of the large community. Security Because of the easy extensibility, there are numerous features for better security: nevertheless, if you use the appropriate ones (TDE, Data Masking), you obtain a very safe database that protects your most significant asset - your data. Scalable PostgreSQL database can expand to meet your needs. There are a variety of technological options for scaling PostgreSQL. As a result, your PostgreSQL database will be able to scale up to meet your needs.
From installing PostgreSQL to creating a new user and database, this guide shows how to rapidly get Postgres up and running on an Ubuntu 20.04 server database
Prerequisites for Installing PostgreSQL on Ubuntu
Install rudimentary firewall and non-root user with sudo capabilities on your server
We used version 20.04 of Ubuntu, but the steps should work for other versions listed above. We shall be using the apt repository and the steps are as shown below
Step1: create a file repo
http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
Step2: Import the repository signing key
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
Step3: update the package list
sudo apt update
Step4: Install the latest version of PostgreSQL on Ubuntu
To install PostgreSQL, run the command below:
sudo apt-get -y install postgresql
Included items in distribution
PostgreSQL comes pre-installed on Ubuntu. Use apt-get (or another apt-driving command) to install PostgreSQL on Ubuntu:
Many distinct The most frequent and relevant packages are:
|postgresql-client-12||client libraries and client binaries|
|postgresql-12||core database server|
|libpq-dev||libraries and headers for C language frontend development|
|postgresql-server-dev-12||libraries and headers for C language backend development|
Step 5: Using PostgreSQL Roles and Databases
Postgres employ ident authentication by default, which means that it associates Postgres roles with a Unix/Linux system account. If a role exists in Postgres, you can access it using a Unix/Linux username with the same name.
The default Postgres role correlates with a user account called postgres, generated during the installation process. You can access Postgres with this account in a few different ways. Switching to the postgres account on your server is one option: perform the following command:
sudo -i -u postgres
you can access postgres prompt with the command :
Run the following commands to exit the PostgreSQL prompt:
This redirects you to the postgres Linux command prompt. Furthermore, run the exit command to return to your regular system user:
This will log you into Postgres without having to go through the bash shell.
You can leave the interactive Postgres session once more by typing the command:
Step 6: Creating a New Role
You can create a new role by running the following command:
If you’d rather use sudo for each command instead of switching to a different account, run:
sudo -u postgres createuser --interactive
Step 7: Creating a New Database After Installing PostgreSQL
When you install PostgreSQL, it activates a default database named PostgreSQL. A conclusion reached by default by the Postgres authentication system is that each role used to log in has access to a database with the same name.
This implies that if the user you created in the previous section is named Raphael, the role will automatically attempt to connect to a database named “Raphael.” You can use the createdb command to construct the necessary database.
You would type something like this if you were logged in as the postgres account:
Possible createdb error
sudo -u postgres -i
After this let’s retry to create the database
root@ubuntu:/home/rdgmh# sudo -u postgres -i postgres@ubuntu:~$ createdb Raphael postgres@ubuntu:~$
The error did not show up anymore
Step 5 : Opening a Postgres Prompt with the New Role
You’ll need a Linux user with the same name as your Postgres role and database to log in with ident based authentication.
You can also use the adduser command to create a new Linux user if you don’t already have one. You’ll need to do this from a non-root account with sudo permissions (i.e., not as the postgres user):
sudo adduser postgresuser
You can switch over to this new account and connect to the database by running the following commands:
sudo -i -u postgresuser psql
If you wish your user to connect to a separate database, you can accomplish so by naming it something like this:
psql -d postgres
In conclusion, we installed the latest version of PostgreSQL from the apt repository. Furthermore, we also walked you through how to create a user, work with postgres and exit the shell.