My experience with exporting Postgres RDS partitioned tables to S3

https://geopet85.github.io/2022/05/15/rds-partitioned-tables-export.html

by higeorge13 • 4 years ago

44 20 4 years ago

geopet85.github.io

Marazan• 4 years ago

Ive had issues in the other direction.

Importing data using the copy from s3 command has a nasty failure state if the client loses connection to the db AND the import command has a problem.

The query hangs with a row exclusive lock and the query _cannot_ be killed. Only option was to reboot the DB.

higeorge134• 4 years ago

You mean this function: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_...? I don't get that Postgres RDS only allows you to import from csv, while the exports are strictly parquet. I know that Postgres implements the COPY command using csv or binary, but i would expect some end-to-end lifecycle implementation by AWS and parquet would at least enforce some schema and reduce some errors including those which caused yours.

iknownothow• 4 years ago

As far I can tell, exporting query results from Postgres RDS to S3 uses Postgres's Copy and only supports CSV and Text [1]. Can you provide a link for parquet exports?

[1] https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/postg...

tomatowurst• 4 years ago

what about using sqlite diff stored in dynamodb that gets synced when lambda is called? thought i saw a repo for that.

i think that has merit too reading this im reminded.

higeorge134• 4 years ago

It would be great if you could share some specifics or share the repo.

metadata• 4 years ago

Scripting data movement is easy only for small and easy jobs. With many thousands of tables and more than a few TB, all kinds of issues start popping up. I read somewhere that 85% of large data migration projects fail. Data warehouses really need an optimal Parquet file sizes to work efficiently, and for Snowflake it's roughly 100-200MB per file. The good way to copy that is relying on DB statistics to determine the optimal number of records per chunk. Then, to have the job finish in a reasonable time, one needs to read a certain number of data chunks in parallel and stream that data into Parquet at S3 (or Azure Blobs). Once the data is up, Snowflake can ingest it.

Shameless plug: my company created a commercial solution which does exactly that (https://www.spectralcore.com/omniloader). Happy to answer any questions.

metadat• 4 years ago

I knew it, it was you.

singlow• 4 years ago

Might consider using the Athena Federated Query. Essentially it is a lambda that knows how to get data from a database instance and write it to s3. It is very well optimized for partitioned tables.

https://github.com/awslabs/aws-athena-query-federation/tree/...

Once that is configured, execute a Create-Table-As (CTAS) or insert query using a select from the configured lambda-backed catalog.

I have only tryed the mysql version, but they docs indicate they are very similar.

akdor1154• 4 years ago

It sounds like you don't have AWS support? In case you do, give it a shot.. it takes a while but eventually you can get a knowledgeable answer. Though im in 50% on it being 'yes this is by design, please watch the RDS change log for future improvements' in this case.

gcbirzan4• 4 years ago

Tell that to the ticket I opened in November that got closed without an answer or even a message a month ago, related exactly to RDS export being crap.

whoknew11222• 4 years ago

There is no mechanism in AWS Support by which tickets are automatically closed without a response. Could someone have gone in and resolved your case without a response? In theory, it's possible. But there's no incentive to do so.

I'd suggest responding to the case, which will reopen it and get it reassigned. A open case that's missed SLA by 6 months will raise eyebrows.

That's assuming what you're saying is correct. I'm not sure what your incentive would be to lie, but I've dealt with more than one support case wherein the customer swears up-and-down that they never changed anything. And whoopsie, Cloudtrail shows they did.

fdr• 4 years ago

....that seems like an unusually enormous number of partitions.

gcbirzan4• 4 years ago

RDS export also has a bug where at some point it will not allow you to make new jobs (let alone list the old ones) because something internally times out when trying to serialise the list of tables from the job. I'm surprised it worked for you, I think we have around 100k tables too and it fails.

higeorge134• 4 years ago

Ah yeah, i forgot to write about this issue. We also never made it to work when providing all the list of partitioned tables, the api call was failing. We were fortunate to just provide the list of 7 tables as input and made it work (somehow).

darkhorse222• 4 years ago

But why? You export in parquet to retain the schema and data but ingesting that data back in requires CSV which loses the schema. How do you restore your table schema if someone deletes it? Why not just use the same ingest from s3 function to write out to s3?

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/postg...

higeorge134• 4 years ago

We don’t ingest back to postgres, but snowflake. And we also have a strict schema definition for our source models in snowflake via dbt.

My experience with exporting Postgres RDS partitioned tables to S3

Related Stories