How to Generate PostgreSQL Test Data on ObjectRocket
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 is5432
.-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:
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