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 command line 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.
- psql: A terminal-based front-end to PostgreSQL database server.
- 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
- What is PostgreSQL?
- Connect Using psql in PostgreSQL Command line
- Connect Using pgAdmin in PostgreSQL Command line
- 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.
Hevo offers a faster way to move data from Databases or SaaS Applications like PostgreSQL into your Data Warehouse to be visualized in a BI tool. Hevo is fully automated and hence does not require you to code.
Get Started with Hevo for Free
Check out some of the cool features of Hevo:
- Completely Automated: The Hevo platform can be set up in just a few minutes and requires minimal maintenance.
- Real-time Data Transfer: Hevo provides real-time data migration, so you can have analysis-ready data always.
- 100% Complete & Accurate Data Transfer: Hevo’s robust infrastructure ensures reliable data transfer with zero data loss.
- Scalable Infrastructure: Hevo has in-built integrations for 100+ sources (including 30+ free sources) that can help you scale your data infrastructure as required.
- 24/7 Live Support: The Hevo team is available round the clock to extend exceptional support to you through chat, email, and support calls.
- Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
- Live Monitoring: Hevo allows you to monitor the data flow so you can check where your data is at a particular point in time.
Sign up here for a 14-Day Free Trial!
Connect Using psql in PostgreSQL Command line
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.
- Documentation for pgAdmin and psql connect to database command line in PostgreSQL 8.4
- 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!