compatibility with terraform-provider-postgresql

[robert-s-lee]

Describe the problem

hashicorp/terraform-provider-postgresql#91 has details.

To Reproduce

Postgres provider runs select version();

On Postgres,

# select version();
                                                     version
------------------------------------------------------------------------------------------------------------------
 PostgreSQL 11.2 on x86_64-apple-darwin18.2.0, compiled by Apple LLVM version 10.0.0 (clang-1000.11.45.5), 64-bit
(1 row)

On CockroachDB,

# select version();
                                       version
--------------------------------------------------------------------------------------
 CockroachDB CCL v19.1.3 (x86_64-apple-darwin14, built 2019/07/08 18:19:30, go1.11.6)
(1 row)

Expected behavior

Jira issue: CRDB-5593

[awoods187]

@timveil did you do anything here when you were using terraform?

[robert-s-lee]

@bdarnell thoughts on forking https://github.com/terraform-providers/terraform-provider-postgresql to fix the issue. This is blocking the customer from using the standard production deployment testing. The customer and Hasipcorp are in favor of forking.

[bdarnell]

Yeah, forking seems like the right thing to do. If it were just the select version(), I'm pretty sure there are ways to make it work in both databases (select setting from pg_catalog.pg_settings where name='server_version'; should work in both), but I think there are enough differences that a cockroach-specific provider is going to be better.

[awoods187]

Plus forking allows us to build upon this in the future. +1

[awoods187]

@vy-ton can you take a look here?

[vy-ton]

@johnrk Can Deployment & Operations create a CockroachDB fork of the Terraform Postgres Provider? AppDev could then investigate any further Postgres compatibility issues.

[aybabtme]

👋 I was wondering if there's plans to support this terraform provider, or to have a dedicated cockroachdb terraform provider? Creating databases, schema, roles, etc, using Terraform would be super helpful.

[johnrk-zz]

Hi @aybabtme, some users have had success using the Postgres Terraform provider with CockroachDB.

Have you happened to try that yet?

[martinkubrak]

Hi, trying this postgreqsql provider I'm getting [ERROR] vertex "postgresql_database.test_db" error: could not get advisory lock for role root: pq: unknown function: pg_advisory_xact_lock() when trying postgresql_database resource. postgresql_role fails with [ERROR] vertex "postgresql_role.test_role" error: error creating role test_role: pq: at or near "connection": syntax error

I've set expected_version to "13.0.0" as per select setting from pg_catalog.pg_settings where name='server_version';

Has anyone had luck with the cyrilgdn/postgresql?

[kylepl]

I've only tested getting Role to work, but I've started a discussion at cyrilgdn/terraform-provider-postgresql#372, with the two versions available that you can build locally and install at your own risk:

• Terraform commit
• Pulumi commit