Move database backup-related code somewhere else than createTables.js

[DavidBruant]

Same as anthill/pheromon#32 (comment)
createTables should only create tables. Other things should be done elsewhere, a new tool can glue these functions together elsewhere too.

For reference, this was introduced in https://github.com/anthill/6element/pull/79/files#diff-f9300e43444141fb3325dec3e7101de0R14
Sorry for not catching that earlier.

[SergeBredin]

@DavidBruant Does this issue refer to the current version of 6element or previous Pheromon which has been moved ?

[DavidBruant]

Current

6element/server/database/management/createTables.js

Lines 14 to 32 in 39077ed

	if (process.env.BACKUP){
	// if a environement variable is set, use it to load some data
	console.log('== A dump file is specified, loading the data. ==');
	sqlFile = '/pheromon/data/' + process.env.BACKUP;
	var gzip = zlib.createGunzip();
	var readStream = fs.createReadStream(sqlFile);
	var proc = spawn('psql', ['-p', process.env.DB_PORT_5432_TCP_PORT, '-h', process.env.DB_PORT_5432_TCP_ADDR, '-U', process.env.POSTGRES_USER, '-d', process.env.POSTGRES_USER]);
	readStream
	.pipe(gzip)
	.pipe(proc.stdin);
	// This code doesn't work because gzip is too slow
	// readStream.on('close', function(){
	// resolve();
	// })
	setTimeout(resolve, 3000); // <-- not a beautiful patch, but it works
	} else {

[SergeBredin]

Fine. We need to adapt it to 6element. Previously, dump process wasn't much necessary because open data files were used to reload the db. But it's not enough anymore, bin data from sensors will involve to save dumps with cron jobs.
So let's keep this issue opened.

[DavidBruant]

Working on it.

[DavidBruant]

Requirements:

• Ability to create backups regularly (daily basis?)
• Ability to create backups at a button push
• Ability to restore a backup at a button push
• Hopefully, ability to download a dump from the server, load it in a local machine to be back in the same data configuration for easier debugging (perf/correctness)

[DavidBruant]

Keep the backup concerns outside of the application docker containers. The host machine can access the docker containers via docker exec. Perhaps do a docker service specifically for this purpose (with backup directory/volume specifically for that purpose). From the host machine, we can then docker exec this service to get the dump in stdin and forward it to any file or the network as in :
docker exec 6element_backup_1 tools/backup > /path/to/backups/6element-latest.bak. This can be triggered in a cron job in the host machine. This can also be launched from command line anytime.

This service can have a directory (read-only) volume for backups when need to restore. When one wants to restore a backup, it'd do:

cp backup-to-restore.bak /path/to/backup-service-volume/backup.bak # or symlink
docker exec -ti 6element_backup_1 tools/restore ./backup.bak # -ti for confirmation input