What Are Databases in PostgreSQL?
A Postgres database - and a computer database in general - is a collection of data stored in computer memory organized specifically for rapid write and read operations.
As already noted Postgres itself is not a database but a database management system (DBMS) or more specifically an relational database management system (RDBMS).
A given Postgres server instance features exactly one database cluster (a collection of databases) which in turn includes at least one database.
Postgres databases data is stored in tables - aka relations ("relation" is a mathematical term for "table").
Postgres database objects are organized by schemas.
Connecting to a Database
Before trying to connect to a Postgres database ensure that a Postgres server is running. The Postgres server process is called
postgres process manages database objects, performs actions on those objects and accepts connections to databases from client processes.
postgres process can accept multiple database connections at the same time.
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.
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
$ psql -U foo
The above command will attempt to connect as the role with the name
foo to the database with the name
To attempt to establish a connection with the explicit database name use
$ psql -d bar
The above command will attempt to connect to the database name
bar with the implicit role name of the system user.
-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
$ 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.
role (user) name,
role password (if any),
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
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.
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
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;
Listing Databases from within psql
To list all databases from within
psql from the current database cluster use the
\l internal command.