[Request for Beta Testers] Destinations V2 for postgres is open for early access!

[gisripa]

Early Access Available!

Hi Airbyte community,

Early access for Destinations V2 for Postgres is now available, bringing you:

• One-to-one mapping: Data from one stream (endpoint or table) will now create one table in the destination - gone are the days where you will need to join multiple tables to learn about your data.
• Improved error handling: Typing errors will no longer fail your sync, and are now easily visible in a new _airbyte_meta column, allowing for better tracking of inconsistencies and resolution of issues.
• Incremental data delivery in final tables: Data will be incrementally delivered in final tables for all initial snapshots. No more waiting hours to see the first rows in your destination table.

How to Enroll

We are actively seeking and iterating on feedback to refine these features prior to general availability. Here's how you can try out the beta:

1. [For Airbyte Self-Managed only] In your Workspace Settings, upgrade destination Postgres to the early access version, 0.6.0. (Airbyte Cloud will automatically pick up this update.)
   

2. Create a new Postgres destination, and enable the Destinations V2 option. Keep in mind that the early access version is NOT suitable for production and we may ask you to reset your data.
   

3. Create connections sending data to the new Postgres destination. Please use a different namespace for these connections to avoid overwriting/modifying any production data..

4. When creating syncs with your new Postgres destination, be sure to choose Raw data (JSON) as your Normalization Method. In the V2 version of the Postgres destination this option will be removed, but in this "hybrid" version of the destination, this option remains for the V1 versions of the sync. You will still be getting final tables with V2 syncs, but the mechanism we will use to generate them is no longer dbt normalization - it's built into the connector.
   

Request for Feedback

As you explore the new functionality, we ask that you pay special attention to table and column name changes, missing or incorrect column types, and the speed and cost of your syncs.

We look forward to hearing about your experience with Postgres Destinations V2. Please don't hesitate to share any feedback or questions in the comments below. You can also join us on Slack if you would like to chat directly with the team about this feature.

[Darian-Morrison]

Running into an issue with a Postgres to Postgres connection using the new Destinations V2:

Context

• Attempting to replicate our applications production database (source) to a replica for analytics database (destination).
• Source database DDL was created using Quoted identifiers for table and column names. All table names are in PascalCase and colum names are in camelCase.
• When using Postgres V1 Destination, all my identifiers are down-cased and the sync works
• This problem was encountered while setting up a fresh connection with an empty destination
• My requirements require that my destination Identifiers match source identifiers (I need to perserve the casing) I know this probably not possible given the documentation but I still tried to hack it to make it work (Aside note - It would be very helpful for me if using Quoted Identifiers was an option)

Problem

• When using Postgres V2 Destination, the destination tables are created using the source database casing, the raw tables are created and then the sync job fails to transfer the data with a bunch of errors in the logs like follows: java.lang.RuntimeException: org.postgresql.util.PSQLException: ERROR: relation "public.public_raw__stream_Job" does not exist Position: 2578 ("public.Job" is table from my source. The raw table that was successfully created in my destination is named "public.public_raw__stream_job" not ""public.public_raw__stream_Job"

Solutions

• Destination V2 probably needs to follow the naming conventions in the docs. For my personal needs, I would prefer that the source schema is replicated exactly as is and that raw stream was named using a Quoted Idenfier if needed to preserve casing

I can provide a full log of the sync error or any additonal information on request

[gisripa]

Tracked here

[dbro]

Thanks for fixing this, I had a simpler version of the same problem: my stream names had some upper case letters which generated an errors as above ( 'ERROR: relation "airbyte_internal._____" does not exist' ). The fix for me was to change all stream names to be all lower case.

The link to the tracking issue does not work for me right now, 404 error.

[gisripa]

@dbro I updated the link

[gisripa]

Fixed and released in 0.6.1. Really appreciate the feedback and do let us know if you see any behavioral anomalies in the connector.

[gisripa]

This was temporarily reverted in the cloud for an unrelated issue, I'll soon publish a 0.6.2 version which should have the fix.

[benjamin-steurer]

@gisripa
I can't get the normalization to work with the Amazon Ads Source.

I've tried SQL Server and the E2E source and the normalization worked just fine.
But when I use Amazon Ads as a source I just get a "metric" column no matter which Sync Mode I use. Postgres V is 0.6.2.

Airbyte V 0.50.47

[gisripa]

@Blochergroup Normalization option using dbt is deprecated and will be removed in later versions. Please try the same with Use Destinations V2 option and deselecting normalization from Transformations tab.

[benjamin-steurer]

@gisripa I got it working with every source (E2E Testing for example) but the source Amazon ads with the exact same setup just doesn't work.

Normalization is indeed turned off. Use destinations V2 is enabled.
So it seems like Amazon Ads is the specific Problem

[benjamin-steurer]

Using the E2E test it source it works

But with Amazon Ads as source its just json:

[gisripa]

📣 ✨ Postgres Destinations V2 is now GA! The beta program has ended and the latest version is now 2.0.0, a breaking change.
All existing Postgres connections should have an upgrade banner with a breaking change deadline of May 31, 2024.
Thank you all for the valuable feedback.

[benjamin-steurer]

@gisripa I updated to the latest version but still have the Issue that I have a "metric" column which isn't normalized.

[gisripa]

@Blochergroup We only normalize the top level columns mentioned in your json schema. If you the source has nested json structures, we map them as Struct airbyte type and we persist them in jsonb column type in postgres.