How To Generate Data With Pagila in Percona Distribution for PostgreSQL

Have you ever faced the need to generate test data for your Postgres database? I am sure you have! This blog post will guide you step-by-step through one of the many ways to get it fast and easy. That will leave you plenty of time to focus on queries. No need to spend time creating your data generation scripts!

For this guide, we will use Pagila, a tool that provides a standard schema that we can use for examples in books, tutorials, articles, samples, etc. The project is open source; you can clone it and start to run the queries. With Pagila, we will create all schema objects and insert the data into our tables.

We will use Percona Distribution for PostgreSQL 12.13 or higher as a database. It is an easy but powerful way to implement an enterprise-grade, fully open source PostgreSQL environment.

Let’s start it!

Requirements

• Docker
  • You can install Docker by following this guide.
  • Manage Docker as a non-root user: sudo usermod -aG docker $USER

Installing Percona Distribution for PostgreSQL

1. On your terminal, pull the Percona Distribution for PostgreSQL image. I am using the 12.13 version.

docker pull perconalab/percona-distribution-postgresql:12.13

2. Run Percona Distribution PostgreSQL container. You must specify POSTGRES_PASSWORD as a non-empty value for the superuser.

docker run --name percona-postgres -e POSTGRES_PASSWORD=secret -d perconalab/percona-distribution-postgresql:12.13

Using Pagila to Generate Data

1. Clone the Pagila GitHub repository.

git clone https://github.com/devrimgunduz/pagila

2. Enter in the Percona Distribution Postgresql container.

docker exec -it  percona-postgres psql -U postgres

3. Create a database called perconadb

CREATE DATABASE perconadb;
\q

4. Create all schema objects (tables, etc.):

cd pagila

We will execute the script pagila-schema.sql inside percona-postgres container. The script will create the schemas objects like tables, views, functions, and constraints

cat pagila-schema.sql | docker exec -i percona-postgres psql -U postgres -d perconadb

5. Insert all data. We will execute pagila-data.sql script inside the container to insert data in all the tables we created before.

cat pagila-data.sql | docker exec -i percona-postgres psql -U postgres -d perconadb

6. Validate the data; let’s check if the tables were created and if it is populated with data.

docker exec -it percona-postgres psql -U postgres
\c perconadb
\dt

SELECT * FROM inventory;

You are ready! The data is there and ready to be used. You can refer to the official documentation of Percona Distribution for PostgreSQL if you want to know the entire collection of tools to help you manage your PostgreSQL database system. You can check Pagila open source project to generate data for Postgres. ∎