Skip to main content

PostgreSQL

Preparation

To allow Shaped to connect to your Postgres database, you need to create a read-only user and pass the details into a Create Dataset request. You can create a read-only user on your Postgres DB with the following commands:

# 1. Create a new user. 
CREATE USER read_only_user WITH PASSWORD 'secure_password1!';

# 2. Grant the user connection to the database.
GRANT CONNECT ON DATABASE database_name TO read_only_user;

# 3. Grant the user usage of the schema.
GRANT USAGE ON SCHEMA public TO read_only_user;

# 4. Grant the user read access to all the tables in the schema. Note you can also
# restrict this to your specific user, item and interaction views.
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only_user;

# 5. Grant the group access to future tables in the schema.
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO read_only_user;

Dataset Configuration

Below are the fields required for the Postgres dataset connector:

FieldExampleDescription
schema_typePOSTGRESSpecifies the connector schema type, in this case "Postgres".
config.tablemoviesThe name of the table to sync.
config.useryour_userAccess account username.
config.passwordpAssw0rd1!Access account Password.
config.hostmy-postgres-db.xxxxxxx.us-east-2.rds.amazonaws.comDatabase hostname.
config.port5432Database port (the default for Postgres is 5432).
config.databasemovielensThe name of the database that contains table to sync.
config.database_schemapublicOptional. The name of the schema that contains table to sync.
config.replication_keyupdated_atThe name of the column that contains a datetime key or ascending id for ordering data during incremental syncs.
config.columns["productId", "color", "brand", "stockLevel"]Optional, the name of the columns you wish to sync from Postgres into Shaped. If not specified, all columns will be synced.

Dataset Creation Example

Below is an example of a Postgres dataset connector configuration:

dataset_name: your_postgres_dataset
schema_type: POSTGRES
config:
table: movies
user: your_user
password: pAssw0rd1!
host: my-postgres-db.xxxxxxx.us-east-2.rds.amazonaws.com
port: 5432
database: movielens
replication_key: updated_at

The following payload will create a Postgres dataset and begin syncing data from Shaped using the Shaped CLI.

shaped create-dataset --file dataset.yaml