[Bug]: Error on postgres restore

[aBMania]

Testcontainers version

0.38.0

Using the latest Testcontainers version?

Yes

Host OS

Linux

Host arch

x86

Go version

1.24.0

Docker version

Docker version 28.1.1, build 4eba377

Docker info

Client: Docker Engine - Community
 Version:    28.1.1
 Context:    default
 Debug Mode: false
 Plugins:
  buildx: Docker Buildx (Docker Inc.)
    Version:  v0.23.0
    Path:     /usr/libexec/docker/cli-plugins/docker-buildx
  compose: Docker Compose (Docker Inc.)
    Version:  v2.35.1
    Path:     /usr/libexec/docker/cli-plugins/docker-compose

Server:
 Containers: 16
  Running: 5
  Paused: 0
  Stopped: 11
 Images: 220
 Server Version: 28.1.1
 Storage Driver: overlay2
  Backing Filesystem: extfs
  Supports d_type: true
  Using metacopy: false
  Native Overlay Diff: true
  userxattr: false
 Logging Driver: json-file
 Cgroup Driver: systemd
 Cgroup Version: 2
 Plugins:
  Volume: local
  Network: bridge host ipvlan macvlan null overlay
  Log: awslogs fluentd gcplogs gelf journald json-file local splunk syslog
 Swarm: inactive
 Runtimes: runc io.containerd.runc.v2
 Default Runtime: runc
 Init Binary: docker-init
 containerd version: 05044ec0a9a75232cad458027ca83437aae3f4da
 runc version: v1.2.5-0-g59923ef
 init version: de40ad0
 Security Options:
  apparmor
  seccomp
   Profile: builtin
  cgroupns
 Kernel Version: 6.8.0-60-generic
 Operating System: Ubuntu 22.04.5 LTS
 OSType: linux
 Architecture: x86_64
 CPUs: 8
 Total Memory: 38.92GiB
 Name: mania-exaion
 ID: 4f2e98b2-ae93-4512-85b3-a2276c32d22e
 Docker Root Dir: /var/lib/docker
 Debug Mode: false
 Experimental: false
 Insecure Registries:
  ::1/128
  127.0.0.0/8
 Live Restore Enabled: false

What happened?

When calling PostgresContainer's Restore method, i expect never receive an error. But sometimes (randomly), i get a database "[DBNAME]" already exists error

Relevant code:

func (c *PostgresContainer) Restore(ctx context.Context, opts ...SnapshotOption) error {
	snapshotName, err := c.checkSnapshotConfig(opts)
	if err != nil {
		return err
	}

	// execute the commands to restore the snapshot, in order
	return c.execCommandsSQL(ctx,
		// Drop the entire database by connecting to the postgres global database
		fmt.Sprintf(`DROP DATABASE "%s" with (FORCE)`, c.dbName),
		// Then restore the previous snapshot
		fmt.Sprintf(`CREATE DATABASE "%s" WITH TEMPLATE "%s" OWNER "%s"`, c.dbName, snapshotName, c.user),
	)
}

I've dug a little, and it appears that DROP DATABASE [DBNAME] with (FORCE) does not ensure that the database is actually deleted. So there seems to be a race condition when trying to create the database again. If the database is not yet deleted, I get the [DBNAME]" already exists error.

Workaround

As a workaround, I was able to implement my own way of restoring the database to my "post-migrations" state by running schema deletion/re-creation and then running my migrations

DROP SCHEMA public CASCADE;
CREATE SCHEMA public;

Thanks for the awesome tool,
Sylvain