Posts Tagged ‘psql’

psql via command line does not have an option for password. To run a scheduled task using psql is pointless without full automation.

Warning postgres recommends against doing this, and instead use a password file.

I use this script to kick off the psql command in python. But you can execute psql straight from the batch file, just check the %ERRORLEVEL% batch variable from the calling method.

@echo off

REM scheduled task point to .bat files
REM besides we need to make sure we have system variables in place

REM export a password for use with the system (no quotes)

REM execute psql by file, even though echo is off, errors will still show
psql -X --variable=ON_ERROR_STOP= -1 -w -f filename.sql

Read Full Post »

This script will monitor a hot folder, take it’s contents and execute the files against a postgres server.

Why post this? This python script in conjunction with a parameter setting batch file can take SQL output and apply it to the postgres database. Originally I was searching how to do this via DOS when I realized I didn’t have all the error catching capability that I wanted.

Developing this came from trying to solve how to systematically apply changes from other systems to one database. This script does not make a distinction between files. So if one system outputs several files that need uploading and separate files target the same record, last loaded is last applied.

I’ve simplified the script a little for ease of posting, this does require system variable PGPASSWORD to run.

import os, glob, shutil

# count
fileCount = 0

# ASSUMES this file is above INCOMING/ BAD/ and ARCHIVE/
filelist = glob.glob("INCOMING/*.sql")

for file in filelist:
    # take the file and thrown it against psql
    # read psql --help for details about options
    # setting ON_ERROR_STOP to nothing tells psql to pass back an error status code
    errorlevel = os.system("psql -X -U some_user -d database --variable=ON_ERROR_STOP= -1 -w -f "+file)
    # check for errors (thrown by psql)
    if errorlevel != 0:
        # error was thrown, lets report it and stash the file
        print errorlevel
        print file + " processed"
        fileCount += 1

print str(fileCount) + " files processed"

Read Full Post »

%d bloggers like this: