0

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?

Phronux
  • 125
  • 1
  • 9

1 Answers1

3

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.

atomic77
  • 178
  • 1
  • 3