5 ms·

My experience with exporting Postgres RDS partitioned tables to S3

44 points by higeorge13 4y ago

Marazan 4y 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.
- higeorge13 4y ago
  You mean this function: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PostgreSQL.S3Import.html 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 4y 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/postgresql-s3-export.html#postgresql-s3-export-examples https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/postg...
    - vanpythonista 4y ago
      You can use a RDS Snapshot to export to S3 in Parquet format: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_ExportSnapshot.html https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_...
tomatowurst 4y 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.
- higeorge13 4y ago
  It would be great if you could share some specifics or share the repo.
metadata 4y 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 https://www.spectralcore.com/omniloader). Happy to answer any questions.
- metadat 4y ago
  I knew it, it was you.
singlow 4y 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/master/athena-postgresql 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 4y 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.
- gcbirzan 4y 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.
  - whoknew1122 4y 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.
    - gcbirzan 4y ago
      I don't know how to check, maybe someone was cleaning tickets older than 3-4 months. But, the status is 'resolved' (again, not sure who can set this), but there has been no communication from AWS since. I just checked now, the issue either was fixed by us dumping less data or by AWS. However, the SLAs that I can see are 'initial response', which for better or worse, sometimes happen. However, the most egregious example of how broken that model is, we had a business critical system down ticket down that got an answer of 'looking into it' in 19 minutes, then ~1:45 an answer of "oh, yeah, I checked and this and this DB you talked about didn't reboot", which was true, the person that made the ticket pointed out the wrong instance. 3 hours after I point the correct DB we get told, yes, you are right, it rebooted. And then on the 1st of April I ask for a timeline of how the writer/reader nodes changed during the reboot, I get an answer on the 20th, even though we pushed in the ticket and through our TAM for an answer. Generally, I'm not really sure how people are so happy with AWS support, every issue that is even the least bit complicated gets wrong answers (took a month for us to convince people, though meetings with our TAM, that it's not a configuration or capacity issue that's causing some issues) or just sits there for ages. Once we got an engineer on the Aurora team assigned to the case I used as an example, we started making progress, we even got a patched version of PostgreSQL RDS deployed, but once the bug was identified, we had to work around it, since a fixed version was not an option in less than a few months. All in all, for the hundreds of thousands of dollars we're spending a month on support, I have a feeling that when we need it, we're kind of alone.
fdr 4y ago
....that seems like an unusually enormous number of partitions.
gcbirzan 4y 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.
- higeorge13 4y 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 4y 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/postgresql-s3-export.html https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/postg...
- higeorge13 4y 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.

44 points by higeorge13 4y 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.

higeorge13 4y ago
You mean this function: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PostgreSQL.S3Import.html 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 4y 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/postgresql-s3-export.html#postgresql-s3-export-examples https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/postg...
  - vanpythonista 4y ago
    You can use a RDS Snapshot to export to S3 in Parquet format: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_ExportSnapshot.html https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_...

tomatowurst 4y 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.

higeorge13 4y ago
It would be great if you could share some specifics or share the repo.

metadata 4y 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 https://www.spectralcore.com/omniloader). Happy to answer any questions.

metadat 4y ago
I knew it, it was you.

singlow 4y 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/master/athena-postgresql 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 4y 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.

gcbirzan 4y 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.
- whoknew1122 4y 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.
  - gcbirzan 4y ago
    I don't know how to check, maybe someone was cleaning tickets older than 3-4 months. But, the status is 'resolved' (again, not sure who can set this), but there has been no communication from AWS since. I just checked now, the issue either was fixed by us dumping less data or by AWS. However, the SLAs that I can see are 'initial response', which for better or worse, sometimes happen. However, the most egregious example of how broken that model is, we had a business critical system down ticket down that got an answer of 'looking into it' in 19 minutes, then ~1:45 an answer of "oh, yeah, I checked and this and this DB you talked about didn't reboot", which was true, the person that made the ticket pointed out the wrong instance. 3 hours after I point the correct DB we get told, yes, you are right, it rebooted. And then on the 1st of April I ask for a timeline of how the writer/reader nodes changed during the reboot, I get an answer on the 20th, even though we pushed in the ticket and through our TAM for an answer. Generally, I'm not really sure how people are so happy with AWS support, every issue that is even the least bit complicated gets wrong answers (took a month for us to convince people, though meetings with our TAM, that it's not a configuration or capacity issue that's causing some issues) or just sits there for ages. Once we got an engineer on the Aurora team assigned to the case I used as an example, we started making progress, we even got a patched version of PostgreSQL RDS deployed, but once the bug was identified, we had to work around it, since a fixed version was not an option in less than a few months. All in all, for the hundreds of thousands of dollars we're spending a month on support, I have a feeling that when we need it, we're kind of alone.

fdr 4y ago

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

gcbirzan 4y 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.

higeorge13 4y 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 4y 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/postgresql-s3-export.html https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/postg...

higeorge13 4y 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.