The are times when it would be nice to have the same sample schema in all relational databases you or your company are working on. This is great if you want to compare how different databases execute the same tasks on the same data. This can be great if you want to test logical replication between different databases and this might be great if you want to test migration tools that support you in migrating from one database to another. You can probably create something on your own or take one of the sample schemata that a vendor provides by default and adapt it to the other vendors. Or you can use something that is already there. When I searched for something that would fit me needs I came across the “Dell DVD Store Database Test Suite”. This provides a sample schema, a data generator and even a sample application for MySQL, Oracle, MSSQL and PostgreSQL. Perfect, lets see how this can be loaded into a PostgreSQL 9.5.2 database.
Once the ds21_postgresql.tar.gz and ds21.tar.gz archives are downloaded and extracted on the database host this is the content:
postgres@delldvdstorepostgresql:/var/tmp/ds2/ [DVDSTORE] ls -la total 136 drwxr-xr-x. 5 postgres postgres 4096 Dec 2 2011 . drwxrwxrwt. 3 root root 63 Apr 12 10:51 .. -rw-r--r--. 1 postgres postgres 5308 Aug 12 2010 CreateConfigFile.pl drwxr-xr-x. 5 postgres postgres 73 May 31 2011 data_files drwxr-xr-x. 2 postgres postgres 4096 Dec 2 2011 drivers -rw-r--r--. 1 postgres postgres 30343 May 13 2011 ds2.1_Documentation.txt -rw-r--r--. 1 postgres postgres 10103 Nov 9 2011 ds2_change_log.txt -rw-r--r--. 1 postgres postgres 1608 Jul 1 2005 ds2_faq.txt -rw-r--r--. 1 postgres postgres 2363 May 5 2011 ds2_readme.txt -rw-r--r--. 1 postgres postgres 5857 Apr 21 2011 ds2_schema.txt -rw-r--r--. 1 postgres postgres 18013 May 12 2005 gpl.txt -rw-r--r--. 1 postgres postgres 32827 Nov 9 2011 Install_DVDStore.pl drwxr-xr-x. 5 postgres postgres 4096 Apr 12 10:49 pgsqlds2
You can read the “ds2.1_Documentation.txt” for a detailed description on what can be done. I’ll show the quick way here. The first step is to create the “ds2” user:
postgres=# create user ds2 superuser password 'ds2';
CREATE ROLE
postgres=# du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
ds2 | Superuser | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
Having the user ready we can start to populate it by executing the “pgsqlds2_create_all.sh” script in the pgsqlds2 sub directory.
postgres@delldvdstorepostgresql:/var/tmp/ds2/pgsqlds2/ [DVDSTORE] chmod +x pgsqlds2_create_all.sh
As the script will try to connect as the postgres user for installing the plpgsql extension I did a little modification to the load script (the ds2 user is superuser anyway and is allowed to install the extension so why not use it):
#createlang plpgsql ds2 createlang -U ds2 plpgsql ds2
The output should be similar to this:
postgres@delldvdstorepostgresql:/var/tmp/ds2/pgsqlds2/ [DVDSTORE] ./pgsqlds2_create_all.sh
createlang: language "plpgsql" is already installed in database "ds2"
DROP DATABASE
CREATE DATABASE
ERROR: role "ds2" already exists
ALTER ROLE
ERROR: relation "customers" does not exist
ERROR: relation "orders" does not exist
ERROR: relation "orderlines" does not exist
ERROR: relation "cust_hist" does not exist
ERROR: relation "inventory" does not exist
ERROR: relation "products" does not exist
DROP TABLE
DROP TABLE
ERROR: relation "inventory" does not exist
DROP OWNED
DROP ROLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
CREATE TABLE
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
ALTER TABLE
COPY 10000
COPY 10000
ALTER TABLE
ALTER TABLE
COPY 1000
COPY 1000
COPY 1000
COPY 1000
COPY 1000
COPY 1000
COPY 1000
COPY 1000
COPY 1000
COPY 1000
COPY 1000
COPY 1000
ALTER TABLE
ALTER TABLE
COPY 5083
COPY 5001
COPY 5164
COPY 4947
COPY 5022
COPY 5065
COPY 5010
COPY 4942
COPY 5010
COPY 5163
COPY 4992
COPY 4951
ALTER TABLE
ALTER TABLE
COPY 5083
COPY 5001
COPY 5164
COPY 4947
COPY 5022
COPY 5065
COPY 5010
COPY 4942
COPY 5010
COPY 5163
COPY 4992
COPY 4951
ALTER TABLE
ALTER TABLE
COPY 10000
ALTER TABLE
ALTER TABLE
COPY 10000
ALTER TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
ALTER TABLE
CREATE INDEX
ALTER TABLE
CREATE INDEX
ALTER TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE FUNCTION
CREATE TRIGGER
setval
--------
16
(1 row)
setval
--------
20000
(1 row)
setval
--------
12000
(1 row)
setval
--------
10000
(1 row)
CREATE ROLE
GRANT
GRANT
GRANT
ANALYZE
You can ignore the errors at the beginning that’s fine (these are statement trying to disable and delete triggers for tables that have been already deleted).
So, what did we get?
postgres=# c ds2
You are now connected to database "ds2" as user "postgres".
ds2=# d
List of relations
Schema | Name | Type | Owner
--------+--------------------------+----------+-------
public | categories | table | ds2
public | categories_category_seq | sequence | ds2
public | cust_hist | table | ds2
public | customers | table | ds2
public | customers_customerid_seq | sequence | ds2
public | inventory | table | ds2
public | orderlines | table | ds2
public | orders | table | ds2
public | orders_orderid_seq | sequence | ds2
public | products | table | ds2
public | products_prod_id_seq | sequence | ds2
public | reorder | table | ds2
(12 rows)
ds2=# di
List of relations
Schema | Name | Type | Owner | Table
--------+-------------------------+-------+-------+------------
public | categories_pkey | index | ds2 | categories
public | customers_pkey | index | ds2 | customers
public | inventory_pkey | index | ds2 | inventory
public | ix_cust_hist_customerid | index | ds2 | cust_hist
public | ix_cust_user_password | index | ds2 | customers
public | ix_cust_username | index | ds2 | customers
public | ix_order_custid | index | ds2 | orders
public | ix_orderlines_orderid | index | ds2 | orderlines
public | ix_prod_actor | index | ds2 | products
public | ix_prod_cat_special | index | ds2 | products
public | ix_prod_category | index | ds2 | products
public | ix_prod_special | index | ds2 | products
public | ix_prod_title | index | ds2 | products
public | orders_pkey | index | ds2 | orders
public | products_pkey | index | ds2 | products
(15 rows)
ds2=# select oid from pg_roles where rolname = 'ds2' ;
oid
-------
17093
(1 row)
ds2=# select proname,prolang::regclass from pg_proc where proowner = 17093;
proname | prolang
--------------------+---------
restock_order | 13280
new_customer | 13280
login | 13280
browse_by_category | 13280
browse_by_actor | 13280
browse_by_title | 13280
purchase | 13280
(7 rows)
ds2=# select tgname,tgrelid::regclass from pg_trigger;
tgname | tgrelid
------------------------------+------------
RI_ConstraintTrigger_a_17160 | customers
RI_ConstraintTrigger_a_17161 | customers
RI_ConstraintTrigger_c_17162 | cust_hist
RI_ConstraintTrigger_c_17163 | cust_hist
RI_ConstraintTrigger_a_17166 | customers
RI_ConstraintTrigger_a_17167 | customers
RI_ConstraintTrigger_c_17168 | orders
RI_ConstraintTrigger_c_17169 | orders
RI_ConstraintTrigger_a_17172 | orders
RI_ConstraintTrigger_a_17173 | orders
RI_ConstraintTrigger_c_17174 | orderlines
RI_ConstraintTrigger_c_17175 | orderlines
restock | inventory
(13 rows)
Some tables, sequences, indexes, triggers and functions. Fine. How big is this database?
ds2=# select * from pg_size_pretty ( pg_database_size ('ds2'));
pg_size_pretty
----------------
25 MB
(1 row)
Quite small. But the good thing with this “ds2” sample application is that you can go for more data if you want. There is a perl script which can be used to generate any amount of data;
postgres@delldvdstorepostgresql:/var/tmp/ds2/ [DVDSTORE] pwd /var/tmp/ds2 postgres@delldvdstorepostgresql:/var/tmp/ds2/ [DVDSTORE] chmod +x Install_DVDStore.pl
So, lets say I want to have a 1GB database. It is a easy as:
postgres@delldvdstorepostgresql:/var/tmp/ds2/ [DVDSTORE] ./Install_DVDStore.pl Please enter following parameters: *********************************** Please enter database size (integer expected) : 1000 Please enter whether above database size is in (MB / GB) : MB Please enter database type (MSSQL / MYSQL / PGSQL / ORACLE) : PGSQL Please enter system type on which DB Server is installed (WIN / LINUX) : LINUX *********************************** *********************************** Initializing parameters... *********************************** Database Size: 1000 Database size is in MB Database Type is PGSQL System Type for DB Server is LINUX *********************************** Calculating Rows in tables!! Small size database (less than 1 GB) Ratio calculated : 100 Customer Rows: 2000000 Order Rows / month: 100000 Product Rows: 1000000 Creating CSV files.... Starting to create CSV data files.... For larger database sizes, it will take time. Do not kill the script till execution is complete. Creating Customer CSV files!!! 1 1000000 US S 0 1000001 2000000 ROW S 0 Customer CSV Files created!! Creating Orders, Orderlines and Cust_Hist csv files!!! Creating Order CSV file for Month jan !!! 1 100000 jan S 1 0 1000000 2000000 Creating Order CSV file for Month feb !!! 100001 200000 feb S 2 0 1000000 2000000 Creating Order CSV file for Month mar !!! 200001 300000 mar S 3 0 1000000 2000000 Creating Order CSV file for Month apr !!! 300001 400000 apr S 4 0 1000000 2000000 Creating Order CSV file for Month may !!! 400001 500000 may S 5 0 1000000 2000000 Creating Order CSV file for Month jun !!! 500001 600000 jun S 6 0 1000000 2000000 Creating Order CSV file for Month jul !!! 600001 700000 jul S 7 0 1000000 2000000 Creating Order CSV file for Month aug !!! 700001 800000 aug S 8 0 1000000 2000000 Creating Order CSV file for Month sep !!! 800001 900000 sep S 9 0 1000000 2000000 Creating Order CSV file for Month oct !!! 900001 1000000 oct S 10 0 1000000 2000000 Creating Order CSV file for Month nov !!! 1000001 1100000 nov S 11 0 1000000 2000000 Creating Order CSV file for Month dec !!! 1100001 1200000 dec S 12 0 1000000 2000000 All Order, Orderlines, Cust_Hist CSV files created !!! Creating Inventory CSV file!!!! Inventory CSV file created!!!! Creating product CSV file!!!! Product CSV file created!!!! Creating build script for PostgreSQL from templates... Template files are stored in respective build folders and the output files are also stored in same folder Template files are named with generic_template at end of their filename and the output files without _template at end Completed creating and writing build scripts for PostgreSQL database... All database build scripts(shell and sql) are dumped into their respective folders. These scripts are created from template files in same folders with '_generic_template' in their name. Scripts that are created from template files have '_' 1000 MB in their name. User can edit the sql script generated for customizing sql script for more DBFiles per table and change the paths of DBFiles. Now Run CreateConfigFile.pl perl script in ds2 folder which will generate configuration file used as input to the driver program.
Once you now re-run the load script:
postgres@delldvdstorepostgresql:/var/tmp/ds2/pgsqlds2/ [DVDSTORE] pwd /var/tmp/ds2/pgsqlds2 postgres@delldvdstorepostgresql:/var/tmp/ds2/pgsqlds2/ [DVDSTORE] ./pgsqlds2_create_all.sh
… you’ll have a database with size:
postgres=# select * from pg_size_pretty ( pg_database_size ('ds2'));
pg_size_pretty
----------------
1741 MB
(1 row)
Not exactly 1GB but really cool. In one of the next posts we’ll do the same with an Oracle database.
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)