#/bin/sh # PostgreSQL database backup script # Version 1.1 [Apr. 17, 2002] Add routine: deleting exipired files. # Version 1.0 [Sep. 10, 2002] First release. # Saito Hiroaki <example@example.com> # * Feature # Dump to DATABASE_NAME_YYMMDD.pgdmp. # Delete expired pgdmp file. # Output log. # Setting PATH="$PATH":/usr/local/pgsql/bin PG=/usr/local/pgsql export LD_LIBRARY_PATH="$LD_LIBRARY_PATH":$PG/lib export PGLIB=$PG/lib export PGDATA=$PG/data # Get all DB name TARGET_DB=`psql -l -P format=unaligned -P fieldsep=, -P tuples_only |perl -F, -lane "if (@F[0] ne 'template0') {print @F[0]}"` DIR=/home/postgres LOG=$DIR/pgdump.log BACKUPDIR=$DIR/dbbackup EXPIRE_DAY=90 # Main routine echo "pg_dump start." `date` >>$LOG date=`date +%Y%m%d` for db_name in $TARGET_DB do DUMP_FILE_PATH=$BACKUPDIR/${db_name}_${date}.pgdmp /usr/local/pgsql/bin/pg_dump $db_name -b -o -Fc > $DUMP_FILE_PATH 2>>$LOG echo "Database $db_name backup complete." `date` >>$LOG done echo "pg_dump complete." `date` >>$LOG echo "Delete expired pgdmp file start." `date` >>$LOG find $BACKUPDIR -type f -daystart -mtime +$EXPIRE_DAY |xargs /bin/rm -f echo "Delete expired pgdmp file complete." `date` >>$LOG
データベース名取得を変更
psql -h example.com -U backup -t -A -c "SELECT datname FROM pg_database WHERE datname <> ALL ('{template0,template1,postgres}')"
