PostgreSQL Database Access from Command Line in Linux

This is another article which is shown how to connect to PostgreSQL Database Server access. It can be done from an operating system runs in GUI (Graphical User Interface) easily to connect to PostgreSQL Database Server with the help of several tools such as PgAdmin among the others.

Unfortunately, the condition is not always the same especially if it is needed to connect to PostgreSQL Database Server in an operating system based on CLI (Command Line Interface). So, in order to do that it is necessary to know several steps which needed to be exercised for the purpose of connecting to PostgreSQL Database Server really can happened. These are the steps :

1. Every request has a specific service which is activated to listen to it. So, first step is to make sure that PostgreSQL Database Server’s service is active. Check whether it is listed and what number of port which is used to listen the request by executing the following command :

cat /etc/service | grep 5432
Description : 
cat : It is a command executes to display content of a file in text and print it on standard output device such as screen, monitor. 
/etc/service : It is a configuration file used normally in major Linux operating system distribution named service located in to  map service name to the correlated port number
| : It is a sign which is a pipe sign used to redirect the output which is generated from the command before the '|' sign
5432 : It is the default port for PostgreSQL Database Server

By executing it in the bash prompt of a terminal, we can check whether MySQL Database Server’s service exist or not. Below is the execution of the above command in a bash prompt :

user@hostname:~$ cat /etc/services | grep 5432 
postgresql      5432/tcp        postgres        # PostgreSQL Database 
postgresql      5432/udp        postgres 
user@hostname:~$ 

2. The execution of the above command describes that PostgreSQL Database Server’s port number listens or handle requests in port 5432. The important thing is to make sure that the service is alive and listening for the request, execute the following command to be able to check it :

netstat -tulpn | grep 5432

This is the output of the command when it is executed on the bash prompt terminal :

user@hostname:~$ netstat -tulpn | grep 5432
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp        0      0 127.0.0.15432          0.0.0.0:*               LISTEN      -
user@hostname:~$
Description : 
netstat : It is a shell command for printing network connections, routing tables, interface statistics and any other related informations. In this context, it is used to check whether PostgreSQL default port number is listening for incoming request or not
-t  : It is an additional parameter for filtering display to only relates on TCP packet. 
-u : It is an additional parameter for filtering display to only relates on UDP packet. 
-l : It is an additional parameter for filtering display to only port in a listening state
-p : It is an additional parameter for displaying the information of PID (Process ID) and also the name of the program
-n : It is an additional parameter for displaying the information of host, port in a numerical addresses.

Another method which can be used to check whether the service of PostgreSQL Database Server is active or not is by typing the following command :

service postgresql status

Below is the output of the executed command :

user@hostname:~# service postgresql status
● postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
Active: active (exited) since Min 2016-09-25 16:51:50 WIB; 33min ago
Process: 13371 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
Main PID: 13371 (code=exited, status=0/SUCCESS)
Tasks: 0
Memory: 0B
CPU: 0
CGroup: /system.slice/postgresql.service
Sep 25 16:51:50 hostname systemd[1]: Starting PostgreSQL RDBMS...
Sep 25 16:51:50 hostname systemd[1]: Started PostgreSQL RDBMS.
user@hostname:~#

As shown in the above output, the service is active and it has already started.

3. The initial preparation has already set, the next step is connecting to PostgreSQL Database Server since it has already found out that the service is active, so, in order to directly access PostgreSQL command console execute the following command in the bash prompt :

psql -Uusername

Below is the output of the command executed :

user@hostname:~# psql -Upostgres
Password for user postgres:
psql (9.5.3, server 9.3.10)
Type "help" for help.
postgres=#

Assumed that PostgreSQL server has already installed and active the above output will be displayed. Off course it is also assumed that the pg_hba.conf which is the configuration file for PostgreSQL authentication access is being correctly configured. It will ask for password when the correct information passed as login information which is the username along with the command. If the password is correct the connection to PostgreSQL Database Server will be succeed and it will directly passed to the PostgreSQL command console as shown above.