PostgreSQL Database blockchain maintenance/optimize
Why do you need maintenance?
Over time, the bases in the postgres tend to grow, and begin to move more slowly due to problems with indexing. To solve these problems we use:
REINDEX - operation of reindexing, fixing damaged indexes in the database.
VACUUM - garbage collection. VACUUM recovers the space occupied by the "dead" data. When performing normal operations with data, PostgreSQL does not physically remove data from the tables; this happens with the FULL VACUUM operation.
How to use db maintenance in SmartHoldem Node-A?
Log into the server
execute the following commands and wait for the operations to complete, it may take some time
cd node sh stop.sh psql sth_smartholdem REINDEX DATABASE sth_smartholdem; vacuum full analyze; \q sh update.sh
Maintenance operations are recommended once a month.
or Create Script:
psql "postgresql://$USER:firstname.lastname@example.org/sth_smartholdem" -c 'REINDEX DATABASE sth_smartholdem' psql "postgresql://$USER:email@example.com/sth_smartholdem" -c 'vacuum full analyze'