How To Generate Data With Pagila in Percona Distribution for PostgreSQL

by Edith Puclla

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
  1. 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
  1. Enter in the Percona Distribution Postgresql container.
docker exec -it  percona-postgres psql -U postgres
  1. Create a database called perconadb
CREATE DATABASE perconadb;
\q
  1. 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

Output

  1. 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

Output

  1. 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

Output

SELECT * FROM inventory;

Output

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. ∎

Edith Puclla

Edith Puclla is a Technology Evangelist of Percona Corporation, studied at 42 Silicon Valley School in California in 2020, and was part of the Outreachy Internship in 2021. She has a background in DevOps and is a Docker and Kubernetes enthusiast.

See all posts by Edith Puclla »

Comments

Percona Live 2023 - CFP
✎ Edit this page on GitHub