PostgreSQL Data Sync
This comprehensive document will walk you through the step-by-step configuration process for PostgreSQL data sync and address specific use cases tailored to PostgreSQL .
Standard Data Sync functionality
Please refer to Data Sync - Getting Started for the standard data sync functionality. The getting started document covers usages of data sync features valid for all integrations.
Configuring PostgreSQL Data Sync
1. Click on "Add new" button
2. Pick the app that you want to sync with PostgreSQL
3. Select the PostgreSQL from the dropdown
4. Select the schema from the dropdown
5. Click on "Next" button
6. Delete any tables that you don't want to sync. If the table you want to sync is not displayed, add it using "Add table" button
7. Click on "Next" button
8. Select the 'Last updated column' from dropdown
9. Click on "Next" button
10. Mark any of the field as unique
11. Click on "Confirm" button
12. Click on "Save" button
13. Click on "Start Sync"
PostgreSQL - Last Updated Column
Timestamp with timezone data type for the last updated column.The "last updated" column is required for Byteline data sync and other sync tools. So, ensure your data sync works seamlessly by setting up the essential "last updated" column. This column is crucial for identifying modified records that need to be synced. You will configure this column when setting up the data sync, as shown in the below screenshot.
Typically, a timestamp column serves this purpose, but PostgreSQL doesn't automatically update it when other row values change. The solution lies in implementing a PostgreSQL trigger.
To add this trigger to your PostgreSQL database, follow the steps below. Make sure to execute these instructions after connecting to the PostgreSQL database terminal:
a. Create the last updated trigger
my_last_updated_column with your "last updated" column name.CREATE FUNCTION update_updated_on_user_task()
RETURNS TRIGGER AS $$
BEGIN
NEW.my_last_updated_column = now();
RETURN NEW;
END;
$$ language 'plpgsql';
b. Associate this trigger with the table
my_table with your table name.CREATE TRIGGER update_user_task_updated_on
BEFORE UPDATE
ON
my_table
FOR EACH ROW
EXECUTE PROCEDURE update_updated_on_user_task();
Syncing Data from localhost Using Ngrok
If your database is hosted locally and you want to sync data to or from it using Byteline, Ngrok can help expose your local database to the internet securely.
Steps to Access a Local Database Using Ngrok
1. Install Ngrok
Download and install Ngrok from https://ngrok.com/download. Follow the installation instructions provided for your operating system.
2. Expose Your Local Database Using Ngrok
Ngrok can expose a TCP connection for your local database. Use the following command to expose your database:
ngrok tcp <port>
Replace <port> with the port your database is running on (e.g., 5432 ).
Example for a PostgreSQL database running on port 5432:
ngrok tcp 5432
Ngrok will generate a public TCP address (e.g., 0.tcp.ngrok.io:12345) that tunnels to your local database.
3. Update Database Configuration in Byteline
In the Byteline data sync configuration, use the Ngrok TCP address as the database host. For example:
Host: 0.tcp.ngrok.io
Port: 12345
Username: Your database username
Password: Your database password
Database Name: The name of your database
4. Test the Connection
Use the "Test Connection" feature in Byteline to ensure the platform can connect to your local database through the Ngrok TCP tunnel.
5. Run the Sync
After successfully testing the connection, initiate the data sync as you normally would. Byteline will access your local database through the Ngrok tunnel.
