PostgreSQL

How to set up PostgreSQL Destination

Prerequisites

• The database must be running version 9.3 or higher.

• Whitelist Gravity IP addresses

• Only support direct connection (your database must be publicly accessible.)

Steps

Create a PostgreSQL gravity user

1. If you haven’t already, connect to your PostgreSQL instance using your SQL client.

2. After connecting, run this command to create a user named gravity. Replace with a secure password:

   CREATE USER gravity WITH PASSWORD '<password>';

3. Next, you’ll assign the CREATE permissions to the gravity user. For <database_name>, enter the name of the database where all gravity-replicated data should be loaded. Note: This must be a pre-existing database.

   GRANT CREATE ON DATABASE <database_name> TO gravity;

4. If you restricted access to the system tables, you’ll also need to run the following commands to grant the gravity user SELECT permissions.

   GRANT SELECT ON ALL TABLES IN SCHEMA information_schema TO gravity;
   GRANT SELECT ON ALL TABLES IN SCHEMA pg_catalog TO gravity;

Create PostgreSQL Destination in Gravity

1. Log in to your Gravity and click on Destinations, Choose PostgreSQL

2. Insert Server, Database, and Port details

3. Insert DB user and DB password details

4. Check the Require TLS checkbox. Note: The database must support and allow SSL/TLS connections for this setting to work correctly.

5. Click Save, Test, and Publish

   • Test checks connectivity to PostgreSQL and permission to create schemas and tables on your PostgreSQL database.