Postgres dump

[Tarnadas]

Hey,

would it be possible to also upload a dump of the Postgres database and not just the blockchain data?
It takes a surreal amount of time to catch up with the current block height.

[frol]

@Tarnadas It would be great to know your use-case, so we don’t solve a wrong problem here (https://xyproblem.info/)

@telezhnaya you may want to start collecting such feedback to consider the solution for our aspirational KR for this quarter (performance improvements / alternatives to the storage)

@mhalambek can this be achieved easily? I see there is a config option to configure backups on GCP, but can those be public?

[frol]

We won't have the capacity to work on the automation in this quarter. We will get back to it next year

[webern]

@frol is it intended that the backup linked above is publicly available? I tried downloading it by clicking on it in Chrome and got access denied. I am new to Google Cloud, but I also tried something like this:

gsutil cp gs://indexer.near.org/postgres/backups/first-backup.sql.gz file:///Users/someuser/first-backup.sql.gz

And received:

AccessDeniedException: 403 [email protected] does not have storage.objects.list access to the Google Cloud Storage bucket.

[frol]

Yes, it was intended to be publically available, but we don't do the public backups regularly, ,so it might have become outdated. Can you tell your expectations about the data? Why do you need a stale database snapshot?

[webern]

I was interested exploring the data with SQL queries* and figured stale data is better than no data. Also, as I understand it, it would take weeks to build that 900GB database so downloading and then running the indexer from there would be faster, right? In any event I no longer plan to do this so you can disregard my inquiry.

• I was actually interested in finding out the account addresses that hold the most NEAR (and how much they hold). Maybe this is readily available somewhere, but using the web explorer you needed to know an account address before you could check the balance.

[frol]

@webern I want to just check that we are on the same page. There is public shared access to the live database (dedicated read-only replica): https://github.com/near/near-indexer-for-explorer#shared-public-access. We might setup credentials for replication, so you might just setup postgres in read-replica mode and pull in all the data. Would that work for you? Are you willing to help us identifying potential pitfalls if we grant such permissions, and test things out?

[webern]

There is public shared access to the live database

I missed that. It probably would have addressed my use case. I won't be able to test it out but the potential pitfall that I see is that a client might be tempted to run their queries on your replica rather than replicating the data for themselves. Another pitfall might be that it will still take a very long time for a client to replicate.

It may be interesting to some clients if the data could be provided in OLAP roll-ups to reduce its size, but you then have the challenge of selecting the best aggregation groupings to satisfy most use cases.

[edoardobassett]

@frol @telezhnaya

Just checking in on this:

Found myself with a similar issue where I need to access the whole history of the blockchain to analyze it, but I am facing problems accessing the data in the shared public access database for very big queries.

In my case, it would be fine to access stale data.

Has there been any updates on setting up credentials for replication or (better) regular / automated DB backups for download?

[khorolets]

@edoardobassett Hey there! We are not working in this direction yet. Still, we don't have it planned. I hope @pkudinov gives an answer to that in a more detailed way.

@pkudinov I want to draw your attention here, perhaps you want to answer.

[edoardobassett]

@frol @khorolets Would it be possible to request a stale version / backup of the database (with the most recent data) similar to what was provided on this discussion on Nov 26th 2021? May be useful for other people as well in the future

[pkudinov]

@edoardobassett we don't have plans to share backups on ongoing basis, but I will see what we can do for your case.

We are working on a more sustainable long-term solution for this, because for many queries involving the whole history, the backup won't help – Postgres simply times out before it executes the query.

Could you share your SQL? We might be able to help you with one-time ad-hoc execution and return the data. After that you can start indexing using NEAR Lake.

[edoardobassett]

@pkudinov thanks a lot, that would be very helpful. Ideally, I would need all the data from the action_receipt_actions table.
SQL Query:
SELECT * FROM action_receipt_actions ORDER BY receipt_included_in_block_timestamp DESC;

Could also work with the data from July 1st 2021 if it makes easier:
SQL Query:
SELECT * FROM action_receipt_actions WHERE receipt_included_in_block_timestamp > 1625097600000000000 ORDER BY receipt_included_in_block_timestamp DESC;

Let me know! Would be of big help 😄