Documentation - Postgres Backup/Restore Steps are incomplete

[Jerksen]

Summary

I was migrating my docker-based nextcloud instance to new hardware and found that the documentation was missing some steps to backup and restore the user that owns the nextcloud tables, which is required to restore the nextcloud database.

My instance details

• Nextcloud 22.2.3 running on the linuxserver.io container
• Postgres 13.1 running on the stander dockerhub image
• Nginx 1.20.2 running on the linuxserver.io swag container

The problem

• I backed up the database using docker-compose exec nextcloud-db pg_dump nextcloud -U nextcloud > /tmp/nc-db.dump
• I tried to restore the database on the new machine using docker-compose exec nextcloud-db psql -U nextcloud -d nextcloud -f nc-db.dump
• The restore command returned role "oc_jeff4" does not exist after most of the commands, due to tailing OWNER TTO oc_jeff4 statements when the restoration script is creating tables.

My solution:

1. Access the old and new servers using pgadmin4
2. copy the SQL statement for the user on the old server
3. run a query on the new server with the copied SQL
4. restore the dump
5. success!

I'm not an advanced postgres user and there is probably a better way to do it. Seems like pg_dumpall -U nextcloud -v --globals-only > /path/to/useraccts.sql is the best way to do this via the command line (like the rest of the backup/restore docs) but it looks like this will dump all the users and some filtering may be needed? E.g. should I parse this list down to only include my oc_jeff4 user?

[pohuing]

Even if we don't know the exact steps right now. This is crucial to be ware of and should at least be noted in the docs. Probably also a note to test if you can restore your backups and get an instance running off it.

This is the second instance backup of mine that's now failing due to docs/issues in the stack(first is mariadb crashing while ingesting a dump) and I can't imagine many hobby users would be aware of the role issue in postgres. I'm honestly surprised not to see more complains about loss of data while trying to restore a backup.