Database Management Basics with PostgreSQL

Introduction

In this lab, you will learn the fundamentals of database management using PostgreSQL. The lab covers essential operations for managing databases, including creating new databases, listing and connecting to existing ones, and removing databases when they are no longer needed.

You will begin by creating a new database using the createdb command and verifying its creation through the psql command. Then, you will learn how to list all available databases and connect to a specific database. Finally, you will explore how to drop a database and view database metadata. This lab provides a hands-on introduction to basic database administration tasks in PostgreSQL.

This is a Guided Lab, which provides step-by-step instructions to help you learn and practice. Follow the instructions carefully to complete each step and gain hands-on experience. Historical data shows that this is a beginner level lab with a 96% completion rate. It has received a 98% positive review rate from learners.

Create a New Database

In this step, you will learn how to create a new database in PostgreSQL. Creating a database is the first step in organizing and storing your data.

Before we begin, let's understand what a database is. A database is a structured collection of data that is organized for easy access, management, and updating. In PostgreSQL, a database is a container for tables, views, and other database objects.

To create a new database, you'll use the createdb command. This command is a wrapper around the SQL command CREATE DATABASE.

Open a terminal: If you don't already have one open, launch a terminal window.
Create a new database: Use the createdb command followed by the name you want to give your database. For example, to create a database named mydatabase, run the following command:
```
createdb mydatabase
```
This command will create a new database named mydatabase. If the command is successful, you won't see any output.
Verify the database creation: To confirm that the database has been created, you can list all the databases using the psql command. First, connect to the postgres default database:
```
psql -d postgres
```
You will be presented with the postgres=# prompt.

List databases: At the postgres=# prompt, execute the following SQL command to list all databases:

\l

This command will display a list of databases, including the one you just created (mydatabase). You should see output similar to this:

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

Notice that mydatabase is in the list.

Exit psql: To exit the psql prompt, type:
```
\q
```
and press Enter. You will return to the regular terminal prompt.

You have now successfully created a new database in PostgreSQL.

List and Connect to Databases

In this step, you will learn how to list available databases and connect to a specific database using the psql command-line tool.

Open a terminal: If you don't already have one open, launch a terminal window.

