PostgreSQL Walkthrough
  • 5 Minutes to read
  • Dark
    Light
  • PDF

PostgreSQL Walkthrough

  • Dark
    Light
  • PDF

This guide will walk you through the steps needed to integrate data from a PostgreSQL database (on-prem or managed service) into a cloud target using Rivery.

Note

This connector can also be used for AWS Aurora (with PostgreSQL compatibility).

The following topics will be covered:

Configure PostgreSQL For Rivery

Note

The following connection instructions will allow Rivery to connect to PostgreSQL for standard extraction. For log-based extraction, follow the configuration guidelines here.

Create a user for Rivery

In order to extract data, it's recommended to create a new user in the database for Rivery:

CREATE USER < myUsername > WITH ENCRYPTED PASSWORD '< password >';

Now, grant the new user you've just created for Rivery to the table or schema you want to extract data from. Use the commands below, and change the <database>, <schema> and <table> structures with your existing schema and table names.

GRANT CONNECT ON DATABASE <database> TO <myUsername>;

GRANT USAGE ON SCHEMA <schema> TO <myUsername>;

To grant SELECT operation to all of the tables in the database, use:

GRANT SELECT ON ALL TABLES IN SCHEMA <schema> TO <myUsername>;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> GRANT SELECT ON TABLES TO <myUsername>;

Pull PostgreSQL Data into a Target

Using Rivery, you can pull data from your PostgreSQL tables and send that data into your target database.

First select 'Create New River' from the top right of the Rivery screen.

image

Choose 'Data Source to Target' as your river type.

In the 'General Info' tab, name your river and give it a description. Next, navigate to the 'Source' tab.

Find PostgreSQL in the list of data sources and select it:

image

Define a Source Connection (this will be the connection created earlier in the process). If you do not yet have a PostgreSQL connection in your Rivery account, you can create a new connection here by clicking 'Create New Connection.'

image

Next, choose your River mode.

image

  • Multi-Tables : Load multiple tables simultaneously from PostgreSQL to your target.
  • Custom Query : Create a custom query and load it into your target.
  • Union Tables : Merge several tables with the same metadata into a single table.
  • Legacy River: Choose a single source table to load into a single target.

Database Migration

For a detailed walk through of Multi-Tables mode, please refer to the Rivery Database Migration docs.

Pulling Data Using a Custom Query

You may also use the Custom Query in order to define a query data pull from Rivery. You may use any query that works in the source, using a specific SELECT query without any other statements. Rivery isn’t compatible using multi-statements or SQL Script in the custom query field.

image

Extract Method

Using Rivery, you can pull your data incrementally or pull the entirety of the data that exists in the table:

image

  • All: Fetch all data in the table using Chunks.

  • Incremental: Gives you the option to run over a column in a table or your custom query top SELECT. You can run over it by filtering Start and End dates or Epoch times

    Moreover, you may choose to run over the date using Daily, Monthly, Weekly, or Yearly chunks.

    Please define the incremental Field to be used in the Incremental Field section. After choosing the incremental field, choose the Incremental Type and the dates/values you would like to fetch.

    Note: Rivery will manage the increments over the runs using the Maximum value in the data. This means you will always get the entire data since the last run, which prevents data holes. You just need to configure your river once.

    Recommended : Define your incremental field in Rivery over a field with an Index or Partitions key in the table.

    image

Please Note:
The Start Date won't be advanced if a River run is unsuccessful.
If you don't want this default setting, click More Options and check the box to advance the start date even if the River run is unsuccessful (Not recommended).
image.png

Limit and Auto Mapping

After defining the extract method, you may choose a limit of top N rows to fetch. Rivery will set your Schema using the Auto Mapping feature. You can also choose fields you want to fetch in the Mapping table and add fields on your own.

image

Union Tables Mode

For like-named tables with identical schemas, the Union Tables river mode can be used to ingest these tables in a single process. Simply enter the table prefix using '*' as a wildcard and then

image

You can click 'Search Tables' below to return a list of tables in the database.

image

Next, choose your extraction method and map the columns by clicking 'Auto Mapping.' Note - the column names and data types are expected to be identical in all tables that are selected to load through the Union Tables mode.

image

Legacy River Mode

This river mode allows for the load of a single source table into a single target table.

image

In the above screenshot, there are steps to define the source table to pull (Rivery will auto-detect available schemas and tables), the extraction method to use, and options for filters or row limits on the data pull.

Multi-Table Mode

This mode allows you to load multiple tables from SQL Server to your target at the same time. There are two Extraction Modes by default: Extraction Standard and Log-Based.

After selecting a Schema and a Source Table, the Table Setting Tab will be displayed.

By clicking the 'Table Settings' tab, you can change the following:

  • Filter by Expression

  • Change the Extraction Method
    If you choose 'Incremental,' you can specify which field will be used to define the increment.

  • Select a Timestamp Foramt
    The SQL standard distinguishes between timestamps with and without time zone by using "+" and "-" literals.
    If you're working with records that have a Timestamp and a Timezone Foramt, you can choose whether or not to filter them out.

    image.png

  • Edit the Table Name

  • Change the Loading Mode

image.png

Analyze Table in Load

Analyze Table in Load collects statistics about the contents of tables in the database, and stores the results in the 'pg_statistic' system catalog. Subsequently, the query planner uses these statistics to help determine the most efficient execution plans for queries.
Consult the Postgres documentation for additional details.

Schedule The River

Once the creation of the river is complete, navigate to the 'Schedule' tab and click 'Schedule Me.'

image

Choose the frequency at which to schedule the river.

image

To notify certain users about a river failure or warning, enable notifications below:

image

You can edit your {Mail_Alert_Group} in the Variables page (find this in the left-hand pane of the browser).

Monitor The River

During the river run, or after the run has complete, you can monitor the river in its 'Activities' tab.

image

In this tab you can monitor each the status of the current river run. For the Multi-Table mode, you can monitor at the table-level (see above).

By toggling between 'Run View' and 'Target View' you can see the river results grouped either by time of run or by target location.

Check out the Targets section to find out how to load the data to your target data warehouse.


Was this article helpful?