Archive for the ‘Postgres’ Category

This allows multiple data stores but without rewriting all sql queries. Note you have to create two functions, one to accept dates, the other to accept times.

Specifically the 4D SQL function DATE_TO_CHAR. Luckily PostgreSQL has the equivalent as a formatting function to_char.

For business reasons it’s not practical to replace all instances of DATE_TO_CHAR to to_char.


Create a function in the postgresql data base that maps the DATE_TO_CHAR function to to_char. Luckily the formatting options I need are available.

Now SELECT DATE_TO_CHAR(DateField1, "YYYY-MM-DD") FROM Table1 will return the correct value regardless of the database queried. It’s important to note this works great for getting integer values from dates and casting as date objects. If queries rely on returning non-iso formatting your mileage may vary.

-- Function: date_to_char(date, text)
CREATE OR REPLACE FUNCTION date_to_char(date, text)
       RETURN to_char($1,$2)::text;
  COST 100;
ALTER FUNCTION date_to_char(date, text) OWNER TO postgres;
-- Function: date_to_char(time without time zone, text)
CREATE OR REPLACE FUNCTION date_to_char(time without time zone, text)
       RETURN to_char($1,$2)::text;
  COST 100;
ALTER FUNCTION date_to_char(time without time zone, text) OWNER TO postgres;

Read Full Post »

There are lots of great backup tools and utilities out there, but utilizing a simple script and cron task to target specific PostgreSQL database is often the fastest way to a locally based backup procedure.

The below shell script is run as the postgres user on a Linux version 2.6.9-42.0.3.ELsmp (Red Hat 3.4.6-3) with PostgreSQL 8.2.0 that has support for command-line execution. Not that there is anything fancy in the code that would require such specific versions.

Unique to the code is the ability to pass in a database name, or none at all to backup the entire cluster. This code only connects and writes files locally. Assumes appropriate permissions given to executing user. Maybe this works just fine for you, otherwise feel free to make your own modifications.

Personally I gzip the output, but improvements could be made to prevent hard disk saturation on larger databases and archiving utilities.


# CRON table for postgres user.

# run backup every night at 22:00 hours (10PM)
0 22 * * * /var/lib/pgsql/backups/backup.sh database_name

# run backup every week at midnight hour on sunday
0 0 * * 0 /var/lib/pgsql/backups/backup.sh


# This script will backup the postgresql database
# and store it in a specified directory

# $1 database name (if none specified run pg_dumpall)

# postgres home folder backups directory
# !! DO NOT specify trailing '/' as it is included below for readability !!

# Date stamp (formated YYYYMMDD)
# just used in file name
CURRENT_DATE=$(date "+%Y%m%d")

# !!! Important pg_dump command does not export users/groups tables
# still need to maintain a pg_dumpall for full disaster recovery !!!

# this checks to see if the first command line argument is null
if [ -z "$1" ]
# No database specified, do a full backup using pg_dumpall
pg_dumpall | gzip - > $BACKUP_DIRECTORY/pg_dumpall_$CURRENT_DATE.sql.gz

# Database named (command line argument) use pg_dump for targed backup
pg_dump $1 | gzip - > $BACKUP_DIRECTORY/$1_$CURRENT_DATE.sql.gz


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 »

Older Posts »

%d bloggers like this: