1

I have code which activates my local Postgres server if it is not currently on, but once this command is sent, then I am unable to re-run anything in my editor. VSCode simply tells me "Code is currently running!" and the Output indicates that it is waiting for the server to disconnect before actually completing the entire script.

I want to be able to connect to postgresql straight-away by using psycopg2 and avoiding having to handle starting / stopping the local server, just as I would be able to with the EnterpriseDB installer version of PostgreSQL. However, if I can start the server, query the database, and then go about my merry way, that would also solve my issue. I want to be able to work on this Python script and others without locking up VSCode.

My issue stems from having to find a work-around for installing PostgreSQL on Windows 10. The installer was leading to a false "COMSPEC" environment variable error, so I unpacked the binaries instead. Unfortunately, I think that there is some issue with the configuration, since I am not able to run a simple query like the one below, which means that Postgres doesn't automatically start when called with psycopg2 in Python :

import psycopg2

conn = psycopg2.connect(
    user='postgres',
    host='127.0.0.1',
    port='5432',
    database='postgres'
)

cursor = conn.cursor()
SQL = 'select * from dual'

records = cursor.fetchall()

for record in records:
    print('dummy :', record[0],'\n')

cursor.close()
conn.close()

^^^ This will return the following error, which is fixed when I start the server with pg_ctl :

Traceback (most recent call last):
  File "c:\Users\UserName\Desktop\Test.py", line 7, in <module>
    database='postgres'
  File "C:\Users\UserName\AppData\Local\Programs\Python\Python37\lib\site-packages\psycopg2\__init__.py", line 126, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: could not connect to server: Connection refused (0x0000274D/10061)
    Is the server running on host "127.0.0.1" and accepting
    TCP/IP connections on port 5432?

I have manually gone into my command prompt and run these :

pg_ctl -D "C:\Program Files\pgsql\data" stop
pg_ctl -D "C:\Program Files\pgsql\data" start

Ideally, I would be able to have this handled automatically, i.e. I can run a script and not need to shut off the server in order to re-run. Ideally, the server could get started in a background process which is separate from the script's process. 

import os
import psycopg2
import subprocess

pg_ctl = r'C:\Program Files\pgsql\bin\pg_ctl.exe'
data_dir = r'C:\Program Files\pgsql\data'

def server_status(exe,data):
    exe=exe
    data=data
    if (os.path.isfile(exe)) and (os.path.isdir(data)) :
        proc = subprocess.Popen([exe,'-D',data,'status'],stdout = subprocess.PIPE)
        server_status = proc.communicate()[0].rstrip().decode("utf-8")
    elif (os.path.isfile(exe)) and not (os.path.isdir(data)) :
        server_status = f'PostgreSQL data does not exist here : \n {data}'
    elif not (os.path.isfile(exe)) and (os.path.isdir(data)) :
        server_status = f'PostgreSQL Executable "pg_ctl.exe" does not exist here : \n {os.path.dirname(exe)}'
    else :
        server_status = 'Input parameters cannot be executed.\nPlease check where "pg_ctl.exe" and the database reside'
    return server_status

def server_on(exe,data):
    exe=exe
    data=data
    if server_status(exe,data) == 'pg_ctl: no server running':
        try: 
            subprocess.check_call([exe,'-D',data,'start'])
            return 'server started'
        except (subprocess.CalledProcessError) as ex:
            return f'Failed to invoke psql: {ex}'
    elif server_status(exe,data) == 'server started':
        return 'server started already'

print(server_status(pg_ctl,data_dir))
server_on(pg_ctl,data_dir)
print(server_status(pg_ctl,data_dir))

If the server is off, I get : 'server started' returned as the server_status. Then I cannot run anything until I manually shutdown the server. "Code is currently running!" is what is returned (by VSCode) once I try to edit the code and re-run immediately afterwards.

Install PostgreSQL with Binaries :

  • Download PostgrSQL Binaries
  • Unzip the downloaded file in the location that you want to have as your base directory for PostgreSQL
  • Open your CMD prompt, navigate to your "bin" e.g. "C:\Program Files\pgsql\bin"
  • Initialize the database : initdb [option...] [ --pgdata | -D ] directory
  • E.g. : initdb.exe -D ../data --username=postgres --auth=trust
  • ^^^ This will create the directory "data" in the same directory as "bin" then create a username "postgres". Note, no password specified here. Only the directory is a required argument
  • Start the server : pg_ctl [option...] [ --pgdata | -D ] directory
  • E.g. pg_ctl.exe start -D ../data
  • ^^^ This will start the server with what was initialized in the "\data" directory
  • Connect to "postgres" now that the server is up : psql --username=postgres
  • Execute : ALTER USER postgres WITH PASSWORD "my_password"
  • Execute : CREATE EXTENSION adminpack;
  • Connect to a database : psql DBNAME USERNAME
  • Switch databases : \c DBNAME
  • Exit : \q
  • Show all active connections in the CMD prompt : netstat -nat
  • edit "postgresql.conf" file as needed (within your "\data" directory) --> E.g. "listen_addresses = 'localhost'" and "port = 5432"
  • Register PostgreSQL as a service : pg_ctl register [-D datadir] [-N servicename] [-U username] [-P password] [-S a[uto] | d[emand] ] [-e source] [-W] [-t seconds] [-s] [-o options]

Links :

StackOverflow :

Update :

I have tried to register PostgreSQL as a service, but I do not have admin privileges. I believe this is the root of my problem, since I only get the error "pg_ctl: could not open service manager" when I try to execute :

  • pg_ctl.exe register -N postgres -D "C:\Program Files\pgsql\data"

I would either need to disable to firewall or have a batch file kick-off a command to start the PostgreSQL server on a separate thread to my Python scripts. Or I could just switch to Linux and literally none of this would be an issue :D

Torc
  • 1,148
  • 6
  • 20
  • 43
  • `subprocess.check_call` is blocking, it waits for completion. A server is designed to be long-running and won't terminate itself without the signal to do so. Dispatching the server will be prone for accidentally spawning multiple servers... – jbndlr May 31 '19 at 21:35
  • That makes sense. I was hoping that since I could have the rest of the script execute, that it would close itself out. So it seems that I can only have this script run once, and then it never ends. I'd be curious to know if I can send off a command to "start the server if it's off" so that when I try to make a connection with psycopg2 in some other script, I don't run into any issues. – Torc May 31 '19 at 22:54

0 Answers0