Gitea: Docker installation with postgres fails

[Axel-G]

i try to install gitea with docker (latest gitea) + postgres(9.6)
after submitting install site gitea is not reachable.

(docker with sqlite runs)

logs:

2020/03/23 10:50:59 routers/init.go:67:initDBEngine() [I] ORM engine initialization attempt #7/10...
2020/03/23 10:50:59 ...rm/session_schema.go:25:Ping() [I] PING DATABASE postgres
2020/03/23 10:50:59 ...m.io/xorm/core/db.go:150:QueryContext() [I] [SQL] SELECT tablename FROM pg_tables WHERE tablename = $1 [version] - 1.521617ms
2020/03/23 10:50:59 ...m.io/xorm/core/db.go:150:QueryContext() [I] [SQL] SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = $1 AND column_name = $2 [version id] - 4.808637ms
2020/03/23 10:50:59 ...m.io/xorm/core/db.go:150:QueryContext() [I] [SQL] SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = $1 AND column_name = $2 [version version] - 2.256231ms
2020/03/23 10:50:59 ...m.io/xorm/core/db.go:150:QueryContext() [I] [SQL] SELECT "id", "version" FROM "version" WHERE "id"=$1 LIMIT 1 [1] - 684.068µs
2020/03/23 10:50:59 ...m.io/xorm/core/db.go:150:QueryContext() [I] [SQL] SELECT tablename FROM pg_tables [] - 702.936µs
2020/03/23 10:50:59 ...m.io/xorm/core/db.go:150:QueryContext() [I] [SQL] SELECT column_name, column_default, is_nullable, data_type, character_maximum_length,
            CASE WHEN p.contype = 'p' THEN true ELSE false END AS primarykey,
            CASE WHEN p.contype = 'u' THEN true ELSE false END AS uniquekey
        FROM pg_attribute f
            JOIN pg_class c ON c.oid = f.attrelid JOIN pg_type t ON t.oid = f.atttypid
            LEFT JOIN pg_attrdef d ON d.adrelid = c.oid AND d.adnum = f.attnum
            LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
            LEFT JOIN pg_constraint p ON p.conrelid = c.oid AND f.attnum = ANY (p.conkey)
            LEFT JOIN pg_class AS g ON p.confrelid = g.oid
            LEFT JOIN INFORMATION_SCHEMA.COLUMNS s ON s.column_name=f.attname AND c.relname=s.table_name
        WHERE n.nspname= $1 AND c.relkind = 'r'::char AND c.relname = $2 AND s.table_schema = $3 AND f.attnum > 0 ORDER BY f.attnum; [ user ] - 7.527901ms
2020/03/23 10:50:59 ...m.io/xorm/core/db.go:150:QueryContext() [I] [SQL] SELECT indexname, indexdef FROM pg_indexes WHERE tablename=$1 [user] - 1.81561ms
2020/03/23 10:50:59 routers/init.go:73:initDBEngine() [E] ORM engine initialization attempt #7/10 failed. Error: sync database struct error: Unknown col updated_unix seq 0, in index updated_unix of table user, columns []
2020/03/23 10:50:59 routers/init.go:74:initDBEngine() [I] Backing off for 3 seconds

[lafriks]

I just encountered same issue, after deleting indexes it still fails and tries to create columns that already exists:

