Connecting to a PostgreSQL Database

Connecting to a PostgreSQL Database can be accomplished in several ways.  Generally, you will need to know a few details about the PostgreSQL database that you are connecting to.

  • Hostname
  • Port
  • Database Name
  • Username
  • Password

Once you have the information above you should be able to connect to your PostgreSQL database.  The most common application used to connect to PostgreSQL is a client application that comes with the database called psql.  Using psql and connecting to a database may be as simple as just typing “psql databasename”.  This simple command run as the postgres user on a database host may connect you to the database as a Superuser.  You will probably need to utilize a bit more information to connect to your PostgreSQL data.  Here is common psql string used to connect to a database on a different host from a Linux host.

psql -h -p 5432 -d mydb -U db_user

This command will connect to the database called mydb on the server  It will use port 5432 and connect as the db_user.  Notice no password is included.  In this case you will be prompted for a password.  Please do not include passwords on the command line, they can be read by other privileged users.  If you want to avoid being prompted you can set an OS variable PGPASSWORD

export PGPASSWORD='h4%[email protected]'

No command line for me, I need a GUI.

Other methods of connecting to your PostgreSQL database include any number of GUI applications.  These applications may run on Windows, Mac, Linux or all platforms.

A few tips to keep your local database administrator happy.

  • Do not stay connected to the database unless you need that connection.
  • Close your transactions or your DBA may close them for you.
  • Turn off any automatic monitoring script your client may run.

Next: Selecting data in PostgreSQL

Was this post helpful?