We’ll cover the following:
- Creating a new AWS postgres RDS instance
- How to compile psycopg (we’ll use docker for that)
- Code for the function
- Lambda function, trigger, schedule
aws rds create-db-instance
--db-subnet-group-name [your subnet group]
--db-security-groups [your security group]
--db-instance-identifier [your instance identifier]
--db-name [your db name]
--master-username [your username]
--master-user-password [your password]--backup-retention-period 3
For compiling psycopg2 we need a linux installation, I am on a mac (don’t hate) so I used a docker container (ubuntu bionic) for the compiles. You can find the Dockerfile here.
The steps for compiling are the following :
# Download sources :
wget -c https://ftp.postgresql.org/pub/source/v11.4/postgresql-11.4.tar.gz
git clone https://github.com/psycopg/psycopg2
tar zxfv postgresql-11.4.tar.gz
# Make a local install dir
#configure, compile, install postgres
cd postgresql-11.4 ; ./configure --prefix=/home/postgres/pgsql-11.4 --without-readline --without-zlib
make ; make install
# Edit psycopg2 setup config
cd psycopg2 ;vim setup.cfg
# Compile psycopg2
python3.6 setup.py build
# Package psycopg2
mv ~/psycopg2 ~/psycopg2_source
cp -r ~/psycopg2_source/build/lib.linux-x86_64-3.6/psycopg2/ ~/
tar cvfz psycopg2.tgz psycopg2/
# Finally, get the package from docker, I just used scp
scp psycopg2.tgz email@example.com:
Next we need to write the Lambda function.
You can find my sample code, along with a binary version of psycopg2 for 11.4, and a deployment script in my repo here.
Keep in mind that the filename (lambda_function.py) and the function name (lambda_handler) are important as they will be defined later when we will create the function in lambda and that the handler has to have this syntax structure :
def handler_name(event, context):
For more information see AWS documentation here. The code is pretty simple, it will connect to an RDS instance, it’ll run a simple statement and it will report the result in slack, but it could be something different like aws SNS or your trigger event might be something completely different, like when a file is created in S3 do something in python and load it to postgres, lambda also supports API gateway. For more information about the different things you can do, see here.
Next, we need to package all these into a “lambda deployment package” AWS documentation can be found here. This is basically a zip that contains all dependencies and the code we want lambda to execute. In my case Slacker (and its dependencies) and the psycopg2 package we compiled earlier. Amazons documentation is pretty clear how to do this, so I won’t repeat the steps here but your zip file should look like this when unzipped:
> tree -L 1
Now that we have the deployment package ready, all we need to do is the lambda function and something that will trigger it on schedule. Go to aws console, hit lambda and create a new function:
Next screen you’ll have to create a new rule and write a schedule expression :
This schedule expression accepts ‘rate’ or a cron expression, see AWS documentation here.
From the function main page in aws console, scroll down to network, add your VPC and last, At ‘Execution role’ add a role or create a new one like the following :
As you may have noticed, I haven’t added any code yet. You can add your zip from the console :
I found it much easier to use a script like the following in order to make testing a bit less tedious. Keep in mind that the following script requires AWS CLI installed:
cp ./function_work_in_progress.py ./lambda_function.py
rm -f package.zip
cd packages ; zip -r9 /Users/vasilis/Work/working_on/projects/lambda/packaging/function.zip .
zip -g function.zip lambda_function.py
zip -g function.zip config.py
zip -g function.zip util.py
aws lambda update-function-code --function-name pg_lambda --zip-file fileb://function.zip
rm -f outfile ;aws lambda invoke --function-name "pg_lambda" outfile ;cat outfile
In this script, I repackage the deployment package, I update the function code on aws and last, I manually invoke the function, which should give me a result in slack. I found this way much easier to work with than making inline changes in AWS console.
Lambda is cool.. kinda, and having a way to schedule simple postgres tasks without having a server, or an ec2 instance is nice, but this is by no means a replacement for crontab, simply because lambda has a lot of limitations. Say you wanted a pgbadger report sent everyday, you are out of luck. Perl is not supported in lambda. But if you want a bloat report or any SQL statement executed on a schedule, lambda could be a solution.
As mentioned before, dockerfile , scripts and a binary of psycopg2 (against pg11.4) can be found here.
Thanks for reading
Source: eVOL Monkey