Restoring a Sample Database into PostgreSQL

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

PostgreSQL has a number of sample database listed on their 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 actually use the backup file. However, for the 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 db. Though you can download and use any of the sample database backup files of your choice, that are available on internet.

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

  1. Download the backup file from GitHub repository.
wget https://github.com/arinpro/postgres-sample-db-dvdrental/raw/master/dvdrental_backup.tar
-bash-4.2$ wget https://github.com/arinpro/postgres-sample-db-dvdrental/raw/master/dvdrental_backup.tar
--2020-08-02 11:52:03--  https://github.com/arinpro/postgres-sample-db-dvdrental/raw/master/dvdrental_backup.tar
Resolving github.com (github.com)... 13.229.188.59
Connecting to github.com (github.com)|13.229.188.59|:443... connected.
HTTP request sent, awaiting response... 302 Found
Location: https://raw.githubusercontent.com/arinpro/postgres-sample-db-dvdrental/master/dvdrental_backup.tar [following]
--2020-08-02 11:52:03--  https://raw.githubusercontent.com/arinpro/postgres-sample-db-dvdrental/master/dvdrental_backup.tar
Resolving raw.githubusercontent.com (raw.githubusercontent.com)... 151.101.8.133
Connecting to raw.githubusercontent.com (raw.githubusercontent.com)|151.101.8.133|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 2835456 (2.7M) [application/octet-stream]
Saving to: ‘dvdrental_backup.tar’

100%[====================================================================================================================================================================================================>] 2,835,456   --.-K/s   in 0.03s   

2020-08-02 11:52:05 (92.5 MB/s) - ‘dvdrental_backup.tar’ saved [2835456/2835456]

2. Create an empty database named dvdrental.

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

createdb dvdrental
-bash-4.2$ createdb dvdrental
-bash-4.2$

3. Restore the downloaded backup file.

pg_restore -d dvdrental <file-path>/dvdrental_backup.tar
-bash-4.2$ pg_restore -d dvdrental /var/lib/pgsql/dvdrental_backup.tar
-bash-4.2$

4. 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 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s