> ## Documentation Index
> Fetch the complete documentation index at: https://xata.io/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# AWS

This guide explains how to use **pgstream** with **AWS RDS/Aurora databases**, covering **snapshots** and **replication**.

👉 Throughout this guide:

* **`pgstreamsource`** refers to the user provided in the **pgstream source URL**.
* **`pgstreamtarget`** refers to the user provided in the **pgstream target URL**.

## Snapshots

### From AWS RDS/Aurora Database (Snapshots)

#### Quick Checklist

* [ ] Create a source user (**`pgstreamsource`**) with access to required schemas/tables.
* [ ] For roles without passwords → no special config needed.
* [ ] Snapshot of roles with passwords is **not supported**.
* [ ] Update YAML config with correct snapshot settings.

#### Steps

1. **User privileges**
   Ensure the **`pgstreamsource`** user (from the pgstream source URL) can access the database schema and tables you need.

2. **Roles handling**

   * **No roles** → no changes required.
   * **Roles without passwords** → no changes required.
   * **Roles with passwords** → **not supported by AWS**.

     If you try to snapshot roles with passwords, you'll see:

     ```
     pg_dumpall: error: query failed: ERROR:  permission denied for table pg_authid
     ```

3. **Config when not snapshotting roles**
   If roles are disabled or not manually managed, add the following to avoid failures:

   ```yaml theme={null}
   snapshot:
     schema:
       pgdump_pgrestore:
         roles_snapshot_mode: "disabled"
         no_owner: true
         no_privileges: true
   ```

   Example full configuration:

   ```yaml theme={null}
   source:
     postgres:
       url: "postgresql://pgstreamsource:password@:5432/db?sslmode=require"
       mode: snapshot
       snapshot:
         mode: full # schema + data
         tables: ["public.*"] # all tables in the public schema
         schema:
           mode: pgdump_pgrestore
           pgdump_pgrestore:
             roles_snapshot_mode: "disabled"
             no_owner: true
             no_privileges: true
   ```

ℹ️ AWS RDS/Aurora-managed roles (`rdsadmin`, `rds_reserved`, `rds_extension`, `rds_ad`, `rds_password`, `rds_iam`, `rds_replication`, `rds_superuser` and `postgres`) will not be snapshotted.

### To AWS RDS/Aurora Database (Snapshots)

#### Quick Checklist

* [ ] Create a target user (**`pgstreamtarget`**) for the pgstream target URL.
* [ ] Grant privileges for schema/database ownership.
* [ ] Grant optional privileges depending on features (create DB, create roles).
* [ ] **Use `disable_triggers: false`** (required for all non-`rds_superuser` users).

#### Steps

The **`pgstreamtarget`** user (from the pgstream target URL) must have the following privileges:

* **Schema ownership**

  ```sql theme={null}
  ALTER DATABASE db OWNER TO pgstreamtarget;
  ALTER SCHEMA public OWNER TO pgstreamtarget;
  ```

* **Database creation** (if `create_target_db` is enabled)

  ```sql theme={null}
  ALTER ROLE pgstreamtarget CREATEDB;
  ```

* **Role creation** (if `roles_snapshot_mode` is `enabled`/`no_passwords`)

  ```sql theme={null}
  ALTER ROLE pgstreamtarget CREATEROLE;
  ```

  ⚠️ `pgstreamtarget` must already hold any privileges it assigns (e.g., `REPLICATION`).

* **Disable triggers** (required for AWS RDS/Aurora)

  ⚠️ **AWS RDS/Aurora only allows setting `session_replication_role`** for `rds_superuser` roles. You **must** disable trigger management in pgstream if using a non superuser role:

  ```yaml theme={null}
  target:
    postgres:
      disable_triggers: false # Required for AWS RDS/Aurora target databases when using non superuser roles
  ```

  ❌ If using `disable_triggers: true` and a role without enough privileges, pgstream will fail with:

  ```
  permission denied to set parameter "session_replication_role"
  ```

## Replication

### From AWS RDS/Aurora Database (Replication)

#### Quick Checklist

* [ ] Ensure logical replication is enabled (`rds.logical_replication=1` in parameter group).
* [ ] **Use the `postgres` user or a user with `rds_superuser`** for initialization.
* [ ] Grant `rds_replication` role for replication privileges.
* [ ] Optionally use a different **`pgstreamsource`** user for streaming.

#### Steps

