Postgres

Introduction

PostgreSQL (Postgres) is a powerful, open-source, object-relational database management system that uses and extends the SQL language. It is known for its stability, reliability, and advanced features such as concurrency control, transaction management, and support for various programming languages. Postgres is the backbone database of many backend production services.

Your can now keep data in sync with all your other CRMs and databases systems with Stacksync!

Check out our Postgres guides to get started!

Things to keep in mind

During setup

• All Postgres tables must have a (single) primary key We use the primary key to keep your data in sync. The primary key must be a single column, composite primary keys are not valid. Check our Postgres snippets collection for additional hints.

• Primary keys must be auto-generated Make sure your primary keys are generated by your database (i.e. have a default value set).

• Double-check that the Postgres user used by Stacksync to connect to your database is the owner (or has the same role as the owner) of the tables you want to map.

If a table you see in your database does not appear in Stacksync, one of the previous conditions is not met. Check our Postgres snippets collection for additional hints.

You must have replication rights to connect your Postgres instance. If you get the following error:

The user does not have the right permissions for replication. Please check the documentation for more information.

Please use the "Postgres Heroku" connector instead of the "Postgres" Connector. They are the same but the "Postgres Heroku" connector does not require replication right in trade for some negligible database performance. So you can use the "Postgres Heroku" connector with your current database even if it is not provided by Heroku as long as you get the error message displayed above.

How to enable Logical Replication? (If not already enabled)

To use logical replication for change data capture the wal_level value must be set to logical. To change the value of this parameter, modifications need to be made to the postgresql.conf file.

1. Open the postgresql.conf file. On Linux, this file can be found in the following directory:

   /etc/postgresql/version/main

2. Find the wal_level variable under WRITE-AHEAD LOG settings.

3. Uncomment it if its commented and set its value to logical: wal_level = logical

4. Restart the PostgreSQL service. On linux it can be restarted with the following commands: sudo service postgresql stop

   sudo service postgresql start

5. Use the following SQL query to ensure logical replication has been enabled: SHOW wal_level; The value displayed should be logical.

6. After enabling, in Stacksync, use Postgres option instead of Postgres Heroku option while creating sync.

After setup

Renaming schema, tables, or columns will break Stacksync configuration. No worries, we get you covered! Please refer to our Update Sync configuration guide.