Make database snapshot available as a postgres dump file about usaspending-api HOT 10 CLOSED

Hi, Joel – We are aware of this issue with the RDS snapshot and are working on a solution that will be free to our customers. This solution should be operable in the next several weeks. You might want to Sign Up on USAspending.gov at the bottom of every page to receive updates about new features and functionality to the site.

from usaspending-api.

Hi the download is now available via an S3 bucket at
http://usaspending-db.s3-website-us-gov-west-1.amazonaws.com/

from usaspending-api.

FYI, did a pg_dump of the RDS snapshot as of mid-June using the directory format and the dump file was 43gb.

Restoring the dump resulted in a database about 300 gb in size, before the indices and materialized views were created.

I anticipate the database will just be shy of a terabyte once its all restored.

For those installations who are maintaining their own copies of the usaspending database (as it appears the API is not really meant for high-volume transactions), it would be nice if there's a way to apply delta restores without having to do a full db restore.

Perhaps, using open source tools like https://pgbackrest.org/.

from usaspending-api.

The restore also cannot be completed because of dependencies on a remote database (dblink) for reference tables.

from usaspending-api.

Awesome @lisaam! The team went above and beyond, complete with detailed instructions that will spare future users all the troubleshooting I had to go through the hard way!

Will go thru the instructions and report my experience. Thanks again!

from usaspending-api.

Hi @lisaam,
Just wanted to confirm that the postgres dump file works like a charm!
Granted it almost took three days to restore it since I was using a relative small VM.

Along those lines, you may want to reference the RDS snapshot that @jedsundwall's team prepared for folks that require the data fast as well. Though it was expensive, restoring the snapshot took minutes.

In this way, you can serve both audiences and resolve #1413 as well.

from usaspending-api.

Closing as I've confirmed that the dump file works!

from usaspending-api.

We desperately need this database (edit* from the US gov't not from Amazon or CKAN), please mirror it or post http://usaspending-db.s3-website-us-gov-west-1.amazonaws.com/ is not being served and agency submissions are not available.

from usaspending-api.

@datocrats-org We apologize for the delay in responding to your question during the government shut down. The issue preventing downloads from USAspending.gov has been resolved. The database download was updated this week and is available for download from https://files.usaspending.gov/database_download/usaspending-db_20190129.zip.

from usaspending-api.

Is there a new 2023 db snapshot?

from usaspending-api.