Configure a Postgres internal storage integration for Snowflake Postgres¶

A Snowflake Postgres internal storage integration allows you to read from and write to the managed storage associated with a Snowflake Postgres instance. This feature enables seamless data movement between Snowflake and your Postgres instances using an external stage. For the full SQL syntax, see CREATE STORAGE INTEGRATION (Postgres Internal Storage).

Postgres instances in Snowflake come with managed storage allocated by the Postgres instance. While users can already access this storage to read files through Iceberg tables using a Snowflake Postgres catalog integration, a storage integration provides a path to write data from Snowflake to the Postgres managed bucket.

Note

Postgres internal storage integrations are currently supported on AWS and Azure only.

Prerequisites¶

A Snowflake account with the Snowflake Postgres feature enabled.
A Postgres instance in READY state. To create a Postgres instance, see CREATE POSTGRES INSTANCE.
The role that creates the storage integration must have the following privileges:
- OWNERSHIP on the Postgres instance.
- CREATE INTEGRATION on the account.

Considerations¶

Consider the following when you use a Postgres internal storage integration:

Cloud provider support: This feature currently supports AWS and Azure only.
Stage restrictions: When creating a stage (see CREATE STAGE) that uses a Postgres internal storage integration, the URL, ENCRYPTION, and DIRECTORY stage properties are disallowed.
Path configuration: The integration automatically appends a /files subpath to the base storage location retrieved from the Postgres instance.

Create a storage integration for Postgres internal storage¶

To create a storage integration for Postgres internal storage, use the CREATE STORAGE INTEGRATION (Postgres Internal Storage) command with TYPE = POSTGRES_INTERNAL_STORAGE.

For example:

CREATE STORAGE INTEGRATION postgres_internal
  TYPE = POSTGRES_INTERNAL_STORAGE
  POSTGRES_INSTANCE = 'my_pg_instance';

Where:

TYPE = POSTGRES_INTERNAL_STORAGE: Specifies the integration type for Postgres managed storage. Required.
POSTGRES_INSTANCE = '<instance_name>': Specifies the name of the Snowflake Postgres instance. Required.

For the full syntax and parameter reference, see CREATE STORAGE INTEGRATION (Postgres Internal Storage).

Create a stage for Postgres storage¶

After creating the integration, you can create an external stage to interact with the storage. Use the RELATIVE_URL property to specify a user-chosen path prefix under the Postgres managed storage that this stage should reference. This value is an arbitrary path that you define to organize files within the managed storage location.

For example:

CREATE STAGE my_pg_stage
  STORAGE_INTEGRATION = postgres_internal
  RELATIVE_URL = '/test_parquet';

Move data between Snowflake and Postgres¶

You can use the COPY FILES command to move data between Snowflake stages and your Postgres managed storage. These examples assume you have already created a separate Snowflake stage in addition to the Postgres stage created above.

Snowflake to Postgres (write)¶

To move data from an existing Snowflake stage into the Postgres managed storage:

COPY FILES
  INTO @<postgres_stage>
  FROM @<source_stage>;

Where <source_stage> refers to a pre-existing Snowflake stage that contains the files you want to move, and <postgres_stage> is the Postgres stage created in the previous step.

On the Postgres side, you can then load the data:

COPY test FROM '@STAGE/test_parquet/data*.parquet';

Where @STAGE is a preconfigured placeholder for the stage location on the Postgres side.

Postgres to Snowflake (read)¶

To move data written by Postgres back into an existing Snowflake stage:

COPY FILES
  INTO @<destination_stage>
  FROM @<postgres_stage>;

Where <destination_stage> refers to a pre-existing Snowflake stage where you want to copy the files, and <postgres_stage> is the Postgres stage created earlier.

Access control¶

The standard Snowflake storage integration privilege model applies:

Ownership: The role that creates the integration must own the associated Postgres instance.
Usage: To use the integration to create a stage, a role must have the USAGE privilege on the integration object.
Stage privileges: Users need standard privileges (for example, READ, WRITE) on the stage itself to perform data movement operations.

For example, to grant another role the ability to create stages using the storage integration:

GRANT USAGE ON INTEGRATION postgres_internal TO ROLE data_engineer_role;