This article will be useful for those who have already installed PostgreSQL on personal computers. In case you still haven’t, this blog post will come in handy. Now, we will focus on 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.
Content
- Connecting to PostgreSQL databases using psql
- Connecting to PostgreSQL databases with pgAdmin
- Connecting to PostgreSQL from dbForge Studio for PostgreSQL
- Most frequent connection errors in PostgreSQL
- Conclusion
Connecting to PostgreSQL databases using psql
psql is a command-line utility coming by default with any PostgreSQL installation that allows you to connect to PostgreSQL and execute various commands to create and manage Postgres databases.
After installing PostgreSQL, you can access this database terminal by searching for it in the Windows search bar. Just type psql and switch to the Apps section:
To connect to the Postgres database from this SQL Shell app, you need the following credentials:
- Server
- Database
- Port
- Username
- Password
How to connect to a local database
If the database is hosted on your personal computer, you can connect to localhost. As we have mentioned earlier, it requires Server, Database, Port, Username, and Password.
The application suggests the default values right away:
Server [localhost]:
Database [postgres]:
Port [5432]:
Username [postgres]:
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.
Note
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, you will get connected to Postgres locally.
How to connect to a remote database
In case you are using several servers, and some of them are remote, you can use psql to connect to remote servers and work with databases on them from your local machine. Actually, the only difference in the connection process is the necessity to enter the credentials for that remote server and the database.
Note
You can enter either a hostname or an IP address for Server.
Before connecting to a remote PostgreSQL server, it’s important to ensure that the server is configured to allow external connections.
How to exit psql
The psql utility is a very lightweight and quick tool, and its commands are short. The simplest way to exit the psql utility is using the below command:
\q
Note
After exiting the psql tool, you need to perform all the steps to connect to PostgreSQL anew. Therefore, make sure to complete the tasks you worked on and exit the database before quitting the psql tool.
Common commands in psql
psql offers a broad range of commands to manipulate PostgreSQL databases.
To list all databases hosted on the server, execute the following command:
\l
To connect to any particular database, use the below command:
\c <database_name>
To list tables in a PostgreSQL database, you can use the \d command to get the basic information or the \dt+ command for the detailed information about the tables (if you add + to commands, you will get additional information in the output):
To describe any specific table, execute \d tablename
You can use psql to list schemas in PostgreSQL, review Postgres functions, and in many other tasks. To see more commands, click here.
Connecting to PostgreSQL databases with pgAdmin
pgAdmin is the free community client for PostgreSQL that is usually installed along with PostgreSQL. While psql is a plain command-line tool, pgAdmin is a graphical user interface (GUI) that provides pretty much the same functionality in a visual mode.
To find the pgAdmin application on your computer, use the Windows search bar under the Apps section:
How to use pgAdmin to set up local databases
Click Add New Server.
In the dialog box that opens, give that server a name on the General tab and proceed to the Connection tab. Enter your hostname and password and click Save.
After that, you will see the newly-added server with the databases hosted there in the Object Explorer pane:
How to connect to remote databases
Connecting to a remote database in pgAdmin is similar to connecting to a local one. You need to add a new server, give it a name, and enter the connection details into the corresponding fields.
Click Save, and the remote server will be added.
Connecting to PostgreSQL from dbForge Studio for PostgreSQL
dbForge Studio for PostgreSQL is a multi-featured integrated development environment (IDE) designed for PostgreSQL and Amazon Redshift specialists and a powerful alternative to pgAdmin. A robust toolset covers all standard tasks of database development and management along with data analysis and reporting, delivering all the functionality in a neat visual interface for the user’s convenience.
The Studio offers a comprehensive set of features for Postgres specialists of any skill level, all within a single IDE. An intuitive SQL editor and formatter, advanced tools for database comparison and synchronization (covering both the database schemas and table data), and professional data migration, as well as data analysis and reporting capabilities, improve every aspect of your workflow. Additionally, advanced customization and automation options allow the users to get rid of manual routine tasks, enhance overall productivity, and save costs significantly.
With dbForge Studio for PostgreSQL, connection to the server takes a couple of clicks.
Open the Studio and click New Connection.
Enter the connection properties: Host, Port, User, and Password. Then, choose the desired database from the drop-down menu, and click Connect.
Most frequent connection errors in PostgreSQL
Connecting to PostgreSQL is typically straightforward when all necessary details are provided. However, errors can occasionally occur due to common issues, which are often easy to resolve. Below, we review the most frequent connection errors and how to fix them.
1. No such file or directory
This error occurs when the local server is not running, or the hostname or port are specified incorrectly.
Fix: Verify that the PostgreSQL server is running and that the hostname and port (default is 5432) are correct. The port must be open and accessible.
2. Connection refused
This error indicates the PostgreSQL server is not responding to a network connection attempt. It often occurs if the server is down, listening on a different port, or is blocked by a firewall.
Fix: Check the server status, confirm the port settings, and ensure the specified port matches the configuration. You may need to review server logs and address any network-related issues, such as firewall block
3. Authentication failed
This error, commonly shown as “authentication failed for user…,” arises when the provided username or password is incorrect.
Fix: Double-check the connection credentials and ensure the username and password match the PostgreSQL user configuration.
4. Database does not exist
This error indicates that the specified database does not exist on the server.
Fix: Verify the database name is correct. You may need to create the database if it does not already exist.
Alternatively, omit the default database during the initial connection process. Instead, connect to the server and use the \l command to list all available databases. Once identified, use the \c database_name command to connect to a specific database
5. Server terminated the connection
The server unexpectedly closes the connection, which signals a critical error.
Fix: Check the PostgreSQL logs to identify the root cause. If necessary, contact the system administrator for further assistance.
6. Too many clients already
This error occurs when the maximum allowed number of connections to the database is exceeded. The limit is controlled by the max_connections parameter.
Fix: Review the current connection count and terminate unnecessary connections if possible
If needed, adjust the max_connections parameter value in the PostgreSQL configuration file to allow more connections.
Common errors can be avoided with proactive checks and proper server configuration. By understanding the root cause of each issue, you can ensure a smooth and reliable connection to your PostgreSQL database.
Conclusion
We have described different 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 can try dbForge Studio for PostgreSQL under full workload using the fully functional free trial that is provided for 30 days. Download the software, install it, and explore all its capacities in your daily workflows!
If your daily tasks involve working with multiple database management systems—a common scenario for most organizations, as relying on a single DBMS is often impractical—Devart provides a powerful multi-database solution: dbForge Edge. It handles database tasks in Microsoft SQL Server, MySQL, MariaDB, Oracle, and PostgreSQL, offering robust functionality for all database routines, from generating high-quality code to managing version control.
