Migrate permission error

Alas, nothing I do in Libretime setup is free of glitches. Since I can’t figure out how to secure the Icecast output given the certificates I have, I’ve turned my attention to migrating data from Libretime Alpha.8 to 3.0.1. I dumped and restored both the /srv/airtime and airtime sql to the new server, and updated config.yml to use these storage locations and db names. I ran install again, then did sudo -u libretime libretime-api migrate (not sure if I was supposed to replace part of that command with airtime references) but it ran until it got:

  Applying legacy.0001_initial...Traceback (most recent call last):
  File "/usr/local/lib/python3.8/dist-packages/django/db/backends/utils.py", line 82, in _execute
    return self.cursor.execute(sql)
psycopg2.errors.InsufficientPrivilege: permission denied for table cc_pref

The command I used to restore the db is:

sudo -u postgres pg_restore --clean --no-acl --no-owner -C -d postgres airtime.sql

Full trace back:

Traceback (most recent call last):
  File "/usr/local/bin/libretime-api", line 8, in <module>
  File "/usr/local/lib/python3.8/dist-packages/libretime_api/manage.py", line 20, in main
  File "/usr/local/lib/python3.8/dist-packages/django/core/management/__init__.py", line 419, in execute_from_command_line
  File "/usr/local/lib/python3.8/dist-packages/django/core/management/__init__.py", line 413, in execute
  File "/usr/local/lib/python3.8/dist-packages/django/core/management/base.py", line 354, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/usr/local/lib/python3.8/dist-packages/django/core/management/base.py", line 398, in execute
    output = self.handle(*args, **options)
  File "/usr/local/lib/python3.8/dist-packages/django/core/management/base.py", line 89, in wrapped
    res = handle_func(*args, **kwargs)
  File "/usr/local/lib/python3.8/dist-packages/django/core/management/commands/migrate.py", line 244, in handle
    post_migrate_state = executor.migrate(
  File "/usr/local/lib/python3.8/dist-packages/django/db/migrations/executor.py", line 117, in migrate
    state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
  File "/usr/local/lib/python3.8/dist-packages/django/db/migrations/executor.py", line 147, in _migrate_all_forwards
    state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
  File "/usr/local/lib/python3.8/dist-packages/django/db/migrations/executor.py", line 227, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/usr/local/lib/python3.8/dist-packages/django/db/migrations/migration.py", line 126, in apply
    operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
  File "/usr/local/lib/python3.8/dist-packages/django/db/migrations/operations/special.py", line 190, in database_forwards
    self.code(from_state.apps, schema_editor)
  File "/usr/local/lib/python3.8/dist-packages/libretime_api/legacy/migrations/0001_initial.py", line 14, in create_schema
    schema_version = get_schema_version()
  File "/usr/local/lib/python3.8/dist-packages/libretime_api/legacy/migrations/_migrations.py", line 20, in get_schema_version
    cursor.execute("SELECT valstr FROM cc_pref WHERE keystr = 'schema_version'")
  File "/usr/local/lib/python3.8/dist-packages/django/db/backends/utils.py", line 66, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/usr/local/lib/python3.8/dist-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/usr/local/lib/python3.8/dist-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.8/dist-packages/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/usr/local/lib/python3.8/dist-packages/django/db/backends/utils.py", line 82, in _execute
    return self.cursor.execute(sql)
django.db.utils.ProgrammingError: permission denied for table cc_pref

I think you should stick as much as possible to the defaults if you are unsure about what you are doing.

You will also find some useful commands in the documentation: Backup | LibreTime

I recommend against using the postgres database, use the one created by the installer (libretime) which is also the one the libretime database user (also created by the installer) has access too.

So you should probably:

Can you elaborate how to “restore the database (in the database created by the installer)”? What would be an example restore command if I did the backup with “sudo -u postgres pg_dump --file=airtime.sql -Fc airtime”?

That restore command gave me an error message when I first tried it leading me into the rabbit hole I’ve put myself. I’ll restore the database back to the 3.0.1 initial state and try again.

Here are the 2 commands you could also use:

Backup the previous airtime database into a plain text SQL file:

sudo -u postgres pg_dump --no-owner --no-privileges airtime > airtime.sql

Restore into the empty libretime database:

sudo -u libretime libretime-api dbshell < airtime.sql

The libretime-api dbshell command is a shortcut to the psql command, and automatically passes the database access details (e.g. database name, user, password).

Hope this helps,
I’ve updated the backup docs to be more “copy-pastable”.