{"id":7570,"date":"2016-04-13T02:28:53","date_gmt":"2016-04-13T00:28:53","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/the-almost-same-sample-schema-for-all-major-relational-databases-1-postgresql\/"},"modified":"2016-04-13T02:28:53","modified_gmt":"2016-04-13T00:28:53","slug":"the-almost-same-sample-schema-for-all-major-relational-databases-1-postgresql","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/the-almost-same-sample-schema-for-all-major-relational-databases-1-postgresql\/","title":{"rendered":"The (almost) same sample schema for all major relational databases (1) – PostgreSQL"},"content":{"rendered":"

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”<\/a>. 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.<\/p>\n

Once the ds21_postgresql.tar.gz<\/a> and ds21.tar.gz<\/a> archives are downloaded and extracted on the database host this is the content:<\/p>\n

\npostgres@delldvdstorepostgresql:\/var\/tmp\/ds2\/ [DVDSTORE] ls -la\ntotal 136\ndrwxr-xr-x. 5 postgres postgres  4096 Dec  2  2011 .\ndrwxrwxrwt. 3 root     root        63 Apr 12 10:51 ..\n-rw-r--r--. 1 postgres postgres  5308 Aug 12  2010 CreateConfigFile.pl\ndrwxr-xr-x. 5 postgres postgres    73 May 31  2011 data_files\ndrwxr-xr-x. 2 postgres postgres  4096 Dec  2  2011 drivers\n-rw-r--r--. 1 postgres postgres 30343 May 13  2011 ds2.1_Documentation.txt\n-rw-r--r--. 1 postgres postgres 10103 Nov  9  2011 ds2_change_log.txt\n-rw-r--r--. 1 postgres postgres  1608 Jul  1  2005 ds2_faq.txt\n-rw-r--r--. 1 postgres postgres  2363 May  5  2011 ds2_readme.txt\n-rw-r--r--. 1 postgres postgres  5857 Apr 21  2011 ds2_schema.txt\n-rw-r--r--. 1 postgres postgres 18013 May 12  2005 gpl.txt\n-rw-r--r--. 1 postgres postgres 32827 Nov  9  2011 Install_DVDStore.pl\ndrwxr-xr-x. 5 postgres postgres  4096 Apr 12 10:49 pgsqlds2\n<\/pre>\n
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:<\/p>\n
\npostgres=# create user ds2 superuser password 'ds2';\nCREATE ROLE\n\npostgres=# du\n                                   List of roles\n Role name |                         Attributes                         | Member of \n-----------+------------------------------------------------------------+-----------\n ds2       | Superuser                                                  | {}\n postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}\n<\/pre>\n
Having the user ready we can start to populate it by executing the “pgsqlds2_create_all.sh” script in the pgsqlds2 sub directory.<\/p>\n
\npostgres@delldvdstorepostgresql:\/var\/tmp\/ds2\/pgsqlds2\/ [DVDSTORE] chmod +x pgsqlds2_create_all.sh\n<\/pre>\n
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):<\/p>\n
\n#createlang plpgsql ds2\ncreatelang -U ds2 plpgsql ds2\n<\/pre>\n
The output should be similar to this:<\/p>\n
\npostgres@delldvdstorepostgresql:\/var\/tmp\/ds2\/pgsqlds2\/ [DVDSTORE] .\/pgsqlds2_create_all.sh\ncreatelang: language \"plpgsql\" is already installed in database \"ds2\"\nDROP DATABASE\nCREATE DATABASE\nERROR:  role \"ds2\" already exists\nALTER ROLE\nERROR:  relation \"customers\" does not exist\nERROR:  relation \"orders\" does not exist\nERROR:  relation \"orderlines\" does not exist\nERROR:  relation \"cust_hist\" does not exist\nERROR:  relation \"inventory\" does not exist\nERROR:  relation \"products\" does not exist\nDROP TABLE\nDROP TABLE\nERROR:  relation \"inventory\" does not exist\nDROP OWNED\nDROP ROLE\nCREATE TABLE\nCREATE TABLE\nCREATE TABLE\nCREATE TABLE\nCREATE TABLE\nCREATE TABLE\nCREATE TABLE\nINSERT 0 1\nINSERT 0 1\nINSERT 0 1\nINSERT 0 1\nINSERT 0 1\nINSERT 0 1\nINSERT 0 1\nINSERT 0 1\nINSERT 0 1\nINSERT 0 1\nINSERT 0 1\nINSERT 0 1\nINSERT 0 1\nINSERT 0 1\nINSERT 0 1\nINSERT 0 1\nCREATE TABLE\nCREATE FUNCTION\nCREATE FUNCTION\nCREATE FUNCTION\nCREATE FUNCTION\nCREATE FUNCTION\nCREATE FUNCTION\nALTER TABLE\nCOPY 10000\nCOPY 10000\nALTER TABLE\nALTER TABLE\nCOPY 1000\nCOPY 1000\nCOPY 1000\nCOPY 1000\nCOPY 1000\nCOPY 1000\nCOPY 1000\nCOPY 1000\nCOPY 1000\nCOPY 1000\nCOPY 1000\nCOPY 1000\nALTER TABLE\nALTER TABLE\nCOPY 5083\nCOPY 5001\nCOPY 5164\nCOPY 4947\nCOPY 5022\nCOPY 5065\nCOPY 5010\nCOPY 4942\nCOPY 5010\nCOPY 5163\nCOPY 4992\nCOPY 4951\nALTER TABLE\nALTER TABLE\nCOPY 5083\nCOPY 5001\nCOPY 5164\nCOPY 4947\nCOPY 5022\nCOPY 5065\nCOPY 5010\nCOPY 4942\nCOPY 5010\nCOPY 5163\nCOPY 4992\nCOPY 4951\nALTER TABLE\nALTER TABLE\nCOPY 10000\nALTER TABLE\nALTER TABLE\nCOPY 10000\nALTER TABLE\nCREATE INDEX\nCREATE INDEX\nCREATE INDEX\nALTER TABLE\nCREATE INDEX\nALTER TABLE\nCREATE INDEX\nALTER TABLE\nCREATE INDEX\nCREATE INDEX\nCREATE INDEX\nCREATE INDEX\nCREATE INDEX\nCREATE FUNCTION\nCREATE TRIGGER\n setval \n--------\n     16\n(1 row)\n\n setval \n--------\n  20000\n(1 row)\n\n setval \n--------\n  12000\n(1 row)\n\n setval \n--------\n  10000\n(1 row)\n\nCREATE ROLE\nGRANT\nGRANT\nGRANT\nANALYZE\n<\/pre>\n
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).<\/p>\n
So, what did we get?<\/p>\n
\npostgres=# c ds2\nYou are now connected to database \"ds2\" as user \"postgres\".\nds2=# d\n                  List of relations\n Schema |           Name           |   Type   | Owner \n--------+--------------------------+----------+-------\n public | categories               | table    | ds2\n public | categories_category_seq  | sequence | ds2\n public | cust_hist                | table    | ds2\n public | customers                | table    | ds2\n public | customers_customerid_seq | sequence | ds2\n public | inventory                | table    | ds2\n public | orderlines               | table    | ds2\n public | orders                   | table    | ds2\n public | orders_orderid_seq       | sequence | ds2\n public | products                 | table    | ds2\n public | products_prod_id_seq     | sequence | ds2\n public | reorder                  | table    | ds2\n(12 rows)\nds2=# di\n                       List of relations\n Schema |          Name           | Type  | Owner |   Table    \n--------+-------------------------+-------+-------+------------\n public | categories_pkey         | index | ds2   | categories\n public | customers_pkey          | index | ds2   | customers\n public | inventory_pkey          | index | ds2   | inventory\n public | ix_cust_hist_customerid | index | ds2   | cust_hist\n public | ix_cust_user_password   | index | ds2   | customers\n public | ix_cust_username        | index | ds2   | customers\n public | ix_order_custid         | index | ds2   | orders\n public | ix_orderlines_orderid   | index | ds2   | orderlines\n public | ix_prod_actor           | index | ds2   | products\n public | ix_prod_cat_special     | index | ds2   | products\n public | ix_prod_category        | index | ds2   | products\n public | ix_prod_special         | index | ds2   | products\n public | ix_prod_title           | index | ds2   | products\n public | orders_pkey             | index | ds2   | orders\n public | products_pkey           | index | ds2   | products\n(15 rows)\n\nds2=# select oid from pg_roles where rolname = 'ds2' ;\n  oid  \n-------\n 17093\n(1 row)\n\nds2=# select proname,prolang::regclass from pg_proc where proowner = 17093;\n      proname       | prolang \n--------------------+---------\n restock_order      | 13280\n new_customer       | 13280\n login              | 13280\n browse_by_category | 13280\n browse_by_actor    | 13280\n browse_by_title    | 13280\n purchase           | 13280\n(7 rows)\n\n\nds2=# select tgname,tgrelid::regclass from pg_trigger;\n            tgname            |  tgrelid   \n------------------------------+------------\n RI_ConstraintTrigger_a_17160 | customers\n RI_ConstraintTrigger_a_17161 | customers\n RI_ConstraintTrigger_c_17162 | cust_hist\n RI_ConstraintTrigger_c_17163 | cust_hist\n RI_ConstraintTrigger_a_17166 | customers\n RI_ConstraintTrigger_a_17167 | customers\n RI_ConstraintTrigger_c_17168 | orders\n RI_ConstraintTrigger_c_17169 | orders\n RI_ConstraintTrigger_a_17172 | orders\n RI_ConstraintTrigger_a_17173 | orders\n RI_ConstraintTrigger_c_17174 | orderlines\n RI_ConstraintTrigger_c_17175 | orderlines\n restock                      | inventory\n(13 rows)\n<\/pre>\n
Some tables, sequences, indexes, triggers and functions. Fine. How big is this database?<\/p>\n
\nds2=# select * from pg_size_pretty ( pg_database_size ('ds2'));\n pg_size_pretty \n----------------\n 25 MB\n(1 row)\n<\/pre>\n
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;<\/p>\n
\npostgres@delldvdstorepostgresql:\/var\/tmp\/ds2\/ [DVDSTORE] pwd\n\/var\/tmp\/ds2\npostgres@delldvdstorepostgresql:\/var\/tmp\/ds2\/ [DVDSTORE] chmod +x Install_DVDStore.pl\n<\/pre>\n
So, lets say I want to have a 1GB database. It is a easy as:<\/p>\n
\npostgres@delldvdstorepostgresql:\/var\/tmp\/ds2\/ [DVDSTORE] .\/Install_DVDStore.pl \nPlease enter following parameters: \n***********************************\nPlease enter database size (integer expected) : 1000\nPlease enter whether above database size is in (MB \/ GB) : MB\nPlease enter database type (MSSQL \/ MYSQL \/ PGSQL \/ ORACLE) : PGSQL\nPlease enter system type on which DB Server is installed (WIN \/ LINUX) : LINUX\n***********************************\n***********************************\nInitializing parameters...\n***********************************\nDatabase Size: 1000 \nDatabase size is in MB \nDatabase Type is PGSQL \nSystem Type for DB Server is LINUX \n***********************************\n\nCalculating Rows in tables!! \nSmall size database (less than 1 GB) \nRatio calculated : 100 \nCustomer Rows: 2000000 \nOrder Rows \/ month: 100000 \nProduct Rows: 1000000 \n\nCreating CSV files....\nStarting to create CSV data files.... \nFor larger database sizes, it will take time.\nDo not kill the script till execution is complete. \n\nCreating Customer CSV files!!! \n1 1000000 US S 0 \n1000001 2000000 ROW S 0 \n\nCustomer CSV Files created!! \n\nCreating Orders, Orderlines and Cust_Hist csv files!!! \n\nCreating Order CSV file for Month jan !!! \n1 100000 jan S 1 0 1000000 2000000 \n\nCreating Order CSV file for Month feb !!! \n100001 200000 feb S 2 0 1000000 2000000 \n\nCreating Order CSV file for Month mar !!! \n200001 300000 mar S 3 0 1000000 2000000 \n\nCreating Order CSV file for Month apr !!! \n300001 400000 apr S 4 0 1000000 2000000 \n\nCreating Order CSV file for Month may !!! \n400001 500000 may S 5 0 1000000 2000000 \n\nCreating Order CSV file for Month jun !!! \n500001 600000 jun S 6 0 1000000 2000000 \n\nCreating Order CSV file for Month jul !!! \n600001 700000 jul S 7 0 1000000 2000000 \n\nCreating Order CSV file for Month aug !!! \n700001 800000 aug S 8 0 1000000 2000000 \n\nCreating Order CSV file for Month sep !!! \n800001 900000 sep S 9 0 1000000 2000000 \n\nCreating Order CSV file for Month oct !!! \n900001 1000000 oct S 10 0 1000000 2000000 \n\nCreating Order CSV file for Month nov !!! \n1000001 1100000 nov S 11 0 1000000 2000000 \n\nCreating Order CSV file for Month dec !!! \n1100001 1200000 dec S 12 0 1000000 2000000 \n\nAll Order, Orderlines, Cust_Hist CSV files created !!! \n\nCreating Inventory CSV file!!!! \n\nInventory CSV file created!!!! \n\nCreating product CSV file!!!! \n\nProduct CSV file created!!!! \n\nCreating build script for PostgreSQL from templates... \n\nTemplate files are stored in respective build folders and the output files are also stored in same folder \n\nTemplate files are named with generic_template at end of their filename and the output files without _template at end \n\nCompleted creating and writing build scripts for PostgreSQL database... \n\nAll database build scripts(shell and sql) are dumped into their respective folders. \n\nThese scripts are created from template files in same folders with '_generic_template' in their name. \n\nScripts that are created from template files have '_' 1000 MB in their name. \n\nUser can edit the sql script generated for customizing sql script for more DBFiles per table and change the paths of DBFiles.\n\nNow Run CreateConfigFile.pl perl script in ds2 folder which will generate configuration file used as input to the driver program.\n<\/pre>\n
Once you now re-run the load script:<\/p>\n
\npostgres@delldvdstorepostgresql:\/var\/tmp\/ds2\/pgsqlds2\/ [DVDSTORE] pwd\n\/var\/tmp\/ds2\/pgsqlds2\npostgres@delldvdstorepostgresql:\/var\/tmp\/ds2\/pgsqlds2\/ [DVDSTORE] .\/pgsqlds2_create_all.sh\n<\/pre>\n
… you’ll have a database with size:<\/p>\n
\npostgres=# select * from pg_size_pretty ( pg_database_size ('ds2'));\n pg_size_pretty \n----------------\n 1741 MB\n(1 row)\n<\/pre>\n
Not exactly 1GB but really cool. In one of the next posts we’ll do the same with an Oracle database.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[652,77],"type_dbi":[],"class_list":["post-7570","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-data","tag-postgresql"],"acf":[],"yoast_head":"\nThe (almost) same sample schema for all major relational databases (1) - PostgreSQL - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/the-almost-same-sample-schema-for-all-major-relational-databases-1-postgresql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"The (almost) same sample schema for all major relational databases (1) - PostgreSQL\" \/>\n<meta property=\"og:description\" content=\"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 […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/the-almost-same-sample-schema-for-all-major-relational-databases-1-postgresql\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2016-04-13T00:28:53+00:00\" \/>\n<meta name=\"author\" content=\"Daniel Westermann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@westermanndanie\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Westermann\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"8 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/the-almost-same-sample-schema-for-all-major-relational-databases-1-postgresql\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/the-almost-same-sample-schema-for-all-major-relational-databases-1-postgresql\\\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"The (almost) same sample schema for all major relational databases (1) – PostgreSQL\",\"datePublished\":\"2016-04-13T00:28:53+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/the-almost-same-sample-schema-for-all-major-relational-databases-1-postgresql\\\/\"},\"wordCount\":440,\"commentCount\":0,\"keywords\":[\"data\",\"PostgreSQL\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/the-almost-same-sample-schema-for-all-major-relational-databases-1-postgresql\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/the-almost-same-sample-schema-for-all-major-relational-databases-1-postgresql\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/the-almost-same-sample-schema-for-all-major-relational-databases-1-postgresql\\\/\",\"name\":\"The (almost) same sample schema for all major relational databases (1) - PostgreSQL - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2016-04-13T00:28:53+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/the-almost-same-sample-schema-for-all-major-relational-databases-1-postgresql\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/the-almost-same-sample-schema-for-all-major-relational-databases-1-postgresql\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/the-almost-same-sample-schema-for-all-major-relational-databases-1-postgresql\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"The (almost) same sample schema for all major relational databases (1) – PostgreSQL\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\",\"name\":\"Daniel Westermann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"caption\":\"Daniel Westermann\"},\"description\":\"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\\\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.\",\"sameAs\":[\"https:\\\/\\\/x.com\\\/westermanndanie\"],\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/daniel-westermann\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"The (almost) same sample schema for all major relational databases (1) - PostgreSQL - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/the-almost-same-sample-schema-for-all-major-relational-databases-1-postgresql\/","og_locale":"en_US","og_type":"article","og_title":"The (almost) same sample schema for all major relational databases (1) - PostgreSQL","og_description":"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 […]","og_url":"https:\/\/www.dbi-services.com\/blog\/the-almost-same-sample-schema-for-all-major-relational-databases-1-postgresql\/","og_site_name":"dbi Blog","article_published_time":"2016-04-13T00:28:53+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"8 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/the-almost-same-sample-schema-for-all-major-relational-databases-1-postgresql\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/the-almost-same-sample-schema-for-all-major-relational-databases-1-postgresql\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"The (almost) same sample schema for all major relational databases (1) – PostgreSQL","datePublished":"2016-04-13T00:28:53+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/the-almost-same-sample-schema-for-all-major-relational-databases-1-postgresql\/"},"wordCount":440,"commentCount":0,"keywords":["data","PostgreSQL"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/the-almost-same-sample-schema-for-all-major-relational-databases-1-postgresql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/the-almost-same-sample-schema-for-all-major-relational-databases-1-postgresql\/","url":"https:\/\/www.dbi-services.com\/blog\/the-almost-same-sample-schema-for-all-major-relational-databases-1-postgresql\/","name":"The (almost) same sample schema for all major relational databases (1) - PostgreSQL - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2016-04-13T00:28:53+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/the-almost-same-sample-schema-for-all-major-relational-databases-1-postgresql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/the-almost-same-sample-schema-for-all-major-relational-databases-1-postgresql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/the-almost-same-sample-schema-for-all-major-relational-databases-1-postgresql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"The (almost) same sample schema for all major relational databases (1) – PostgreSQL"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66","name":"Daniel Westermann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","caption":"Daniel Westermann"},"description":"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.","sameAs":["https:\/\/x.com\/westermanndanie"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/7570","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=7570"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/7570\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=7570"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=7570"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=7570"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=7570"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}