postgres schema does not exist

Question

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 .../[email protected]/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

If you're issuing the commands from psql, add a line with \c gitea before CREATE SCHEMA gitea; to connect to the new db and create the schema there. select * from information_schema.schemata to see schemas available to you in the db you're currently connected to. — Zegarek
– Zegarek, Commented Jun 5, 2023 at 9:00
@Zegarek Is it valid to add \c gitea to a sql file, which includes the CREATE commands shown in my post? — user3142695
– user3142695, Commented Jun 5, 2023 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
– Zegarek, Commented Jun 5, 2023 at 12:17
@Zegarek I'm asking because I need to add it to a initial script like it is done here: stackoverflow.com/a/69676168/3142695 — user3142695
– user3142695, Commented Jun 5, 2023 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
– Belayer, Commented Jun 5, 2023 at 16:50

SebDieBln · Accepted Answer · 2023-06-05 08:50:20Z

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.

answered Jun 5, 2023 at 8:50

SebDieBln

3,4891 gold badge12 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Adrian Klaver Over a year ago

Changing databases within a connection is not possible in PostgreSQL. is not true. See dblink and postgres_fdw.

Collectives™ on Stack Overflow

postgres schema does not exist

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related