Struggling to use initialScript + ensureDatabases + ensureUsers (postgresql) #109273
Description
Hello, first time writing here I think. So, thank you all for nixos, love it.
The problem: I'm trying to migrate my workflow to nixops, but I'm struggling to correctly setup the database at nixops deploy. As an example of what I need to accomplish, this prototype code works fine except when I plug my real database.sql, It's 8mb so the formating/concatenation causes a stack overflow:
services.postgresql = {
enable = true;
package = pkgs.postgresql_10;
enableTCPIP = true;
authentication = pkgs.lib.mkOverride 10 ''
local all all trust
host all all ::1/128 trust
'';
initialScript = let
database = pkgs.lib.fileContents ./database.sql;
in
pkgs.writeText "backend-initScript" ''
CREATE ROLE datastore WITH LOGIN PASSWORD 'secret' CREATEDB;
CREATE DATABASE datastore;
GRANT ALL PRIVILEGES ON DATABASE datastore TO datastore;
\c datastore;
${database}
'';
};
I could use initialScript = ./database.sql; instead, and put create/grant/connect inside database.sql. But that file is generated by pg_dump and it's not supposed to be touched by humans in this case. So I'm trying to use this instead:
initialScript = ./database.sql;
ensureDatabases = ["datastore"];
ensureUsers = [{
name = "datastore";
ensurePermissions = {
"DATABASE datastore" = "ALL PRIVILEGES";
};
}];
But, it seems that nix is calling the initialScript before creating the database. So for example if I put \c datastore; in database.sql, nixops deploy fails:
postgres[1180]: [1180] FATAL: database "datastore" does not exist
postgresql-post-start[1178]: psql:/nix/store/qmsfm1dag0mmsgqm5h024787w91di9p5-database.sql:5: \connect: FATAL: database "datastore" does not exist
It works if I comment initialScript = ./database.sql, the (empty..) database exists on the target machine and then I can manually upload and evaluate database.sql but I'm trying to avoid doing that. If possible, how could this be automated at nixops deploy?
Btw, I'm on nixos 20.09 + unstable. It seems that all this is being addresed or refactored by #107342 but I wonder how people manages to get it working as for now.
Thanks.