Connecting to a PostgreSQL Database from the Command Line

PostgreSQL is a powerful open-source relational database management system. Connecting to a PostgreSQL database from the command line is essential for database administration, querying, and scripting. This guide walks you through installing libpq, using connection strings, and exploring alternative connection methods.

Installing libpq #

The PostgreSQL client library, libpq, includes essential command-line tools such as psql, which allows you to interact with a PostgreSQL database. Here’s how to install it on different operating systems:

Linux (Debian/Ubuntu) #

sudo apt update
sudo apt install libpq-dev postgresql-client

macOS (Using Homebrew) #

brew install libpq

If you receive a message that libpq is keg-only, you may need to manually add it to your system’s PATH. To make these configurations permanent in the Fish shell, add the following lines to your config.fish file (located at ~/.config/fish/config.fish):

# Add libpq to PATH
fish_add_path /opt/homebrew/opt/libpq/bin

# Set LDFLAGS for libpq
set -gx LDFLAGS "-L/opt/homebrew/opt/libpq/lib"

# Set CPPFLAGS for libpq
set -gx CPPFLAGS "-I/opt/homebrew/opt/libpq/include"

# Set PKG_CONFIG_PATH for libpq
set -gx PKG_CONFIG_PATH "/opt/homebrew/opt/libpq/lib/pkgconfig"

Save the file and restart your terminal or run source ~/.config/fish/config.fish to apply the changes immediately.

Windows #

For Windows, install the PostgreSQL binaries, which include libpq. You can download them from the official PostgreSQL website. Alternatively, you can use the Windows Subsystem for Linux (WSL) and install libpq as on Linux.

Connecting to a PostgreSQL Database #

Once libpq is installed, you can use psql to connect to a PostgreSQL database. There are multiple ways to specify the connection details.

Using a Connection String #

A connection string is a compact way to provide all necessary parameters in a single string. The general format is:

psql "postgresql://username:password@host:port/database"

If you have SSL enabled, you can modify the connection string like this:

psql "postgresql://myuser:mypassword@localhost:5432/mydatabase?sslmode=require"

Using Individual Parameters #

Instead of using a connection string, you can specify each parameter separately:

psql -h localhost -p 5432 -U myuser -d mydatabase

You will be prompted to enter the password unless using a password file (~/.pgpass).

Using Environment Variables #

PostgreSQL allows setting environment variables to avoid passing credentials in the command line. Common variables include:

export PGHOST=localhost
export PGPORT=5432
export PGUSER=myuser
export PGPASSWORD=mypassword
export PGDATABASE=mydatabase

This method improves security by avoiding credentials in command history.

Using a .pgpass File #

For automatic authentication without entering a password each time, create a ~/.pgpass file:

echo "localhost:5432:mydatabase:myuser:mypassword" >> ~/.pgpass
chmod 600 ~/.pgpass

Now, psql will read credentials from this file without prompting for a password.