Explorer DB Backup Available on GCS

[eduohe]

Context

As we approach the deadline to sunset the explorer PostgreSQL DB on April 30th, in case the community wants to continue to support the explorer indexer/db for their own needs we exported the DB with data until 2024-03-10 (block height 9,820,214 to 114,475,640)

The backup files are available in this GCS bucket gs://explorer-db-public.

It's public and the flag Requester Pays is On, this means that users will pay only for network egress and NEAR will continue to pay for storage, the files are compressed.

Prerequisites

Make sure that you have:

1. A GCP account
2. gsutil
3. pgrestore. On Mac we can install with: brew install libpq
4. A PostgreSQL server to restore the DB
5. Create a database called mainnet_explorer

How to restore the Explorer DB

1. Copy the backup files. Example to copy from GCS to a local folder gsutil cp -r gs://explorer-db-public my_local_dir
2. Run pg_restore: pg_restore -U postgres -h <host> -p <port> --no-acl --no-owner --jobs=<num CPUs available to run the restore in parallel> --format=directory -d mainnet_explorer -v ./my_local_dir/pg_dump_20240310/mainnet_explorer_20230310

Notes

• This DB contains ~15TB of data
• Depending on the resources of PostgreSQL Server the restore can take many hours (or days) to insert the data and create the table indexes.