Call to `pg_ctl` fails (exception raised) when creating test database

[jamesfowkes]

In summary, my issue is that when pytest-postgresql tries to use pg_ctl to create the test database, an exception is thrown. I can't see any particular information in the exception that might narrow down the specifics of what is happening.

I am pretty new to using postgres so please forgive any stupidity on my part.

System/environment information:

• Windows 10
• Running in VScode
• Python 3.12.0 virtual environment
• pytest-postgresql version 6.1.1
• pg_ctl --version output is pg_ctl (PostgreSQL) 17.0

As far as I can see from various old issues, running on windows should be supported?

Issue Details
I am using the following code to create my application fixture:

from pytest_postgresql import factories
test_db = factories.postgresql_proc(port=None, dbname="test_db")

@pytest.fixture(scope="session")
def app(test_db):

    pg_host, pg_port, pg_user, pg_pass, pg_dbname = test_db.host, test_db.port, test_db.user, test_db.password, test_db.dbname
    connection_str = f"postgresql+psycopg2://{pg_user}:@{pg_host}:{pg_port}/{pg_dbname}"
    
    _app = create_app({
        "SQLALCHEMY_DATABASE_URI": connection_str
    })

    with _app.app_context():
        db.drop_all()
        db.create_all()

    yield _app

When I try to run a test, an exception is thrown at the point where pg_ctl is invoked to create the database (line 186 of executor.py):

.venv\Lib\site-packages\mirakuru\base.py:180: in __enter__
    return self.start()
.venv\Lib\site-packages\pytest_postgresql\executor.py:147: in start
    self.init_directory()
.venv\Lib\site-packages\pytest_postgresql\executor.py:186: in init_directory
    subprocess.check_output(init_directory, env=self._envvars, stderr=subprocess.STDOUT)
