Restoring a Sample Database into PostgreSQL

In the process of learning a new database technology, testing out various features and following a tutorial, what you require is a sample database. Like other DBMS, there are several sample databases available for PostgreSQL as well.

PostgreSQL has several sample databases listed on its official site. You can go and check them out here.

Most of the available sample database requires you to perform a few steps like unzipping\decompressing and munging data before you could use the backup file. However, for convenience, I have placed a readily available backup file of dvdrental sample database in GitHub that you can download and use anytime.

For this demonstration, we will use the backup file of dvdrental sample database. Though you can download and use any of the sample database backup files of your choice, that are available on the internet.

For setting up the sample database, follow the below steps:

1. Download the backup file from the GitHub repository.

wget https://github.com/arinpro/postgres-sample-db-dvdrental/raw/master/dvdrental_backup.tar

2. Create an empty database named dvdrental.

You can use a name of your choice as well; it doesn’t matter.

createdb dvdrental

3. Restore the downloaded backup file.

pg_restore -d dvdrental <file-path>/dvdrental_backup.tar

1. Connect and verify the newly created database dvdrental.

• Get the list the currently available database and you will see the newly created database dvdrental on the list.

postgres=# \l
                                    List of databases
     Name      |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
---------------+----------+----------+-------------+-------------+-----------------------
 dvdrental     | postgres | 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
(6 rows)

• Connect to the database.

postgres=# \c dvdrental
You are now connected to database "dvdrental" as user "postgres".
dvdrental=#

• you can check the list of the available table as well.

dvdrental=# \dt+
                          List of relations
 Schema |     Name      | Type  |  Owner   |    Size    | Description 
--------+---------------+-------+----------+------------+-------------
 public | actor         | table | postgres | 40 kB      | 
 public | address       | table | postgres | 88 kB      | 
 public | category      | table | postgres | 8192 bytes | 
 public | city          | table | postgres | 64 kB      | 
 public | country       | table | postgres | 8192 bytes | 
 public | customer      | table | postgres | 96 kB      | 
 public | film          | table | postgres | 464 kB     | 
 public | film_actor    | table | postgres | 264 kB     | 
 public | film_category | table | postgres | 72 kB      | 
 public | inventory     | table | postgres | 224 kB     | 
 public | language      | table | postgres | 8192 bytes | 
 public | payment       | table | postgres | 888 kB     | 
 public | rental        | table | postgres | 1224 kB    | 
 public | staff         | table | postgres | 16 kB      | 
 public | store         | table | postgres | 8192 bytes | 
(15 rows)

dvdrental=#

The sample database dvdrental is ready to be used now 🚀