2020/03/23 16:27:06 ...rm/session_schema.go:25:Ping() [I] PING DATABASE postgres
2020/03/23 16:27:06 ...m.io/xorm/core/db.go:150:QueryContext() [I] [SQL] SELECT tablename FROM pg_tables WHERE tablename = $1 [version] - 4.661716ms
2020/03/23 16:27:06 ...m.io/xorm/core/db.go:150:QueryContext() [I] [SQL] SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = $1 AND column_name = $2 [version id] - 17.156228ms
2020/03/23 16:27:06 ...m.io/xorm/core/db.go:150:QueryContext() [I] [SQL] SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = $1 AND column_name = $2 [version version] - 4.858879ms
2020/03/23 16:27:06 ...m.io/xorm/core/db.go:150:QueryContext() [I] [SQL] SELECT "id", "version" FROM "version" WHERE "id"=$1 LIMIT 1 [1] - 787.79µs
2020/03/23 16:27:06 ...m.io/xorm/core/db.go:150:QueryContext() [I] [SQL] SELECT tablename FROM pg_tables [] - 690.347µs
2020/03/23 16:27:06 ...m.io/xorm/core/db.go:150:QueryContext() [I] [SQL] SELECT column_name, column_default, is_nullable, data_type, character_maximum_length,
        CASE WHEN p.contype = 'p' THEN true ELSE false END AS primarykey,
        CASE WHEN p.contype = 'u' THEN true ELSE false END AS uniquekey
    FROM pg_attribute f
        JOIN pg_class c ON c.oid = f.attrelid JOIN pg_type t ON t.oid = f.atttypid
        LEFT JOIN pg_attrdef d ON d.adrelid = c.oid AND d.adnum = f.attnum
        LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
        LEFT JOIN pg_constraint p ON p.conrelid = c.oid AND f.attnum = ANY (p.conkey)
        LEFT JOIN pg_class AS g ON p.confrelid = g.oid
        LEFT JOIN INFORMATION_SCHEMA.COLUMNS s ON s.column_name=f.attname AND c.relname=s.table_name
    WHERE n.nspname= $1 AND c.relkind = 'r'::char AND c.relname = $2 AND s.table_schema = $3 AND f.attnum > 0 ORDER BY f.attnum; [ user ] - 8.176759ms
2020/03/23 16:27:06 ...m.io/xorm/core/db.go:150:QueryContext() [I] [SQL] SELECT indexname, indexdef FROM pg_indexes WHERE tablename=$1 [user] - 1.623879ms
2020/03/23 16:27:06 ...m.io/xorm/core/db.go:273:ExecContext() [I] [SQL] ALTER TABLE "user" ADD "id" BIGSERIAL PRIMARY KEY  NOT NULL  [] - 1.111688ms
2020/03/23 16:27:06 routers/init.go:102:GlobalInit() [F] ORM engine initialization failed: sync database struct error: pq: column "id" of relation "user" already exists

@lunny most probably related to PR #10646

[lunny]

https://gitea.com/xorm/xorm/pulls/1624 should fix this. I will release xorm v1.0.1 and send PR to gitea ASAP.

[lunny]

@lafriks could you try to set SCHEMA=public?

[lunny]

I found it's also failed with postgres when switch to release/v1.11, anyone has the same problem?

