Setup

The DBT-2 test kit has been ported to work with PostgreSQL starting with version 7.3. It has be updated to work with later version and backwards compatibility may vary. Source code for PostgreSQL can be obtained from their website at: https://www.postgresql.org/

To install PostgreSQL from source:

./configure --prefix=<installation dir>
make
make install

Prior to PostgreSQL 8.0 this additional make command is required to ensure the server include files get installed:

make install-all-headers

After installing PostgreSQL, the DBT-2 C stored functions need to be compiled and installed, if they are to be used instead of pl/pgsql stored functions or other client side transaction logic:

cd storedproc/pgsql/c
make
make install

The make install command will need to be run by the owner of the database installation.

When testing PostgreSQL in a multi-tier system configuration, verify that the database has been configuration to accept TCP/IP connections. For example, the listen_addresses parameter must be set to listen for connections on the appropriate interfaces. Remote connections must also be allowed in the pg_hba.conf file. The simplest (and insecure) way would be to trust all connections on all interfaces.

Also prior to PostgreSQL 8.0, pg_autovacuum should be installed. If installing from source, it is located in the contrib/pg_autovacuum directory.

The following subsections have additional PostgreSQL version specific notes.

A really quick howto

Edit examples/dbt2_profile and follow the notes for the DBT2PGDATA directory. DBT2PGDATA is where the database directory will be created.

Create a 1 warehouse database by running:

dbt2 build pgsql

Run a 5 minute (300 second) test by running:

dbt2 run -d 300 pgsql /tmp/result

Building the Database

The dbt2-pgsql-build-db script is designed to handle the following steps:

1. create the database
2. create the tables
3. generate and load the tables
4. create the indexes
5. vacuum the database

The dbt2-pgsql-build-db script is also designed to generate data in parallel based on the number of processors detected on the system, and will stream the data into the database. The script currently do not let you control the degree of parallelism, or whether the data should be created as files first, but these can be controlled manually by running the dbt2-datagen binary directly.

The other significant choices available are:

• -r drop the existing database first
• -s <c | plpgsql> use C or pl/pgsql stored functions, where plpgsql is the default
• -t use tablespaces for tables and indexes
• -u the executing user has privileges to restart the database system, and drop and create a database

See the usage output with the -h for the complete list of options.

The following command will create a default sized 1 warehouse database:

dbt2 build pgsql

Environment Configuration

The DBT-2 scripts required environment variables to be set in order to work properly (e.g. examples/dbt2_profile) in order for the scripts to work properly. For example:

DBT2PORT=5432; export DBT2PORT
DBT2DBNAME=dbt2; export DBT2DBNAME
DBT2PGDATA=/tmp/pgdata; export DBT2PGDATA

An optional environment variable can be set to specify a different location for the transaction logs (i.e. pg_xlog or pg_wal) when the dbt2-pgsql-init-db script:

DBT2XLOGDIR=/tmp/pgxlogdbt2; export DBT2XLOGDIR

The environment variables may need to be defined in ~/.ssh/environment file on each system for multi-tier environment for ssh. The ssh daemon may need to be configured to enable the use of user environment files. Make sure PATH is set to include the location where the DBT-2 executables and PostgreSQL binaries are installed, if not in the default PATH. For example:

DBT2PORT=5432
DBT2DBNAME=dbt2
DBT2PGDATA=/tmp/pgdata
PATH=/usr/local/bin:/usr/bin:/bin:/opt/bin

Tablespace Notes

The scripts assumes a specific tablespace layout for keeping the scripts simple.

The ${DBT2TSDIR} environment variable defines the directory where all tablespace devices will be mounted. Directories or symlinks can be substituted for what is assumed to be a mount point from this point forward.

dbt2-pgsql-create-tables and dbt2-pgsql-create-indexes are where the tablespaces are created.

The expected mount points or symlinks, which must also be writeable by the database owner, need to be at:

${DBT2TSDIR}/warehouse
${DBT2TSDIR}/district
${DBT2TSDIR}/customer
${DBT2TSDIR}/history
${DBT2TSDIR}/new_order
${DBT2TSDIR}/orders
${DBT2TSDIR}/order_line
${DBT2TSDIR}/item
${DBT2TSDIR}/stock
${DBT2TSDIR}/index1
${DBT2TSDIR}/index2
${DBT2TSDIR}/pk_customer
${DBT2TSDIR}/pk_district
${DBT2TSDIR}/pk_item
${DBT2TSDIR}/pk_new_order
${DBT2TSDIR}/pk_order_line
${DBT2TSDIR}/pk_orders
${DBT2TSDIR}/pk_stock
${DBT2TSDIR}/pk_warehouse

AppImage Notes