Documentation

Connecting to Postgres

First, make sure you've configured your replication. Now, configure the connection:

  1. How do you want to connect to Alooma? If it's via an SSH server (recommended), check out how to connect via SSH. Otherwise, you'll need to whitelist access to Alooma's IP addresses.

    • 52.35.19.31/32

    • 52.88.52.130/32

    • 52.26.47.1/32

    • 52.24.172.83/32

  2. Add and name your PostreSQL input from the Plumbing screen and enter the following details:

    • Hostname or IP address of the PostgreSQL server (default port is 5432)

    • User name and Password

    • Database name

  3. If your PostgreSQL server is behind an SSH server you can connect to PostgreSQL via SSH.

  4. Choose the replication method you'd like to use for PostgreSQL database replication.

    For full dump/load replication:

    • A comma-separated list of the names of the tables you want to replicate.

    • The frequency at which you'd like to replicate your tables. The more frequent, the more fresh your data will be, but the more load it puts on your PostgreSQL database.

    For incremental replication, provide:

    • (for using a timestamp update indicating column) A table/update indicator column pair for each table you want to replicate. For example, "table1" with an update indicator of "updated_at"

    • For xmin - xmin::text::bigint

    • Don't have an update indicator column? Let us know! We can still make incremental load work for you via xmin.

    Note

    If you're using incremental replication for your database, and not using xmin, we recommend — especially for larger databases — that you create an index on the update indicator columns (updated_at in Postgres/MySQL, dtLastModified in SQL Server/Oracle, for example).

    If you do not index the indicator column, your database will:

    • Create a potentially large temp table, which could end up filling the disk.

    • Sort the table at load, which is CPU intensive and will add time to the process.

    Follow the instructions for creating an index here: https://www.postgresql.org/docs/9.5/static/sql-create/docs

    If you are using Postres on Aurora, you MUST create an index.

    For log replication (CDC on RDS or self-hosted):

  5. Enter a schema name/prefix (incremental and full dump/load replication only).

  6. Configure loading of historical data (log replication/CDC only).

Keep the mapping mode to the default of OneClick if you'd like Alooma to automatically map all PostgreSQL tables exactly to your data destination. Otherwise, they'll have to be mapped manually from the Mapper screen.

That's it! You're done integrating PostgreSQL and Alooma.

Search results

    No results found