Broken database import when migrating server
EDIT: My initial problem is solved, though I would suggest a few modifications in the doc to avoid this issue in the future, please see my last comment.
I was migrating from ServerA to ServerB following https://docs.funkwhale.audio/admin/migration.html#non-docker (both ServerA and ServerB are non-docker setup).
I get problems when trying to load the db.dump
with sudo -u funkwhale pg_restore -d funkwhale db.dump
: pg_restore: [archiver] input file appears to be a text format dump. Please use psql.
Using psql
instead of pg_restore
, I need to:
- create the role
funkwhale
- create the database
funkwhale
- grant the access of
funkwhale
tofunkwhale
following https://docs.funkwhale.audio/installation/external_dependencies.html#database-setup-postgresql:sudo -u postgres psql
CREATE DATABASE "funkwhale"
WITH ENCODING 'utf8';
CREATE USER funkwhale;
GRANT ALL PRIVILEGES ON DATABASE funkwhale TO funkwhale;
Then I am able to import db.dump
: psql -d funkwhale -f db.dump
. It seems to be working, though I get a few errors with the tags, see log below (only the error lines). I ran this as the funkwhale
user:
psql:db.dump:30: ERROR: must be owner of extension plpgsql
psql:db.dump:37: ERROR: permission denied to create extension "citext"
HINT: Must be superuser to create this extension.
psql:db.dump:44: ERROR: extension "citext" does not exist
psql:db.dump:51: ERROR: permission denied to create extension "unaccent"
HINT: Must be superuser to create this extension.
psql:db.dump:58: ERROR: extension "unaccent" does not exist
psql:db.dump:2329: ERROR: type "public.citext" does not exist
LINE 3: name public.citext NOT NULL,
^
psql:db.dump:2332: ERROR: relation "public.tags_tag" does not exist
psql:db.dump:2352: ERROR: relation "public.tags_tag" does not exist
psql:db.dump:3100: ERROR: relation "public.tags_tag" does not exist
psql:db.dump:72967: ERROR: relation "public.tags_tag" does not exist
psql:db.dump:73382: invalid command \.
psql:db.dump:73389: ERROR: syntax error at or near "1"
LINE 1: 1 Alternative 2020-01-23 14:16:15.096892+01
^
psql:db.dump:79064: ERROR: relation "public.tags_tag" does not exist
psql:db.dump:79072: ERROR: relation "public.tags_tag" does not exist
psql:db.dump:81350: ERROR: relation "public.tags_tag" does not exist
So basically, the questions are:
- Should the doc be changed to the
psql
import method (instead ofpg_restore
) - If not, how to dump the db in order to be able to import it with
pg_restore
? - Does this have an influence on the errors during the import regarding the tags?
- How to fix those errors with the tags?
Thanks in advance for your input! Let me know if more input from me is required.
Context
Funkwhale version(s) affected: 1.0.1, non-docker setup