How to Use PostgreSQL Command line to Manage Databases? | Made Easy

A PostgreSQL Command line, in general, is a software program. And, we execute a command line on a Command Line Interface (CLI) to directly interact with data on a database. Although a Graphical User Interface (GUI) provides a much better user experience, PostgreSQL CLI acts vital when managing applications or operating systems with greater control, reliability, and conventionality.

PostgreSQL is an RDBMS (Relational Database Management System) that allows effortless and secured data collection. Today, PostgreSQL is used as the primary data storage repository (as a data warehouse) for many websites, mobile and analytics applications. And, you would have noticed (when you were installing the PostgreSQL database server) some tools that offer data professionals with a high understanding of SQL a seamless way to manage databases in PostgreSQL using command lines.

The tools are known as an interactive terminal program — psql and pgAdmin.

1. psql: A terminal-based front-end to PostgreSQL database server.
2. pgAdmin: A web-based front-end to PostgreSQL database server.

We can use psql (terminal or command line) and pgAdmin connection tool to connect our PostgreSQL database and manage it, too.

This tutorial article will explain how to use the interactive terminal programs to connect and manage your PostgreSQL database in the Command Line Interface. And to get a sound understanding of the subject, we will also discuss, in brief, about PostgreSQL and its features.

Table of Contents

1. What is PostgreSQL?
2. Connect Using psql in PostgreSQL Command line
   • Access psql Directly Using sudo in PostgreSQL Command line
3. Connect Using pgAdmin in PostgreSQL Command line
4. Conclusion

What is PostgreSQL?

PostgreSQL is an open-source relational database management system (RDBMS). PostgreSQL, with its top-of-the-class performance optimization and advanced datatypes features, has made a name for itself in the data community. Thanks to its feature-rich suite experience, robust and reliable performance, PostgreSQL has become the preferred choice, ranking as the 4th most popular database management system worldwide.

PostgreSQL operates flawlessly with all major operating systems like Linux, Windows, and Mac OS. The fact that PostgreSQL offers multi-application interoperability, the platform provides its users with an advantage to manage their databases with a high degree of control and flexibility.

Some key features of PostgreSQL are as follows:

• It’s open-source, hence free: PostgreSQL is an open-source RDBMS which makes it suitable for a young team of developers. Moreover, it’s easy to download and compatible with all major operating systems.
• It’s known for its diverse user base: Apple, Cisco, Etsy, Facebook, Instagram, Red Hat, Skype, Spotify, and Yahoo all have one thing in common, all leverage PostgreSQL for their everyday database needs. 
• It’s highly reliable and compatible with multiple data types: PostgreSQL’s write-ahead logging makes it a highly fault-tolerant database. Moreover, high compatibility with multiple data types makes its use cases synonymous with today’s data professionals’ pain points, making it the de-facto solution.
• And, it’s secure too: PostgreSQL offers a robust access control system and follows all major security compliance frameworks. To include a few, PostgreSQL is LDAP, GSSAPI, SSPI, etc. complaint. Learn more.

Connect Using psql in PostgreSQL Command line (CLI)

Now, to get started with psql Connect to database command line you, first have to install PostgreSQL, After installing PostgreSQL, a default database and user account will be created, named ‘postgres.’ Now, to log into the database, run the command line provided below in the Command Line Interface of your operating system.

sudo -i -u postgres

The below command is an example from Ubuntu, a Linux-based operating system.

To get the desired result in Centos or Fedora (Red Hat-based systems) run either of the two following commands:

su postgres

or

su -i postgres

The commands mentioned above will provide you with root privileges on the user account ‘postgres.’

Note: These commands will also work for other user names too. For example, if a user is called “ThisIsATest,” then, in the command lines, “postgres” will be replaced with “ThisIsATest” by default.

Now, to start using psql connect to database command line, enter the command provided below:

psql

The concurrent screen will follow (shown below). This confirms that you can now manage — edit and execute queries — PostgreSQL database.

It’s easy to manage a large number of databases and users in PostgreSQL. You can identify the current connection or identify users by simply running the command line provided below:

conninfo

The following screen will confirm which user and database are currently underusing.

Access database Directly using ‘sudo’ in PostgreSQL Command line

It is possible to directly access the PostgreSQL database directly, bypassing the bash shell. Using the below command line, you can easily access your database, but only if you are sure every aspect of your database is correctly configured.

sudo -i -u postgres psql

Note: With the previous method you worked with databases by running queries. But, by using the sudo method, the -u (user) option makes ‘sudo’ to run some special commands as a user, not the root. 

Connect Using pgAdmin in PostgreSQL Command line

Used as a graphical tool in PostgreSQL to manage databases, pgAdmin is a web-based front-end to PostgreSQL database server. To access the database using pgAdmin, first, you will need to install and configure the latest version of your browser and create a new pgAdmin user account. An email and a password are required to authenticate access.

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

After the authentication process is complete, you can access the phAdmin 4 interface using the command line given below:

http://localhost/pgadmin4 

or

http://ip-adress/pgadmin4

Now, use the email and password created previously to authenticate the user. Now, the user interface will load and now you would need to navigate as shown in the image below. (Servers>Create>Server)

Under the “Create – Server” section, the General and Connection tabs will now be used to enter values such as server name and user credentials for the database.

Now that the connection to your database has been established, the following interface will provide an overview of the user’s databases. You can execute queries by clicking on Tools > Query Tool or by pressing ALT + Shift + Q.

Conclusion

Let’s conclude. Through this tutorial article, we provided two ways to connect to the database command line. First, by using the psql method, and second, by using the pgAdmin method. And if you need to learn more on the subject, either of these two articles can help.

1. Documentation for pgAdmin and psql connect to database command line in PostgreSQL 8.4
2. Documentation for pgAdmin and psql connect to database command line in PostgreSQL 9.1

On that mote, extracting and managing complex data, from a diverse set of data sources can be a challenging task and this is where Hevo can help!

Hevo Data offers a faster way to move data from Databases or SaaS applications into your Data Warehouse to be visualized in a BI tool. Hevo is fully automated and hence does not require you to code.

Visit our Website to Explore Hevo

Hevo Data will automate your data transfer process, hence allowing you to focus on other aspects of your business like Analytics, Customer Management, etc. This platform allows you to transfer data from 100+ multiple sources to Cloud-based Data Warehouses like Snowflake, Google BigQuery, Amazon Redshift, etc. It will provide you with a hassle-free experience and make your work life much easier.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand

You can also have a look at our unbeatable pricing that will help you choose the right plan for your business needs!