How to Connect to a PostgreSQL Database From Command Line in Linux

Introduction

PostgreSQL is a powerful, open source object-relational database system. It is a popular choice for many small and large projects and has the advantage of being standards-compliant and having many advanced features like reliable transactions and concurrency without read locks. In this tutorial, we will show you how to connect to a PostgreSQL database from the command line in Linux. We will cover the basic steps of connecting to a PostgreSQL database, creating a database, and running SQL queries. By the end of this tutorial, you will have a better understanding of how to connect to a PostgreSQL database from the command line in Linux.

How to Connect to a PostgreSQL Database From Command Line in Linux

1. Log in to your Linux system as a user with sudo or root privileges.

2. Install the PostgreSQL client packages by running the following command:

sudo apt-get install postgresql-client

3. Connect to the PostgreSQL database server by typing the following command. Replace username with your username and dbname with the name of the database you want to access:

psql -h localhost -U username dbname

4. Enter the password for the user when prompted.

5. You should now be connected to the PostgreSQL database server and can run SQL queries.
[ad_1]

Introduction

PostgreSQL is an open source relational database management system.

Psql is an interactive terminal program for working with PostgreSQL. Use psql to edit, automate, and execute queries in PostgreSQL.

pgAdmin is a web interface for managing PostgreSQL databases. It provides a visual, user-friendly environment with a host of practical solutions that make managing databases easy.

In this tutorial, you will learn how to connect to PostgreSQL from the command line using psql or pgAdmin.

connect to a postgresql database

Prerequisites

  • This guide assumes that you have already installed PostgreSQL and created a database you want to access.
  • Access to a command-line/terminal window
  • Sudo or root privileges
  • pgAdmin 4 installed

How to Connect to PostgreSQL Using psql

Installing PostgreSQL creates a default database and user account, both called ‘postgres.’

To log into the ‘postgres’ user account type the following command in the terminal:

sudo -i -u postgres

This example shows the command in a Debian-based distribution, Ubuntu.

Log in to Postgre with single command.

For the same result on a Red Hatbased system, (e.g., Centos and Fedora) use any of the following commands:

su postgres

or

su -i postgres

These commands open a bash shell and give the user ‘postgres’ root privileges within that shell.

The same command applies if you have already created a different user and a corresponding database with the same name. If a user called ‘test1’, that role will attempt to connect to a database called ‘test1’ by default.

To begin using psql, enter the command:

psql

The following screen confirms you are now able to edit and execute queries in PostgreSQL.

psql-comand-terminal-program-postgres

PostgreSQL can support and maintain a large number of databases and users simultaneously. Once you log in, it is easy to confirm the current connection and user information.

Simply enter the command:

\conninfo
Command to get information about your postgre connection.

The output helps to determine which user and database you are currently interacting with.

How to Access psql Directly Using sudo

It is possible to connect with PostgreSQL directly and bypass the intermediary bash shell.

If you are sure that all the components of your databases and users are correctly configured, you can log into psql directly:

sudo -i -u postgres psql

The -u (user) option causes sudo to run the specified command as a user other than root. As with the previous method, you can now work with databases by executing queries.

How to Access PostgreSQL With pgAdmin

The pgAdmin is a graphical tool for managing PostgreSQL databases. After installing and configuring the latest browser version of pgAdmin 4, you need to create an initial pgAdmin user account.

The basic setup requires an email and a password to authenticate access to the web interface.

python /usr/lib/python2.7/site-packages/pgadmin4-web/setup.py

Once the email and password are defined, access the pgAdmin4 interface by using:

http://localhost/pgadmin4 

Or:

http://ip-adress/pgadmin4

To authenticate, use the email address and password created previously. Once the user interface loads, add a PostgreSQL server by navigating to Servers > Create > Server.

How to access postgre using PGAdmin 3.

The General and Connection tabs allow you to enter values for your server name and database user credentials.

How to create new server with PgAdmin3.

The Hostname/address is the location of the machine where the PostgreSQL server is running. A connection with your user account is established once you save the information entered. The interface presents an overview of the databases that your user account has access to.

To enter and execute queries, click Tools > Query Tool or press ALT+Shift+Q within the current database.

query tool to enter and execute queries

Conclusion

This article provided two (2) simple solutions on how to connect to a PostgreSQL database.

If you are looking for a terminal-based solution, psql is an excellent choice for speed and effectiveness.

The GUI based pgAdmin provides a secure, user-friendly way to log in, administer, and shape databases to fit your requirements. A graphical interface can be indispensable when working on a host of databases simultaneously.

Alternatively, you can also connect to PostgreSQL with SQL Workbench.

[ad_2]

How to Connect to a PostgreSQL Database From Command Line in Linux

PostgreSQL is a powerful, open source object-relational database system. It has a strong reputation for reliability, data integrity, and correctness. It is a popular choice for many small and large projects and has the advantage of being standards-compliant and having many advanced features like reliable transactions and concurrency without read locks.

In this tutorial, we will show you how to connect to a PostgreSQL database from the command line in Linux. We will also show you how to create a new user and grant permissions to the user.

Prerequisites

  • A Linux system with PostgreSQL installed.
  • Access to a terminal window/command line.

Connect to PostgreSQL Database

To connect to the PostgreSQL database server, follow these steps:

  1. Open a terminal window.
  2. Connect to the PostgreSQL database server by typing the following command: psql -h hostname -U username -d database
  3. Enter the password when prompted.
  4. You should now be connected to the PostgreSQL database server.

Create a New User

To create a new user, follow these steps:

  1. Connect to the PostgreSQL database server.
  2. Create a new user by typing the following command: CREATE USER username WITH PASSWORD 'password';
  3. Grant permissions to the user by typing the following command: GRANT ALL PRIVILEGES ON DATABASE database TO username;
  4. The new user should now be created and have the necessary permissions.

Conclusion

In this tutorial, we showed you how to connect to a PostgreSQL database from the command line in Linux. We also showed you how to create a new user and grant permissions to the user.

Jaspreet Singh Ghuman

Jaspreet Singh Ghuman

Jassweb.com/

Passionate Professional Blogger, Freelancer, WordPress Enthusiast, Digital Marketer, Web Developer, Server Operator, Networking Expert. Empowering online presence with diverse skills.

jassweb logo

Jassweb always keeps its services up-to-date with the latest trends in the market, providing its customers all over the world with high-end and easily extensible internet, intranet, and extranet products.

GSTIN is 03EGRPS4248R1ZD.

Contact
Jassweb, Rai Chak, Punjab, India. 143518
Item added to cart.
0 items - 0.00