The basic connection process

Connecting to a PostgreSQL database requires four pieces of information: the hostname (or IP address) of the server, the port number (usually 5432), the database name, and your username and password. Once you have these details, you can connect using the command line, a graphical tool, or code in your application.

The simplest way to test a connection is through psql, PostgreSQL's built-in command-line client. If PostgreSQL is installed on your machine, you can open a terminal and type a connection command. If you're connecting to a remote server, you'll need the server's address and your credentials. If you're connecting to a local database on your own machine, you may only need the database name.

Key Takeaways

  • You need the hostname, port number, database name, username, and password to connect to any PostgreSQL database.
  • The psql command-line tool is the fastest way to test whether a connection works before building an application.
  • Connection strings format this information in a single line that your code can read, and the format varies slightly by programming language.
  • Most connection problems come from incorrect hostnames, firewalls blocking port 5432, or wrong credentials — test each one separately.
  • Graphical tools like pgAdmin and DBeaver let you manage databases and run queries without writing command-line syntax.

Connecting from the command line with psql

The psql command takes the form psql -h hostname -U username -d database_name. Replace hostname with the server address (or localhost if the database is on your machine), username with your PostgreSQL user, and database_name with the name of the database you want to access. After you press Enter, psql will prompt you for the password.

For a local database where you are the system user, you can often skip some flags. Typing just psql database_name may work if PostgreSQL is configured to trust your system login. Once connected, you'll see a prompt like database_name=#, which means you can type SQL commands directly.

If the connection fails, psql will tell you why — "could not translate host name" means the hostname is wrong, "connection refused" usually means the server is not running or the port is blocked, and "password authentication failed" means the username or password is incorrect. Test each part separately before moving forward.

Connection strings for applications

When you write code that connects to PostgreSQL, you pass a connection string instead of typing commands manually. A connection string bundles all the connection details into one line that your code can read. The format depends on what language or library you're using, but they all contain the same information.

In Python with the psycopg2 library, a connection string looks like postgresql://username:password@hostname:5432/database_name. In Node.js with the pg library, you might write it as a separate object with properties for host, port, user, password, and database. In Java with JDBC, the format is jdbc:postgresql://hostname:5432/database_name. Check your library's documentation for the exact syntax, but the pieces are always the same.

Never hardcode passwords directly in your code. Instead, store them in environment variables or a configuration file that is not committed to version control. Most production systems use a secrets manager or a .env file that your code reads at startup.

Using graphical tools like pgAdmin and DBeaver

If you prefer not to type commands, pgAdmin and DBeaver are graphical applications that let you connect to PostgreSQL databases and run queries through a user interface. Both are free and work on Windows, Mac, and Linux.

In pgAdmin, you right-click on "Servers" in the left panel, choose "Register" and then "Server", and fill in the hostname, port, username, and password in a form. Once saved, pgAdmin connects and shows you all the databases, tables, and other objects on that server. You can write SQL queries in the editor window and see results immediately.

DBeaver works similarly: you go to File > New Database Connection, choose PostgreSQL from the list, enter the connection details, and test the connection before saving. DBeaver also works with many other databases, so if you work with multiple systems, a single tool can manage them all. Both tools let you browse the database structure, edit data, and export results to CSV or other formats.

Troubleshooting common connection problems

The most common reason a connection fails is that the hostname is wrong or the server is not reachable. If you are connecting to a remote server, verify the hostname by asking the database administrator or checking your documentation. If you are connecting to localhost and it fails, make sure PostgreSQL is actually running on your machine — on Windows, check Services; on Mac or Linux, try sudo systemctl status postgresql.

Firewall and network rules are the second most common culprit. PostgreSQL uses port 5432 by default, and if a firewall blocks that port, the connection will time out or be refused. If you control the server, check that PostgreSQL is listening on the correct port (look in the postgresql.conf file). If you do not control the server, ask the administrator whether port 5432 is open to your IP address.

Wrong credentials are straightforward to fix: double-check the username and password, and make sure you are using the correct database name. PostgreSQL usernames are case-sensitive on most systems. If you have forgotten the password, you will need to reset it through the system administrator or by restarting PostgreSQL in single-user mode (a process that varies by operating system).

Connection pooling for applications

If your application makes many connections to PostgreSQL, opening a new connection each time is slow and wastes resources. Connection pooling keeps a set of connections open and reuses them, so your code asks the pool for a connection instead of creating a new one.

PgBouncer is a lightweight connection pooler that sits between your application and PostgreSQL. Your application connects to PgBouncer on localhost, and PgBouncer manages the actual connections to the PostgreSQL server. This is especially useful if you have many short-lived connections, like in a web application that handles thousands of requests per second.

Most PostgreSQL libraries also have built-in pooling. In Python, the psycopg2 library has a connection pool class. In Node.js, the pg library lets you create a pool object that manages multiple connections. Check your library's documentation for how to enable pooling and set the pool size.

Frequently Asked Questions

What is the default port for PostgreSQL?

PostgreSQL listens on port 5432 by default. If the server administrator has changed it, you will need to specify the custom port in your connection command or string using the -p flag in psql or the port parameter in your code.

Can I connect to PostgreSQL over the internet?

Yes, but you need the server's public IP address or domain name, and the firewall must allow connections on port 5432 from your location. For security, many administrators restrict remote connections to specific IP addresses or require a VPN. Ask your database administrator whether remote access is permitted and what credentials to use.

What does "peer authentication failed" mean?

This error occurs when PostgreSQL tries to match your system username to a PostgreSQL user and fails. It usually happens on local connections. Create a PostgreSQL user that matches your system username, or use the -U flag to specify a different PostgreSQL user when connecting.

How do I know if my connection is encrypted?

By default, psql and most libraries do not encrypt the connection. To use SSL/TLS encryption, add sslmode=require to your connection string or use the -d flag with psql. The server must have SSL certificates installed and enabled. Ask your administrator whether SSL is available and whether it is required.

Can I connect to PostgreSQL from a web browser?

Not directly — PostgreSQL is not a web service. You need a tool like pgAdmin, which runs in your browser and connects to PostgreSQL on your behalf. Alternatively, you can build a web application in Python, Node.js, or another language that connects to PostgreSQL and displays the results in a browser.