How to connect to PostgreSQL Server

This is part of our PostgreSQL Tutorial for Beginners. On this article, we are going to learn how to connect to PostgreSQL Server. Please navigate to our previous post to install PostgreSQL 10.4 on Ubuntu 18.04, Debian and all derivatives. There are two different ways to connect to PostgreSQL Server. 

• Via command line
• Using PgAdmin (GUI)

Connect to PostgreSQL via Command line

When you install PostgreSQL Server, it will also install an important tool for connecting to the server. The following example, we are going to connect to PostgreSQL Server from a Linux machine. The tool that we are talking about is “psql”. Its a command line tool to connect and manage the PostgreSQL Server from a command line environment. 

Switch to postgres user

In order to connect to PostgreSQL server via command line, we need to connect using postgres user. Otherwise you will get the following error.

dhani@dhani-thinkpad:~$ psql
psql: FATAL: role "dhani" does not exist

use this command to switch to postgres user on Linux

sudo -i -u postgres

Now execute psql command

psql

Output:

$ psql
psql (9.6.3)
Type "help" for help.

postgres=#

Now you are connected to the server. You can start using the SQL command to perform database operation. For example, this command will create a new database. Don’t forget the semicolon (;) at the end of the sql syntax.

create database mydatabase_01;

Output:

postgres=# create database mydatabase_01;
CREATE DATABASE

Now let’s list all the databases.

\l

Output:

To quit from psql, simply type the following syntax:

\q

For more details about the PostgreSQL Syntax, we will create a special post for this. Stay tuned on this blog for more PostgreSQL Tutorial.

Connect Using PgAdmin GUI

If you follow my previous tutorial to install PostgreSQL on Ubuntu 18.04, you will have a chance to install the PostgreSQL GUI Tool, PgAdmin. PgAdmin provides a web based GUI to manage the PostgreSQL server. It is a simple and easy to use GUI tool for PostgreSQL users. You can create new database, tables management, user management and many more. 

For Windows users, you can download and install PgAdmin for Windows. You can also find the PgAdmin installation binary for some Linux distributions.