List available databases: To list all databases, you can use the psql command with the -l option (that's a lowercase 'L'). This option lists all databases on the server. Since you haven't specified a database to connect to, psql will attempt to connect using your current username.

psql -l

This command will display a list of databases, along with their owners, encodings, and other information. You should see output similar to this:

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

This output shows the names of the databases, their owners, and other details. You should see the mydatabase database that you created in the previous step.

Connect to a specific database: To connect to a specific database, use the psql command with the -d option, followed by the name of the database. For example, to connect to the mydatabase database, run the following command:
```
psql -d mydatabase
```
If the connection is successful, you will be presented with the mydatabase=# prompt. This indicates that you are now connected to the mydatabase database.

Verify the connection: To verify that you are connected to the correct database, you can execute a simple SQL command, such as:

SELECT version();

This command will display the version of the PostgreSQL server you are connected to. The output will look something like this:

                                                 version
----------------------------------------------------------------------------------------------------------------
 PostgreSQL 14.7 (Debian 14.7-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
(1 row)

Exit psql: To exit the psql prompt, type:
```
\q
```
and press Enter. You will return to the regular terminal prompt.

You have now successfully listed the available databases and connected to a specific database using psql.

Drop a Database

In this step, you will learn how to drop (delete) a database in PostgreSQL. Dropping a database is a permanent action, so it's important to be careful when using this command. All data within the database will be lost.

Open a terminal: If you don't already have one open, launch a terminal window.
Drop the database: Use the dropdb command followed by the name of the database you want to drop. For example, to drop the mydatabase database, run the following command:
```
dropdb mydatabase
```
This command will drop the mydatabase database. If the command is successful, you won't see any output.

Important: Before dropping a database, ensure that no one is connected to it. If there are active connections, the dropdb command might fail.
Verify the database deletion: To confirm that the database has been dropped, you can list all the databases using the psql command. First, connect to the postgres default database:
```
psql -d postgres
```
You will be presented with the postgres=# prompt.

List databases: At the postgres=# prompt, execute the following SQL command to list all databases:

\l

This command will display a list of databases. Verify that mydatabase is no longer in the list.

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

If mydatabase is not listed, it has been successfully dropped.

Exit psql: To exit the psql prompt, type:
```
\q
```
and press Enter. You will return to the regular terminal prompt.

You have now successfully dropped a database in PostgreSQL. Remember to exercise caution when dropping databases, as this action is irreversible.

View Database Metadata

In this step, you will learn how to view database metadata in PostgreSQL. Metadata is "data about data." In the context of a database, metadata describes the structure and characteristics of the database itself, including information about tables, columns, data types, and constraints.

Open a terminal: If you don't already have one open, launch a terminal window.
Connect to the postgres database: Since we dropped mydatabase in the previous step, we'll connect to the default postgres database to explore metadata.
```
psql -d postgres
```
You will be presented with the postgres=# prompt.

List tables: To list all tables in the currently connected database, you can use the \dt command.

\dt

Since we haven't created any tables yet, the output should be empty or show system tables. It might look like this:

Did not find any relations.

             List of relations
 Schema |         Name          | Type  |  Owner
--------+-----------------------+-------+----------
 pg_catalog | pg_aggregate          | table | postgres
 pg_catalog | pg_am               | table | postgres
 pg_catalog | pg_amop             | table | postgres
...

The output shows the schema, name, type, and owner of each table.

Describe a table: To view detailed information about a specific table, you can use the \d command followed by the table name. Since we don't have a user-created table, let's examine a system table, for example, pg_class.

\d pg_class

This command will display the structure of the pg_class table, including column names, data types, and any constraints. The output will be quite extensive, but it provides a comprehensive overview of the table's definition.

                                     Table "pg_catalog.pg_class"
      Column       |   Type    | Collation | Nullable | Default | Storage  | Stats target | Description
-------------------+-----------+-----------+----------+---------+----------+--------------+-------------
 oid               | oid       |           | not null |         | plain    |              |
 relname           | name      |           | not null |         | plain    |              |
 relnamespace      | oid       |           | not null |         | plain    |              |
 reltype           | oid       |           | not null |         | plain    |              |
 reloftype         | oid       |           | not null |         | plain    |              |
 relowner          | oid       |           | not null |         | plain    |              |
 relam             | oid       |           | not null |         | plain    |              |
 relfilenode       | oid       |           | not null |         | plain    |              |
 reltablespace     | oid       |           | not null |         | plain    |              |
 relpages          | integer   |           | not null |         | plain    |              |
 reltuples         | real      |           | not null |         | plain    |              |
 relallvisible     | boolean   |           | not null |         | plain    |              |
 reltoastrelid     | oid       |           | not null |         | plain    |              |
 relhasindex       | boolean   |           | not null |         | plain    |              |
 relhasrules       | boolean   |           | not null |         | plain    |              |
 relhastriggers    | boolean   |           | not null |         | plain    |              |
 relhassubclass    | boolean   |           | not null |         | plain    |              |
 relrowsecurity    | boolean   |           | not null |         | plain    |              |
 relforcerowsecurity| boolean   |           | not null |         | plain    |              |
 relispopulated    | boolean   |           | not null |         | plain    |              |
 relreplident      | character |           | not null |         | plain    |              |
 relispartition    | boolean   |           | not null |         | plain    |              |
 relrewrite        | oid       |           | not null |         | plain    |              |
 relacl            | aclitem[] |           |          |         | plain    |              |
 reloptions        | text[]    |           |          |         | plain    |              |
 relpartbound      | pg_node_tree |           |          |         | extended |              |
Indexes:
    "pg_class_oid_index" UNIQUE, btree (oid)
    "pg_class_relname_nsp_index" UNIQUE, btree (relname, relnamespace)
Foreign-key constraints:
    "pg_class_relam_fkey" FOREIGN KEY (relam) REFERENCES pg_am(oid)
    "pg_class_relnamespace_fkey" FOREIGN KEY (relnamespace) REFERENCES pg_namespace(oid)
    "pg_class_relowner_fkey" FOREIGN KEY (relowner) REFERENCES pg_authid(oid)
    "pg_class_reltoastrelid_fkey" FOREIGN KEY (reltoastrelid) REFERENCES pg_class(oid)
Referenced by:
    TABLE "pg_statistic_ext" CONSTRAINT "pg_statistic_ext_relid_fkey" FOREIGN KEY (relid) REFERENCES pg_class(oid)
    TABLE "pg_rewrite" CONSTRAINT "pg_rewrite_ev_class_fkey" FOREIGN KEY (ev_class) REFERENCES pg_class(oid)
    TABLE "pg_trigger" CONSTRAINT "pg_trigger_tgrelid_fkey" FOREIGN KEY (tgrelid) REFERENCES pg_class(oid)

Exit psql: To exit the psql prompt, type:
```
\q
```
and press Enter. You will return to the regular terminal prompt.

You have now successfully viewed database metadata in PostgreSQL using psql. This allows you to understand the structure and properties of your databases and tables.

Summary

In this lab, you learned the basics of database management in PostgreSQL, starting with creating a new database. The process involves using the createdb command followed by the desired database name in the terminal. Successful execution creates the database without any output.

To verify the creation, you connect to the default postgres database using psql -d postgres and then use the \l command to list all available databases, confirming the newly created database is present.