PostgreSQL - Loading a Database

Last Updated : 20 Sep, 2021

In this article we will look into the process of loading a PostgreSQL database into the PostgreSQL database server.
Before moving forward we just need to make sure of two things:

PostgreSQL database server is installed on your system.
A sample database.

For the purpose of this article, we will be using a sample database which is DVD rental database.
You can download the sample dvdrental database from here.

The Sample Database:

So, the DVD rental database that we will be using ahead in the article represents a DVD rental store. The objects in the database includes:

15 tables
1 trigger
8 functions
1 domain
7 views
13 sequences

ER Model of the sample Database:

Tables in the Sample Database:
There are 15 tables in our sample database which are listed below:

actor – stores actors data including first name and last name.
film – stores films data such as title, release year, length, rating, etc
film_actor – stores the relationships between films and actors.
category – stores film’s categories data.
film_category- stores the relationships between films and categories.
store – contains the store data including manager staff and address.
inventory – stores inventory data.
rental – stores rental data.
payment – stores customer’s payments.
staff – stores staff data.
customer – stores customers data.
address – stores address data for staff and customers
city – stores the city names.
country – stores the country names.

So now we know everything about our sample DVD rental database, let us move on to loading the same database to the PostgreSQL database server. The steps to which are listed below:

Step 1: Create a DVD rental Database, by opening the SQL shell. Once you open up the shell, you will need to add the necessary credentials for your database, which would somewhat look like below:

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

Now using the CREATE DATABASE statement create a new database as follows:

CREATE DATABASE dvdrental;

Step 2: Load the database file by creating a folder at your desired location(say, c:\users\sample_database\dvdrental.tar). Now open up command prompt and Navigate to the bin folder of the PostgreSQL installation folder as below:

Use the pg_restore tool to load data into the dvdrental database that we had just created as using the command:

pg_restore -U postgres -d dvdrental C:\users\sample_datbase\dvdrental.tar

It would somewhat look like below:

Now enter your database user Password and your sample database will be loaded.

Verify Database Load:

Now if you need to verify if the sample database is loaded, use the below command to get into the database in SQL shell:

\c

Now to list all the tables in the database, use the below command:

\dt

The result should look like below:

rajukumar19

Improve

Article Tags :

PostgreSQL - Loading a Database

The Sample Database:

Verify Database Load:

Explore

Basics

Database Operations

Data Types

Querying Tables

Table Operations

Modifying Data

Conditionals

Control Flow

Transactions & Constraints

JOINS & Schemas