Datastream failed to find the publication

[Renee_Cooper]

We're having a frustrating problem with Datastream.   

1. We set up a user in our Postgres (CloudSQL) db with all the settings as listed in the Datastream documentation
2. We set up a publication from the database we wanted, with 7 of the tables marked for streaming
3. We have a connection profile set up with a proxy set (that we know is working) to connect to our Postgres db
4. We have a destination connection profile set up as well to BQ
5. We use the connection profiles to set up the stream for the 7 tables

Some of them backfill but not all.  We are getting the "Datastream failed to find the publication" error even though the publication exists, the proxy is working (for other streams), and some of the tables are syncing.  

The Validation step is all green, no errors.

Trying to figure out what the issue might be and how to correct it.  These tables are really large with nested JSON columns (that don't exceed the nesting limit though); we really don't want to have to export to CSV from Postgres and try to load those CSVs into BQ.  (tried and failed already)

[ms4446]

Here are some things you can try to troubleshoot the issue you are having with Datastream:

1. Check the Datastream Logs: Navigate to the "Logs Explorer" in the Google Cloud Console and filter by the Datastream resource type to gain insights into any potential errors or warnings.

2. Verify Publication Configuration: Ensure that the publication is correctly set up by executing the following SQL query on your PostgreSQL instance:

SELECT * FROM pg_publication_tables WHERE pubname = '<publication_name>';

    3. Check Replication Slot Status: Verify the status of the replication slot with:

SELECT * FROM pg_replication_slots WHERE slot_name = '<replication_slot_name>';

Note: Not all PostgreSQL configurations use replication slots, especially if logical replication isn't enabled. If you don't find a replication slot, your setup might not be utilizing one.

4. Service Account Permissions: Confirm that the Datastream service account possesses the required database-level permissions for reading data and the necessary BigQuery permissions for writing data.

5. Adjust Stream Settings:

   • Consider enhancing the stream bandwidth to potentially improve performance.
   • Reevaluate the stream type. If you're using the CDC stream type, consider switching to the Full load stream type. The Full load method performs a comprehensive load of the tables you aim to stream, which might be more efficient for extensive tables compared to the CDC stream type.

Additional Tips:

• Ensure the Datastream service account has the appropriate permissions to access both the tables and the JSON columns you intend to stream.
• For large tables with nested JSON columns, increasing the Datastream stream bandwidth might enhance the stream's performance.
• Depending on your specific needs and the nature of your data, experimenting with different Datastream stream types might yield better results.

[Renee_Cooper]

The problem turned out to be order-of-events.  The publication has to be created before the replication slot or else it will not find the publication once it does exist.
Probably not normally an issue, but if you are experimenting trying to learn how to set these up, it's easy to get the ordering mixed up when you are creating/destroying things.