This article will be useful for those who have already installed PostgreSQL on their personal computer. In case you still haven’t, this blog post will come in handy. Now, we will focus on some of the most popular ways of accessing the PostgreSQL database on Windows. We will describe the PostgreSQL connection options using psql, pgAdmin, and fast and convenient dbForge Studio for PostgreSQL by Devart. Moreover, here you will find the instructions on connecting to local and remote PostgreSQL servers.
- Connecting to PostgreSQL Database Using psql
- Connect to PostgreSQL Database Server Using pgAdmin
- Connect to PostgreSQL Database from dbForge Studio for PostgreSQL
Connecting to PostgreSQL Database Using psql
psql is a front-end to PostgreSQL represented as a terminal. It allows you to connect to PostgreSQL from the command line. This application requires technical experience and presupposes interaction with PostgreSQL databases by typing in the commands, executing them, and getting the query results.
SQL Shell (psql) usually comes along with the PostgreSQL installation by default. To access the Postgres database terminal, type SQL Shell in the Windows search bar.
To connect to the Postgres database from the shell script, you will need to enter the following credentials:
psql offers a wide diversity of commands to manipulate PostgreSQL databases. Let us talk more specifically about some of the most common ones.
List tables in your database:
Describe a table:
List all schemas:
Connect to another database:
If you add + to any of the commands listed above, you will get some additional information in the output. For example,
\d+ tablename will result in an enlarged description of the table in question. To see more commands, click here.
Connecting to a Local Database Using psql
If your database is hosted on your personal computer, you will need to know how to connect to PostgreSQL localhost. As we have mentioned earlier, such connection requires entering Server, Database, Port, Username, and Password. The application suggests the default values right away:
Password for user postgres:
If you simply press Enter without entering any variables, they will be filled in automatically with the default values. Although, you will have to enter a password anyway.
You will not see any characters while entering the password. This is how psql protects your security. Just keep typing and press Enter.
In a couple of seconds, your query is processed and you are now connected to Postgres locally.
Connect to Remote Database Using psql
In case you are using more than one server, you still can use psql to connect to a remote database from your local machine. Technically speaking, this process does not differ much from the one we described earlier. The only difference is that you will have to enter the remote database credentials. Please, note that before connecting to a remote PostgreSQL server, it’s important to ensure that the server is configured to allow external connections.
The query will look somewhat like this in the psql command line:
You can enter either a hostname or an IP address for Server.
Connect to PostgreSQL Database Server Using pgAdmin
pgAdmin is the community client for using PostgreSQL. It is usually installed along with PostgreSQL. While psql is a simple command-line tool, pgAdmin is a graphical user interface that provides pretty much the same functionality.
To find the pgAdmin application on your computer, use the Windows search bar:
Using pgAdmin to Set Up Local Database
1. In Browser, select Servers. Then, click Objects, point to Create, and click Server.
3. In the dialog box that pops up, enter the local server name and go to the Connection tab.
4. Before clicking the Save button, enter the hostname and password for the postgres user.
5. After that, you will see the new Server in the menu:
Connect to Remote Database Using pgAdmin
Connecting to a remote database in pgAdmin very much resembles connecting to a local one. Just like in the previous part of this article, you will need to create a Server, enter the connection details into the corresponding fields in the pop-up window, and click Save.
However, make sure to type in the correct credentials for the remote database:
Connect to PostgreSQL Database from dbForge Studio for PostgreSQL
Connecting to a PostgreSQL database has never been easier. With dbForge Studio for PostgreSQL, it only takes a couple of clicks. In case you are willing to see it for yourself, you are always welcome to check out our free 30-day trial version of the solution.
To begin with, open the IDE. In the Database menu, click New Connection.
After that, you will see a pop-up Database Connection Properties window.
Type in the corresponding connection properties: Host, Port, User, and Password. Then, choose the desired database from the drop-down menu.
Finally, click Test Connection. If all the data was entered correctly, you should see a Successful Connection message. To save your work, hit Connect.
In this article, we have described the ways of connecting to local and remote PostgreSQL databases. SQL Shell (psql) is an easy way to connect and manipulate a database by means of command-line queries. As for pgAdmin, it might be more convenient for those who don’t wish to memorize tons of commands but would like to work in a more user-friendly environment. Finally, dbForge Studio for PostgreSQL is a perfect combination of an intuitive graphical interface and an augmented command line. You may also check our feature-by-feature comparison of pgAdmin and dbForge Studio and have a proper look at all the differences.
In case you are in a search of convenient database tools, check out our solutions for SQL Server, MySQL, and Oracle.