Postgres Database Backup

ShiroG · March 8, 2020, 10:33pm

I am trying to get a working backup solution for Nextcloud just in case something happens. I have Nextcloud running behind a reverse proxy with docker-compose on an Ubuntu Server. Creating a backup for the data, or config folder is not the problem but I can’t figure out how to create a backup for my Postgres database. I have very little experience working with databases so I might be missing something trivial.

My docker-compose file looks like this

  version: '3.7'
  
  services:
    db_next:
      image: postgres:alpine
      restart: always
      volumes:
        - db_next:/var/lib/postgresql/data
      env_file:
        - ./env/nextcloud/.env.db

nextcloud:
  image: nextcloud:apache
  restart: always
  volumes:
    - nextcloud:/var/www/html/
    - nextcloud_data:/var/www/html/data
    - nextcloud_config:/var/www/html/config
    - nextcloud_apps:/var/www/html/custom_apps
  ports:
    - 8080:80
  env_file:
    - ./env/nextcloud/.env
  depends_on:
    - db_next
  networks:
    - proxy-tier
    - default

 volumes:
   db_next:
   nextcloud:
   nextcloud_data:
     driver: local
     driver_opts:
       type: none
       device: /mnt/cloud1/nextcloud/data
       o: bind
   nextcloud_config:
     driver: local
     driver_opts:
       type: none
       device: /mnt/cloud1/nextcloud/config
       o: bind
   nextcloud_apps:
     driver: local
     driver_opts:
       type: none
       device: /mnt/cloud1/nextcloud/apps
       o: bind

Google told me about SQL Dumps so i tried that one first. I already failed when trying to create the Backup file.

docker exec -t -u postgres your-db-container pg_dumpall -c > dump_date +%d-%m-%Y"_"%H_%M_%S.sql

With user postgres I get this error:

pg_dumpall: error: could not connect to database "template1": FATAL:  role "postgres" does not exist

I tried replacing the username with nextcloud since I have defined it as nextcloud in my .env file but then it complains that the user couldn’t be found.

I know that there is a section in the documentation about backup but I really have no idea what to do with that line:

PGPASSWORD="password" pg_dump [db_name] -h [server] -U [username] -f nextcloud-sqlbkp_`date +"%Y%m%d"`.bak

So my question is, how should I backup my database?

Paradox551 · March 8, 2020, 11:24pm

Use the same sql user/password nextcloud is using. If that fails it’s likely a docker issue (port, etc)

ShiroG · March 9, 2020, 7:19pm

You mentioned a password. Where do I specify that? The command itself doesn’t ask for it.

Reiner_Nippes · March 9, 2020, 8:24pm

it should be:

docker exec <nextcloud-db-container-name> pg_dump -c -U <nextcloud-db-user> <nextcloud-db> > /.../nextcloud-db.dump

you’ll find a script here:

github.com

ReinerNippes/nextcloud_on_docker/blob/master/roles/prep_backup/templates/backup_nextcloud.sh.j2

#!/bin/bash

export RESTIC_REPOSITORY="{{ restic_repo }}"
export RESTIC_PASSWORD="{{ restic_backup_secret }}"

# abort entire script if any command fails
set -e

# unlock restic to keep automatic backups going
{{ restic_install_path }}/restic unlock --cleanup-cache

# Make sure nextcloud is enabled when we are done
trap "docker exec -u www-data nextcloud php occ maintenance:mode --off" EXIT

# set nextcloud to maintenance mode
docker exec -u www-data nextcloud php occ maintenance:mode --on

{% if nextcloud_db_type == 'mysql' %}
sudo docker exec nextcloud-db mysqldump --single-transaction -h localhost -u {{ nextcloud_db_user }} -p{{ nextcloud_db_passwd }} {{ nextcloud_db_name }} > {{ nextcloud_database_dump_dir }}/db_dump_{{ nextcloud_db_type }}_nextcloud.sql
{% elif nextcloud_db_type == 'pgsql' %}

This file has been truncated. show original

hope you can read jinja templates. and you need restic install.