---
url: /v6.2/en-us/rds-replace-of-postgres.md
---
# Replace HENGSHI metadb

During the use of HENGSHI SENSE services, you can replace the built-in HENGSHI metadb according to business needs. This article explains the process of using cloud service PostgreSQL to replace the HENGSHI metadb.

## Preparation Work

Before configuration, please complete the following preparations on the cloud service PostgreSQL.

It is recommended to configure the hengshi user as a high-privilege user.

1. \[Required] Configure Hengshi Business Database\
   Create users and databases. You can refer to the example below for operations. In the example, both the username and account name are hengshi.

   > \[!IMPORTANT] Important
   > When creating the `hengshi` and `syslog` users, their passwords must not contain `%`, `&`, or `+`. When configuring `HS_PG_PWD` and `HS_SYSLOG_PWD` in `conf/hengshi-sense-env.sh`, use single quotes only, not double quotes; for example, `HS_PG_PWD='password'`.

   ```sql
   DO $body$ BEGIN IF NOT EXISTS (SELECT * FROM pg_catalog.pg_user WHERE  usename = 'hengshi') THEN CREATE ROLE hengshi CREATEDB CREATEROLE LOGIN PASSWORD 'hengshi202020'; END IF; END $body$;
   set role hengshi;
   create database hengshi;
   create database hengshi_sense_internal_storage;
   reset role;
   ```

2. \[Required] Configure Hengshi Log Database\
   Create a Hengshi log user, establish a Hengshi log database, and grant database permissions to the Hengshi log user. Refer to the example below for log user-related preparations.
   ```sql
   \c hengshi
   DO $body$ BEGIN IF NOT EXISTS (SELECT * FROM pg_catalog.pg_user WHERE usename = 'syslog') THEN CREATE ROLE syslog LOGIN PASSWORD 'syslog'; END IF; END $body$;
   GRANT ALL PRIVILEGES ON DATABASE hengshi to syslog;
   CREATE DATABASE syslog;
   GRANT ALL PRIVILEGES ON DATABASE syslog to syslog;
   ```

3. \[Optional] Configure Read-Only Account\
   Prepare a read-only account (this operation is configured after successfully starting the Hengshi service). Refer to the example below to set up a read-only account.
   ```sql
   \c hengshi
   DO $body$ BEGIN IF NOT EXISTS (SELECT * FROM pg_catalog.pg_user WHERE  usename = 'hsro') THEN CREATE ROLE hsro LOGIN PASSWORD 'hsro202020'; END IF; END $body$;
   GRANT USAGE ON SCHEMA public TO hsro;
   ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO hsro;
   GRANT SELECT ON ALL TABLES IN SCHEMA public TO hsro;
   GRANT USAGE ON SCHEMA quartz TO hsro;
   ALTER DEFAULT PRIVILEGES IN SCHEMA quartz GRANT SELECT ON TABLES TO hsro;
   GRANT SELECT ON ALL TABLES IN SCHEMA quartz TO hsro;
   ```

4. \[Required] Configure Hengshi Business Database Permissions\
   Use a super administrator account to perform user authorization operations.\
   Hengshi-sense version >= 4.4 requires the cloud service PostgreSQL database hengshi user to have CREATE/USAGE permissions for the pg_catalog (schema) of the hengshi and hengshi_sense_internal_storage databases, or the hengshi user must have super administrator privileges.

   * Check whether the hengshi user has super administrator privileges

   ```sql
   select usename from pg_catalog.pg_user where usesuper = 't' and usename = 'hengshi';
   # If the query result is not empty, it means the hengshi user has super administrator privileges.
   ```

   > \[!TIP] Tip\
   > If the hengshi user has super administrator privileges, ignore the following steps.

   * Connect to the hengshi and hengshi_sense_internal_storage databases in sequence and perform authorization operations.

   ```sql
   \c hengshi
   GRANT ALL ON SCHEMA pg_catalog TO hengshi;
   \c hengshi_sense_internal_storage
   GRANT ALL ON SCHEMA pg_catalog TO hengshi;
   ```

   * Connect to the hengshi and hengshi_sense_internal_storage databases in sequence and check whether the authorization operation was successful.

   ```sql
   \c hengshi
   select nspname,pri_t,rolname from (SELECT nspname, (aclexplode(COALESCE(nspacl, acldefault('n', nspowner)))).grantee        AS grantee, (aclexplode(COALESCE(nspacl, acldefault('n', nspowner)))).privilege_type AS pri_t FROM pg_namespace WHERE nspname = 'pg_catalog') a left join pg_roles b on a.grantee=b.oid where rolname = 'HS_PG_USR';
   \c hengshi_sense_internal_storage
   select nspname,pri_t,rolname from (SELECT nspname, (aclexplode(COALESCE(nspacl, acldefault('n', nspowner)))).grantee        AS grantee, (aclexplode(COALESCE(nspacl, acldefault('n', nspowner)))).privilege_type AS pri_t FROM pg_namespace WHERE nspname = 'pg_catalog') a left join pg_roles b on a.grantee=b.oid where rolname = 'HS_PG_USR';
   ```

   * If the authorization is successful, the query result will be as follows, indicating that the hengshi user already has USAGE and CREATE permissions.

|nspname   | pri_t  | rolname|
|------------|--------|-------|
|pg_catalog | USAGE  | HS_PG_USR|
|pg_catalog | CREATE | HS_PG_USR|

* Use the hengshi user to connect to the hengshi and hengshi_sense_internal_storage databases in sequence. Use the following SQL to verify user permissions. If no error message appears during execution, the authorization operation is complete.
  ```sql
  CREATE OR REPLACE FUNCTION pg_catalog.myTestCreate(text)
   RETURNS numeric
   IMMUTABLE STRICT AS
   $
   BEGIN
   RETURN $1::numeric;
   EXCEPTION
       WHEN OTHERS THEN
           RETURN NULL;
   END
   $ LANGUAGE plpgsql;

   select myTestCreate('123');

   DROP FUNCTION pg_catalog.myTestCreate(text);
  ```

## Modify Database Configuration

Modify the following configuration items in the file `conf/hengshi-sense-env.sh`, then restart HENGSHI SENSE. After restarting, the HENGSHI metadb will be successfully replaced.

```shell
HS_PG_HOST=xxx         # HENGSHI business database host
HS_PG_PORT=5432        # HENGSHI business database port
HS_PG_DB="hengshi"     # HENGSHI business database dbname
HS_PG_USR=hengshi      # HENGSHI business database user
HS_PG_PWD='hengshi'    # HENGSHI business database password

export INTERNAL_STORAGE_DB_NAME="hengshi_sense_internal_storage"  # HENGSHI internal storage database

HS_SYSLOG_HOST=xxxx    # HENGSHI log database host (usually the same as the business database)
HS_SYSLOG_PORT=5432    # HENGSHI log database port (usually the same as the business database)
HS_SYSLOG_DB=syslog    # HENGSHI log database dbname
HS_SYSLOG_USR=syslog   # HENGSHI log database user
HS_SYSLOG_PWD='syslog' # HENGSHI log database password

export HS_PG_IF_EXTERNAL=true  # Declare the use of an external PG database to replace the HENGSHI business database
```