From 329fd4bcf6504c74e3906e51c6fc2124bc09cc02 Mon Sep 17 00:00:00 2001
From: Alexandre Alapetite <alexandre@alapetite.fr>
Date: Tue, 30 Apr 2024 08:31:13 +0200
Subject: CLI database backup and restore (#6387)

* CLI database backup and restore
Can also be used to migrate from one database to another (e.g. MySQL to PostgreSQL) or to ease upgrade to a major PostgreSQL version (e.g. 15 to 16).

* +x

* Fix some cases

* Update to docker-compose-v2

* More documentation
---
 cli/README.md      |  8 +++++++
 cli/db-backup.php  | 20 +++++++++++++++++
 cli/db-restore.php | 65 ++++++++++++++++++++++++++++++++++++++++++++++++++++++
 3 files changed, 93 insertions(+)
 create mode 100755 cli/db-backup.php
 create mode 100755 cli/db-restore.php

(limited to 'cli')

diff --git a/cli/README.md b/cli/README.md
index 1ce70b5c1..366f456d5 100644
--- a/cli/README.md
+++ b/cli/README.md
@@ -121,6 +121,14 @@ cd /usr/share/FreshRSS
 ```sh
 cd /usr/share/FreshRSS
 
+./cli/db-backup.php
+# Back-up all users respective database to `data/users/*/backup.sqlite`
+
+./cli/db-restore.php --delete-backup --force-overwrite
+# Restore all users respective database from `data/users/*/backup.sqlite`
+# --delete-backup:	delete `data/users/*/backup.sqlite` after successful import
+# --force-overwrite:	will clear the users respective database before import
+
 ./cli/db-optimize.php --user username
 # Optimize database (reduces the size) for a given user (perform `OPTIMIZE TABLE` in MySQL, `VACUUM` in SQLite)
 ```
diff --git a/cli/db-backup.php b/cli/db-backup.php
new file mode 100755
index 000000000..290b5cc7b
--- /dev/null
+++ b/cli/db-backup.php
@@ -0,0 +1,20 @@
+#!/usr/bin/env php
+<?php
+declare(strict_types=1);
+require(__DIR__ . '/_cli.php');
+
+performRequirementCheck(FreshRSS_Context::systemConf()->db['type'] ?? '');
+$ok = true;
+
+foreach (listUsers() as $username) {
+	$username = cliInitUser($username);
+	$filename = DATA_PATH . '/users/' . $username . '/backup.sqlite';
+	@unlink($filename);
+
+	echo 'FreshRSS backup database to SQLite for user “', $username, "”…\n";
+
+	$databaseDAO = FreshRSS_Factory::createDatabaseDAO($username);
+	$ok &= $databaseDAO->dbCopy($filename, FreshRSS_DatabaseDAO::SQLITE_EXPORT);
+}
+
+done((bool)$ok);
diff --git a/cli/db-restore.php b/cli/db-restore.php
new file mode 100755
index 000000000..6ea6f4a7d
--- /dev/null
+++ b/cli/db-restore.php
@@ -0,0 +1,65 @@
+#!/usr/bin/env php
+<?php
+declare(strict_types=1);
+require(__DIR__ . '/_cli.php');
+
+performRequirementCheck(FreshRSS_Context::systemConf()->db['type'] ?? '');
+
+$cliOptions = new class extends CliOptionsParser {
+	public string $deleteBackup;
+	public string $forceOverwrite;
+
+	public function __construct() {
+		$this->addOption('deleteBackup', (new CliOption('delete-backup'))->withValueNone());
+		$this->addOption('forceOverwrite', (new CliOption('force-overwrite'))->withValueNone());
+		parent::__construct();
+	}
+};
+
+if (!empty($cliOptions->errors)) {
+	fail('FreshRSS error: ' . array_shift($cliOptions->errors) . "\n" . $cliOptions->usage);
+}
+
+FreshRSS_Context::initSystem(true);
+Minz_User::change(Minz_User::INTERNAL_USER);
+$ok = false;
+try {
+	$error = initDb();
+	if ($error != '') {
+		$_SESSION['bd_error'] = $error;
+	} else {
+		$ok = true;
+	}
+} catch (Exception $ex) {
+	$_SESSION['bd_error'] = $ex->getMessage();
+}
+if (!$ok) {
+	fail('FreshRSS database error: ' . (empty($_SESSION['bd_error']) ? 'Unknown error' : $_SESSION['bd_error']));
+}
+
+foreach (listUsers() as $username) {
+	$username = cliInitUser($username);
+	$filename = DATA_PATH . "/users/{$username}/backup.sqlite";
+	if (!file_exists($filename)) {
+		fwrite(STDERR, "FreshRSS SQLite backup not found for user “{$username}”!\n");
+		$ok = false;
+		continue;
+	}
+
+	echo 'FreshRSS restore database from SQLite for user “', $username, "”…\n";
+
+	$databaseDAO = FreshRSS_Factory::createDatabaseDAO($username);
+	$clearFirst = isset($cliOptions->forceOverwrite);
+	$ok &= $databaseDAO->dbCopy($filename, FreshRSS_DatabaseDAO::SQLITE_IMPORT, $clearFirst);
+	if ($ok) {
+		if (isset($cliOptions->deleteBackup)) {
+			unlink($filename);
+		}
+	} else {
+		fwrite(STDERR, "FreshRSS database already exists for user “{$username}”!\n");
+		fwrite(STDERR, "If you would like to clear the user database first, use the option --force-overwrite\n");
+	}
+	invalidateHttpCache($username);
+}
+
+done((bool)$ok);
-- 
cgit v1.2.3