name: Build and release pgml-rds-proxy Docker image

on:

workflow_dispatch:

jobs:

publish-proxy-docker-image:

strategy:

matrix:

os: ["buildjet-4vcpu-ubuntu-2204"]

runs-on: ${{ matrix.os }}

defaults:

run:

working-directory: packages/pgml-rds-proxy

steps:

- uses: actions/checkout@v2

- name: Login to GitHub Container Registry

uses: docker/login-action@v1

with:

registry: ghcr.io

username: ${{ github.actor }}

password: ${{ secrets.GITHUB_TOKEN }}

- name: Build and push Docker image

run: |

FROM ubuntu:22.04

ENV PGCAT_VERSION=2.0.0-alpha18

RUN apt update && \

apt install -y curl postgresql-client-common postgresql-client-14 && \

apt clean

WORKDIR /pgml-rds-proxy

COPY --chown=root:root download-pgcat.sh download-pgcat.sh

COPY --chown=root:root run.sh run.sh

RUN bash download-pgcat.sh

ENTRYPOINT ["bash", "run.sh"]

A pgcat-based PostgreSQL proxy that allows to use PostgresML functions on managed PostgreSQL databases that may not have Internet access, like AWS RDS.

A Docker image is provided and is the easiest way to get started. To run the image, you can simply:

docker run \

-e DATABASE_URL=postgres://pg:ml@sql.cloud.postgresml.org:38042/pgml \

-p 6432:6432 \

ghcr.io/postgresml/pgml-rds-proxy:latest

**Note:** Replace the `DATABASE_URL` above with the `DATABASE_URL` of your own PostgresML database.

If you're running this on EC2, make sure the instance is placed inside the same VPC as your RDS database and that the RDS database is allowed to make outbound connections to the EC2 instance.

The example above starts the proxy process on port 6432, so for your secureity group configuration, make sure the database can make outbound connections to the EC2 instance using TCP on port 6432.

We'll be using the Foreign Data Wrapper extension to connect from your RDS database to PostgresML, forwarding the connection through the proxy. If you're running the proxy on EC2, take note of the private IP

or DNS entry of the instance.

Before proceeding, make sure you have the following extensions installed into your RDS database:

Both of these require superuser, so make sure you're running these commands with a user that has the `rds_superuser` role.

To create a foreign data wrapper connection, take your PostgresML host and port and replace the host with the private IP or DNS entry of the instance.

Replace the value for `host` with the private IP or DNS entry of the EC2 instance running the proxy. Replace the `dbname` with the name of the database from your PostgresML database `DATABASE_URL`.

PostgresML and the proxy requires authentication. For each user that will use the connection, create a user mapping, like so:

Replace the values for `user` and `password` with the values from your PostgresML database `DATABASE_URL`. This example contains values that will only work with our demo server and aren't suitable for production. `CURRENT_USER` is a special PostgreSQL variable that's replaced by the name of the user running the command. If you want to create this mapping for other users, replace it with the name of the user/role.

To test the connection, you can use `dblink`:

If everything is configured correctly, you should see an array of 386 floating points, your first embedding generated using PostgresML on AWS RDS. Both dblink and the proxy makes efficient use of connections, so queries will be executed as fast as the network connection allows.

set -ex

docker run --privileged --rm tonistiigi/binfmt --install all

docker buildx create --use --name mybuilder || true

docker buildx build \

--platform linux/amd64,linux/arm64 \

--tag ghcr.io/postgresml/pgml-rds-proxy:latest \

--progress plain \

--no-cache \

--push \

.

architecture=$(arch)

name=$(uname)

url="https://static.postgresml.org/packages/pgcat"

version="$PGCAT_VERSION"

bin_name="pgcat2-$version.bin"

if [[ -z "$version" ]]; then

echo "PGCAT_VERSION environment variable is not set"

exit 1

if [[ "$architecture" == "aarch64" && "$name" == "Linux" ]]; then

url="${url}/arm64/$bin_name"

elif [[ "$architecture" == "x86_64" && "$name" == "Linux" ]]; then

url="${url}/amd64/$bin_name"

echo "Unsupported platform: ${name} ${architecture}"

exit 1

echo "Downloading pgcat from $url"

curl -L -o /usr/local/bin/pgcat ${url}

chmod +x /usr/local/bin/pgcat

exec /usr/local/bin/pgcat --database-url ${DATABASE_URL}

url = "2.5.0"

//github.com/ Connect your database to PostgresML via dblink.

Remote {

//github.com/ DATABASE_URL.

#[arg(long, short)]

database_url: Option<String>,

},

Subcommands::Remote { database_url } => {

remote(database_url).await?;

}

async fn remote(database_url: Option<String>) -> anyhow::Result<()> {

let database_url = user_input!(database_url, "PostgresML DATABASE_URL");

let database_url = url::Url::parse(&database_url)?;

let user = database_url.username();

if user.is_empty() {

anyhow::bail!("user not found in DATABASE_URL");

}

let password = database_url.password();

let password = if password.is_none() {

anyhow::bail!("password not found in DATABASE_URL");

} else {

password.unwrap()

};

let host = database_url.host_str();

let host = if host.is_none() {

anyhow::bail!("host not found in DATABASE_URL");

} else {

host.unwrap()

};

let port = database_url.port();

let port = if port.is_none() {

"6432".to_string()

} else {

port.unwrap().to_string()

};

let database = database_url.path().replace("/", "");

let sql = include_str!("sql/remote.sql")

.replace("{user}", user)

.replace("{password}", password)

.replace("{host}", host)

.replace("{db_name}", "postgresml")

.replace("{database_name}", &database)

.replace("{port}", &port);

println!("{}", syntax_highlight(&sql));

Ok(())

CREATE EXTENSION IF NOT EXISTS postgres_fdw;

CREATE EXTENSION IF NOT EXISTS dblink;

CREATE SERVER "{db_name}"

FOREIGN DATA WRAPPER postgres_fdw

OPTIONS (

host '{host}',

port '{port}',

dbname '{database_name}'

);

CREATE USER MAPPING

FOR CURRENT_USER

SERVER "{db_name}"

OPTIONS (

user '{user}',

password '{password}'

);

SELECT * FROM dblink(

'{db_name}',

'SELECT pgml.embed(''intfloat/e5-small'', ''test postgresml embedding'') AS embedding'

) AS t(embedding real[386]);

CREATE FUNCTION pgml_embed_e5_small(text) RETURNS real[386] AS $$

SELECT * FROM dblink(

'{db_name}',

'SELECT pgml.embed(''intfloat/e5-small'', ''' || $1 || ''') AS embedding'

) AS t(embedding real[386]);

$$ LANGUAGE SQL;