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:
- 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
dvdrentalon 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.
dbaguides.com 