Installing PGAA on WarehousePG v1.11

Prerequisites

Before installing PGAA, ensure you have:

  • A running WarehousePG 7.4 or later cluster on x86_64 RHEL 8 or RHEL 9.
  • Access to the EDB repository configured on the coordinator host.
  • The gpadmin user with sudo privileges on all hosts.

All steps must be performed as the gpadmin user unless otherwise noted.

Installing the extension

Downloading and installing the package

  1. Set up your EDB subscription credentials on the coordinator:

    export EDB_SUBSCRIPTION_TOKEN=<your-token>
    export EDB_SUBSCRIPTION_PLAN=analytics
    curl -1sSLf "https://downloads.enterprisedb.com/$EDB_SUBSCRIPTION_TOKEN/$EDB_SUBSCRIPTION_PLAN/setup.rpm.sh" | sudo -E bash
    sudo dnf download edb-whpg7-pgaa

    edb-whpg7-pgfs is a declared dependency of edb-whpg7-pgaa and will be downloaded automatically.

  2. On the coordinator, create a file all_hosts listing all hosts in the WHPG cluster. For example:

    cdw
    scdw
    sdw1
    sdw2
  3. Transfer and install the packages on all hosts:

    gpssh -f all_hosts -e "scp gpadmin@$(hostname):~/edb-whpg7-pgaa*.rpm /tmp/ && sudo dnf install -y /tmp/edb-whpg7-pgaa*.rpm"
  4. Verify that the packages are installed on all hosts:

    gpssh -f all_hosts -e 'rpm -q edb-whpg7-pgaa edb-whpg7-pgfs'

Enabling the extension

In WarehousePG installations, Seafowl is managed as a systemd service rather than as a background worker controlled by the extension itself. The RPM installs the systemd unit file automatically.

  1. On the coordinator, append PGAA and PGFS to the list of shared libraries using a comma separator, and restart the cluster.

    gpconfig -c shared_preload_libraries -v '<other_libraries>,pgaa,pgfs'
    gpstop -ra
  2. Enable and start the Seafowl service on all hosts, including the coordinator and every segment host:

    gpssh -f all_hosts -e 'sudo systemctl enable seafowl && sudo systemctl start seafowl'
    Note

    If installing in an environment where systemd isn't available (for example, Docker), you must daemonize the Seafowl worker through some other means, such as a supervisor process or an entrypoint script. The critical constraint is that exactly one Seafowl worker must be running per host. Running multiple workers on the same host, or failing to run one at all, will cause problems.

    Note

    Seafowl exposes a Prometheus metrics endpoint on port 9090 by default. If Prometheus is already running on the same host and bound to port 9090, Seafowl will fail to start. To resolve the conflict, edit the value of port in /etc/edb/pgaa/seafowl.toml on the affected host and restart the Seafowl service.

  3. Connect to the coordinator and create the extension on each database where you want to use PGAA:

    CREATE EXTENSION IF NOT EXISTS pgaa CASCADE;

    Using CASCADE ensures that required dependencies, such as the Postgres File System (PGFS), are automatically installed.

Upgrading the extension

To upgrade an existing PGAA installation on WarehousePG to a newer version, complete the steps in this section instead of Installing the extension.

Note

Update the extension before you preload the new PGAA library. If you preload the library first, the PGAA upgrade worker can restart the coordinator while it updates the extension.

  1. On the coordinator, download the new package version:

    export EDB_SUBSCRIPTION_TOKEN=<your-token>
    export EDB_SUBSCRIPTION_PLAN=analytics
    curl -1sSLf "https://downloads.enterprisedb.com/$EDB_SUBSCRIPTION_TOKEN/$EDB_SUBSCRIPTION_PLAN/setup.rpm.sh" | sudo -E bash
    sudo dnf download edb-whpg7-pgaa
  2. Transfer and upgrade the package on all hosts, including the coordinator and every segment host:

    gpssh -f all_hosts -e "scp gpadmin@$(hostname):~/edb-whpg7-pgaa*.rpm /tmp/ && sudo dnf install -y /tmp/edb-whpg7-pgaa*.rpm"
  3. Restart the Seafowl service on all hosts to load the new binary:

    gpssh -f all_hosts -e 'sudo systemctl restart seafowl'
  4. Remove PGAA and PGFS from the list of shared libraries, and restart the cluster. Keep any other libraries in the list.

    gpconfig -c shared_preload_libraries -v '<other_libraries>'
    gpstop -ra

    If pgaa and pgfs are the only libraries in the list, remove the setting instead:

    gpconfig -r shared_preload_libraries
    gpstop -ra
  5. Connect to the coordinator and update the extension in each database where PGAA is installed:

    ALTER EXTENSION pgaa UPDATE;
  6. Confirm that each database has the new version:

    SELECT extversion FROM pg_extension WHERE extname = 'pgaa';
  7. Add PGAA and PGFS back to the list of shared libraries, and restart the cluster:

    gpconfig -c shared_preload_libraries -v '<other_libraries>,pgaa,pgfs'
    gpstop -ra
Note

Upgrade the package on every host, coordinator and segments, and restart Seafowl before running ALTER EXTENSION. If a host still has the older pgaa library, the update fails with an error such as could not find function "..._wrapper" in file ..., because the SQL migration scripts expect functions that only exist in the newer library.

If the coordinator is already restarting repeatedly because the new library was preloaded first, stop the cluster with gpstop -a -M immediate, and remove pgaa and pgfs from shared_preload_libraries in postgresql.conf on the coordinator and every segment host. Then start the cluster and continue at the step that updates the extension.

Verifying the installation

  1. Confirm that the extension is active using the \dx meta-command or by calling the version function:

    SELECT pgaa.pgaa_version();
  2. Verify that the Seafowl service is running on all hosts:

    gpssh -f all_hosts -e 'sudo systemctl status seafowl --no-pager'

Checking service health and logs

Seafowl writes structured JSON logs via systemd.

  • To check the status and recent log output on a single host:

    sudo systemctl status seafowl
  • To stream live logs:

    sudo journalctl -u seafowl -f
  • To check logs across all hosts at once:

    gpssh -f all_hosts -e 'sudo journalctl -u seafowl --no-pager -n 50'

For WarehousePG coordinator and segment logs, see Viewing the database server log files.