PostgreSQL Tutorial: Load PostgreSQL Sample Database

Summary: in this tutorial, we will show you how to load the PostgreSQL sample database into the PostgreSQL database server.

Before going forward with this tutorial, you need to have:

• A PostgreSQL database server installed on your system.
• A PostgreSQL sample database called dvdrental.

Load the sample database using psql tool

First, launch the psql tool.

>psql

Second, enter the account’s information to log in to the PostgreSQL database server. You can use the default value provided by psql by pressing the Enter keyboard. However, for the password, you need to enter the one that you provided during PostgreSQL installation.

Server [localhost]:
Database [postgres]:
Port [5432]:
Username [postgres]:
Password for user postgres:

Third, enter the following CREATE DATABASE statement to create a new dvdrental database.

postgres=# CREATE DATABASE dvdrental;
CREATE DATABASE

PostgreSQL will create a new database named dvdrental.

Finally, enter the exit command to quit psql:

postgres=# exit

Then, navigate the bin folder of the PostgreSQL installation folder:

C:\>cd C:\Program Files\PostgreSQL\12\bin

After that, use the pg_restore tool to load data into the dvdrental database:

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

In this command:

• The -U postgres specifies the postgresuser to login to the PostgreSQL database server.
• The -d dvdrental specifies the target database to load.

Finally, enter the password for the postgres user and press enter

Password:

It takes about seconds to load data stored in the dvdrental.tar file into the dvdrentaldatabase.

Load the DVD Rental database using the pgAdmin

The following shows you step by step on how to use the pgAdmin tool to restore the sample database from the database file:

First, launch the pgAdmin tool and connect to the PostgreSQL server.

Second, right click the Databases and select the Create > Database… menu option:

Third, enter the database name dvdrental and click the Save button:

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

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

Fifth, enter the path to the sample database file e.g., c:\sampledb\dvdrental.tar and click the Restore button:

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

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

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

Let’s start learning PostgreSQL and have fun!