Supabase•4mo ago

Supabase Queues (pgmq)

Hello server, I was wondering if anyone has gotten PSMQ to work properly in the supabase container running locally? I don't think the supabase documentation talks about this, it assumes you'll be working with the queues only remotely. I wanted to create and test queues locally rather than having to interface with the cloud. I was successfully able to do it, just curious if anyone else faced this issue. Thanks!

14 Replies

inder•4mo ago

https://discord.com/channels/839993398554656828/1377145368814555186

jjcantuOP•4mo ago

@inder Thanks for this. I was more so asking about how you guys got queues to work locally. The thread above still relies on the queue in the cloud, does it not?

inder•4mo ago

No, it talks about setting up queues locally with supabase cli localhost

jjcantuOP•4mo ago

Sorry, I may have a misunderstanding. So when you initially set up queues and ran

supabase start

supabase start

your queues worked straight out of the box?

inder•4mo ago

Make sure your config.toml looks like this https://discord.com/channels/839993398554656828/1377145368814555186/1377220600321544302 And you also have to enable it via dashboard

jjcantuOP•4mo ago

Yep, already done that. I'm not sure why I assumed that it wasn't supported locally. I guess the docs don't say it explictly. Spent the last 3 hours setting up scripts where in the documentation does it tell us to expose the schema in the toml? Not saying you're wrong, just want to see if I missed something

inder•4mo ago

I couldn't find about exposing schema in local instances in docs. Only this part is there to expose schema. This is for managed instance. Just remember, by default only public and graphql schemas are exposed via rest api. On local instances, you've to manage everything yourself

jjcantuOP•4mo ago

I see, thank you. I appreciate your help! I remember the issue, when I created my migration file I was getting:

ERROR: schema "pgmq" does not exist (SQLSTATE 3F000)
At statement 0: -- Create the bills queue using PGMQ
SELECT pgmq.create('bills')

ERROR: schema "pgmq" does not exist (SQLSTATE 3F000)
At statement 0: -- Create the bills queue using PGMQ
SELECT pgmq.create('bills')

I just created scripts to install pgmq in my docker container when running

supabase start

supabase start

I guess however, this was not necessary. Either way, appreciate your help! 🔥

inder•4mo ago

You can set up via migrations if you don't want to enable it manually everytime via dashboard this is a valid approach otherwise everytime you reset local db, you'll have to go into dashboard and setup queues

jjcantuOP•4mo ago

okay cool, so it was not pointless. Nice.

#!/bin/bash

# Supabase wrapper script with PGMQ installation
# Usage: ./scripts/supabase.sh [start|stop|reset|status|migration:up|migration:new]

set -e

SUPABASE_DIR="packages/supabase"
SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
PROJECT_ROOT="$(dirname "$SCRIPT_DIR")"

cd "$PROJECT_ROOT/$SUPABASE_DIR"

case "$1" in
  "start")
    echo "🚀 Starting Supabase..."
    supabase start
    echo "📦 Installing PGMQ..."
    ./install-pgmq.sh
    echo "✅ Supabase started with PGMQ installed!"
    ;;
  "stop")
    echo "🛑 Stopping Supabase..."
    supabase stop
    echo "✅ Supabase stopped!"
    ;;
  "reset")
    echo "🔄 Resetting Supabase database..."
    supabase db reset
    echo ""
    echo "⚠️  If you see errors about 'pgmq' extension, run the following commands:"
    echo "  cd packages/supabase"
    echo "  ./install-pgmq.sh"
    echo "  supabase migration up"
    echo ""
    echo "✅ Database reset complete!"
    ;;
  "status")
    echo "📊 Supabase status:"
    supabase status
    ;;
  "migration:up")
    echo "⬆️  Running migrations..."
    supabase migration up
    echo "✅ Migrations applied!"
    ;;
  "migration:new")
    if [ -z "$2" ]; then
      echo "❌ Error: Migration name is required"
      echo "Usage: pnpm supabase migration:new <migration_name>"
      exit 1
    fi
    echo "🆕 Creating new migration: $2"
    supabase migration new "$2"
    echo "✅ Migration created!"
    ;;
  *)
    echo "❌ Error: Unknown command '$1'"
    echo "Usage: pnpm supabase [start|stop|reset|status|migration:up|migration:new]"
    echo ""
    echo "Commands:"
    echo "  start           - Start Supabase and install PGMQ"
    echo "  stop            - Stop Supabase"
    echo "  reset           - Reset database (see message for PGMQ)"
    echo "  status          - Show Supabase status"
    echo "  migration:up    - Run pending migrations"
    echo "  migration:new   - Create new migration (requires name)"
    exit 1
    ;;
esac

#!/bin/bash

# Supabase wrapper script with PGMQ installation
# Usage: ./scripts/supabase.sh [start|stop|reset|status|migration:up|migration:new]

set -e

SUPABASE_DIR="packages/supabase"
SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
PROJECT_ROOT="$(dirname "$SCRIPT_DIR")"

cd "$PROJECT_ROOT/$SUPABASE_DIR"

