AWS Lambda PostgreSQL Client

Docker Build

After googling (see helpful references at the end) i used Docker to get the binaries and libraries i needed to bundle up with the lambda function.

FROM centos:7
RUN yum -y install yum-utils rpmdevtools
RUN yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
WORKDIR /tmp
RUN yumdownloader postgresql11
RUN yumdownloader postgresql11-libs
RUN rpmdev-extract *rpm
RUN mkdir -p /var/task
RUN cp -r /tmp/postgresql11-libs-11.6-2PGDG.rhel7.x86_64/usr/pgsql-11/lib/* /var/task
RUN cp -r /tmp/postgresql11-11.6-2PGDG.rhel7.x86_64/usr/pgsql-11/bin/* /var/task
WORKDIR /var/task
RUN zip -r9 /tmp/postgresql11-client-with-libraries.zip *
docker build -t pg-scheduler-build ./docker
docker run -it -v /~Documents/git/docker-postgresql-scheduler:/tmp/macbook pg-scheduler-build
cp postgresql11-client-with-libraries.zip /tmp/macbook
>ls
clusterdb libecpg.so.6.11 libpq.so.5.11 pg_isready pg_waldump
createdb libecpg_compat.so.3 libpqwalreceiver.so pg_receivewal pgbench
createuser libecpg_compat.so.3.11 pg_archivecleanup pg_restore psql
dropdb libpgfeutils.a pg_basebackup pg_rewind reindexdb
dropuser libpgtypes.so.3 pg_config pg_test_fsync vacuumdb
libecpg.so libpgtypes.so.3.11 pg_dump pg_test_timing
libecpg.so.6 libpq.so.5 pg_dumpall pg_upgrade

Lambda Packaging

Following the standard instructions from the AWS website i added the bootstrap and function.sh files to the same directory as my PostgreSQL libraries and binaries.

#!/bin/sh

set -euo pipefail

# Initialization - load function handler
source $LAMBDA_TASK_ROOT/"$(echo $_HANDLER | cut -d. -f1).sh"

# Processing
while true
do
HEADERS="$(mktemp)"
# Get an event
EVENT_DATA=$(curl -sS -LD "$HEADERS" -X GET "http://${AWS_LAMBDA_RUNTIME_API}/2018-06-01/runtime/invocation/next")
REQUEST_ID=$(grep -Fi Lambda-Runtime-Aws-Request-Id "$HEADERS" | tr -d '[:space:]' | cut -d: -f2)

# Execute the handler function from the script
RESPONSE=$($(echo "$_HANDLER" | cut -d. -f2) "$EVENT_DATA")

# Send the response
curl -X POST "http://${AWS_LAMBDA_RUNTIME_API}/2018-06-01/runtime/invocation/$REQUEST_ID/response" -d "$RESPONSE"
done
function handler () {
EVENT_DATA=$1
echo "$EVENT_DATA" 1>&2;
RESPONSE="Echoing request: '$EVENT_DATA'"
export LD_LIBRARY_PATH=$PWD
export PGPASSWORD=xxxxxxxxxxxxx
echo `/var/task/psql -d demodb -h demodb.xxxxxxxxx.eu-west-1.rds.amazonaws.com -U xxxxxxx -f create-table.sql`
echo $RESPONSE
}
insert into wehavelearnt (id, topic) values (1 , 'terraform is for infrastructure as code');
insert into wehavelearnt (id, topic) values (2 , 'it has providers, resources, variable, outputs and modules');
insert into wehavelearnt (id, topic) values (3 , 'the is a PostgreSQL provider');
insert into wehavelearnt (id, topic) values (4 , 'the main commands are init, plan, apply and destroy');
>ls
bootstrap libecpg.so libpq.so.5 pg_isready pgbench
clusterdb libecpg.so.6 libpq.so.5.11 pg_receivewal psql
create-table.sql libecpg.so.6.11 libpqwalreceiver.so pg_restore reindexdb
createdb libecpg_compat.so.3 pg_archivecleanup pg_rewind vacuumdb
createuser libecpg_compat.so.3.11 pg_basebackup pg_test_fsync
dropdb libpgfeutils.a pg_config pg_test_timing
dropuser libpgtypes.so.3 pg_dump pg_upgrade
function.sh libpgtypes.so.3.11 pg_dumpall pg_waldump
> zip ../function.zip *

Lambda Creation

In the AWS Console i can now create the Lambda.

xxxxxxxxx@xxxxxxxx:xxxxxxxx> select * from wehavelearnt;
+------+------------------------------------------------------------+
| id | topic |
|------+------------------------------------------------------------|
| 1 | terraform is for infrastructure as code |
| 2 | it has providers, resources, variable, outputs and modules |
| 3 | the is a PostgreSQL provider |
| 4 | the main commands are init, plan, apply and destroy |
+------+------------------------------------------------------------+
SELECT 4
Time: 0.076s

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Matt Houghton

Matt Houghton

20 Followers

Data Architect @CDL_Software , AWS Community Builder, 13 x AWS Certified. Qlik Global Luminary 50.