Can't set schema_name in dockerized PostgreSQL database

Question

I'm trying to setup a PostgreSQL Docker Container. I want to create a schema on startup and set search_path to that schema.
I've created an initialization .sql file and it's copied to the /docker-entrypoint-initdb.d/ folder where it's executed by docker-entrypoint.sh file on the container's startup. The entrypoint file comes from the official docker library repo.
This .sql file contains the following code:

CREATE SCHEMA solwa_dev;
SET search_path TO solwa_dev;
-- table creations, sequences, other commands

Everything executes properly, but after the initialization completes search_path is not setup accordingly. If I type this in a terminal:

docker exec -it <docker_container> psql -U postgres -c "show search_path;"

it shows "$user", public". After I use the same command to SET search_path TO solwa_dev; it still shows both $user and public schemas, but no solwa_dev schema!

Why is this happening and how can I solve this problem?

atomic77 · Accepted Answer · 2018-02-12 04:56:53Z

5

Running SET search_path .. will only set the search path for the currently open session.

To set it permanently, you can do so at the role level:

ALTER ROLE <role> SET search_path TO ...

the database level:

ALTER DATABASE <db> SET search_path TO ...

or system-wide with the search_path setting in postgresql.conf

See this answer for more details.

answered Feb 12, 2018 at 4:56

atomic77

1981 silver badge3 bronze badges

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

Collectives™ on Stack Overflow

Can't set schema_name in dockerized PostgreSQL database

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related