Introduction to PostgreSQL and Your First Database

Connecting to PostgreSQL

PostgreSQL is already installed on the virtual machine. Click the Start PostgreSQL button to set up the environment. Once the environment is ready, open the terminal.

Installing PostgreSQL

First, update the package list:

sudo apt update

Then install PostgreSQL and the contrib package:

sudo apt install postgresql postgresql-contrib

The postgresql package is the database server. postgresql-contrib is an add-on that is commonly installed alongside it.

Checking the Service Status

After installation, the PostgreSQL service is already running. To verify, check its status:

systemctl status postgresql

The output should show that the service is active and working.

Connecting to psql

Connect to the PostgreSQL interactive terminal:

sudo -u postgres psql

The # prompt indicates you are in superuser mode.

Listing Existing Databases

List all databases with:

\l

You will see three system databases that come with PostgreSQL. Do not modify them.

Creating a Database

Create a new database:

CREATE DATABASE sandbox;

The output CREATE DATABASE confirms success. SQL keywords are written in uppercase, while identifiers like database names are in lowercase. This convention improves readability.

Verify the database was created:

\l

You should see sandbox in the list.

Using pgAdmin

pgAdmin is a visual interface for PostgreSQL. Open the pgAdmin link provided in the environment setup. Log in using the credentials from the environment file (email, password, host, and database password).

Registering a Server

1. Right-click on Servers and select Register Server.
2. In the General tab, enter a name (e.g., postgresvm).
3. Go to the Connection tab. Enter the host name (from the environment, e.g., localhost) and the password (e.g., postgres).
4. Click Save.

The server appears in the left panel.

Creating a Database in pgAdmin

1. Expand the server, right-click on Databases, and select Create Database.
2. Enter the database name (e.g., demo) and click Save.

The new database appears under Databases. This is the same operation as CREATE DATABASE demo in the terminal, just with a graphical interface.