Database schema for PostgreSQL Cluster Console

[vitabaks]

Issue: https://github.com/vitabaks/postgresql_cluster/issues/589

As part of that PR, a database schema is being developed for the PostgreSQL cluster console.

Tables:

• cloud_providers
  • Table containing cloud providers information
• cloud_regions
  • Table containing cloud regions information for various cloud providers
• cloud_instances
  • Table containing cloud instances information (including the approximate price) for various cloud providers
• cloud_volumes
  • Table containing cloud volume information (including the approximate price for various cloud providers
• cloud_images
  • Table containing cloud images information for various cloud providers
    • Note: For all cloud providers except AWS, the image is the same for all regions. For AWS, the image must be specified for each specific region.
• secrets
  • Table containing secrets for accessing cloud providers and servers
    • Note: The data is encrypted using the pgcrypto extension and a symmetric key. This symmetric key is generated at the application level and is unique for each installation.
• projects
  • Table containing information about projects
• environments
  • Table containing information about environments
• clusters
  • Table containing information about Postgres clusters
• servers
  • Table containing information about servers within a Postgres cluster
• extensions
  • The table stores information about Postgres extensions, including name, description, supported Postgres version range, and whether the extension is a contrib module or third-party.
  • 'postgres_min_version' and 'postgres_max_version' define the range of Postgres versions supported by extensions. If the postgres_max_version is NULL, it is assumed that the extension is still supported by new versions of Postgres.
• operations
  • Table containing logs of operations performed on cluster.
    • Note: The migration includes a DO block that checks for the presence of the timescaledb extension. If the extension is installed, the operations table is converted into a hypertable with monthly partitioning. Additionally, the block checks the timescaledb license. If the license is a Community license (timescale), a hypertable compression policy is created for partitions older than one month.

Views:

• v_secrets_list
  • Displays a list of secrets (without revealing secret values) along with additional metadata such as creation and update timestamps. It also includes information about whether each secret is in use and, if so, provides details on which clusters and servers are utilizing the secret.
• v_operations
  • Displays a list of operations, with additional columns such as the name of the cluster and environment.

Functions:

• update_server_count
  • Function to update the server_count column in the clusters table.
    • Note: This function calculates the number of servers associated with a specific cluster and updates the server_count accordingly. The trigger update_server_count_trigger is automatically executed whenever there are INSERT, UPDATE, or DELETE operations on the servers table. This ensures that the server_count in the clusters table is always accurate and up-to-date.
• add_secret
  • Function to add a secret
    • Usage example: SELECT add_secret('cloud_secret', 'AWS', '{"AWS_ACCESS_KEY_ID": "<CONTENT>", "AWS_SECRET_ACCESS_KEY": "<CONTENT>"}', 'my_encryption_key');
• update_secret
  • Function to update a secret.
    • Usage example: SELECT update_secret(<secret_id>, '<new_secret_type>', '<new_secret_name>', '<new_secret_value>', '<encryption_key>');
• get_secret
  • Function to get a secret value
    • Usage example: SELECT get_secret(1, 'my_encryption_key');
• get_extensions
  • Function to get a list of available extensions, all or 'contrib'/'third_party' only
    • Usage example: SELECT get_extensions(16);, SELECT get_extensions(16, 'contrib');, SELECT get_extensions(16, 'third_party');