Fix #972 and should help with improved goose support for cockroachdb/cockroach#13546 and PgBouncer when used with transaction pooling.

This PR adds a few things.

1. A new goose provider option WithLocker which uses the new lock.Locker interface:

// Locker is the interface to lock and unlock the database.
//
// Unlike [SessionLocker], the Lock and Unlock methods are called on a [*sql.DB] and do not require
// the same connection to be used for both methods.
type Locker interface {
	Lock(ctx context.Context, db *sql.DB) error
	Unlock(ctx context.Context, db *sql.DB) error
}

Note, although we added Postgres support, this could easily be extended to other databases either via a custom Locker implementation or a solid PR within Goose for one of the natively supported databases.

2. A NewPostgresTableLocker implementation which uses a new table goose_lock (configurable) to provide connection-independent locking with automatic lease expiration

Usage:

locker, err := lock.NewPostgresTableLocker() // <--- NEW
if err != nil {
	return err
}
provider, err := goose.NewProvider(
	database.DialectPostgres,
	db,
	os.DirFS(dir),
	goose.WithLocker(locker), // <--- NEW
)
if err != nil {
	return err
}

The defaults are documented, but I'll add them here for brevity.

• Table: "goose_lock"
• Lock ID: 4097083626 (crc32 IEEE of "goose")
• Lease duration: 30s (lock expires if heartbeat stops)
• Heartbeat interval: 5s (keeps lock alive)
• Lock acquisition: 5s retry intervals, 5min timeout
• Lock release: 2s retry intervals, 1min timeout

In other words, when multiple instances start simultaneously, one acquires the lock and runs migrations (refreshing every 5 seconds) while others retry every 5 seconds for up to 5 minutes, with automatic failover after 30 seconds if the heartbeat stops.