Skip to main content

Set up the Origin by Corvus PostgreSQL database

PostgreSQL v15+ is required for Origin by Corvus server installations. Create the database, user, and grants before you install Origin by Corvus Server, so the installer has something to connect to.

You can run PostgreSQL on the same host as Origin by Corvus, on a separate server, or as a managed service such as Amazon RDS or Azure Database for PostgreSQL. See the database software documentation for the supported versions and the minimum version policy.

Create the database and user​

  1. Create an PostgreSQL server instance. See the PostgreSQL documentation for details. When the installation is complete, the PostgreSQL server is running, and a Linux user account called postgres has been created.

  2. Create the Origin by Corvus database and user:

    1. Access PostgreSQL by running:

      sudo -u postgres psql
    2. Create the database:

      CREATE DATABASE origin WITH ENCODING 'UTF8' LC_COLLATE='en_US.UTF-8' LC_CTYPE='en_US.UTF-8' TEMPLATE=template0;

    If this steps fails with an error message like invalid LC_COLLATE locale name: "en_US.UTF-8", you need to generate the locale first using locale-gen en_US.UTF-8.

    1. Create the Origin by Corvus user with a secure password:

      CREATE USER mmuser WITH PASSWORD 'mmuser-password';
    2. Grant database access to the user:

      GRANT ALL PRIVILEGES ON DATABASE origin to mmuser;
    3. If using PostgreSQL v15.x or later, additional grants are required:

      ALTER DATABASE origin OWNER TO mmuser;
      -- Connect to the origin database so the schema grants below apply to the right schema
      \c origin
      ALTER SCHEMA public OWNER TO mmuser;
      GRANT USAGE, CREATE ON SCHEMA public TO mmuser;

Allow remote connections​

Complete this section only if the database runs on a different host from Origin by Corvus Server. A database local to the application host needs no additional configuration.

  1. Edit postgresql.conf to allow remote connections:

Edit /etc/postgresql/{version}/main/postgresql.conf:

listen_addresses = '*'
  1. Configure client authentication by editing pg_hba.conf.

    Add the following line, replacing {origin-server-IP}:

    host all all {origin-server-IP}/32 md5

    Restrict the source to the Origin by Corvus server's address rather than a wider range, so the database only accepts connections from the application layer.

  2. Restart the PostgreSQL service to apply the configuration changes:

    sudo systemctl restart postgresql

Troubleshooting​

Origin by Corvus can't use a fresh PostgreSQL v15 installation​

PostgreSQL v15 introduces changes that may affect compatibility with previous releases. If you're deploying a fresh installation of PostgreSQL v15 and Origin by Corvus can't create its schema, run this command to ensure that you can use Origin by Corvus:

GRANT CREATE ON SCHEMA public TO PUBLIC;

Full-text search doesn't use indexes with a non-English default_text_search_config​

Origin by Corvus uses default_text_search_config for full-text search in PostgreSQL databases, as opposed to a hardcoded text search config. However, indexes are still created with a hardcoded text search config (english) and as a result, full-text search may never use the indexes.

Some of the tables in Origin by Corvus, like Posts or Users, contain GIN indexes to improve the database full-text search feature in PostgreSQL.

These indexes need to be built against a specific language, and when they're created they're hard-coded to English. Full-text search queries are always performed using the default_text_search_config database setting. In order for the full-text search feature to leverage the indexes, the language specified in the query needs to match the language specified in the index.

If the default_text_search_config is not set to english, the GIN indexes will not be used. Database administrators can work around this by dropping the specific GIN index they're interested in and rebuilding it with the value of default_text_search_config.

For example, if the default language of your server is Spanish:

-- Create the new index with a new name before dropping the old one
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_posts_message_txt_spanish ON posts USING gin(to_tsvector('spanish', message));

-- Check that the new index does work. If it does, drop the old one and rename the new one:
DROP INDEX CONCURRENTLY IF EXISTS idx_posts_message_txt;
ALTER INDEX idx_posts_message_txt_spanish RENAME TO idx_posts_message_txt;

Change the PostgreSQL username and password​

We recommend changing the PostgreSQL username and password in the .env file.

Next steps​

Once the database is ready, continue with the rest of the preparation checklist, or go straight to your install method: Kubernetes, Linux, or containers.