Initialization script when PGData is not empty

[tomera-cyera]

I know that when the PGData directory is not empty the container skips the initialization and therefore won't run the initialization script in /docker-entrypoint-initdb.d

I would like to use a way to give the container scripts that will run on startup no matter what (for example to create a role postgres if one does not exist already)

[tianon]

If you want a solution that does something with a started database (like our existing initdb scripts do), see https://github.com/docker-library/postgres/issues/173, https://github.com/docker-library/postgres/issues/821, https://github.com/docker-library/postgres/issues/191, https://github.com/docker-library/postgres/issues/929 for some other issues that have some good thoughts / suggestions (one of which this is probably a duplicate of).

[tomera-cyera]

Hi @tianon! Thanks for your response. I managed to run an init script on my db but it fails with role "postgres" does not exist (it was probably deleted). I don't know what roles the db has and I want to use the init script to verify the postgres role exists (and if not to create one).

[LaurentGoderre]

You can run

docker exec -u postgres -it postgres psql    
psql (16.2 (Debian 16.2-1.pgdg120+2))
Type "help" for help.

postgres=# \du
                             List of roles
 Role name |                         Attributes                         
-----------+------------------------------------------------------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS

[tomera-cyera]

Hi @LaurentGoderre unfortunately your solution would not work in my case because there is no role postgres so I'm unable to execute any command with -u postgres, that's why I would like to init the db with a postgres role creation

[LaurentGoderre]

@tomera-cyera I believe postgres in this case refers to the OS user which is postgres not the db role.

[tomera-cyera]

No, I refer to the postgres db role in case I initialize the container with data-dir that does not have the role postgres I want to make sure the postgres role will be created if not exist @LaurentGoderre

[LaurentGoderre]

OOh you mean at initialization?

[tomera-cyera]

@LaurentGoderre, yes. you see I need to initialize the db to have a postgres role even when the datadir already exists (a.k.a the db already initialized)

[yuvall-cyera]

@tianon @LaurentGoderre what do you think?

[tianon]

@yuvall-cyera can you please be more specific about what it is you're looking for feedback on? IMO the thread above is pretty clear (if you need one-time initialization, the image has that as-is, and if you need more than that, see the references in https://github.com/docker-library/postgres/issues/1229#issuecomment-2064636436)