0

I'm totally new to postgres. On my postgres db I've created a database:

CREATE ROLE gitea WITH LOGIN PASSWORD 'gitea';
CREATE DATABASE gitea WITH OWNER gitea TEMPLATE template0 ENCODING UTF8 LC_COLLATE 'en_US.UTF-8' LC_CTYPE 'en_US.UTF-8';
CREATE SCHEMA gitea;
GRANT ALL ON SCHEMA gitea TO gitea;
ALTER USER gitea SET search_path=gitea;

Running the gitea application I do get this error, which says schema is not existing. What did I do wrong on the db?

2023/06/05 08:12:13 .../cli@v1.22.10/app.go:277:Run() [I] PING DATABASE postgresschema
2023/06/05 08:12:13 models/db/engine.go:190:InitEngineWithMigration() [I] [SQL] SELECT tablename FROM pg_tables WHERE schemaname = $1 [gitea] - 3.310737ms
2023/06/05 08:12:13 ...ations/migrations.go:518:Migrate() [I] [SQL] CREATE TABLE IF NOT EXISTS "gitea"."version" ("id" BIGSERIAL PRIMARY KEY  NOT NULL, "version" BIGINT NULL);  [] - 512.953µs
2023/06/05 08:12:13 cmd/migrate.go:40:runMigrate() [F] Failed to initialize ORM engine: migrate: sync: pq: schema "gitea" does not exist
user3142695
  • 15,844
  • 47
  • 176
  • 332
  • If you're issuing the commands from [`psql`](https://www.postgresql.org/docs/current/app-psql.html), add a line with [`\c gitea`](https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-META-COMMANDS) before `CREATE SCHEMA gitea;` to connect to the new db and create the schema there. [`select * from information_schema.schemata`](https://www.postgresql.org/docs/15/infoschema-schemata.html) to see schemas available to you in the db you're currently connected to. – Zegarek Jun 05 '23 at 09:00
  • @Zegarek Is it valid to add `\c gitea` to a sql file, which includes the CREATE commands shown in my post? – user3142695 Jun 05 '23 at 12:08
  • Only if it will be interpreted by `psql`. It is a client-specific meta-command. To make it portable, you need to split your script into one that sets up the db operating from on of the pre-existing databases (the top two lines), and another script that's meant to run already within the newly setup db (3rd line onwards). Then setup the client to open a separate session for the second script. – Zegarek Jun 05 '23 at 12:17
  • @Zegarek I'm asking because I need to add it to a initial script like it is done here: https://stackoverflow.com/a/69676168/3142695 – user3142695 Jun 05 '23 at 13:29
  • There is no need for `\c` in your sql file, you can specify both database and user directly as: `psql -d gitea -U gitea ...` – Belayer Jun 05 '23 at 16:50

1 Answers1

0

You created the schema in the current database (probably postgres), not in the one you just created. After creating the new database you need to close the connection and open a new connection to the new database. Only then can you create a schema in the new database.

Changing databases within a connection is not possible in PostgreSQL.

SebDieBln
  • 3,303
  • 1
  • 7
  • 21
  • `Changing databases within a connection is not possible in PostgreSQL.` is not true. See [dblink](https://www.postgresql.org/docs/current/dblink.html) and [postgres_fdw](https://www.postgresql.org/docs/current/postgres-fdw.html). – Adrian Klaver Jun 05 '23 at 14:57