DROP POSTGRES INSTANCE¶

Syntax¶

DROP POSTGRES INSTANCE [ IF EXISTS ] <name>

Parameters¶

name

Specifies the identifier for the Postgres instance to drop.

If the identifier contains spaces or special characters, the entire string must be enclosed in double quotes. Identifiers enclosed in double quotes are also case-sensitive.

For more information, see Identifier requirements.

Access control requirements¶

A role used to execute this operation must have the following privileges at a minimum:

For instructions on creating a custom role with a specified set of privileges, see Creating custom roles.

For general information about roles and privilege grants for performing SQL actions on securable objects, see Overview of Access Control.

Usage notes¶

• Currently, dropped Postgres instances can’t be recovered; you must recreate them. However, if you have created a fork of the instance, the fork remains independent and unaffected. To make it easier to recreate instances later, you might use DESC POSTGRES INSTANCE to capture the details of each instance before dropping it.

• When this command is issued, Snowflake terminates the Postgres instance and releases the associated compute resources. Billing for compute resources stops after the instance is fully terminated.

• All data stored in the Postgres instance is permanently deleted. Ensure you have backed up any important data before dropping the instance.

• If the instance has high availability enabled, the HA standby is also dropped along with the primary instance.

• If the instance has read replicas, those replicas are also dropped when the primary instance is dropped.

• Forked instances are independent copies. Dropping the source instance doesn’t affect any instances that were forked from it.

• When the IF EXISTS clause is specified and the target object doesn’t exist, the command completes successfully without returning an error.

Examples¶

Drop a Postgres instance:

DROP POSTGRES INSTANCE my_postgres;

Drop a Postgres instance only if it exists:

DROP POSTGRES INSTANCE IF EXISTS my_postgres;

Use the flow operator to find an instance to drop:

-- Find the oldest instance
-- Then use SET and IDENTIFIER() to drop it
SET oldest_instance = (
  SHOW POSTGRES INSTANCES
    ->> SELECT "name"
        FROM $1
        ORDER BY "created_on"
        LIMIT 1
);

DROP POSTGRES INSTANCE IDENTIFIER($oldest_instance);

Find instances below a storage threshold before dropping:

-- Identify small instances
SHOW POSTGRES INSTANCES
  ->> SELECT "name", "storage_size", "created_on"
      FROM $1
      WHERE "storage_size" < 50
      ORDER BY "storage_size";

DROP POSTGRES INSTANCE some_extremely_small_instance;

Check ownership before attempting to drop:

SHOW GRANTS ON POSTGRES INSTANCE my_postgres;

-- Verify that you have OWNERSHIP privilege, then drop
DROP POSTGRES INSTANCE my_postgres;