case "$1" in
  "start")
    echo "🚀 Starting Supabase..."
    supabase start
    echo "📦 Installing PGMQ..."
    ./install-pgmq.sh
    echo "✅ Supabase started with PGMQ installed!"
    ;;
  "stop")
    echo "🛑 Stopping Supabase..."
    supabase stop
    echo "✅ Supabase stopped!"
    ;;
  "reset")
    echo "🔄 Resetting Supabase database..."
    supabase db reset
    echo ""
    echo "⚠️  If you see errors about 'pgmq' extension, run the following commands:"
    echo "  cd packages/supabase"
    echo "  ./install-pgmq.sh"
    echo "  supabase migration up"
    echo ""
    echo "✅ Database reset complete!"
    ;;
  "status")
    echo "📊 Supabase status:"
    supabase status
    ;;
  "migration:up")
    echo "⬆️  Running migrations..."
    supabase migration up
    echo "✅ Migrations applied!"
    ;;
  "migration:new")
    if [ -z "$2" ]; then
      echo "❌ Error: Migration name is required"
      echo "Usage: pnpm supabase migration:new <migration_name>"
      exit 1
    fi
    echo "🆕 Creating new migration: $2"
    supabase migration new "$2"
    echo "✅ Migration created!"
    ;;
  *)
    echo "❌ Error: Unknown command '$1'"
    echo "Usage: pnpm supabase [start|stop|reset|status|migration:up|migration:new]"
    echo ""
    echo "Commands:"
    echo "  start           - Start Supabase and install PGMQ"
    echo "  stop            - Stop Supabase"
    echo "  reset           - Reset database (see message for PGMQ)"
    echo "  status          - Show Supabase status"
    echo "  migration:up    - Run pending migrations"
    echo "  migration:new   - Create new migration (requires name)"
    exit 1
    ;;
esac

inder•4mo ago

I see you have a script install-pgmq . Are you installing extension or enabling it? Because this extension is pre-installed

jjcantuOP•4mo ago

#!/bin/bash

echo "Installing PGMQ in Supabase PostgreSQL container..."

# Get the PostgreSQL container ID
CONTAINER_ID=$(docker ps --filter "name=supabase_db" --format "{{.ID}}")

if [ -z "$CONTAINER_ID" ]; then
    echo "Error: Supabase PostgreSQL container not found. Make sure Supabase is running."
    exit 1
fi

echo "Found container: $CONTAINER_ID"

# Check if PGMQ is already installed
if docker exec $CONTAINER_ID psql -U postgres -d postgres -c "SELECT 1 FROM pg_extension WHERE extname = 'pgmq';" 2>/dev/null | grep -q 1; then
    echo "PGMQ is already installed!"
    exit 0
fi

# Install build dependencies (PostgreSQL headers are already available)
echo "Installing build dependencies..."
docker exec $CONTAINER_ID apt-get update
docker exec $CONTAINER_ID apt-get install -y build-essential git

# Clone and build PGMQ
echo "Building PGMQ..."
docker exec $CONTAINER_ID bash -c "cd /tmp && rm -rf pgmq && git clone https://github.com/tembo-io/pgmq.git"
docker exec $CONTAINER_ID bash -c "cd /tmp/pgmq/pgmq-extension && make"
docker exec $CONTAINER_ID bash -c "cd /tmp/pgmq/pgmq-extension && make install"

# Clean up
echo "Cleaning up..."
docker exec $CONTAINER_ID apt-get remove -y build-essential git
docker exec $CONTAINER_ID apt-get autoremove -y
docker exec $CONTAINER_ID rm -rf /tmp/pgmq

echo "PGMQ installation complete!"
echo "You can now run your migrations."

#!/bin/bash

echo "Installing PGMQ in Supabase PostgreSQL container..."

# Get the PostgreSQL container ID
CONTAINER_ID=$(docker ps --filter "name=supabase_db" --format "{{.ID}}")

if [ -z "$CONTAINER_ID" ]; then
    echo "Error: Supabase PostgreSQL container not found. Make sure Supabase is running."
    exit 1
fi

echo "Found container: $CONTAINER_ID"

# Check if PGMQ is already installed
if docker exec $CONTAINER_ID psql -U postgres -d postgres -c "SELECT 1 FROM pg_extension WHERE extname = 'pgmq';" 2>/dev/null | grep -q 1; then
    echo "PGMQ is already installed!"
    exit 0
fi

# Install build dependencies (PostgreSQL headers are already available)
echo "Installing build dependencies..."
docker exec $CONTAINER_ID apt-get update
docker exec $CONTAINER_ID apt-get install -y build-essential git

# Clone and build PGMQ
echo "Building PGMQ..."
docker exec $CONTAINER_ID bash -c "cd /tmp && rm -rf pgmq && git clone https://github.com/tembo-io/pgmq.git"
docker exec $CONTAINER_ID bash -c "cd /tmp/pgmq/pgmq-extension && make"
docker exec $CONTAINER_ID bash -c "cd /tmp/pgmq/pgmq-extension && make install"

# Clean up
echo "Cleaning up..."
docker exec $CONTAINER_ID apt-get remove -y build-essential git
docker exec $CONTAINER_ID apt-get autoremove -y
docker exec $CONTAINER_ID rm -rf /tmp/pgmq

echo "PGMQ installation complete!"
echo "You can now run your migrations."

installing.

inder•4mo ago

You don't have to. Its preinstalled. You just need to enable it.

jjcantuOP•4mo ago

ahh, okay. I'll revisit this. Thank you, I prefer to do it via migrations as I reset and stop often.

Gaming

Programming

Supabase Queues (pgmq)

Did you find this page helpful?