Data Modelling - Databases in PostgreSQL

Introduction

PostgreSQL databases are the main containers that hold schema, tables, views, functions, and other database objects. Understanding how to create, connect, and manage databases is fundamental to working with PostgreSQL.

Creating a Database

A new database can be created using the CREATE DATABASE statement. Here's an example:

CREATE DATABASE mydatabase;
                    

CREATE DATABASE
                    

This command creates a new database named mydatabase.

Connecting to a Database

To start working with a specific database, you need to connect to it. This can be done using the \c command in the psql client:

\c mydatabase;
                    

You are now connected to database "mydatabase" as user "yourusername".
                    

This command connects you to the mydatabase database.

Listing Databases

You can list all available databases in PostgreSQL using the \l command in the psql client:

\l
                    

                                   List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges   
-----------+----------+----------+---------+-------+-----------------------
 mydatabase | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
(4 rows)
                    

This command lists all databases, their owners, encodings, collations, and access privileges.

Dropping a Database

A database can be removed from the system using the DROP DATABASE statement. For example:

DROP DATABASE mydatabase;
                    

DROP DATABASE
                    

This command deletes the mydatabase database.

Note: You cannot drop a database that is currently connected. You must first disconnect from the database before dropping it.

Backing Up a Database

PostgreSQL provides the pg_dump utility for backing up a database. Here is an example command to back up a database:

pg_dump mydatabase > mydatabase_backup.sql
                    

[No output, command executes]
                    

This command creates a backup of the mydatabase database and saves it to the mydatabase_backup.sql file.

Restoring a Database

You can restore a database from a backup using the psql utility. Here is an example command to restore a database:

psql mydatabase < mydatabase_backup.sql
                    

[No output, command executes]
                    

This command restores the mydatabase database from the mydatabase_backup.sql file.