PostgreSQL Backup Script
#!/bin/sh
DB_NAME=mydatabase
DATE=`date +%Y%m%d%H%M%S`
DIR=/backups/pgsql
PG_DUMP=/usr/bin/pg_dump
$PG_DUMP $DB_NAME | bzip2 > $DIR/$DB_NAME_$DATE.sql.bz2
find $DIR -mtime +7 -exec rm {} \;
ignore latin1 problem via psql
CREATE DATABASE _ WITH ENCODING 'UTF8' OWNER _ LC_COLLATE 'en_US.UTF-8' LC_CTYPE 'en_US.UTF-8' TEMPLATE template0;
PostgreSQL Sequence Updates
I had a problem with PostgreSQL pgdump recently. My setval() calls were all set to '1'. I whipped up this quick script to fix things: #!/usr/bin/env python DB_NAME = 'my_db' from subprocess import Popen, PIPE import re exclude = [ 'tablename', 'rows' ] tp = re.compile( '[^a-z_]' ) ts = Popen( [ "/usr/bin/psql", DB_NAME, "-c SELECT tablename FROM pg_tables WHERE tablename NOT LIKE 'pg_%' AND tablename NOT LIKE 'sql_%' ORDER BY tablename" ], stdout=PIPE ).communicate()[ 0 ].split( ' ' ) tables = [] for t in ts: t = tp.sub( '', t ) if len( t ) == 0 or t in exclude: continue tables.append( t ) for t in tables: sql = "SELECT pg_catalog.setval( pg_get_serial_sequence( '%s', 'id' ), ( SELECT MAX( id ) FROM %s ) + 1 );" % ( t, t ) print Popen( [ "/usr/bin/psql", DB_NAME, "-c %s" % sql ], stdout=PIPE ).communicate()[ 0 ]