1. **Enable logical replication**

   Logical replication requires `wal_level` to be set to `logical`.

   **Check current setting:**

   ```sql theme={null}
   SHOW wal_level;
   ```

   **If not set to `logical`, update it:**

   AWS RDS and Aurora require modifying the database parameter group:

   1. Go to the [AWS RDS Console](https://console.aws.amazon.com/rds/)
   2. Navigate to **Parameter groups** in the left sidebar
   3. Create a new parameter group or select your existing custom parameter group
      * **Note:** You cannot modify the default parameter groups
   4. Search for and modify the `rds.logical_replication` parameter:
      * Set `rds.logical_replication` to `1` (enabled)
   5. Apply the parameter group to your RDS/Aurora instance:
      * Go to your database instance
      * Click **Modify**
      * Under **Database options**, select your custom parameter group
      * Apply the changes
   6. **Reboot your database instance** for the changes to take effect

   **Verification:**

   After the reboot, verify the setting:

   ```sql theme={null}
   SHOW wal_level;  -- Should return 'logical'
   SHOW rds.logical_replication;  -- Should return 'on' or '1'
   ```

   ⚠️ **Important notes:**

   * Setting `rds.logical_replication=1` automatically sets `wal_level=logical`
   * This change **requires a database reboot**
   * Logical replication may increase storage usage due to retained WAL files
   * Ensure you have adequate monitoring for replication slot lag

2. **Replication phases**

   * **Initialization** → requires elevated privileges to create schemas, event triggers, and replication slots.
   * **Streaming** → can use a dedicated `pgstreamsource` user.

#### Initialization

Initialization does the following:

* Creates `pgstream` schema
* Creates replication slot (if missing)
* Creates event triggers/functions for schema changes

⚠️ **Use a user with `rds_superuser` privileges** (typically the `postgres` user) for initialization, as creating event triggers requires elevated privileges:

```yaml theme={null}
source:
  postgres:
    url: "postgresql://postgres:password@:5432/db?sslmode=require"
    mode: replication
```

**Required privileges for initialization user:**

```sql theme={null}
-- The postgres user typically has these by default
-- If using a different user, grant:
GRANT rds_superuser TO pgstreamsource;
GRANT rds_replication TO pgstreamsource;
```

#### Streaming

After initialization, you can:

* Keep using `postgres`, or
* Transfer ownership to a dedicated **`pgstreamsource`** role.

```sql theme={null}
-- Allow ownership transfer
GRANT pgstreamsource TO current_user;

-- Transfer ownership
ALTER DATABASE db OWNER TO pgstreamsource;
ALTER SCHEMA pgstream OWNER TO pgstreamsource;
ALTER SCHEMA <schema> OWNER TO pgstreamsource;
ALTER TABLE pgstream.schema_log OWNER TO pgstreamsource;
ALTER TABLE pgstream.table_ids OWNER TO pgstreamsource;
ALTER SEQUENCE pgstream.xid_serial OWNER TO pgstreamsource;

-- Replication privileges (AWS RDS/Aurora specific)
GRANT rds_replication TO pgstreamsource;
```

### To AWS RDS/Aurora Database (Replication)

#### Quick Checklist

* [ ] Ensure **`pgstreamtarget`** has schema/database ownership.
* [ ] If combined with snapshot → follow [Snapshot target requirements](#to-aws-rdsaurora-database-snapshots).

#### Steps

Privileges required:

```sql theme={null}
ALTER DATABASE db OWNER TO pgstreamtarget;
ALTER SCHEMA <schema> OWNER TO pgstreamtarget;
```

## Troubleshooting

<AccordionGroup>
  <Accordion title="permission denied for table pg_authid">
    **Cause**: Roles with passwords require pg\_authid access.\
    **Fix**: AWS RDS doesn't allow access to `pg_authid`. Disable role passwords (`roles_snapshot_mode: disabled` or `no_passwords`).
  </Accordion>

  <Accordion title="permission denied to set parameter &#x22;session_replication_role&#x22;">
    **Cause**: AWS RDS only allows setting this parameter when using an `rds_superuser` role.\
    **Fix**: Set `disable_triggers: false` in target config or use a role with enough privileges.
  </Accordion>

  <Accordion title="permission denied for schema public when transferring ownership">
    **Cause**: Target role lacks CREATE privilege on schema.\
    **Fix**: Grant CREATE on schema to the role: `GRANT CREATE ON SCHEMA public TO role_name;`
  </Accordion>

  <Accordion title="Role creation fails">
    **Cause**: `pgstreamtarget` lacks privileges it tries to assign.\
    **Fix**: Grant required privileges to `pgstreamtarget` first, or use a user with more privileges.
  </Accordion>

  <Accordion title="must be superuser to create event triggers">
    **Cause**: Initialization user lacks sufficient privileges.\
    **Fix**: Use the `postgres` user or grant `rds_superuser`: `GRANT rds_superuser TO username;`
  </Accordion>

  <Accordion title="cannot set parameter &#x22;rds.logical_replication&#x22;">
    **Cause**: Parameter can only be set in parameter group.\
    **Fix**: Modify the parameter group via AWS Console, not via SQL.
  </Accordion>
</AccordionGroup>