Right, let’s talk about getting in. The psql client is your primary teletype into the kingdom of Postgres. It’s how you’ll issue commands, panic when you see a query taking too long, and then frantically cancel it. But before you can do any of that, you need to connect. And Postgres, in its infinite wisdom, gives you approximately one million ways to do it. I’ll cover the ones you’ll actually use.

The Connection String: Your All-in-One Passport

This is the most common and often most convenient method. It’s a single string that bundles all the connection parameters together in a URL-like format. The basic syntax is:

postgresql://[user[:password]@][netloc][:port][/dbname][?param1=value1&...]

Now, let’s crack this open. The most minimalist version just specifies the database name on the local machine. It will assume your system username is the Postgres user and that no password is needed (which is often the default for local connections, a fact that would horrify a security auditor).

psql postgresql:///mydatabase

But we’re rarely that lucky. To connect to a remote server with a specific user and password, you’d use:

psql postgresql://myuser:mypassword@db.server.com:5432/production_db

Wait. Stop. Did you just type your password into the terminal? In clear text? It’ll be saved in your shell history for all to see. This is, technically speaking, a Terrible Idea™. We’ll fix that in a second. The connection string is powerful, but it’s a blunt instrument. Use it for quick tests, but lean on the next methods for real work.

Command-Line Flags: The Explicit Approach

Prefer to be explicit? Use flags. This is my go-to for scripts and one-liners because it’s perfectly clear what each component is.

psql -h db.server.com -p 5432 -d production_db -U myuser -W

The -W flag is the “I have a password, please prompt me for it” flag. This is infinitely better than putting it in the connection string because it won’t get logged. It’ll ask you interactively. The main flags are straightforward: -h for host, -p for port, -d for database, -U for user. Forgot them? psql --help is your brilliant, if terse, friend.

Environment Variables: The Lazy (and Smart) Person’s Choice

This is the professional’s move. Set these variables once in your shell session or profile, and psql (and most other Postgres client tools) will obediently use them as defaults for any missing connection parameters. No more typing the same hostname for the 500th time.

The key ones are:

  • PGHOST: The hostname. localhost is the default, which is why we often skip it.
  • PGPORT: The port. The default is 5432, which is why Postgres uses it. Don’t change it unless you have to; you’ll just confuse yourself later.
  • PGUSER: Your username.
  • PGPASSWORD: Tread carefully here. Yes, you can set the password in an environment variable. It’s better than the command line, but it’s still a risk. Your password could end up in logs or be visible in process lists. Just be aware.
  • PGDATABASE: The database to connect to.

So, instead of that long command above, you could do this in your shell:

export PGHOST=db.server.com
export PGUSER=myuser
export PGDATABASE=production_db
psql

It will still prompt for a password (unless you’ve set up trust or ident authentication), but you’ve saved yourself a ton of typing. This is fantastic for switching between different project environments.

The Password File: .pgpass for True Sanity

Remember me yelling about passwords on the command line? Here’s the solution: the .pgpass file. This is a hidden file in your home directory (~/.pgpass) where you can store passwords for specific connections. The format is simple but crucially specific:

hostname:port:database:username:password

You can use * as a wildcard for any field. So, to automatically supply a password for your production database, you’d create an entry:

db.server.com:5432:production_db:myuser:SuperSecretPassword123!

Critical Step: You MUST set the permissions on this file so that only you can read it. The psql client will ignore it if it’s world-readable. This is a fantastic feature.

chmod 600 ~/.pgpass

Now, you can just run psql -h db.server.com -U myuser production_db and it will connect seamlessly, without prompts or password leaks. This is the way.

The Service File: .pg_service.conf for the Truly Organized

If connection strings are still too messy for you, there’s one more layer of abstraction: the service file. You can define a named “service” with all your connection parameters in a file (~/.pg_service.conf or /etc/pg_service.conf).

[my-awesome-service]
host=db.server.com
port=5432
user=myuser
dbname=production_db

You can then reference it by name with the -S flag (or the PGSERVICE environment variable):

psql -S my-awesome-service

This is overkill for most individuals but can be a godsend in complex environments with dozens of connection profiles. It keeps the messy details out of your scripts and history.

The beauty—and sometimes frustration—of psql is that all these methods can be combined. psql will resolve them in a specific order of precedence, with command-line flags generally winning. My advice? Use environment variables for your most common defaults and .pgpass for security, and then use explicit flags for the one-off connections that break the mold.