Home > Resources > Blog

PostgreSQL Basics

May 17, 2022 by Bibhas Bhattacharya
Category: Angular

In this tutorial, you will get hands-on experience using the PostgreSQL database. The tutorial is
divided into the following parts:
• Verify Prerequisites
• Load the dvdrental Sample Database
These tutorial instructions assume that you are logging into a linux machine with the
username: ‘wasadmin’. If your username differs, remember to substitute the one you have
wherever you see ‘wasadmin’ in the instructions.

Part 1 – Verify Prerequisites

In this part of the tutorial, you will check to make sure the required software is loaded on your
machine. This tutorial needs you to do specific setup in order to follow the steps in the tutorial.

This includes:
• PostgreSQL Server
• PostgresSQL Client

PostgreSQL Server

1. Open a terminal window.
2. Execute the following command to check the status of the PostgresSQL server:
systemctl status postgresql
The command’s output should look like this:

If the postgres server is running the “Active:” line will say “active” like this:
Active: active …

If the server is stopped you will see:
Active: inactive …

3. Enter ‘q’ to get back the user prompt.

4. If it is not already started then start the database server now using this command:
systemctl start postgresql
Related commands (for reference):
systemctl stop postgresql – stops the db server
systemctl restart postgresql – restart the db after changing config

PostgreSQL Client

1. Execute the following command to change to the postgres role:
sudo -i -u postgres

2. Enter password.
The prompt should change something like this depending on your login name:
wasadmin@ubuntu2110:~$

3. Check out the current directory:
pwd
You should see the following path:
/var/lib/postgresql

4. Start the postgres command line client:
psql
The prompt should change to:
postgres=#

5. Enter the following command to see the psql help pages:
\?
You should see:

6. While you are viewing the help pages the following commands are available, try
them:
{enter-key} – advances by one line
{space-bar} – advances one page
q – quits the help pages

7. List the available databases using the following command:
\l

You should see a list like this:

8. Connect to the ‘postgres’ database using the following command:
\c postgres
You should get this response:
You are now connected to database “postgres” as user “postgres”.

9. Display the available tables with the following command:
\dt
You should get this response:
Did not find any relations.

10. Create a table named ‘hello’ with the following command:
CREATE TABLE IF NOT EXISTS public.hello( greeting character varying(45) NOT null);
The output should show the following:
CREATE TABLE

11. Display the available databases:
\dt
You should get:

12. Insert a row:
insert into public.hello values(‘Hello Database World!’);
You should get:
INSERT 0 1

13. Select from the table:
select * from public.hello;
You should get:

14. Exit the psql client:
\q
The prompt changes to:
postgres@ubuntu2110:~$

15. Exit the postgres role:
exit
The prompt changes to:
wasadmin@ubuntu2110:~$

Part 2 – Load the dvdrental sample database

In this part, you will load and explore the dvdrental sample database.
1. Open a new Terminal.

2. Check your current directory:
pwd
It should be:
/home/wasadmin/
Yours may differ based on your login name.

3. Create and navigate into a working directory:
cd LabWork

4. Check your current directory again:
pwd
It should show:
/home/wasadmin/LabWork

5. Unzip the \LabFiles\dvdrental.zip file into the current directory:
unzip ~/LabFiles/postgresql/dvdrental.zip -d .
[Don’t forget the period ‘.’ at the end of the command]
This should extract the file “dvdrental.tar’ to the current directory.

6. Check that the dvdrental.tar file was extracted:
dir

7. Copy “dvdrental.tar” to a directory where the postgres role can access it:
sudo cp dvdrental.tar /var/lib/postgresql

8. Enter wasadmin for password.

9. Check that the file was copied:
sudo ls /var/lib/postgresql/
The output should show the “dvdrental.tar’ file.

10. Switch to the ‘postgres’ role:
sudo -i -u postgres

11. Check the current directory, it should be the one you copied the file to:
pwd
Output:
/var/lib/postgresql

12. Verify that the ‘dvdrental.tar’ file is in the current directory:
ls -l
The output should show the “dvdrental.tar’ file.

13. Run the following command to create an empty database named ‘dvdrental’:
psql -c ‘create database dvdrental’ -c ‘\l’
This command uses the psql tool to execute two commands; the first creates the database,
the second lists the available databases.
The database you created ‘dvdrental’ should be in the list.
After executing the commands you are returned back to your original prompt. (not to the
psql cli prompt)
Now we are going to use the postgresql ‘pg_restore’ command line tool to load the
database from the ‘dvdrental.tar’ file.

14. Run the following command:
pg_restore –dbname=dvdrental –verbose dvdrental.tar

15. Run the following command to connect to the ‘dvdrental’ database and display its
tables:
psql -c ‘\c dvdrental’ -c ‘\dt’
You should see:

These are the tables in the dvdrental database.

16. Open the psql CLI:
psql
The prompt changes to: postgres=#

17. Change to the ‘dvdrental’ database:
\c dvdrental
You should see:
You are now connected to database “dvdrental” as user “postgres”.

18. List the available tables: (you should get the same list as above)
\dt

19. Display the ‘film_id’, ‘title’, and ‘description’ for first 5 records in the ‘film’ table:
select film_id, title, description from film limit 5;
You should get: