How to add read-replicas a database that is already connected on hasura cloud

[BenoitRanque]

While it is currently possible when creating the connection,
it is currently not possible to add read replicas to a postgres database that is already connected.

This is a UI issue, and will be fixed soon.

In the meantime, this can be worked around by writing some metadata manually.

Instructions to follow

[BenoitRanque]

0. set an environment variable for the connection string of the read-only replica.
   This is done in project settings > env vars > new env var
   For the purpose of this example we will assume the variable is PG_READONLY_DATABASE_URL
1. The next steps will require hasura cli. If you haven't yet, you should install it by following the instructions available here
2. We are assuming you aren't already using the cli to version control your metadata. If you are, skip to step 8
3. Initialize the project directory by runing hasura init. You will be prompted to enter a name, defaulting to "hasura"
4. cd into the project directory. Further commands will be assumed to run from that directory.
5. Edit the config.yaml file. You must set the endpoint key to project url, which should be something like https://my-project.hasura.app
6. You can also set your admin secret in that file, however this is not good practice, especially if you plan to commit this metadata to a repository. Instead, create a .env file in the current directory, with the contents HASURA_GRAPHQL_ADMIN_SECRET=<your admin secret>. Make sure it is gitignored.
7. You may now export your project's metadata. Run hasura metadata export. The metadata directory will now contain your current metadata.
8. Open the metadata/databases.yaml file. Assuming you have a database connected, it should look something like this:

- name: default
kind: postgres
configuration:
  connection_info:
    database_url:
      from_env: PG_DATABASE_URL
    isolation_level: read-committed
    use_prepared_statements: false
tables: "!include default/tables/tables.yaml"

9. Edit the file to add your read replicas

- name: default
kind: postgres
configuration:
  connection_info:
    database_url:
      from_env: PG_DATABASE_URL
    isolation_level: read-committed
    use_prepared_statements: false
  read_replicas:
    - database_url:
        from_env: PG_READONLY_DATABASE_URL
      isolation_level: read-committed
      pool_settings:
        connection_lifetime: 600
      use_prepared_statements: false
tables: "!include default/tables/tables.yaml"

10. (Optional) you can add multiple read replicas if you so wish:

- name: default
kind: postgres
configuration:
  connection_info:
    database_url:
      from_env: PG_DATABASE_URL
    isolation_level: read-committed
    use_prepared_statements: false
  read_replicas:
    - database_url:
        from_env: PG_READONLY_DATABASE_URL
      isolation_level: read-committed
      pool_settings:
        connection_lifetime: 600
      use_prepared_statements: false
    - database_url:
        from_env: PG_READONLY_DATABASE2_URL
      isolation_level: read-committed
      pool_settings:
        connection_lifetime: 600
      use_prepared_statements: false
tables: "!include default/tables/tables.yaml"

11. Run hasura metadata apply
12. Your read replicas should now be connected.

[unoscar]

Hi @BenoitRanque,

Thank you for this response. I think I am almost there. when I am applying the configuration on step 11 I am getting this response from the terminal

and this is what my UI was showing at the hasura console

I am using AWS Aurora, from my understanding the user credentials I am using on the already existing Database Connection URL will be the same for the read replica (of course with the updated URL from the read replica)

[unoscar]

So sorry, the issue was totally on my end. I forgot the letter "s" at the end of my database connection URL

Everything seems to be working great with the read replica. Thank you very much @BenoitRanque