Would adding a feature to "reset" the postgres container to a known schema be useful?

[vinaysshenoy]

For additional context, I use Flyway to handle database migrations in my app. For the test suite, it's mostly integration tests where I spin up and tear down the server between each test. As part of this, I also do the same with the app dependencies, which is a postgres database.

This essentially meant that between each test, I was:

• Setting a new postgres docker container
• Migrating the database to the current version of the schema
• Dropping the existing container and deleting the local store

I initially tried starting the container and migrating it once before running all the tests and then manually deleting the data in the tables, but that led to other issues because this is a script that needs to be maintained and constantly updated whenever new database changes happen, so I dropped it.

I also tried experimenting with tmpfs to speed things up, but had trouble setting it up with GItHub actions (where I run the CI) so I tried a different approach, which worked really well and sped up my CI times by over 50%! What I ended up doing was:

1. Spinning up the container once and running the migration to the latest schema
2. Creating a schema-only dump of the database and saving it to a temporary directory
3. Before each test:
   • Drop the database
   • Create the database again
   • Restore the schema from the saved dump

This had a massive performance benefit on the test suite, as you can see from the measurements below:

Before

construct-postgres-container:: Total: 3 minutes, 47.05 seconds, Median: 1.47 seconds, p90: 1.51 seconds
run-db-migrations:: Total: 5 minutes, 41.4 seconds, Median: 2.23 seconds, p90: 2.31 seconds
shut-down-db:: Total: 34.89 seconds, Median: 0.22 seconds, p90: 0.24 seconds

After

construct-postgres-container:: Total: 1.39 seconds, Median: 1.39 seconds, p90: 1.39 seconds
run-db-migrations:: Total: 2.19 seconds, Median: 2.19 seconds, p90: 2.19 seconds
dump-db-schema:: Total: 0.19 seconds, Median: 0.19 seconds, p90: 0.19 seconds
reset-db:: Total: 59.52 seconds, Median: 0.38 seconds, p90: 0.4 seconds
shut-down-db:: Total: 0.21 seconds, Median: 0.21 seconds, p90: 0.21 seconds

I managed to get this into a fairly portable setup as part of the postgres container:

import { PostgreSqlContainer, StartedPostgreSqlContainer } from "@testcontainers/postgresql"

class ResettablePostgreSqlContainer extends PostgreSqlContainer {
    private dumpSaveDirectory: string = ""

    override async start(): Promise<StartedResettablePostgreSqlContainer> {
        const container = await super.start()

        return new StartedResettablePostgreSqlContainer(this.dumpSaveDirectory, container)
    }

    withDumpSaveLocation(dir: string): ResettablePostgreSqlContainer {
        this.withBindMounts([
            {
                source: dir,
                target: "/tmp"
            }
        ])
        this.dumpSaveDirectory = dir

        return this
    }
}

class StartedResettablePostgreSqlContainer extends StartedPostgreSqlContainer {
    private readonly dbDumpPath: string

    constructor(
        dumpSaveDirectory: string,
        container: StartedPostgreSqlContainer
    ) {
        if (dumpSaveDirectory.length === 0) {
            throw new Error(`Cannot save DB schema without a path, provide a location using "withDumpSaveLocation(string)" when starting the container!`)
        }

        super(container, container.getDatabase(), container.getUsername(), container.getPassword())
        this.dbDumpPath = `${dumpSaveDirectory}/pg_container_${this.getId()}.dump`
    }

    private async runPostgresCommand(
        cmd: string
    ): Promise<void> {
        const result = await this.exec(cmd.split(' '))

        if (result.exitCode > 0) {
            throw new Error(JSON.stringify(result.output))
        }
    }

    public async saveDatabaseSchema(
        dbPort: number = 5432
    ): Promise<void> {
        const dbConnectionPart = `-h localhost -p ${dbPort} -U ${this.getUsername()}`
        const dbName = this.getDatabase()

        await this.runPostgresCommand(`pg_dump ${dbConnectionPart} -s -f ${this.dbDumpPath} -F tar ${dbName}`)
    }

    public async resetDatabaseToSavedSchema(
        dbPort: number = 5432
    ): Promise<void> {
        const dbConnectionPart = `-h localhost -p ${dbPort} -U ${this.getUsername()}`
        const dbName = this.getDatabase()

        await this.runPostgresCommand(`dropdb ${dbConnectionPart} ${dbName}`)
        await this.runPostgresCommand(`createdb ${dbConnectionPart} ${dbName}`)
        await this.runPostgresCommand(`pg_restore ${dbConnectionPart} -s -d ${dbName} ${this.dbDumpPath}`)
    }
}

export { ResettablePostgreSqlContainer, StartedResettablePostgreSqlContainer }

Would this be something that would make sense to get into the main repository? I would be happy to raise a PR if so.

[cristianrgreco]

I like the idea @vinaysshenoy, thanks for raising it. I'm aware that Docker are working on a checkpoint feature (https://docs.docker.com/engine/reference/commandline/checkpoint/), which would achieve a similar result but would work for all containers, not just postgres. I wonder if it'd be better to look into that instead.

[vinaysshenoy]

This seems cool! Although experimental at the moment. Would it make sense to have something like this available as documentation then since the performance gains have actually been great for us.

Then I can look into moving to the checkpoint feature once it becomes stable.