Reading Time: 5 mins.
What’s PostgreSQL?
PostgreSQL, one of the most popular open-source database management systems (object-relational) and it supports both SQL and NoSQL language. It has huge community support which is backed by more than 20 years. Primarily used to run data analysis applications and as a data warehouse, PostgreSQL is completely ACID compliant. The database management system, PostgreSQL is designed in such a way to handle a range of workloads from a single machine to a web service with multiple concurrent users.
Why do we go with PostgreSQL?
- It supports modern application features like XML, JSON while others do not completely provide support to all the modern features.
- It supports Materialized Views when some of the other databases don’t.
- One of the few database management systems that do not compromise with performance and works exceedingly high while executing complex queries.
Prerequisites:
- To be logged in as a non-root user with sudo privileges.
- Installation of all the required Postgre packages.
Step 1: Installing PostgreSQL
By default, you have the necessary Postgre packages in your Ubuntu repositories. For installing the PostgreSQL, you can use the apt packaging system. To get the additional utilities and features for your PostgreSQL database system, install along with the -contrib package.
sudo apt update sudo apt install postgresql postgresql-contrib
To check the version, connect the psql tool with the PostgreSQL server and thereby for printing its version.
sudo -u postgres psql -c "SELECT version();"
Output
version ------------------------------------------------------------------------------------------------------------------------------- PostgreSQL 12.4 (Ubuntu 12.4-0ubuntu0.20.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-10ubuntu2) 9.3.0, 64-bit (1 row)
Step 2: Verifying Postgres service
To verify whether the installed PostgreSQL is up and running, type the following command.
$ sudo service postgresql status
Output
● postgresql.service - PostgreSQL RDBMS Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled) Active: active (exited) since Tue 2020-09-29 13:41:48 IST; 12min ago Process: 25987 ExecStart=/bin/true (code=exited, status=0/SUCCESS) Main PID: 25987 (code=exited, status=0/SUCCESS) Sep 29 13:41:48 user systemd[1]: Starting PostgreSQL RDBMS... Sep 29 13:41:48 user systemd[1]: Finished PostgreSQL RDBMS.
Now that we successfully installed PostgreSQL on your system, let’s move forward.
Step 3: Accessing the Postgres Shell
In the cases of accessing regular Unix-style technologies, you have users and groups to handle the authorization and authentication. When it comes to Postgres, you have something called roles (a much more flexible term) to access its shell. Further, Postgres supports multiple authentications to enable its shell access, they are – Trust, Password, Ident, Peer. By default, Postgres follows the Peer authentication methods and its client authentication has been defined in the configuration file namely pg-hba.conf. The access of the Postgres Shell can be done in two ways, there are as follows.
- Switching to the postgres user
- By direct shell access
i) Switching to the postgres user
At first, log into your PostgreSQL server and switch to the account postgres. Now use the tool, psql to access the Postgres prompt.
$ sudo su - postgres $ psql psql (12.4 (Ubuntu 12.4-0ubuntu0.20.04.1)) Type "help" for help.
From now, you can access PostgreSQL whenever required. To exit from the Postgres shell, type the following.
postgres=# \q
ii) By direct shell access
Yet another way to directly access the Postgres shell is to use the simple sudo command. This prevents you from the in-between extra step, intermediary bash.
sudo -u postgres psql
Again to exit the session, type:
postgres=# \q
You can log into your server as postgres only from the localhost and remember, multiple use cases will be required for more than one Postgres role.
Step 4: Creating postgres roles and databases
i) Creating Postgres role
Now that you have configured Postgres’ role within the database, you’re free to create new roles by typing the command createrole in your command line utility (CLI). Also, to name the new role and to provide superuser permissions (if preferred), you can use the prompt –interactive flag.
In order to create a new user, make sure that you have logged in using the postgres account.
$ sudo su - postgres postgres@user:~$
To create a new user, type the below command.
createuser --interactive
In case, you’re opting to make use of the sudo command, i.e. without switching from your normal account., type:
sudo -u postgres createuser --interactive
It prompts you to choose your preferences and depending on that, type the right Postgres commands.
Output
Enter name of role to add: test Shall the new role be a superuser? (y/n) y
To gain more control, check out the man page for passing the additional flags.
man createuser
We have successfully added a new user to your PostgreSQL server but there’s one more step – adding databases. Let’s see that in the next section.
ii) Create database
You can log in using both of the above-created roles. Here, we’re going with the default Postgres user.
sudo -u postgres psql
postgres=# create database test;
Output CREATE DATABASE
postgres=# create user demo with encrypted password ‘demo123;
Output CREATE ROLE
postgres=# grant all privileges on database test to demo;
Output GRANT
Note: In this step, as you can see, we have created the sample database along with the user.
Step 5: Enabling Remote Connections
If you are using a dedicated server for postgres we have to change the default listen value to * since PostgreSQL listens only on the local interface.
sudo nano /etc/postgresql/12/main/postgresql.conf
Let’s change the listen to directive value as shown below.
Output
#------------------------------------------------------------------------------ # CONNECTIONS AND AUTHENTICATION #------------------------------------------------------------------------------ # - Connection Settings -# listen_addresses = '*' # what IP address(es) to listen on; # comma-separated list of addresses; # defaults to 'localhost'; use '*' for all # (change requires restart) port = 5432 # (change requires restart) max_connections = 100 # (change requires restart)
Note: In case, if you use the same server for both web and postgres, then there is no need to change the value, retain the same value as localhost.
Next, save the file and restart the Postgres service. To do so, type:
$ sudo service postgresql restart
To confirm the changes, use the ss utility:
sudo ss -nlt | grep 5432
Output
$ ss -nlt | grep 5432 LISTEN 0 244 0.0.0.0:5432 0.0.0.0:* LISTEN 0 244 [::]:5432 [::]:*
From the above output, it is clear that PostgreSQL server is listening on all interfaces (0.0.0.0):
To accept the remote connections, configure the server by editing the pg_hba.conf file. Let’s open the conf file by entering the below command. Here, you can find some examples which help you to choose according to your requirements.
sudo nano /etc/postgresql/12/main/pg_hba.conf
Output
# TYPE DATABASE USER ADDRESS METHOD # The user test can access all databases from all locations using md5 password host all test 0.0.0.0/0 md5 # The user test can access only the testdb from all locations using md5 password host testdb test 0.0.0.0/0 md5 # The user test can access all databases from a trusted location (192.168.1.112) without a password host all test 192.168.1.112 trust
In your firewall, open the port 5432 and allow access from the 192.168.1.0/24 subnet by entering the following command.
sudo ufw allow proto tcp from 192.168.1.0/24 to any port 5432
Note:
- Make sure your firewall is configured to accept connections only from trusted IP ranges.
- If you’re using the same server, you need not configure anything, just leave as default.
Step 6: Applying the basic commands
Let us have a look at some of the basic commands of PostgreSQL.
To access the Postgres shell, type:
sudo -u postgres psql
To connect to Postgres database, type:
sudo psql -d database -U user -W
If you’re using a diff host, type:
sudo psql -h host -d database -U user -W
For checking db with connected user, type:
\c
For listing databases, type:
\l
For listing tables, type:
\dt
List users and their roles, type:
\du
For command history, type:
\s
For quitting, type:
\q
Conclusion:
As a quick summary, this article has taken you to the steps involved in the installation of one of the most widely used and powerful database management systems, PostgreSQL. Initially named as POSTGRES, it had a name change as PostgreSQL – referring its support to the SQL language. Besides the mere Postgres installation part, you have seen the how-to of verifying its service status, accessing its shell, creating roles and database, enabling the remote connection and last but not least, some of its basic commands used when it comes to configuring the Postgres server.