1. Run Steampipe as a Service

You can run Steampipe in service mode to expose it as a database endpoint. This allows you to connect to it from any Postgres-compatible client.

steampipe service start

2. Schedule or Trigger Data Pulls

You can use cron jobs or AWS Lambda functions to schedule or trigger Steampipe queries. Here’s an example using a cron job:

a. Create a Script to Run Steampipe Queries

Create a script (steampipe_query.sh) to run your Steampipe queries and store the results in the local Steampipe PostgreSQL database.

#!/bin/bash
# Run Steampipe query and store results in local database
steampipe query "create table if not exists aws_ec2_inventory as select * from aws_ec2_instance;"
steampipe query "insert into aws_ec2_inventory select * from aws_ec2_instance on conflict do nothing;"

b. Set Up a Cron Job

Set up a cron job to run the script at regular intervals.

crontab -e
# Add the following line to run the script every hour
0 * * * * /path/to/steampipe_query.sh

3. Replicate Data to Aurora PostgreSQL

As discussed earlier, you can set up logical replication from the Steampipe PostgreSQL instance to Aurora PostgreSQL. Here’s a recap of the steps:

a. Enable Logical Replication on Steampipe PostgreSQL

Edit the postgresql.conf file in your Steampipe PostgreSQL instance to enable logical replication:

wal_level = logical
max_replication_slots = 4
max_wal_senders = 4

Restart the PostgreSQL service to apply these changes.

b. Create a Replication Slot

Create a replication slot on the Steampipe PostgreSQL instance:

SELECT * FROM pg_create_logical_replication_slot('steampipe_slot', 'pgoutput');

c. Create a Publication on Steampipe PostgreSQL

Create a publication on the Steampipe PostgreSQL instance for the tables you want to replicate:

CREATE PUBLICATION steampipe_pub FOR TABLE aws_ec2_inventory;

d. Create a Subscription on Aurora PostgreSQL

Create a subscription on the Aurora PostgreSQL instance to pull data from the Steampipe PostgreSQL instance:

CREATE SUBSCRIPTION steampipe_sub
CONNECTION 'host=steampipe_host port=5432 user=replication_user password=your_password'
PUBLICATION steampipe_pub;

4. Monitor and Maintain Replication

Monitor the replication process to ensure it is running smoothly. Use PostgreSQL’s built-in tools and views to check the status of replication slots, subscriptions, and publications.

Example Script for Data Transfer

Here’s a simplified example script to set up the replication:

#!/bin/bash
# Enable Logical replication on Steampipe PostgreSQL
echo "wal_level = Logical" >> /path/to/steampipe/postgresql.conf
echo "max_replication_slots = 4" >> /path/to/steampipe/postgresql.conf
echo "max_wal_senders = 4" >> /path/to/steampipe/postgresql.conf
pg_ctl restart -D /path/to/steampipe/data
# Create replication slot
psql -U steampipe_user -d steampipe_db -c "SELECT * FROM pg_create_logical_replication_slot('steampipe_slot', 'pgoutput');"
# Create publication
psql -U steampipe_user -d steampipe_db -c "CREATE PUBLICATION steampipe_pub FOR TABLE aws_ec2_inventory;"
# Create subscription on Aurora PostgreSQL
psql -h aurora-endpoint -U aurora_user -d aurora_db -c "CREATE SUBSCRIPTION steampipe_sub CONNECTION 'host=steampipe_host port=5432 user=replication_user password=your_password' PUBLICATION steampipe_pub;"

judell/steampipe-aurora.md