Postgresql 13 to 16 - db issue

1

Hi,

I am trying to migrate from one machine A to machine B. Machine A is running postgresql 13 while Machine B is running postgresql 16. I dumped the indico database on machine A and restored it on Machine B.

When I do indico db version, I have the following error:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.InsufficientPrivilege) permission denied for table alembic_version

Any gotchas between migrate from the old database to the new one? It is probably some permission thing. Please advise. Thank you very much.

Jacky

2

Hi,

I following another method from the post, Migrating from 2.3.3 on CentOS 7 > Ubuntu, to migrate the DB.
I don’t have the permission denied error. I am not sure why that db migration works without the error.

3

Without the commands you used I can’t give any advice regarding how to deal with the errors you got. Anyway. it sounds like your problem resolved itself when you used the second method.

4

On Machine A (with postgresql 13):

pg_dump indico -Fc -f backup.dump

copy it to Machine B (with postgresql 16):

su - postgres -c ‘createuser indico’
su - postgres -c ‘createdb -O indico indico’
su - postgres -c ‘psql indico -c “CREATE EXTENSION unaccent; CREATE EXTENSION pg_trgm;”’
su - postgres -c ‘pg_restore -d indico -O backup.dump’

I would like to know why this way didn’t work but the other way works.

Jacky

5

You ran pg_restore as the postgres superuser but specified -O to skip ownership changes.

Either run it as indico (then the owner is correct anyway) or do not use -O because a superuser can change the ownership to the correct (indico) user.

6

Thank you! I dropped the indico db. Ran the whole procedures again without the -O works now.