How to export dataset from PostgreSQL to CSV on AWS so that users can download it?

Question

I have an API where users can query some time-series data. But now I want to make the entire data set available for users to download for their own uses. How would I go about doing something like this? I have RDS, an EC2 instance setup. What would my next steps be?

Answer 1

In this scenario and without any other data or restrictions given, I would use S3 bucket in the center of this process.

1. Create an S3 Bucket to save the database/dataset dump.
2. Dump the database/dataset to S3. ( examples: docker, lambda )
3. Manually transform dataset to CSV or use a Lambda triggered on every dataset dump. (not sure if pg_dump can give you CSV out of the box)
4. Host those datasets in a bucket accessible to your users and allow access to them as per case:

• You can create a publicly available bucket and share its HTTP URL.
• You can create a pre-signed URL to allow limited access to your dataset

S3 is proposed since its cheap and you can find a lot of readily available tooling to work with.