Installing and connecting

In plain English

The PostgreSQL server is the background program that actually stores your data, while a client is any program, such as psql, that connects to it and asks for data. psql is the official command-line client you open in a terminal to type SQL. A connection string packs everything the client needs into one short line: the host, the port, the database name, the user and the password. A role is a database user account, and the database is the named container where your tables live. Installing PostgreSQL gives you a server on your own machine and a psql client to talk to it.

Step by step

1. 1Install PostgreSQL; it runs a server in the background, usually listening on port 5432.
2. 2Open psql and connect to the default postgres database.
3. 3Create a role for your app with login and a password.
4. 4Create a database owned by that role.
5. 5Connect to the new database using a connection string.

create role app login password 'secret';
create database myapp owner app;

Common mistakes

• Doing normal work in psql as the superuser (often named postgres) is risky, because that account can change or drop anything.
• Forgetting that the server and the client are separate programs causes confusion, because the server must already be running before psql can connect.
• Storing your app data in the default postgres database mixes it with system data, so create a dedicated database instead.
• Committing a connection string with a real password into source control leaks a credential.