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”.

Sorry for the delay, real work keeping me busy. I’m still not making progress, here is the result where airtime.sql is created with the exact command shown on old system, file is copied with scp, then the restore fails:

sudo -u libretime libretime-api dbshell < airtime.sql
psql: error: FATAL: database “airtime” does not exist
CommandError: “psql -U libretime -h localhost -p 5432 airtime” returned non-zero exit status 2.

Hmm, maybe the backup command included the database name (which should not be present), or you changed the database name in the config file, and the database wasn’t created by the installer in the first place.

I’ve just made a database dump, using the exact same command and there is no database name information in it, so I suspect you changed the database configuration in config.yml.

Are you referring to this? The old system (alpha.8), the file /etc/airtime/airtime.conf has this in it:
host = localhost
dbname = airtime
dbuser = airtime
dbpass = airtime

I wouldn’t have created the db outside of the Libretime install.

No, I am referring to the host where the restore error occurs.

The installer script created a libretime database, and automatically sets the password in the config.yml file. This means when running the import command, libretime-api will read this configuration and load the SQL in the newly created database.

If you changed your database name on the new system, you will not be able to use the already created database.

OK I was tinkering with that. Let me make sure it’s back to default…

We’re up and running on 3.0.1 on a new server with all the data moved over. Thanks for bearing with me.