RhodeCode CE/EE

That is odd and unexpected. I'm assuming repositories table is in a bad state somehow.

Can you check on your postgres if repositories (repo_id) column is unique?

Was there any upgrade problems in older days related to database?

Best,

Somehow this got into SPAM:

Postgres has unique repo_id for each row but they are not sequential:

rhodecode=> SELECT repo_id FROM repositories;

repo_id

---------

       1
       3
       6
       7
       9
      10
      11
      12
      13
      14
      15
      19
      20
      21
      22
      23
      24
      26
      27
      28
      30
      32
      33
      34
      35
      36
      37
      38
      39
      29

(30 rows)


##### mysql to postgres migration steps:

# yum install pgloader-3.4.1-3.rhel7.x86_64.rpm

# su - rhodecode

$ cat<<EOF>>migration_command

load database

    from mysql://rhodecode:see-my.cnf-for-this-password@localhost:3306/rhodecode

    into postgresql://rhodecode:see-pgpass-for-this-password@127.0.0.1/rhodecode



WITH truncate,

     create no tables,

     create no indexes,

     reset no sequences



ALTER schema 'rhodecode' rename to 'public'



CAST type datetime to timestamp

;

EOF

$ ssh -L 3306:127.0.0.1:3306 username_mgr@mysqlhostname -N -f

$ scl enable rh-postgresql96 -- pgloader --dry-run migration_command

## make sure connection to both databases was successful

$ scl enable rh-postgresql96 -- pgloader migration_command

$ mv /tmp/pgloader/pgloader.log .

## make sure the data looks reasonable and the license_key made it

$ scl enable rh-postgresql96 -- psql rhodecode

rhodecode=> SELECT app_settings_value FROM rhodecode_settings WHERE app_settings_name = 'license_key';



## Fixup the database sequencing

$ scl enable rh-postgresql96 -- psql rhodecode

DO

$$

DECLARE

    seqname text;

    nspname text;

   seqval bigint;

BEGIN

    FOR nspname, seqname IN select n.nspname, c.relname FROM

pg_class c INNER JOIN pg_namespace n ON (c.relnamespace = n.oid)

WHERE c.relkind = 'S' AND n.nspname = 'public' LOOP

        EXECUTE format('SELECT last_value FROM %I.%I', nspname, seqname) INTO seqval;

        PERFORM setval(quote_ident(nspname)||'.'||quote_ident(seqname), seqval + 10000);

    END LOOP;

END;

$$ LANGUAGE plpgsql;

\q

Can you check please if repo_id has an UNIQUE index set for Postgres? This is a requirement for this to continue, maybe the migration didn't convert that properly?

Is this what you are looking for:

rhodecode=> \d repositories_repo_id_seq
Sequence "public.repositories_repo_id_seq"
Column | Type | Value

---------------+---------+--------------------------
sequence_name | name | repositories_repo_id_seq
last_value | bigint | 10001
start_value | bigint | 1
increment_by | bigint | 1
max_value | bigint | 9223372036854775807
min_value | bigint | 1
cache_value | bigint | 1
log_cnt | bigint | 0
is_cycled | boolean | f
is_called | boolean | t
Owned by: public.repositories.repo_id

Can you show output as in this example:

rhodecode=# select * from pg_indexes where tablename = 'repositories';
 schemaname |  tablename   |            indexname            | tablespace |                                                indexdef
------------+--------------+---------------------------------+------------+---------------------------------------------------------------------------------------------------------
 public     | repositories | repositories_pkey               |            | CREATE UNIQUE INDEX repositories_pkey ON public.repositories USING btree (repo_id)
 public     | repositories | repositories_repo_name_hash_key |            | CREATE UNIQUE INDEX repositories_repo_name_hash_key ON public.repositories USING btree (repo_name_hash)
 public     | repositories | r_repo_name_idx                 |            | CREATE INDEX r_repo_name_idx ON public.repositories USING btree (repo_name)
(3 rows)

If you're missing any of those, please create as in indexdef example.

Stephen -

The create no indexes refers to the old database that is a source of data.

I believe that if you have a NEW database created it comes with all indexes and requirements meet. Then Pgloader just transfers the data.
Would you have any idea why the creation of initial database would lost indexes?

I wonder if maybe we should try to do a PG -> PG migration to get the indexes back in place?