Table of Contents
Postgres sample dbs - What is covered here ?
We are releasing a free utility to deploy postgres sample database- You can deploy pagila , pgbench, dvdrental and postgres_air databases using this utility https://github.com/klouddb/klouddb_tools/tree/main/postgres_sampledb
Prerequisites
Make sure you have all the prerequisites addressed . For example psycopg2 and python3 are needed to run this utility in your environment . NOTE: You need git-lfs for postgres_air db as it is a large file (for other dbs you dont need git-lfs)
If you dont have psycopg2 you might see an error like below
python3 execute.py
Traceback (most recent call last):
File "execute.py", line 2, in
import os, psycopg2, sys
ModuleNotFoundError: No module named 'psycopg2'
How to deploy this utility ?
Once you clone the repo you will find a default env file called “env.example” . You can create a new hidden file .env from this file . Replace db_hostname , db_name , db_username,db_password and db_port before executing the script
Below are default contents of the sample file
Once you complete above steps , you can proceed with your run . You will be prompted for the dbname and you can give pagila , sampledb , dvdrental or postgres_air as your options here
Deploying Pagila
You can execute below command and give ‘pagila’ as input
python3 execute.py Please enter the database name you want to create, options are pagila, sampledb, dvdrental, postgres_air:pagila
For a successful run you should see messages like below popping up on your screen . Make sure that there are no errors
Once the install is done make sure you have all the objects present as illustrated in the ER diagram
https://github.com/klouddb/klouddb_tools/blob/main/postgres_sampledb/erdiagrams/pagilaERdiagram.png
You can also do \l+ pagila to check the size and \dt+ to check the relation size
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description --------+--------------+----------+---------+---------+-------------------+-------+------------+------------- pagila | collectadmin | UTF8 | C.UTF-8 | C.UTF-8 | | 16 MB | pg_default | (1 row)
Deploying pgbench sample db
You can deploy it using below method . You need to give your input as ‘sampledb’
python3 execute.py Please enter the database name you want to create, options are pagila, sampledb, dvdrental, postgres_air:sampledb
You will be prompted for the size of db , you can give whatever size you want . In our example we gave input as ‘2’ which creates a db with 2GB size . Our script automatically computes the scaling factor for you
A successful run should yield a final message like below
creating the Database sampledb enter the size of the database you want in GBs Example 5:2 .. .. vacuuming... creating primary keys... done in 362.57 s (drop tables 0.00 s, create tables 0.01 s, client-side generate 160.05 s, vacuum 124.93 s, primary keys 77.58 s).
You can see that it automatically calculated scaling factor as 128 for this scenario.Once the install is done make sure you have all the objects present as illustrated in the ER diagram
https://github.com/klouddb/klouddb_tools/blob/main/postgres_sampledb/erdiagrams/pgbench_sampledb_ERdiagram.png
You can also check \l+ and \dt+ as shown in previous section
Deploying postgres_air
You can deploy using below command . You need to give ‘postgres_air’ as input
python3 execute.py Please enter the database name you want to create, options are pagila, sampledb, dvdrental, postgres_air:postgres_air
If you dont have git-lfs you might see an error like below
unzipping the postgres air file. pls wait it will take 5-10 mins Archive: postgres_air.zip End-of-central-directory signature not found. Either this file is not a zipfile, or it constitutes one disk of a multi-part archive. In the latter case the central directory and zipfile comment will be found on the last disk(s) of this archive.
Please install git-lfs to resolve above error(since this is a large file we need that).Once above issues are resolved you should see something like below to indicate that it is working
creating the Database postgres_air unzipping the postgres air file. pls wait it will take 5-10 mins Archive: postgres_air.zip inflating: postgres_air.sql
Once the install is done make sure you have all the objects present as illustrated in the ER diagram
You can also check \l+ and \dt+ as shown in previous section
Deploying dvdrental
You can deploy using below command . You need to give ‘dvdrental’ as input
python3 execute.py Please enter the database name you want to create, options are pagila, sampledb, dvdrental, postgres_air:dvdrental
Once the install is done make sure you have all the objects present as illustrated in the ER diagram
https://github.com/klouddb/klouddb_tools/blob/main/postgres_sampledb/erdiagrams/dvdrentalschema.png
You can also check \l+ and \dt+ as shown in previous section
ER diagrams for all sample dbs
We are also attaching all the ER diagrams to this repo. ER diagrams are really helpful to know all the objects and their relationships
Pagila ER diagram
Pgbench sampledb ER diagram
Dvdrental ER diagram
Postgres_air ER diagram
Original contributors
Below are the original contributors and repos aggregated
Pagila
Dvdrental
Postgres_air
FAQs
It will drop the existing database after your approval . Once the deletion is done you can create the database again
This utility aggregates all the sample dbs in one place for you . Apart from that we also have ER diagrams for all the sample dbs at one place . This utility also computes the scaling factor automatically for you
Python3,psycop2g and git-lfs for postgres_air
Please reach us at support@klouddb.io
That is needed only for postgres_air database . If you dont install it , postgres_air download will fail