..\..\..\.pyenv\pyenv-win\versions\3.12.0\Lib\subprocess.py:466: in check_output
    return run(*popenargs, stdout=PIPE, timeout=timeout, check=True,
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

input = None, capture_output = False, timeout = None, check = True
popenargs = (['C:/PROGRA~1/POSTGR~1/17/bin\\pg_ctl', 'initdb', '--pgdata', 'C:\\Users\\James\\AppData\\Local\\Temp\\pytest-of-James\\pytest-20\\pytest-postgresql-test_db0\\data-17330', '-o', '--username=postgres --auth=trust'],)
kwargs = {'env': {'LANG': 'C.UTF-8', 'LC_ALL': 'C.UTF-8', 'LC_CTYPE': 'C.UTF-8'}, 'stderr': -2, 'stdout': -1}
process = <Popen: returncode: 3221225477 args: ['C:/PROGRA~1/POSTGR~1/17/bin\\pg_ctl',...>
stdout = b'', stderr = None, retcode = 3221225477

< ... snip some lines for clarity ... >

>               raise CalledProcessError(retcode, process.args,
                                         output=stdout, stderr=stderr)
E               subprocess.CalledProcessError: Command '['C:/PROGRA~1/POSTGR~1/17/bin\\pg_ctl', 'initdb', '--pgdata', 'C:\\Users\\James\\AppData\\Local\\Temp\\pytest-of-James\\pytest-20\\pytest-postgresql-test_db0\\data-17330', '-o', '--username=postgres --auth=trust']' returned non-zero exit status 3221225477.

..\..\..\.pyenv\pyenv-win\versions\3.12.0\Lib\subprocess.py:571: CalledProcessError

As far as I can tell there's no useful information captured by the exception.
If I try running this command on the terminal, I get this:

(.venv) PS C:\Users\James\Documents\Projects\glogbook-db> pg_ctl initdb --pgdata C:\\Users\\James\\AppData\\Local\\Temp\\pytest-of-James\\pytest-20\\pytest-postgresql-test_db0\\data-17330 -o --username=postgres --auth=trust
726b3549C:\Program Files\PostgreSQL\17\bin\pg_ctl.exe: illegal option -- auth=trust

so I commented out line 183 (options += ["--auth=trust"]), which resulted the same exception above being raised. If I run that command (i..e without --auth=trust) in the terminal, it appears to succeed:

(.venv) PS C:\Users\James\Documents\Projects\glogbook-db> pg_ctl initdb --pgdata C:\\Users\\James\\AppData\\Local\\Temp\\pytest-of-James\\pytest-20\\pytest-postgresql-test_db0\\data-17330 -o --username=postgres         
3;CThe files belonging to this database system will be owned by user "James".
This user must also own the server process.

The database cluster will be initialized with locale "English_United Kingdom.1252".
The default database encoding has accordingly been set to "WIN1252".
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory C:/Users/James/AppData/Local/Temp/pytest-of-James/pytest-20/pytest-postgresql-test_db0/data-17330 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... windows
selecting default "max_connections" ... 100
selecting default "shared_buffers" ... 128MB
selecting default time zone ... Europe/London
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
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.

Success. You can now start the database server using:

    "C:\Program Files\PostgreSQL\17\bin\pg_ctl" -D C:/Users/James/AppData/Local/Temp/pytest-of-James/pytest-20/pytest-postgresql-test_db0/data-17330 -l logfile start

Given I can run this command from a terminal, maybe there is a permissions issue with running under python/pytest (that's a guess)? Honestly I'm at a loss from here. Any advice would be appreciated.

[jamesfowkes]

Further information: if I run this in the Python REPL it appears to succeed:

>>> import subprocess
>>> args=['pg_ctl', 'initdb', '--pgdata', 'C:\\Users\\James\\AppData\\Local\\Temp\\pytest-of-James\\pytest-23\\pytest-postgresql-test_db0\\data-32058', '-o', '--username=postgres']
>>> res = subprocess.check_output(args)
initdb: warning: enabling "trust" authentication for local connections
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.

>>> [print(l) for l in res.decode("utf-8").splitlines()]
The files belonging to this database system will be owned by user "James".
This user must also own the server process.

The database cluster will be initialized with locale "English_United Kingdom.1252".
The default database encoding has accordingly been set to "WIN1252".
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory C:/Users/James/AppData/Local/Temp/pytest-of-James/pytest-23/pytest-postgresql-test_db0/data-32058 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... windows
selecting default "max_connections" ... 100
selecting default "shared_buffers" ... 128MB
selecting default time zone ... Europe/London
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok


Success. You can now start the database server using:

    ^"C^:^\Program^ Files^\PostgreSQL^\17^\bin^\pg^_ctl^" -D C:/Users/James/AppData/Local/Temp/pytest-of-James/pytest-23/pytest-postgresql-test_db0/data-32058 -l logfile start

[jamesfowkes]

More info, if I run pytest from the terminal, the exception is raised:

(.venv) PS C:\Users\James\Documents\Projects\glogbook-db> pytest --rootdir=c:\\Users\\James\\Documents\\Projects\\glogbook-db c:\\Users\\James\\Documents\\Projects\\glogbook-db\\tests\\test_filetype.py::TestFiletype::test_filetype_creation_valid_data
<...snip...>
>               raise CalledProcessError(retcode, process.args,
                                         output=stdout, stderr=stderr)
E               subprocess.CalledProcessError: Command '['C:/PROGRA~1/POSTGR~1/17/bin\\pg_ctl', 'initdb', '--pgdata', 'C:\\Users\\James\\AppData\\Local\\Temp\\pytest-of-James\\pytest-24\\pytest-postgresql-test_db0\\data-25643', '-o', '--username=postgres']' returned non-zero exit status 3221225477.

..\..\..\.pyenv\pyenv-win\versions\3.12.0\Lib\subprocess.py:571: CalledProcessError

[jamesfowkes]

Based on the above, I commented out the passing of the environment variables to the subprocess:

subprocess.check_output(init_directory)#, env=self._envvars)

This results in the data creation step succeeding (I think), but the server is unable to start:

>           raise ProcessExitedWithError(self, exit_code)
E           mirakuru.exceptions.ProcessExitedWithError: The process invoked by the <pytest_postgresql.executor.PostgreSQLExecutor: "C:/PROGRA~1/POSTGR~1/17/bin\pg_ctl start -D "C:\Users\James\AppData\Local\Temp\pytest-of-James\pytest-26\pytest-postgresql-test_db0\data-21404" -o "-F -p 21404 -c log_destination='stderr' -c logging_collector=off -c unix_socket_directories='C:\Users\James\AppData\Local\Temp' " -l "C:\Users\James\AppData\Local\Temp\pytest-of-James\pytest-26\pytest-postgresql-test_db0\postgresql.21404.log" -w" 0x1fb44447aa0> executor has exited with a non-zero code: 1.

.venv\Lib\site-packages\mirakuru\base.py:527: ProcessExitedWithError
---------------------------- Captured stderr setup ----------------------------
initdb: warning: enabling "trust" authentication for local connections
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.
pg_ctl: could not start server
Examine the log output.

I fixed this by removing the single quotes from -c log_destination='stderr' on line 53 of executor.py.

And fixing the following error was similar, removing the single quotes from -c unix_socket_directories='{unixsocketdir}' on line 55.

I see now that I am having the same issue as #303 and that my assumption of windows support may have been incorrect.

[jamesfowkes]

Having implemented the fix here: #303 (comment)

I now have a working test suite. I had to also add unixsocketdir to the fix:

PostgreSQLExecutor.BASE_PROC_START_COMMAND = PostgreSQLExecutor.BASE_PROC_START_COMMAND.replace("'{unixsocketdir}'", "{unixsocketdir}")

And I have these changes on lines 183-186 of executor.py:

#options += ["--auth=trust"] # --auth=trust causes pg_ctl to fail with "illegal option"
init_directory += ["-o", " ".join(options)]
# Do not parse envvars to check_output, this was causing pc_ctl to fail, cause not determined.
subprocess.check_output(init_directory)#, env=self._envvars)

[fizyk]

@jamesfowkes sorry for this late response. This isn't WSL/WSL2 is it? Because the windows support isn't implemented yet, as I do not have windows available (I mean, I do, but it's used exclusively as a gaming machine, nothing more)
So, if you'd like to add support for Windows, I'm all in favour, and having an implementation ready with windows on CI it would be easier for anyone in the future to address the windows based issues.