Destinations
PostgreSQL
Learn how to configure PostgreSQL as a destination in Artie, including host and port settings, service account creation, schema ownership, and the required database permissions.
Required settings
- Host name
- Port (default is
5432) - Service account
Creating a service account
-- Create a service account
CREATE USER artie WITH PASSWORD 'password';
-- Grant create on database to the service account, so we can handle creating the necessary schemas.
GRANT CREATE ON DATABASE your_database TO artie;
-- If the schema exists, change the owner to the service account.
-- This is because there are certain DDL operations such as "ALTER TABLE" or "DROP TABLE" that is restricted to the owner.
ALTER SCHEMA schema_name OWNER TO artie;Advanced features
Partition mirroring
It requires a PostgreSQL source. Enable Mirror source partitions in your pipeline’s destination advanced settings, then add the partitioned parent table, not its individual partitions.
When it runs
| When | What Artie does |
|---|---|
| Every deploy, including new tables, backfills, and resumed pipelines | Creates the destination parent table and any missing partitions before Artie writes data. If a table that is about to be backfilled can’t be fully created, the deploy fails and the backfill doesn’t start. |
| Daily, 7:00–8:00 AM Pacific Time | Creates destination partitions for source partitions added since the last run. This runs with automatic schema changes, so it requires them to be enabled and only covers running pipelines. When it creates partitions, Artie sends the automatic schema changes email with the created tables and the CREATE TABLE statements it ran. |
Partitions added to the source are mirrored at most once a day, so create them at least a day before rows arrive, for example with pg_partman’s premake.
Key behaviors
- Supports
RANGE,LIST, andHASHpartitions,DEFAULTpartitions, and sub-partitions. - Only runs
CREATE TABLE IF NOT EXISTS, one table per transaction, with a 5-second lock timeout. A partition that times out waiting for a lock is created on the next run. - Never detaches, drops, renames, truncates, or moves rows. If you detach, split, merge, or rename source partitions, update the destination manually.
- If a backfill is set to drop the destination table first, Artie truncates it instead, so the partitions stay in place.
- Doesn’t convert an existing unpartitioned destination table. The deploy fails if that table is about to be backfilled.
What happens if a row arrives before its partition exists?
Without a DEFAULT partition, the write fails and Artie retries it until the partition is created. With a DEFAULT partition, the row lands in the default, and the new partition can’t be created until you move those rows out of the default partition.
Which tables can't be mirrored?
A partition replicated on its own instead of through its parent, foreign-table partitions, tables unified across schemas, table or partition names longer than 63 bytes, and partition key columns with uppercase letters or special characters, because Artie lowercases destination column names.