How to recover/restore using database dump and ncdata

[k4my4b]

My last borg backup is from a day ago but I have a more recent backup of ncdata and nextcloud_aio_database_dump, how can I can restore everything using the two? if at all possible

[k4my4b]

My last borg backup is from a day ago but I have a more recent backup of ncdata and nextcloud_aio_database_dump, how can I can restore everything using the two? if at all possible

I just dropped the database and recreated a new one

dropdb -U nextcloud nextcloud_database -f
createdb -U nextcloud nextcloud_database

granted permissions according to start.sh (where owner=oc_nextcloud db=nextcloud_database)

all-in-one/Containers/postgresql/start.sh

Lines 112 to 114 in 0b15352

	ALTER DATABASE "$POSTGRES_DB" OWNER TO "$DB_OWNER";
	GRANT ALL PRIVILEGES ON DATABASE "$POSTGRES_DB" TO "$DB_OWNER";
	GRANT ALL PRIVILEGES ON SCHEMA public TO "$DB_OWNER";

and then

docker exec -i nextcloud-aio-database psql -U nextcloud nextcloud_database < ./nextcloud_aio_database_dump/_dump/database-dump.sql

I have no Idea if this is a good idea or not but it seems to have worked

[nikbpetrov]

As a complete donkey myself, I had to do this a few times (don't ask), so I (i.e. chatGPT) wrote me a script. Hope that helps someone:

#!/bin/bash

# Define variables
DB_CONTAINER="nextcloud-aio-database"
DB_NAME="nextcloud_database"
DB_USER="nextcloud"

# Check if the database container is running
echo "Checking if the database container is running..."
if ! docker ps --format '{{.Names}}' | grep -q "$DB_CONTAINER"; then
  echo "Error: Database container '$DB_CONTAINER' is not running."
  exit 1
fi

# Retrieve the database password from the container
echo "Retrieving the database password..."
POSTGRES_PASSOWRD=$(docker exec -i $DB_CONTAINER bash -c 'echo $POSTGRES_PASSWORD')
if [ -z "$POSTGRES_PASSOWRD" ]; then
  echo "Error: Could not retrieve database password."
  exit 1
fi

# Drop the existing database
echo "Dropping the existing database: $DB_NAME"
docker exec -i $DB_CONTAINER psql -U $DB_USER --dbname=postgres --command "DROP DATABASE IF EXISTS \"$DB_NAME\";"
if [ $? -ne 0 ]; then
  echo "Error: Failed to drop database $DB_NAME."
  exit 1
fi

# Create a new database
echo "Creating a new database: $DB_NAME"
docker exec -i $DB_CONTAINER psql -U $DB_USER --dbname=postgres --command "CREATE DATABASE \"$DB_NAME\";"
if [ $? -ne 0 ]; then
  echo "Error: Failed to create database $DB_NAME."
  exit 1
fi

# Grant permissions
echo "Granting permissions to $DB_USER on $DB_NAME"
docker exec -i $DB_CONTAINER psql -U $DB_USER --dbname=postgres <<EOF
ALTER DATABASE "$DB_NAME" OWNER TO "$DB_USER";
GRANT ALL PRIVILEGES ON DATABASE "$DB_NAME" TO "$DB_USER";
GRANT ALL PRIVILEGES ON SCHEMA public TO "$DB_USER";
EOF

if [ $? -ne 0 ]; then
  echo "Error: Failed to grant permissions."
  exit 1
fi

echo "Database reset and permissions granted successfully!"