ERROR: relation "<some relation>" does not exist (SQLSTATE 42P01)

[shimonp21]

What is the issue

Running cloudquery fetch fails with many ERROR: relation "<some relation>" does not exist (SQLSTATE 42P01)

Who is affected?

Setups where the username used to connect to PostgreSQL is cloudquery . More specifically, You may encounter this problem if you are running an older version of the cloudquery binary (<= v0.28.0), and a newer version of the provider ( aws >= v0.12.17 , gcp >= v0.8.16, azure >= v0.11.13, k8s >= v0.5.12, terraform >= v0.4.11, okta >= v0.5.11, digitalocean >= v0.5.13).

What is the cause of the issue?

• PostgreSQL tables belong to ‘schemas’. By default, when accessing (Creating/Inserting/Selecting) tables, the schema is decided by a search_path . The default search_path is "$user", public. This means that, when creating/inserting/selecting a table, PostgreSQL will first try the schema with the same name as your PosgreSQL username. If such a table is not found. For instance, When connecting to PostgreSQL with username someuser and running SELECT * FROM sometable, PostgreSQL will first look for someuser.sometable, and, if the table is not found, the table public.sometable.
• This can cause unpredictable behavior that depends on the PostgreSQL username. For instance, when running with PostgreSQL username cloudquery, tables are created in the cloudquery schema (usually used for internal cloudquery metadata), instead of the public schema.
• PR fix: Issues when PG username is 'cloudquery' cq-provider-sdk#371, released in cq-provider-sdk v0.12.2, changed the way cloudquery connects to PostgreSQL to always use search_path=public.
• This change introduced compatibility issues when running a newer provider version and an older cloudquery executable. The older cloudquery executable will create tables in cloudquery schema, but the newer provider will look for the tables in the public schema.

What action can I take to fix this?

• Option 1 - upgrade your cloudquery executable

  1. Run cloudquery provider drop [your-provider] —-force. (Required, because your tables need to be recreated). Note that this will drop all your tables until you re-run fetch!

  2. Upgrade your cloudquery executable to v0.28.0 or above

     • Note that if upgrading to v0.29.0 or above, you will encounter a different breaking change: the move from config.hcl file to cloudquery.yml . If choosing this route, you will need to either:

       • Convert your config.hcl to a cloudquery.yml file (re-run cloudquery init and edit the connection and providers sections. You can also take a look at examples on our docs and hub).
       • Add --config config.hcl to your commandlines (hcl is still supported for now, but requires this explicit commandline flag).

     • Linux - latest version

       curl -L https://github.com/cloudquery/cloudquery/releases/latest/download/cloudquery_linux_x86_64 -o cloudquery
       chmod a+x cloudquery
       

     • Linux - v0.28.0:

       curl -L https://github.com/cloudquery/cloudquery/releases/download/v0.28.0/cloudquery_Linux_x86_64 -o cloudquery
       chmod a+x cloudquery
       

     • macOS (latest):

       curl -L https://github.com/cloudquery/cloudquery/releases/latest/download/cloudquery_darwin_x86_64 -o cloudquery
       chmod a+x cloudquery
       # Also possible: "brew upgrade cloudquery"
       # For arm64: curl -L https://github.com/cloudquery/cloudquery/releases/latest/download/cloudquery_darwin_arm64 -o cloudquery
       

     • macOS (v0.28.0)

       
       curl -L https://github.com/cloudquery/cloudquery/releases/download/v0.28.0/cloudquery_Darwin_x86_64 -o cloudquery
       chmod a+x cloudquery
       
       # for arm64: curl -L https://github.com/cloudquery/cloudquery/releases/download/v0.28.0/cloudquery_Darwin_arm64 -o cloudquery
       

  3. re-run cloudquery fetch. You should no longer see table not found errors.

• Option 2 - pin your provider version to a version before this change, until you are ready to upgrade your cloudquery executable:

  • In your config.hcl file, change your provider block to (choose the one you need:)

  provider "aws" {
    version = "v0.12.16"
  }
  
  provider "gcp" {
    version = "v0.8.15"
  }
  
  provider "azure" {
    version = "v0.11.12"
  }
  
  provider "k8s" {
    version = "v0.5.11"
  }
  
  provider "terraform" {
    version = "v0.4.10"
  }
  
  provider "okta" {
    version = "v0.5.10"
  }
  
  provider "digitalocean" {
    version = "v0.5.12"
  }
  

  Where can I get further assistance?

  • Don’t hesitate to contact us on discord!

[shimonp21]

Closing. The writeup can still be useful if you were using the specified versions.