• A PostgreSQL database server.
• APostgreSQL sample database calleddvdrental.

Load the sample database using the psql & pg_restore tool

psql is a terminal-based client tool to PostgreSQL. It allows you to enter queries, send them to PostgreSQL for execution, and display the results.

pg_restore is a utility for restoring a database from an archive.

To create a database and load data from an archive file, you follow these steps:

• First, connect to thePostgreSQL database server usingpsql orpgAdmin.
• Second, create a blank database calleddvdrental.
• Third, load data from the sample database file into thedvdrental database usingpg_restore.

1) Create the dvdrental database

First, open the Command Prompt on Windows or Terminal on Unix-like systems and connect to the PostgreSQL server usingpsql tool:

psql -U postgres

It’ll prompt you to enter a password for thepostgres user:

Password for user postgres:

The password for thepostgres user is the one you entered during thePostgreSQL installation.

After entering the password correctly, you will be connected to the PostgreSQL server.

The command prompt will look like this:

postgres=#

Second, create a new database calleddvdrental usingCREATE DATABASE statement:

CREATE DATABASE dvdrental;

Output:

CREATE DATABASE

PostgreSQL will create a new database calleddvdrental.

Third, verify the database creation using the\l command. The\l command will show all databases in the PostgreSQL server:

\l

Output:

List of databases   Name    |  Owner   | Encoding | Locale Provider |          Collate           |           Ctype            | ICU Locale | ICU Rules |   Access privileges-----------+----------+----------+-----------------+----------------------------+----------------------------+------------+-----------+----------------------- dvdrental | postgres | UTF8     | libc            | English_United States.1252 | English_United States.1252 |            |           | postgres  | postgres | UTF8     | libc            | English_United States.1252 | English_United States.1252 |            |           | template0 | postgres | UTF8     | libc            | English_United States.1252 | English_United States.1252 |            |           | =c/postgres          +           |          |          |                 |                            |                            |            |           | postgres=CTc/postgres template1 | postgres | UTF8     | libc            | English_United States.1252 | English_United States.1252 |            |           | =c/postgres          +           |          |          |                 |                            |                            |            |           | postgres=CTc/postgres(4 rows)

The output shows thatdvdrental on the list, meaning that you have created thedvdrental database successfully.

Note that other databases such aspostgres,template0, andtemplate1 are the system databases.

Fourth, disconnect from the PostgreSQL server and exit thepsql using theexit command:

exit

2) Restore the sample database from a tar file

Fifth, download the sample database (dvdrental.zip) and extract thetar file to the directory such asD:\sampledb\postgres\dvdrental.tar on Windows.

Sixth, load thedvdrental database using thepg_restore command:

pg_restore -U postgres -d dvdrental D:\sampledb\postgres\dvdrental.tar

In this command:

• The-U postgres instructspg_restore to connect the PostgreSQL server using thepostgres user.
• The-d dvdrental specifies the target database to load.

It’ll prompt you to enter the password for thepostgres user. Enter the password for thepostgres user and press the Enter (or Return key):

Password:

It’ll take about seconds to load data stored in thedvdrental.tar file into thedvdrental database.

3) Verify the sample database

First, connect to the PostgreSQL server using thepsql command:

psql -U postgres

Second, switch the current database todvdrental:

\c dvdrental

The command prompt will change to the following:

dvdrental=#

Third, display all tables in thedvdrental database:

\dt

Output:

List of relations Schema |     Name      | Type  |  Owner--------+---------------+-------+---------- public | actor         | table | postgres public | address       | table | postgres public | category      | table | postgres public | city          | table | postgres public | country       | table | postgres public | customer      | table | postgres public | film          | table | postgres public | film_actor    | table | postgres public | film_category | table | postgres public | inventory     | table | postgres public | language      | table | postgres public | payment       | table | postgres public | rental        | table | postgres public | staff         | table | postgres public | store         | table | postgres(15 rows)

Load the DVD Rental database using the pgAdmin

pgAdmin is a web-based graphic user interface (GUI) for interacting with the PostgreSQL server.

The following shows you step-by-step how to use the pgAdmin to restore thesample database from the database file:

First, launch thepgAdmin tool andconnect to the PostgreSQL server.

Second, right-click theDatabases and select theCreate > Database… menu option:

Third, enter the database namedvdrental and click theSave button:

You’ll see the new empty database created under theDatabases node:

Fourth, right-click on thedvdrental database and choose theRestore… menu item to restore the database from the downloaded database file:

Fifth, enter the path to the sample database file such asc:\sampledb\dvdrental.tar and click theRestore button:

Sixth, the restoration process will complete in a few seconds and show the following dialog once it completes:

Finally, open thedvdrental database from the object browser panel, you will find tables in thepublic schema and other database objects as shown in the following picture:

In this tutorial, you have learned how to load thedvdrental sample database into the PostgreSQL database server for practicing PostgreSQL.

Let’s start learning PostgreSQL and have fun!

Last updated onJanuary 20, 2024

On this page

• Load the sample database using the psql & pg_restore tool
• Load the DVD Rental database using the pgAdmin