2020/03/24 20:48:08 cmd/web.go:107:runWeb() [I] Starting Gitea on PID: 1536
2020/03/24 20:48:08 ...s/setting/setting.go:861:NewContext() [T] Custom TimeFormat: 2006-01-02 15:04:05
2020/03/24 20:48:08 ...s/setting/setting.go:871:NewContext() [I] Default UI Location is Asia/Shanghai
2020/03/24 20:48:08 ...dules/setting/git.go:91:newGit() [I] Git Version: 2.24.0, Wire Protocol Version 2 Enabled
2020/03/24 20:48:09 routers/init.go:87:GlobalInit() [T] AppPath: /Volumes/data/Projects/gitea/gitea/gitea
2020/03/24 20:48:09 routers/init.go:88:GlobalInit() [T] AppWorkPath: /Volumes/data/Projects/gitea/gitea
2020/03/24 20:48:09 routers/init.go:89:GlobalInit() [T] Custom path: /Volumes/data/Projects/gitea/gitea/custom
2020/03/24 20:48:09 routers/init.go:90:GlobalInit() [T] Log path: /Volumes/data/Projects/gitea/gitea/log
2020/03/24 20:48:09 ...dules/setting/log.go:233:newLogService() [I] Gitea v1.11.2+9-ge2da9cd21 built with GNU Make 3.81, go1.14.1 : bindata, sqlite, sqlite_unlock_notify
2020/03/24 20:48:09 ...dules/setting/log.go:276:newLogService() [I] Gitea Log Mode: Console(Console:trace)
2020/03/24 20:48:09 ...dules/setting/log.go:276:newLogService() [I] Gitea Log Mode: File(File:trace)
2020/03/24 20:48:09 ...les/setting/cache.go:45:newCacheService() [I] Cache Service Enabled
2020/03/24 20:48:09 ...s/setting/session.go:44:newSessionService() [I] Session Service Enabled
2020/03/24 20:48:09 ...ules/setting/cors.go:39:newCORSService() [I] CORS Service Enabled
2020/03/24 20:48:09 ...es/setting/mailer.go:106:newMailService() [I] Mail Service Enabled
2020/03/24 20:48:09 ...es/setting/mailer.go:128:newNotifyMailService() [I] Notify Mail Service Enabled
2020/03/24 20:48:09 routers/init.go:59:initDBEngine() [I] Beginning ORM engine initialization.
2020/03/24 20:48:09 routers/init.go:66:initDBEngine() [I] ORM engine initialization attempt #1/10...
2020/03/24 20:48:09 ...rm.io/xorm/engine.go:351:Ping() [I] PING DATABASE postgres
2020/03/24 20:48:09 .../xorm/session_raw.go:78:queryRows() [I] [SQL] SELECT tablename FROM pg_tables WHERE schemaname = $1 AND tablename = $2 []interface {}{"public", "version"} - took: 5.40574ms
2020/03/24 20:48:09 .../dialect_postgres.go:942:IsColumnExist() [I] [SQL] SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = $1 AND table_name = $2 AND column_name = $3 [public version id]
2020/03/24 20:48:09 .../dialect_postgres.go:942:IsColumnExist() [I] [SQL] SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = $1 AND table_name = $2 AND column_name = $3 [public version version]
2020/03/24 20:48:09 .../xorm/session_raw.go:76:queryRows() [I] [SQL] SELECT "id", "version" FROM "version" WHERE "id"=$1 LIMIT 1 []interface {}{1} - took: 2.205468ms
2020/03/24 20:48:09 routers/init.go:72:initDBEngine() [E] ORM engine initialization attempt #1/10 failed. Error: migrate: get: pq: relation "version" does not exist

[Grimeton]

Hi,

just upgraded to the latest docker image and run into the same problem:

2020/03/24 17:58:40 ...m.io/xorm/core/db.go:150:QueryContext() [I] [SQL] SELECT column_name, column_default, is_nullable, data_type, character_maximum_length,
            CASE WHEN p.contype = 'p' THEN true ELSE false END AS primarykey,
            CASE WHEN p.contype = 'u' THEN true ELSE false END AS uniquekey
        FROM pg_attribute f
            JOIN pg_class c ON c.oid = f.attrelid JOIN pg_type t ON t.oid = f.atttypid
            LEFT JOIN pg_attrdef d ON d.adrelid = c.oid AND d.adnum = f.attnum
            LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
            LEFT JOIN pg_constraint p ON p.conrelid = c.oid AND f.attnum = ANY (p.conkey)
            LEFT JOIN pg_class AS g ON p.confrelid = g.oid
            LEFT JOIN INFORMATION_SCHEMA.COLUMNS s ON s.column_name=f.attname AND c.relname=s.table_name
        WHERE n.nspname= $1 AND c.relkind = 'r'::char AND c.relname = $2 AND s.table_schema = $3 AND f.attnum > 0 ORDER BY f.attnum; [ user ] - 6.744368ms
2020/03/24 17:58:40 ...m.io/xorm/core/db.go:150:QueryContext() [I] [SQL] SELECT indexname, indexdef FROM pg_indexes WHERE tablename=$1 [user] - 2.129323ms
2020/03/24 17:58:40 routers/init.go:73:initDBEngine() [E] ORM engine initialization attempt #7/10 failed. Error: sync database struct error: Unknown col lower_name seq 0, in index lower_name of table user, columns []
2020/03/24 17:58:40 routers/init.go:74:initDBEngine() [I] Backing off for 3 seconds

Went back to 1.11.3 [1], working again.

[1] - https://hub.docker.com/layers/gitea/gitea/1.11.3/images/sha256-34836528de2a1cd5df49ef9b2133e00f522e0f9de5eaa549c3864ced06c223ea?context=explore

[adasauce]

thanks @Grimeton , rolled back to that image and it seems to have come back up.

hoping that 1.11.3 isn't too far off the latest tag that I last pulled a couple weeks ago incase I have migrations applied to my DB that are ahead of it.