Dockerized postgres: How to define a procedure in a specific database?

Question

I have the following setup for my postgres docker container:

mydir > tree
.
├── Dockerfile
├── proc_createInstallerUser.sql
├── docker-entrypoint-initdb.d
└── init.sql

mydir > cat Dockerfile
FROM postgres
COPY init.sql /docker-entrypoint-initdb.d/
COPY proc_createInstallerUser.sql /docker-entrypoint-initdb.d/

mydir > cat init.sql
CREATE USER appUser WITH LOGIN PASSWORD 'appUser';
CREATE DATABASE testdb OWNER appUser;
GRANT ALL PRIVILEGES ON DATABASE testdb TO appUser;

mydir > cat proc_createInstallerUser.sql
create or replace procedure public.createNewInstallerUser(newUserName varchar, newPassword varchar)
    language plpgsql
as $procedure$
    begin
        raise notice 'Create newInstallerUser() called with arguments % %', newUserName, newPassword;
    end;
$procedure$;
;

Docker faithfully executes my init.sql and proc_createInstallerUser.sql, but my stored procedure is defined in a database named "root". I'd like for it to be placed in my "testdb" database that I create in my init.sql. I tried inserting a "connect to testdb" statement as the first line of my stored procedure, but that's throwing a syntax error. How would I go about defining the procedure in the correct database instead of it defaulting to root?

Mohamed Yasser · Accepted Answer · 2022-05-27 06:34:07Z

1

try running a CMD in your Dockerfile using the second method mentioned here.

answered May 27, 2022 at 6:34

Mohamed Yasser

82212 silver badges28 bronze badges

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

3 Comments

DigitalEye Over a year ago

Thanks. Running it manually via psql from the host works, but I don't have psql in my container to be able to use CMD in my docker file to trigger psql. Are there any alternatives to using psql?

DigitalEye Over a year ago

My bad. The stock postgres image does have psql. So the approach of using CMD with psql will work. But, how would I go about specifying the connection string when the database server is yet to start? If I do CMD PGPASSWORD=<mypassword> psql <myuser> -h 127.0.0.1 -d testdb -f proc_createInstaller.sql, I get an error saying server is not listening on port 5432 on localhost.

Mohamed Yasser Over a year ago

I know how to fix that in docker-compose.yaml, but I will try to figure it out in Docker. In docker-compose, the host name is the service name; so your database doesn't actually run on localhost, it runs on the servicename container. In docker, try to link the container and your local machine using a p flag when running the image. -p 5432:5432 and try again. This should let you communicate with port 5432 on the container through port 5432 on your localhost. The first argument is the port on your local machine, the second is the port on the ocntainer.

Collectives™ on Stack Overflow

Dockerized postgres: How to define a procedure in a specific database?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related