Infrastructure at your Service

Daniel Westermann

The (almost) same sample schema for all major relational databases (1) – PostgreSQL

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:

[email protected]:/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.

[email protected]:/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:

[email protected]:/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;

[email protected]:/var/tmp/ds2/ [DVDSTORE] pwd
/var/tmp/ds2
[email protected]:/var/tmp/ds2/ [DVDSTORE] chmod +x Install_DVDStore.pl

So, lets say I want to have a 1GB database. It is a easy as:

[email protected]:/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:

[email protected]:/var/tmp/ds2/pgsqlds2/ [DVDSTORE] pwd
/var/tmp/ds2/pgsqlds2
[email protected]:/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.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Daniel Westermann
Daniel Westermann

Principal Consultant & Technology Leader Open Infrastructure