How to Generate PostgreSQL Test Data on ObjectRocket

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

While test data is indispensable for assessing system performance and developing new web applications, producing data from scratch can be an extremely cumbersome task. However, it is a fairly straightforward, quick and simple process to generate PostgreSQL test data on ObjectRocket and this tutorial will explain how to execute this function. A current account and access to the ObjectRocket instance is required to perform the functions described in this tutorial.

Prerequisites

The following prerequisites are required to generate PostgreSQL test data on ObjectRocket:

  • PostgreSQL version 12 must be installed and configured on the local system as this version currently supports the ObjectRocket instance. The currently installed version of Postgres can be verified by executing the psql -V command in the terminal.

  • A current account and access to the ObjectRocket instance is required.

  • A basic knowledge of how to use the Docker container.

Create a Docker container

A Docker container is often used for developing applications with a container as Docker can increase the speed, performance, portability and scalability of many applications.

Copy and paste the following syntax in the terminal to create a docker container:

1
docker run --name pg_or -e POSTGRES_PASSWORD=pass123 -p 4123:4123 -d postgres

As shown below, this should generate a series of alpha-numeric characters that will serve as the Docker container ID:

1
b39793de824eeaeb8447b4b47e506bf384693717a8b0a5aa42759f5d5fb1146f

Docker should now pull up the PostgreSQL image to use in the container.

Understanding the docker syntax and flags used:

  • --name — This flag is used to set and provide the specific name of the docker container.
  • -e — This command establishes the environment variable of the Docker container value that permits setting the PostgreSQL password.
  • -p — This flag binds the ports of the localhost to the docker container and allows for accessing the PostgreSQL connection. The default port is 5432.
  • -d — This flag launches and starts the Docker container, in the background, and pulls up the image of PostgreSQL in the Docker hub.

Once it has been created, verify the container by executing the following command:

1
docker ps

This should display all of the Docker containers located on the device, as shown here:

1
2
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                              NAMES
b39793de824e        postgres            "docker-entrypoint.s…"   2 minutes ago       Up 2 minutes        0.0.0.0:4123->4123/tcp, 5432/tcp   pg_or

Now access the PostgreSQL interactive terminal in the ObjectRocket instance by using the following Docker exec syntax:

1
docker exec -it pg_or psql -U orkb -h ingress.hkybrhnz.launchpad.objectrocket.cloud -p 4123 -d postgres

NOTE: Obtain the connection URL from the ObjectRocket instance with the connect tab on the -kbd-VIEW MORE DETAILS-/kbd- button that is used to remotely access PostgreSQL. This is shown in the following screenshot:

Screenshot of the ObjectRocket Mission Control Panel connect tab

Use the PostgreSQL to create a table

With access into the PostgreSQL interactive shell now established, create a table with the following details:

1
2
3
4
5
6
7
8
9
10
11
                               Table "public.test_data"
 Column  |         Type         | Collation | Nullable |           Default            
---------+----------------------+-----------+----------+------------------------------
 tbl_id  | bigint               |           | not null | generated always as identity
 name    | text                 |           | not null |
 address | text                 |           | not null |
 gender  | character varying(6) |           | not null |
Indexes:
    "test_data_pkey" PRIMARY KEY, btree (tbl_id)
Check constraints:
    "test_data_gender_check" CHECK (gender::text = ANY (ARRAY['male'::character varying, 'female'::character varying]::text[]))

Use PostgreSQL to insert data

The following steps show how to insert data into the new table created in PostgreSQL in the ObjectRocket instance.

First, use the INSERT statement along with the random() function to generate the following data:

1
2
3
4
INSERT INTO test_data(name, address, gender)
SELECT md5(RANDOM()::TEXT), md5(RANDOM()::TEXT),
CASE WHEN RANDOM() < 0.5 THEN
'male' ELSE 'female' END FROM generate_series(1, 9999999);

The generating of this data may take some time, depending on the input value(s)

NOTE: The random function uses the md5, as it produces 128-bit of hash value that is an algorithm for hash function.

Use the PostgreSQL select statement

Now execute the following SELECT statement to display the results of the data generated in the test_data table:

1
SELECT COUNT(*) FROM test_data;

Note that the count function can be used to return the count of rows in the table.

For example, to display the count of data generated that has a gender of “male,” execute the following command: SELECT COUNT(*) FROM test_data WHERE gender = 'male';

This should produce the following results:

1
2
3
4
  count  
---------
 5000388
(1 row)

Finally, execute the following LIMIT clause to see the data that was recorded in the rows of the table:

1
SELECT * FROM test_data LIMIT 10;

The output should resemble the following:

1
2
3
4
5
6
7
8
9
10
11
12
13
 tbl_id |               name               |             address              | gender
--------+----------------------------------+----------------------------------+--------
      1 | af472cf018c53c65092b27353bce2541 | 542021dc3fbb9841c2936d44c0f82300 | male
      2 | 80d8bd021c8a14e90af877063fc93bd2 | a3ec0543b653c34e11e8695e0d2311fc | female
      3 | 1170886f04cf5c0662b6c04648b333d1 | 745265635a35e8c5223b6e4f71a72d84 | male
      4 | 21b3cdaea1fda605e467842da125134f | bcf0b3ec6318a7ea5aa82c5d39f9d75c | male
      5 | 946c6263b760edd0afe01d421d80a80e | 118898486aa81d9cded0d6a37fa0ce6d | female
      6 | 7855a83acf773afc0c18fb068476ec0e | 941ca1f8e8ec53c6f38bba8797fdbd3e | male
      7 | 520ecbeff45f3e51a04c50b2c4b3b027 | 738035c1eea12783d6e859fef7145f19 | male
      8 | 0e8ce43ce683c744202ba81ee333264a | 0df86ea6e040e78f7e974012058dd04d | female
      9 | be1ad290fe2786a949fc1b8c7d46ed8b | e140709ae211bc2ea2606f799bb6add8 | male
     10 | 1b9d489eee67bb99e24ce8f5fe048b94 | 1064744a0047c6bad4062c3ffe4be72e | male
(10 rows)

Conclusion

This tutorial explained how to generate PostgreSQL test data on ObjectRocket. The tutorial first explained how to create a docker container, verify the container was created and display all of the current Docker containers. The article then gave a breakdown of the Docker syntax and flags and then explained how to access the PostgreSQL interactive terminal in the ObjectRocket instance. The tutorial then explained how to use PostgreSQL to create a table, insert data into the table and then execute the SELECT statement to display the results of the generated test data in the table. Lastly, the tutorial covered how to view the data recorded in the rows of the table. Remember that PostgreSQL version 12 must be installed as this is the version that currently supports the ObjectRocket instance.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.