How to Install Postgres database on ubuntu Debian 11

Postgres database is an open-source database management system mostly preferred for its reliability, scalability, and many other features it comes with, Big organizations like apple, Cisco, and Fujitsu use PostgreSQL.

PostgreSQL supports various data types like integers, text, dates, and times and more advanced data types like array and JSON.PostgreSQL supports both SQL and NoSQL data models, other features include transactions, stored procedures, triggers, and views

PostgreSQL is highly customizable and supports various programming languages, including- Python, Java, Ruby, and C++. The Operating Systems platforms that PostgreSQL supports include Linux, Windows, and macOS.

Steps to install PostgreSQL on Ubuntu Debian 11

step one is to update the ubuntu package list, we will use this command:-

sudo apt update

Once the packages are installed, we now proceed with the installation of the Postgres database and its packages using these commands:-

sudo apt install PostgreSQL postgresql-contrib

In a situation, you get an error like below

Install the certificate for secure SSL using the below command

sudo apt install wget ca-certificates

After Running that now proceed to add the certificate to the apt-key management utility and create a configuration file with the PostgreSQL repository placed inside by running the commands below on terminal

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'

Once all the above commands are successful, We now proceed with Postgres database installation by running the update command and the install one,

sudo apt update
sudo apt install PostgreSQL postgresql-contrib

we can then check the installation status using the command below

systemctl status postgresql

The image above shows us that PostgreSQL is successfully installed and running, the user “postgres” is automatically created,to switch to that user and use we run the command below;-

sudo su - postgres

To open the PostgreSQL Shell we run the following command

psql

and when we want to create a new user, we run the command below in PostgreSQL Shell

CREATE USER edu WITH PASSWORD 'edu';

Once the user is now created we proceed to create the database, we can do this using the command below, and to quit the PostgreSQL Shell we use the command \q;-

CREATE DATABASE edu_test;
\q

How to Configure PostgreSQL

By default after installation PostgreSQL will only be accessed via a local machine, to make it accessible via an IP Address from another machine in the network we need to modify the configuration file pg_hba.conf which is located in /etc/postgresql/<version>/main/ for our case the path is /etc/postgresql/13/main since our PostgreSQL installation is version 13.

How to Create a table and Insert Data

here is an example to create a table and the insert the sample data into PostgreSQL using the command

CREATE TABLE edu_edu (
id SERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
age INTEGER NOT NULL
);

//code to insert data to the database

INSERT INTO edu_edu (name, age) VALUES ('edu edu', 29);

How to Query the data in the table

to get the data we just inserted we use the below command to

SELECT * FROM edu_edu;

the result we get is the data we inserted in the above commands

How to Backup and restore the database

Back up of any database is so that we do not lose any data,pg_dump is the command commonly used to backup the database, also when you want to restore it will be easy when we had made backups as shown in the below commands for example as below

//Backup command
pg_dump edu_test> edu_test.sql

//restore command
pg_restore -d edu_test edu_test.sql

How to manage permissions

PostgreSQL comes with a user and permission management system,to achieve this below are the commands to use;-

//Create a new user
CREATE USER edu WITH PASSWORD 'edupassword';

//Grant permissions to a user
GRANT ALL PRIVILEGES ON mydatabase TO edu;
//Restrict access to a table
REVOKE ALL ON mytable FROM edu;

To Learn More about PostgreSQL relational database management system we recommend checking out the official PostgreSQL documentation