Migrate instance

Administrators
#1

Hi everyone,

I’ve installed a new Indico 2.0 istance an my organization datacenter, but now I need to move to another one. I’d like to know of there is any way migrate the database, from the old istance to the new one, or I’ve to just dump and restore it.
At same how to proceed for the customization I did on the old one.

Thanks in advace.

Migrating data to new instace
#2

Just reinstall it on the new machine but instead of doing the setup wizard etc., restore a database dump (pg_restore / pg_dump), copy the config (/opt/indico/etc) and materials/attachments (/opt/indico/archive, possibly the legacy archive folder as well if you migrated from 1.2).

Regarding customizations, if you used the CUSTOMIZATION_DIR, simply copy that folder as well. If you modified the code/templates directly inside the virtualenv… well, let’s hope you didn’t :wink:

#3

Thanks for the answer @ThiefMaster, but

I already did the setup wizard :confounded: damn…
What can I do?

#4

No problem, running it doesn’t do any harm (and you actually don’t have to manually create some of the other directories like tmp, cache and assets!). Just restore your database dump now and copy the old config (or compare the two to see if there are any differences)

#5

Ok,tnx again I’ll do it and let you know

#6

I finally found some time to try to restore the database, but it doesn’t work, following what i did.

In the old machine
pg_dump indico > /tmp/indico_db.bak

then i moved in the new one, where I ran:
psql indico < /tmp/indico_db.bak &> file.log
But many errors like ...already exists...

I tried also in the old machine
pg_dump -Fc indico > /tmp/indico.db
In the new machine
pg_restore -v -c -1 -d indico /tmp/indico.db &> file.log
In this last case I get

pg_restore: [archiver (db)] Error from TOC entry 4010; 1259 16891 INDEX ix_affiliations_name_unaccent indico
pg_restore: [archiver (db)] could not execute query: ERROR:  function unaccent(unknown, text) does not exist
LINE 1: SELECT unaccent('unaccent', value)
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:  SELECT unaccent('unaccent', value)
CONTEXT:  PL/pgSQL function indico.indico_unaccent(text) line 3 at RETURN
    Command was: CREATE INDEX ix_affiliations_name_unaccent ON users.affiliations USING gin (indico.indico_unaccent(lower((name)::text)) publ...

Even though the unaccent extension should be exist, buecause in the installation process there is su - postgres -c 'psql indico -c "CREATE EXTENSION unaccent; CREATE EXTENSION pg_trgm;"'

Any hint on that?
Thanks in advance.

#7

Very strange. These are the commands we use all the time and it works fine (on a database that has the two extensions of course):

pg_dump indico -Fc -f backup.dump
pg_restore -d indico -O backup.dump
1 Like
#8

I managed to migrate successfully the instance in the new location. Thanks a lot for the support.

To get i t works, I had to drop the old indico database and recreate it, then I created the extensions again, so I was able to restore the correct DB successfully.

Thanks again.

1 Like
#9

Hi, I have also the need of coping instances between different environments (prod, qa, dev…).

We cloned an instance between prod and qa using VMWare tools. Everything was fine excepting the registration forms of an event that were lost.

So we have tried the steps shown above to fix the problem…after droping and restoring the database, not running the setup wizard, I’m getting the following error in the log file:

ProgrammingError: (psycopg2.ProgrammingError) permission denied for schema indico
[SQL: ‘SELECT indico.settings.id AS indico_settings_id, indico.settings.module AS indico_settings_module, indico.settings.name AS indico_settings_name, indico.settings.value AS indico_settings_value \nFROM indico.settings \nWHERE indic$
2019-01-14 18:14:01,441 ERROR celery.app.trace Task event_reminders[f628b4af-09aa-43be-b508-adeac75ae0d7] raised unexpected: ProgrammingError(’(psycopg2.ProgrammingError) permission denied for schema events\nLINE 2: FROM ev…

Any clue what could be missing???.

Thanks in advance for your advice.

DdM

#10

Make sure to restore the backup as the same user indico uses to access the database (usually a user named indico). That way it’s guaranteed that the user will own everything in the DB.

#11

Thank you I’ll give a try!. Best!

#12

Just as a note:
We have also faced the case of copying a db of one instance to another, and

didn’t work. When we followed this method, the permission denied for schema message still came up.

The method we took instead was:
(when moving the db from A to B)

$ su - indico 
$ pg_dump indico -Fc -f <the-dump-file>.dump
 (move the dump file to B)
  • at B: restore the DB as a postgres user
$ su - postgres
$ pg_restore -d indico -O <the-dump-file>.dump

At this point, the table and schema were all owned by the postgres user, so we changed the ownership in the following way:

$ su - postgres
$ psql indico
$ SELECT 'ALTER TABLE ' || schemaname || '.' || tablename || ' OWNER TO indico;' FROM pg_tables WHERE tableowner ='postgres';

This generated many (200 for us) lines of commands that look like

ALTER TABLE categories.principals OWNER TO indico;

so by executing all of them, their ownerships could be changed. Note that some of the commands (55 for us) did not work due to some permission errors, but it was fine to ignore them. Then we did the same thing for another set of commands generated by

$ SELECT DISTINCT 'ALTER SCHEMA ' || schemaname || ' OWNER TO indico;' FROM pg_tables WHERE tableowner ='postgres';

After that the permission denied for schema error didn’t appear any more.
The normal DB should work with just this.

#13

pg_restore with the -O switch should be fine, we use it all the time ourselves. Getting “permission denied” for changing some ACLs or comments on plpgsql is perfectly fine in that case (there are just a few of those).

The risk when restoring as superuser is that you might still end up with some things being owned by postgres instead of the Indico user. Sequences and functions for example. That might be problematic during future migrations which would then fail if the indico user cannot ALTER them.

#14

The amount of confusion on this topic actually demands for its own documentation page. I will try doing it on my PC. If I get it working, I’ll share the documentation.

#15

I get the following error upon typing

pg_restore -d indico -O indico17022022.dump

Error

pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 5868; 0 0 COMMENT EXTENSION pg_trgm 
pg_restore: error: could not execute query: ERROR:  must be owner of extension pg_trgm
Command was: COMMENT ON EXTENSION pg_trgm IS 'text similarity measurement and index searching based on trigrams';


pg_restore: from TOC entry 5869; 0 0 COMMENT EXTENSION unaccent 
pg_restore: error: could not execute query: ERROR:  must be owner of extension unaccent
Command was: COMMENT ON EXTENSION unaccent IS 'text search dictionary that removes accents';


pg_restore: warning: errors ignored on restore: 2

Should I consider this as normal?

#16

Yes, you can and should ignore that error. It’s completely normal because only a superuser would be able to set these comments (which are completely irrelevant anyway).

1 Like