Request a Demo
26.1. Configuring the Metadata Catalog
Prev UpChapter 26. Metadata CatalogHome Next

26.1. Configuring the Metadata Catalog #

26.1.1. Defining a Metadata Catalog Connection (define_catalog_connection)
26.1.2. Initializing the Metadata Catalog (init)

Metadata tables of pgpro_metastore are stored in the axe_catalog schema of the Postgres Pro DBMS, or in the so-called metadata catalog. This catalog can be initialized on the local Postgres Pro AXE server or on a remote server.

26.1.1. Defining a Metadata Catalog Connection (define_catalog_connection) #

You must define a metadata catalog connection regardless of whether the metadata catalog is initialized on the local Postgres Pro AXE server or on a remote server.

Required privileges: Postgres Pro AXE administrator only. For a full list of stored procedures and privileges, refer to Section 12.1.

Execute the following command on the Postgres Pro AXE server: 

  SELECT metastore.define_catalog_connection('host', 'port', 'database', 'role_name', 'password') ON CONFLICT DO NOTHING;

Where:

  • host and port: The hostname and port for connecting to the server where the metadata catalog must be initialized later.

  • database: The name of the server database where metadata tables must be created.

  • role_name and password: The Postgres Pro role and password for logging in to the Postgres Pro DBMS on the server with the metadata catalog. This role must have privileges to create and modify metadata tables (that is, heap tables) in the specified database.

If one of these parameters is missing, pass an empty string as a value.

Example 26.1. Defining a Metadata Catalog Connection without a Username and Password

  SELECT metastore.define_catalog_connection('localhost','5433','postgres','','') ON CONFLICT DO NOTHING;

For the changes to take effect, reconnect to the Postgres Pro AXE server (not to the server with the metadata catalog).

Postgres Pro AXE performs the following actions:

  1. Checks input parameters and user privileges.

  2. Updates the foreign server (metastore_catalog) using the specified public connection parameters of the server with the metadata catalog: 

        ALTER SERVER metastore_catalog
      OPTIONS (
        SET host     'host',
        SET port     'port',
        SET dbname   'database',
        SET user     'role_name'
      ) ON CONFLICT DO NOTHING;

  3. Updates the user mapping for the PUBLIC role using the specified private connection parameters of the server with the metadata catalog: 

      ALTER USER MAPPING FOR PUBLIC
    SERVER metastore_catalog
    OPTIONS (
      SET password 'password'
    ) ON CONFLICT DO NOTHING;

If the connection to the server with the metadata catalog fails when performing one of the stored procedures, you are automatically reconnected using the last connection parameters.

For example: 

  postgres=# SELECT metastore.remove_storage('mt_storage') ON CONFLICT DO NOTHING;
  INFO:  Connection failed! Trying to reconnect.
  INFO:  Reconnecting...1
  INFO:  Reconnect successful!
          remove_storage
  -----------------------------------
  Storage was removed successfully.
  (1 row)

26.1.2. Initializing the Metadata Catalog (init) #

When you initialize the metadata catalog, metadata tables are created in the server database.

Before performing this instruction, define a metadata catalog connection.

Required privileges: Postgres Pro AXE administrator only. For a full list of stored procedures and privileges, refer to Section 12.1.

Execute the following command on the Postgres Pro AXE server: 

  SELECT metastore.init(force_init bool DEFAULT false)

Where force_init specifies whether to reinitialize the metadata catalog if it is already initialized on the server.

Postgres Pro AXE performs the following actions:

  1. Checks input parameters and user privileges.

  2. If the force_init parameter is set to false and the axe_catalog schema already exists on the server, returns a warning that the metadata catalog is already initialized and terminates the command. Otherwise, Postgres Pro AXE initializes the metadata catalog on the server.

  3. If the force_init parameter is set to true, performs the following actions:

    1. Deletes all proxy tables from the metastore schema on the Postgres Pro AXE server.

    2. Reinitializes the metadata catalog on the server.

Prev Up Next
Chapter 26. Metadata Catalog Home 26.2. Connecting to a Remote Server with the Metadata Catalog
epub pdf