ensuring reliable restore and enabling possible system transfer - a two part process
In the version:3.* series, the principla user (usually called 'admin') can now restore the database via the menu: [System]->[Maintenance]->[Restore] That said however, it is important not to rely wholly on this facility alone but to backup and restore also by Postgres tools: pg_dump and pg_restore.
Moreover for a comprehensive restore in the event of a server renewal/failure, one would follow the steps below particularly with regard to the application files where all the templates and users are kept.
a. Restore the Application
Assumption: There was functional SQL-System running previously on the server. This step is not necessary simply for a database restore.
Via an FTP client (eg. FileZilla) or via Webmin->Tools->Upload - Download, upload the application backup (eg. example.com-SQL-ledger3.2.12-20240531.tar.gz) to the regular user home directory (eg. /home/sammy). Input the following commands to restore the target database eg. testdb:
sammy@imega:$ sudo mv [MyServerName]-SQL-ledger3.2.12-[date].tar.gz /usr/local
sammy@imega:$ cd /usr/local
sammy@imega:/usr/local$ sudo tar -xzvf [MyServerName]-SQL-ledger3.2.12-[date].tar.gz
sammy@imega:/usr/local$ sudo chmod -R 775 sql-ledger/ Set the correct permissions.
The application restore is then complete. For a comprehensive restore, all the related databases need to be restored first to the PostgreSQL server, then follows the restore of the SQL-Ledger application backup.
b.1 Restore a Database
While in the application 'SQL-Ledger', go to System->Maintenance->Restore and choose the desired sql.gz file and continue with the restore process.
b.2 Restore a Database (with 'pg-restore')
This is an example. Via an FTP client (eg. FileZilla) or via Webmin->Tools->Upload - Download, upload the database backup (eg. testdb-3.2.12-20240522.sql.gz) to the regular user home directory (eg. /home/sammy). Input the following commands to restore the target database eg. testdb:
sammy@imega:$ sudo mv testdb-3.2.12-20240522.sql.gz /usr/local/backups - make the file available to the backups directory
sammy@imega:$ cd /usr/local/backups
sammy@imega:/usr/local/backups$ sudo chown postgres:postgres testdb-3.2.12-20240522.sql.gz
sammy@imega:/usr/local/backups$ sudo su postgres
postgres@imega:/usr/local/backups$ dropdb testdb
postgres@imega:/usr/local/backups$ createdb -O sql-ledger testdb
postgres@imega:/usr/local/backups$ PGPASSWORD=[mysql-ledgerpasswd] gunzip -c testdb-3.2.12-20240522.sql.gz | psql -Usql-ledger testdb Note: wait for the end of its transactions.
postgres@imega:/usr/local/backups$ exit // to return to user: sammy
Thereafter one should be able to login (with a user linked to the target database) to the SQL-ledger and find it restored at the time of its backup.