Terraform: `terraform import` doesn't work with multiple postgresql providers (aliases)

[one1zero1one]

Terraform Version

Terraform v0.11.7
+ provider.postgresql v0.1.1

Terraform Configuration Files

(config was adapted from the docu example to illustrate behaviour)

provider "postgresql" {
  alias    = "pg1"
  host     = "postgres_server_ip1"
  username = "postgres_user1"
  password = "postgres_password1"
}

provider "postgresql" {
  alias    = "pg2"
  host     = "postgres_server_ip2"
  username = "postgres_user2"
  password = "postgres_password2"
}

resource "postgresql_database" "my_db1" {
  provider = "postgresql.pg1"
  name     = "my_db1"
}

resource "postgresql_database" "my_db2" {
  provider = "postgresql.pg2"
  name     = "my_db2"
}

Expected Behavior

When I run terraform import postgresql_database.my_db1 my_db1 or/and terraform import postgresql_database.my_db2 my_db2 it should import them and I should see it in the state.

Actual Behavior

When I run terraform import postgresql_database.my_db1 my_db1 I get

Error: provider.postgresql: Error initializing PostgreSQL client: error detecting capabilities: error PostgreSQL version: dial tcp :5432: getsockopt: connection refused

If I run it like this terraform import -provider="postgresql.pg1" postgresql_database.my_db1 my_db1 , i get:

postgresql_database.my_db1: Importing from ID "my_db1"...
postgresql_database.my_db1: Import complete!
  Imported postgresql_database (ID: my_db1)
postgresql_database.my_db1: Refreshing state... (ID: my_db1)

Error: provider.postgresql: Error initializing PostgreSQL client: error detecting capabilities: error PostgreSQL version: dial tcp :5432: getsockopt: connection refused

but nothing is saved in the state.

Worth mentioning that _without using aliases_ (with one one provider) it works fine.
However, my end goal is to move the imported objects in modules (terraform state mv), I have modules ready to plug the code in as soon as the objects in the state are ok. I tried 'reusing' the same provider, but I get into trouble as soon as I plug the modules code in.

Steps to Reproduce

Have to databases from two different postgres providers, terraform init, terraform import

Additional Context

Tried in 0.10.8 and latest, same behaviour. Can't tell if terraform or postgres issue.

[jbardin]

Hi @one1zero1one,

Sorry this is a bit confusing. The import command has a -provider flag to indicate which provider to use. There's an open issue #17139 about having import get the provider from the configuration.

[one1zero1one]

@jbardin thanks for your feedback, however I tried using -provider as described in my issue, and I get a strange error

postgresql_database.my_db1: Importing from ID "my_db1"...
postgresql_database.my_db1: Import complete!
  Imported postgresql_database (ID: my_db1)
postgresql_database.my_db1: Refreshing state... (ID: my_db1)

Error: provider.postgresql: Error initializing PostgreSQL client: error detecting capabilities: error PostgreSQL version: dial tcp :5432: getsockopt: connection refused

without anything in the state. @jbardin should I open an issue specifically for this ?

[jbardin]

Hi @one1zero1one,

Sorry, I missed that second command inline there.
I'm also not sure offhand what's at fault here, but I'll keep this as a core bug for now since I don't see the provider doing anything specifically around import.

Is it possible to get the trace log from your import example, which might have more clues?

Thanks!

[apparentlymart]

Hi @one1zero1one,

Although the example you shared has literal placeholder values for provider arguments, does your _actual_ config use variables or other interpolation expressions for these?

I ask because the error message you saw here seems to suggest that the provider was configured with an incomplete configuration, which might indicate that you're hitting the limitation being discussed in #13018.

[one1zero1one]

Thanks @jbardin for keeping this open, trace in this gist. Hey @apparentlymart - provider is configured with constants.

My original use-case has state in s3, but I tried also with local state. In fact, I could also replicate the issue using local containers (and local state) as follows.

docker-compose.yml:

---
version: '3'
services:
  db1:
    image: postgres:9.5
    environment:
      POSTGRES_USER: user1
      POSTGRES_PASSWORD: password1
      POSTGRES_DB: db1
    volumes:
      - db1:/var/lib/postgresql/data
    ports:
      - 5401:5432
  db2:
    image: postgres:9.5
    environment:
      POSTGRES_USER: user2
      POSTGRES_PASSWORD: password2
      POSTGRES_DB: db2
    volumes:
      - db2:/var/lib/postgresql/data
    ports:
      - 5402:5432
volumes:
  db1: {}
  db2: {}

main.tf:

terraform {
  required_version = ">= 0.10.0"
}

provider "postgresql" {
  alias    = "db1"
  host     = "localhost"
  port     = "5401"
  username = "user1"
  password = "password1"
  sslmode  = "disable"
}

resource "postgresql_database" "db1" {
  provider = "postgresql.db1"
  name     = "db1"
}

provider "postgresql" {
  alias    = "db2"
  host     = "localhost"
  port     = "5402"
  username = "user2"
  password = "password2"
  sslmode  = "disable"
}

