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)