Initial database not created and init script refuses to run

[danielo515]

With this basic example, I can't get postgres to contain the expected database I want.

        process-compose."default" = {config, ...}: let
          dbuser = "postgres";
          dbpass = "postgres";
          dbName = "example";
        in {
          imports = [
            inputs.services-flake.processComposeModules.default
          ];
          services.postgres.pg1 = {
            enable = true;
            superuser = dbuser;
            initialScript.before = ''
              CREATE USER ${dbuser} WITH SUPER PASSWORD '${dbpass}';
            '';
            initialDatabases = [
              {
                name = dbName;
                schemas = [];
              }
            ];
          };

First time I run it, process-compose exits immediately, next time I try to start it, the pgint script refuses to run:

+ export PGDATA=./data/pg1                                                                                        ║│
│║+ PGDATA=./data/pg1                                                                                               ║│
│║+ export PGPORT=5432                                                                                              ║│
│║+ PGPORT=5432                                                                                                     ║│
│║+ POSTGRES_RUN_INITIAL_SCRIPT=false                                                                               ║│
│║+ [[ ! -d ./data/pg1 ]]                                                                                           ║│
│║+ echo 'Setting up postgresql.conf'                                                                               ║│
│║Setting up postgresql.conf                                                                                        ║│
│║+ cp /nix/store/1cz3y8b8fbdysx51afappcjga2w3yaaw-postgresql.conf ./data/pg1/postgresql.conf                       ║│
│║+ [[ false = \t\r\u\e ]]                                                                                          ║│
│║+ echo                                                                                                            ║│
│║                                                                                                                  ║│
│║PostgreSQL database directory appears to contain a database; Skipping initialization                              ║│
│║                                                                                                                  ║│
│║+ echo 'PostgreSQL database directory appears to contain a database; Skipping initialization'                     ║│
│║+ echo                                                                                                            ║│
│║+ unset POSTGRES_RUN_INITIAL_SCRIPT                                                                               ║│
│║

[srid]

First time I run it, process-compose exits immediately

Do you have logs from the first run? The log you pasted above seems to be from second run.

[danielo515]

Maybe it has something to do with an invalid initialScript.before?

› PC_DISABLE_TUI=true nix run

warning: Git tree '/Users/danielo/GIT/whatsapp-family' is dirty
+ PC_CONFIG_FILES=/nix/store/vgbhi6srsjqmhmmjk0zl4ll8fq23n80v-process-compose-default.json
+ process-compose --no-server
[pg1-init       ] + export PGDATA=./data/pg1
[pg1-init       ] + PGDATA=./data/pg1
[pg1-init       ] + export PGPORT=5432
[pg1-init       ] + PGPORT=5432
[pg1-init       ] + POSTGRES_RUN_INITIAL_SCRIPT=false
[pg1-init       ] + [[ ! -d ./data/pg1 ]]
[pg1-init       ] + initdb --locale=C --encoding=UTF8 -U postgres -D ./data/pg1
[pg1-init       ] The files belonging to this database system will be owned by user "danielo".
[pg1-init       ] This user must also own the server process.
[pg1-init       ]
[pg1-init       ] The database cluster will be initialized with locale "C".
[pg1-init       ] The default text search configuration will be set to "english".
[pg1-init       ]
[pg1-init       ] Data page checksums are disabled.
[pg1-init       ]
[pg1-init       ] creating directory data/pg1 ... ok
[pg1-init       ] creating subdirectories ... ok
[pg1-init       ] selecting dynamic shared memory implementation ... posix
[pg1-init       ] selecting default "max_connections" ... 100
[pg1-init       ] selecting default "shared_buffers" ... 128MB
[pg1-init       ] selecting default time zone ... Europe/Madrid
[pg1-init       ] creating configuration files ... ok
[pg1-init       ] running bootstrap script ... ok
[pg1-init       ] performing post-bootstrap initialization ... ok
[pg1-init       ] initdb: warning: enabling "trust" authentication for local connections
[pg1-init       ] initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.
[pg1-init       ] syncing data to disk ... ok
[pg1-init       ]
[pg1-init       ]
[pg1-init       ] Success. You can now start the database server using:
[pg1-init       ]
[pg1-init       ]     pg_ctl -D ./data/pg1 -l logfile start
[pg1-init       ]
[pg1-init       ] + POSTGRES_RUN_INITIAL_SCRIPT=true
[pg1-init       ] + echo
[pg1-init       ]
[pg1-init       ] PostgreSQL initdb process complete.
[pg1-init       ]
[pg1-init       ] + echo 'PostgreSQL initdb process complete.'
[pg1-init       ] + echo
[pg1-init       ] + echo 'Setting up postgresql.conf'
[pg1-init       ] Setting up postgresql.conf
[pg1-init       ] + cp /nix/store/1cz3y8b8fbdysx51afappcjga2w3yaaw-postgresql.conf ./data/pg1/postgresql.conf
[pg1-init       ] + [[ true = \t\r\u\e ]]
[pg1-init       ] + echo
[pg1-init       ] + echo 'PostgreSQL is setting up the initial database.'
[pg1-init       ]
[pg1-init       ] PostgreSQL is setting up the initial database.
[pg1-init       ] + echo
[pg1-init       ]
[pg1-init       ] ++ mktemp -d /tmp/pg-init-XXXXXX
[pg1-init       ] + PGHOST=/tmp/pg-init-bowXvU
[pg1-init       ] + export PGHOST
[pg1-init       ] + trap 'remove_tmp_pg_init_sock_dir "$PGHOST"' EXIT
[pg1-init       ] + trap 'pg_ctl -D "$PGDATA" -m fast -w stop' EXIT
[pg1-init       ] + pg_ctl -D ./data/pg1 -w start -o '-c unix_socket_directories=/tmp/pg-init-bowXvU -c listen_addresses= -p 5432'
[pg1-init       ] waiting for server to start....2025-02-24 22:58:37.255 GMT [68780] LOG:  starting PostgreSQL 17.2 on aarch64-apple-darwin23.6.0, compiled by clang version 19.1.5, 64-bit
[pg1-init       ] 2025-02-24 22:58:37.255 GMT [68780] LOG:  listening on Unix socket "/tmp/pg-init-bowXvU/.s.PGSQL.5432"
[pg1-init       ] 2025-02-24 22:58:37.257 GMT [68783] LOG:  database system was shut down at 2025-02-24 22:58:37 GMT
[pg1-init       ] 2025-02-24 22:58:37.259 GMT [68780] LOG:  database system is ready to accept connections
[pg1-init       ]  done
[pg1-init       ] server started
[pg1-init       ] + echo 'CREATE USER postgres WITH SUPER PASSWORD '\''postgres'\'';
[pg1-init       ] '
[pg1-init       ] + psql_with_args -d postgres
[pg1-init       ] + psql -U postgres -v ON_ERROR_STOP=1 -d postgres
[pg1-init       ] 2025-02-24 22:58:37.362 GMT [68792] ERROR:  unrecognized role option "super" at character 27
[pg1-init       ] 2025-02-24 22:58:37.362 GMT [68792] STATEMENT:  CREATE USER postgres WITH SUPER PASSWORD 'postgres';
[pg1-init       ] ERROR:  unrecognized role option "super"
[pg1-init       ] LINE 1: CREATE USER postgres WITH SUPER PASSWORD 'postgres';
[pg1-init       ]                                   ^
[pg1-init       ] + pg_ctl -D ./data/pg1 -m fast -w stop
[pg1-init       ] waiting for server to shut down....2025-02-24 22:58:37.364 GMT [68780] LOG:  received fast shutdown request
[pg1-init       ] 2025-02-24 22:58:37.364 GMT [68780] LOG:  aborting any active transactions
[pg1-init       ] 2025-02-24 22:58:37.365 GMT [68780] LOG:  background worker "logical replication launcher" (PID 68786) exited with exit code 1
[pg1-init       ] 2025-02-24 22:58:37.365 GMT [68781] LOG:  shutting down
[pg1-init       ] 2025-02-24 22:58:37.365 GMT [68781] LOG:  checkpoint starting: shutdown immediate
[pg1-init       ] 2025-02-24 22:58:37.366 GMT [68781] LOG:  checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.001 s, sync=0.001 s, total=0.001 s; sync files=2, longest=0.001 s, average=0.001 s; distance=0 kB, estimate=0 kB; lsn=0/1507C40, redo lsn=0/1507C40
[pg1-init       ] 2025-02-24 22:58:37.367 GMT [68780] LOG:  database system is shut down
[pg1-init       ]  done
[pg1-init       ] server stopped

[danielo515]

Yes, that was indeed the problem.
I think that a trap should be set that, in case the init script fails for any reason, it wipes the data directory.
Or at least, somehow show the real error that crashes the whole thing, or a pre-validation step. Not sure, I'm new to nix and process compose 😅

[danielo515]

what if I want to add an additional database? Will I need to delete the existing one in order for the init script to run?

[shivaraj-bh]

what if I want to add an additional database? Will I need to delete the existing one in order for the init script to run?

As of now, yes. I can open an issue to make the init script smarter. I don’t think it should be hard to support this.

Edit: Created #468