resource "postgresql_database" "db2" {
  provider = "postgresql.db2"
  name     = "db2"
}

terraform init works ok, terraform plan shows expected results, apply fails since dbs already exist (as expected). It's only when trying to import that it says both import complete and failed with nothing stored in state).

terraform import -provider="postgresql.db1" postgresql_database.db1 db1
postgresql_database.db1: Importing from ID "db1"...
postgresql_database.db1: Import complete!
  Imported postgresql_database (ID: db1)
postgresql_database.db1: Refreshing state... (ID: db1)
Error importing: 1 error(s) occurred:

* provider.postgresql: Error initializing PostgreSQL client: error detecting capabilities: error PostgreSQL version: dial tcp :5432: getsockopt: connection refused

trace

[arthur-c]

I have the same issue with only one provider but with an alias. Removing the alias solve the issue.

Terraform v0.11.11
+ provider.postgresql v0.1.3

[Thana404]

I have the same issue with only one provider but with an alias. Removing the alias solve the issue.

Terraform v0.11.11
+ provider.postgresql v0.1.3

I second this. I just faced a similar issue during a db and role import, and I used the same trick to fix it (removing the alias).

Here's the error log I've got:

[terragrunt] 2019/02/18 14:20:05 Running command: terraform import postgresql_role.db_role my-db-role                                                                                                       

Error: provider.postgresql: Error initializing PostgreSQL client: error detecting capabilities: error PostgreSQL version: pq: password authentication failed for user "postgres"

Terraform v0.11.11
provider.postgresql v0.1.3

[heisian]

Also having trouble with this:

no alias

# postgresql.tf
variable "psql_host" {}
variable "psql_port" {}
variable "psql_database" {}
variable "psql_username" {}
variable "psql_password" {}

provider "postgresql" {
  host = "${var.psql_host}"
  port = "${var.psql_port}"
  database = "${var.psql_database}"
  username = "${var.psql_username}"
  password = "${var.psql_password}"
}

resource "postgresql_database" "production" {
  name = "apidb"
}

> terraform import postgresql_database.production production
postgresql_database.production: Importing from ID "production"...
postgresql_database.production: Import complete!
  Imported postgresql_database (ID: production)
postgresql_database.production: Refreshing state... (ID: production)

Error: postgresql_database.production (import id: production): 1 error occurred:
        * import postgresql_database.production result: production: import postgresql_database.production (id: production): Terraform detected a resource with this ID doesn't
exist. Please verify the ID is correct. You cannot import non-existent resources using Terraform import

We initially see success then an error. When running terraform plan, we see that the import didn't occur.

alias

# postgresql.tf
variable "psql_host" {}
variable "psql_port" {}
variable "psql_database" {}
variable "psql_username" {}
variable "psql_password" {}

provider "postgresql" {
  alias = "apidb"
  host = "${var.psql_host}"
  port = "${var.psql_port}"
  database = "${var.psql_database}"
  username = "${var.psql_username}"
  password = "${var.psql_password}"
}

resource "postgresql_database" "production" {
  provider = "postgresql.apidb"
  name = "apidb"
}

> terraform import postgresql_database.production postgresql

Error: provider.postgresql: Error initializing PostgreSQL client: error detecting capabilities: error PostgreSQL version: dial tcp :5432: connect: connection refused

Cannot connect (seems to be using incorrect credentials, not those specificied)

[jowrjowr]

I'm seeing a variation of this and I'm not sure if its the same thing.

```Terraform v0.12.23

• provider.aws v2.53.0
• provider.postgresql v1.5.0

During normal applies, postgresql works fine without incident.

However when I try to do a state import, on something that does NOT touch the postgresql provider, this happens:

```terraform import module.main.module.database.aws_cloudwatch_log_group.postgresql '/aws/rds/instance/portal-prod/postgresql'
module.main.module.database.aws_cloudwatch_log_group.postgresql: Importing from ID "/aws/rds/instance/portal-prod/postgresql"...
module.main.module.database.aws_cloudwatch_log_group.postgresql: Import prepared!
  Prepared aws_cloudwatch_log_group for import
module.main.module.database.aws_cloudwatch_log_group.postgresql: Refreshing state... [id=/aws/rds/instance/portal-prod/postgresql]

Error: Error initializing PostgreSQL client: error detecting capabilities: error PostgreSQL version: pq: password authentication failed for user "postgres"

  on .terraform/modules/main.database/database.tf line 103, in provider "postgresql":
 103: provider "postgresql" {

This persists even with wiping out the .terraform local state.

[mildwonkey]

I am going to close this issue due to inactivity. Several related issues with import have been fixed in v0.12 and v0.13, and the underlying code has changed quite a bit. If you are still experiencing an issue in terraform v0.13, please open a new issue and fill out the template so we can investigate.
Thanks!

[hashibot[bot]]

I'm going to lock this issue because it has been closed for _30 days_ ⏳. This helps our maintainers find and focus on the active issues.

If you have found a problem that seems similar to this, please open a new issue and complete the issue template so we can capture all the details necessary to investigate further.