Databases

Connecting to a Database with psql from within the Shell

There are several ways of establishing a connection to a Postgres database. One of them is using the psql client that comes with the Postgres server installation.

In addition to executing commands from the shell psql allows - once connected to a database - to execute SQL commands.

Executing simply psql in the shell without any options will attempt to connect with a role name and the database name of the current system user. If no such role or database exists the attempt will fail.

To attempt to establish a connection with explicit role name use -U option.

$ psql -U foo

The above command will attempt to connect as the role with the name foo to the database with the name foo.

To attempt to establish a connection with the explicit database name use -d option.

$ psql -d bar

The above command will attempt to connect to the database name bar with the implicit role name of the system user.

The -U and -d options can be combined.

$ psql -U user -d bar

A shorthand for psql -U user -d bar is psql -U user bar.

To check the name of the currently connected role use the SELECT current_user; command.

To check the name of the currently connected database use the SELECT current_database(); command.

Connecting to a Database with psql from within the psql Prompt

Once connected to a database and being within the psql prompt it is possible to establish the connection to another database (which terminates the connection to the current database) using the \c command.

$ psql bar 

bar=> \c foo;
You are now connected to database "foo" as user "bar".

Connecting to a Database with Non-psql Clients

psql is not the only way to connect to a Postgres database nor is it the primary one.

The primary purpose of any database is to be used as a rapid write and read data storage by any application.

An application (such as a Django, Next.js or Ruby on Rails app) to establish a direct connection to a Postgres database needs to provide:

• host / socket e.g. 127.0.0.1 or foo.bar.compute.amazonaws.com,

• port e.g. 5432,

• role (user) name,

• role password (if any),

• database name,

• other - if necessary (e.g. SSL mode).

In addition it is possible to establish a connection to a remote Postgres database using SSH tunnel.

When the client and the server processes are located on separate hosts the communication is effected over TCP/IP protocol.

Creating a Database with the createdb Shell Command

To create a database from the shell use the createdb foo command where foo is the database name to be created.

Executing simply createdb will attempt to create a database with the name of the current system user and using the role with the name of the current system user.

Creating a Database with an SQL Command

To create a database with an SQL command use the CREATE DATABASE command.

CREATE DATABASE foo;

Dropping a Database with the dropdb Shell Command

To drop a database from the shell use the dropdb foo command.

Dropping a Database with an SQL Command

To drop a database with an SQL command use the DROP DATABASE command.

DROP DATABASE foo;