How do I update or remove extensions in PostgreSQL with Pulumi?

In this guide, we will demonstrate how to manage PostgreSQL extensions using Pulumi. Specifically, we will cover how to update an existing extension and how to remove an extension from a PostgreSQL database. This process is essential for maintaining the functionality and performance of your database.

Steps to Update or Remove PostgreSQL Extensions

1. Import the necessary Pulumi packages: We will use the postgresql package to manage PostgreSQL resources.
2. Define the PostgreSQL extension resource: We’ll create a resource for the PostgreSQL extension and specify its properties.
3. Update the extension: Modify the properties of the extension to update it.
4. Remove the extension: Use the dropCascade property to remove the extension if needed.

Below is a TypeScript program that demonstrates these steps:

import * as pulumi from "@pulumi/pulumi";
import * as postgresql from "@pulumi/postgresql";

// Define the PostgreSQL provider
const pgProvider = new postgresql.Provider("pgProvider", {
    host: "your-postgresql-host",
    port: 5432,
    username: "your-username",
    password: "your-password",
    database: "your-database",
});

// Define the PostgreSQL extension resource
const myExtension = new postgresql.Extension("myExtension", {
    name: "pg_stat_statements",
    schema: "public",
    version: "1.7", // Specify the version to update the extension
    database: "your-database",
}, { provider: pgProvider });

// To remove the extension, you can set the dropCascade property to true
const removeExtension = new postgresql.Extension("removeExtension", {
    name: "pg_stat_statements",
    schema: "public",
    version: "1.7", // This property is required but will be ignored when removing
    database: "your-database",
    dropCascade: true, // This will remove the extension
}, { provider: pgProvider });

Key Points

• Provider Configuration: The postgresql.Provider is configured with the connection details for your PostgreSQL database.
• Extension Resource: The postgresql.Extension resource is used to manage the PostgreSQL extension. You can specify the name, schema, version, and database properties.
• Updating Extensions: To update an extension, modify the version property.
• Removing Extensions: Use the dropCascade property to remove the extension.

Summary

In this guide, we demonstrated how to manage PostgreSQL extensions using Pulumi. We covered how to update an extension by specifying a new version and how to remove an extension using the dropCascade property. This approach allows you to manage your PostgreSQL extensions declaratively using infrastructure as code.