Spanner not support the Postgresql dialect

Describe the Bug

If you have a cloud spanner instance created with dialect == POSTGRESQL migration fails when trying to create the SchemaMigrations table

Steps to Reproduce

Create a database like:

gcloud spanner databases create $dbname --database-dialect=POSTGRESQL --instance=$instance

The run the migrate CLI like:

migrate -path /path/to/migrations \
   -database spanner://projects/${project}/instances/${instance}/databases/${dbname}?x-clean-statements=true up

And you get an error like:

2023/12/31 01:34:50 error: rpc error: code = InvalidArgument desc = [ERROR] syntax error at or near "PRIMARY"; failed to parse the DDL statements. in line 0: CREATE TABLE SchemaMigrations (                                            │
│     Version INT64 NOT NULL,                                                                                                                                                                                                              │
│     Dirty    BOOL NOT NULL                                                                                                                                                                                                               │
│     ) PRIMARY KEY(Version)

And, indeed, if you check the docs for the postgres dialect that syntax for specifying the primarty key isn't supported. Expected Behavior A clear and concise description of what you expected to happen.

Migrate Version

4.17.0

Loaded Source Drivers e.g. s3, github, go-bindata, gcs, file Obtained by running: migrate -help

Loaded Database Drivers

Database drivers: stub, firebirdsql, cockroachdb, crdb-postgres, yugabyte, neo4j, mongodb, clickhouse, postgres, redshift, sqlserver, spanner, cassandra, mysql, pgx4, pgx5, cockroach, yugabytedb, firebird, pgx, postgresql, ysql, rqlite, mongodb+srv

Go Version

Not relevant; I download migrate from your releases page.

Additional context

Cloud spanner supports 2 dialects: GoogleSQL and PostgreSQL. The latter is compatible with PostgreSQL and lets you write SQL statements that work either with cloud spanner or postgres. However, you have to specify the dialect when you create the spanner instance and it seems golang-migrate always tries to create the SchemaMigrations table using a DDL that isn't Postgres compatible. As far as I can tell, there isn't an option to specify the dialect when running the migrations CLI.

golang-migrate / migrate

Spanner not support the Postgresql dialect #1021