What is PostgreSQL?
PostgreSQL or Postgres is one of the most popular RDBMS (Relational Database Management System). It’s free and open-source RDBMS emphasizing extensibility and technical standards compliance.
PostgreSQL is designed to handle a range of workloads. It is an advanced RDBMS with an enterprise-class grade, supports both SQL (relational) and JSON (non-relational) querying.
PostgreSQL can be used for simple applications to the data warehouse and web application with many concurrent users.
What we will do?
In this tutorial, we will show you how to install and configure PostgreSQL on Ubuntu 20.04.
For this tutorial, make sure you have got a Ubuntu 20.04 server with root privileges.
Below is the tutorial about the Ubuntu 20.04 server installation.
Before going any further, log in to your Ubuntu server and type the sudo command to get the root privileges on your system.
Step 1 – Install PostgreSQL on Ubuntu 20.04
First, we will install PostgreSQL to the Ubuntu system. And we will use the PostgreSQL packages provided by the official Ubuntu repository, which is the latest version of PostgreSQL 20.
Install PostgreSQL using the apt command below.
apt install postgresql postgresql-contrib
Once the installation is complete, start the PostgreSQL service and add it to the system boot.
systemctl start postgresql systemctl enable postgresql
And the PostgreSQL service has been started, check it using the following command.
systemctl status postgresql
As a result, the PostgreSQL is up and running on the Ubuntu 20.04. And by default, it’s running on the TCP port ‘5432‘, check the PostgreSQL port using netstat command below.
As a result, you’ve successfully installed the PostgreSQL on Ubuntu 20.04 server.
Step 2 – Setup PostgreSQL Authentication on Ubuntu 20.04
The PostgreSQL database supports for multiple authentication methods, which is controlled by the configuration file ‘/etc/postgresql/12/main/pg_hba.conf’.
The most commonly used authentication methods for PostgreSQL are below.
- Trust – This method allows users/roles to connect without any password, as long as the requirement is met in the configuration ‘pg_hba.conf’.
- Password – This method will ask the user/role for its password when connecting to the PostgreSQL. supported options are md5, scram-sha-256, or password as plain-text.
- Ident – This method works by obtaining the client operating system username. Only supports for TCP/IP connection and additional options ‘map’ for user mapping.
- Peer – This method is similar to Ident, but only works on local connections.
In this step, you will learn how to set up the PostgreSQL authentication using the password authentication method, and you will use the ‘md5’ as a supported method.
To set up the authentication method, go to the ‘/etc/postgresql/12/main’ directory and edit the configuration ‘pg_hba.conf’.
cd /etc/postgresql/12/main/ vim pg_hba.conf
Change the authentication method for local connection to md5 as below.
# "local" is for Unix domain socket connections only local all all md5
Save and close.
Next, restart the PostgreSQL service using the command below.
systemctl restart postgresql
As a result, you’ve changed the authentication for local connection with the ‘md5’ as its Password authentication method.
Step 3 – Connect to PostgreSQL Shell
PostgreSQL comes with a default user called ‘postgres’, which can be used to log in to the PostgreSQL shell.
To log in to the PostgreSQL shell, run the command below.
sudo -i -u postgres psql
Once you’ve logged in, check the connection info using the following query.
As a result, you’ve connected to the default database ‘postgres‘ with the default user ‘postgres‘ via socket in the ‘/var/run/postgresql’ directory at port ‘5432‘.
Step 4 – Basic PostgreSQL Operation
In this step, you will learn about the basics of PostgreSQL operations, such as create a user/role, create a database, create a table, insert data, etc.
– Create User/Role
To create user/role with login permission, run the following query.
CREATE ROLE gahyeon WITH LOGIN ENCRYPTED PASSWORD 'gahyeonpasswd';
After that, check all users/roles on PostgreSQL.
As a result, you’ve created a new role named ‘gahyeon‘ with permission to log in and connect to the database.
– Create Database and Grant Privileges
To create the database on the PostgreSQL, run the following query.
CREATE DATABASE mydatabase;
Next, grant the user ‘gahyeon‘ to access the database ‘mydatabase‘.
GRANT ALL PRIVILEGES ON DATABASE mydatabase TO gahyeon;
Now check available databases on PostgreSQL.
As a result, you’ve created a new database named ‘gahyeon‘ and give the user ‘gahyeon‘ permission to access the database.
– Connect to PostgreSQL Shell as Specific Role
To connect to the PostgreSQL shell using the specific user/role, log in to the postgres user using the following command.
su - postgres
Next, connect to the PostgreSQL shell as user ‘gahyeon‘, to the database ‘mydatabase‘ using the following command.
psql -U gahyeon -d mydatabase
Now type the password for user ‘gahyeon’. Once you’ve logged in, check the connection info.
As a result, you’ve connected to the PostgreSQL shell as a specific user ‘gahyeon‘ to the database ‘mydatabase‘.
– Create Table and Insert Data
To create the table, run the following queries.
CREATE TABLE account( user_id serial PRIMARY KEY, username VARCHAR (50) UNIQUE NOT NULL, password VARCHAR (50) NOT NULL, email VARCHAR (355) UNIQUE NOT NULL, created_on TIMESTAMP NOT NULL, last_login TIMESTAMP );
After that, insert the data to the table ‘account’ using the queries below.
INSERT INTO account VALUES(1,'gahyeon','passgahyeon','[email protected]', '2019-09-01', '2019-12-10');
Next, check list tables on the database and dump all data from the table using the following query.
\dt select * from account;
As a result, you’ve created a new table ‘account‘ and inserted new data into it.
And finally, you’ve successfully installed the PostgreSQL on Ubuntu 20.04 Server.