This section guides you through a worked data generation example in PostgreSQL. It includes steps to create an empty target database, which is then used in the example. It provides links to further examples and to resources to understand how the data generatorworks.

Contents

Preparation

Preparation of Data Generator

Please do the following before beginning the worked example

1. Install the data generator CLI.
2. Verify your installation by running the following command in a terminal window:
   1. Windows cmd: datagenerator.exe --version
   2. Windows PowerShell: ./datagenerator --version
   3. Linux: ./datagenerator --version

The data generator should report its version number as below (example in Windows cmd).

C:\DataGeneration\>datagenerator.exe --version
0.2.0.0

Preparation of Database Engine

PostgreSQL comes with a command-line SQL query tool, psql. It can be found under the directory where you have installed PostgreSQL, in Windows this is typically C:\Program Files\PostgreSQL\16\bin. You can run psql by opening a command prompt directly in this directory, or add the directory to your PATH environment variable to enable running psql from any where on your computer.

Executing SQL queries in psql requires setting up a PostgreSQL user and password. If you don't have one already, the easiest way to do this is using pgAdmin. Entering pgAdmin for the first time, you will be prompted to create a password for the user postgres. After creating the password and finishing the setup in pgAdmin, execute the following at a command prompt:

psql -U postgres

Enter your password when prompted. If successful you should see something similar to the following (can vary depending on your PostgreSQL version, warnings about code page can be safely ignored for the purpose of this worked example):

psql (16.0)
Type "help" for help.

postgres=#

Now you have entered psql and you should be able to execute SQL queries against the databases you created. To exit psql, simply type:

quit

Create a target database

Create an empty database called TargetDatabase in PostgreSQL.

You can do this using a graphical tool like pgAdmin.

Alternatively, you can do this by executing this SQL query below, either in pgAdmin or in psql.

CREATE DATABASE "TargetDatabase";

Populate the database

First, connect to the newly created SourceDatabase, using pgAdmin or the command below in psql:

\c "TargetDatabase"

If connected successfully, you should see psql showing TargetDatabase=# instead of postgres=#.

After that, download the SQL script postgres-sample.sql and execute it in the new database.

Again, you can do this by loading the script into pgAdmin, or executing the command below in psql.  Substitute the correct path to the script file you have just downloaded.

\i /scripts/xants-sample-db/sample-postgresql-database.sql

This will create a sample database with four tables, containing example data. It represents a simple social media website, where users create posts and comment on posts, and users belong to organizations.

Check the database contents

Check that the tables exist and contain data. Use pgAdmin, or use a SQL query as below which lists all the table names:

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_SCHEMA='public';

And a SQL query as below which to list the data in a table:

SELECT * FROM "Users";

Run the data generator

The commands below run the data generator. If the commands cannot run successfully, you may need to work through the connection strings section below to configure them to your environment.

Click on the sections below to view the example command lines:

./datagenerator.exe `
--target-connection-string 'Host=localhost;Port=5430;Database=TargetDatabase;Username=postgres;Password=P455w07d' `
--database-engine PostgreSQL `
--rows-to-generate 1000

./datagenerator.exe ^
--target-connection-string "Host=localhost;Port=5432;Database=TargetDatabase;Username=postgres;Password=P455w07d" ^
--database-engine PostgreSQL ^
--rows-to-generate 1000

Connection strings

You may need to change the connection string to suit your environment. The connection string identifies the target database. 

Change Username and Password to match that of your own.

Use Host to specify the PostgreSQL server containing the database. In the example above we are using localhost to connect to an instance running on the same computer that is running the data generator.

We have further advice on connection strings in our troubleshooting and known limitations section. There is also a lot of information online about connection strings.

Output from the command

The command will produce detailed output similar to that shown below. The details may vary. Check that the last line of output says Data generation completed.

2023-11-07 13:42:28.956 +00:00 [INFO] DataGenerator has started in 'Debug' mode. Log folder is C:\ProgramData\Red Gate\Logs\TDM\DataGenerator.
2023-11-07 13:42:28.971 +00:00 [INFO] Validating data generation configuration data...
2023-11-07 13:42:29.127 +00:00 [INFO] Completed initial validation of data generation configuration data in 00h 00m 00s.154ms
2023-11-07 13:42:29.164 +00:00 [INFO] Using Application Insights telemetry reporter in bucket 'tdm-test' (debug) in developer mode.
2023-11-07 13:42:29.504 +00:00 [INFO] Running PostgreSql database table extraction using target 'TargetDatabase' database on 'tcp://localhost:5439' server...
2023-11-07 13:42:29.577 +00:00 [INFO] Successfully extracted 4 table(s) (with 4 relationships) from target 'TargetDatabase' database on 'tcp://localhost:5439' server in 00h 00m 00s.073ms.
2023-11-07 13:42:29.656 +00:00 [INFO] Validating target 'TargetDatabase' database on 'tcp://localhost:5439' server structure...
2023-11-07 13:42:29.674 +00:00 [INFO] Completed validation of target 'TargetDatabase' database on 'tcp://localhost:5439' server structure in 00h 00m 00s.017ms...
2023-11-07 13:42:29.747 +00:00 [INFO] Creating data generation plan
2023-11-07 13:42:29.875 +00:00 [INFO] Starting to generate data
2023-11-07 13:43:16.478 +00:00 [INFO] Data generation completed in 00h 00m 47s.317ms.

Check the data in the target database

Check the contents of the tables in the target database. You should find that each table contains 1000 data rows.

Congratulations! This shows that the data has been generated successfully.

Further examples

The documentation on configuration files explains the formats and provides worked examples of their use, that build on this worked example. 

Learning more about how the data generator works

Now that you have carried out the worked example, you might like to understand more about how the data generator works. Please consult the page Using the worked example to understand the data generator. This page uses the output and the commands from the worked example to explain how the data generator produces referentially valid data.