Created
June 1, 2012 12:54
-
-
Save denitram/2851937 to your computer and use it in GitHub Desktop.
Postgresql --- Backup one database
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/bin/sh | |
PGBIN_DIR=/usr/bin/ | |
PSQL="${PGBIN_DIR}/psql -w --username=postgres" | |
PG_DUMPALL="${PGBIN_DIR}/pg_dumpall --username=postgres" | |
PG_DUMP="${PGBIN_DIR}/pg_dump --username=postgres" | |
# | |
CRON_DIR=/home/postgres/cron | |
#DUMP_DIR=${HOME}/cron | |
#DUMP_DIR=/data/db/backups | |
DUMP_DIR=/data/postgres/backup | |
PGDATA_DIR=/data/db/pgdata | |
################################### | |
#DATABASES=`${PSQL} -A -t -q << HIERO | |
#select datname from pg_database WHERE datname NOT IN ( 'template0' ) | |
#AND datname NOT LIKE 'pub_%' \g | |
#HIERO` | |
DATABASES="intspecinf_ont" | |
DAG="120601" | |
############################################ | |
## Dumpen van tablespacedefinities | |
## Dumpen van users en rollen | |
## -t := tablespacedefinities only | |
## -r := Roles only | |
## NB: --host optie gebruikt een connectie via IP (loopback) | |
## ; zonder de -host-vlag gaat de connectie over een unix-domain socket. | |
## Dit wordt hier gebruikt om password-prompts te vermijden (!) | |
## pg_hba.conf heeft een allow voor locale postgres. Dit is een veiligheids- | |
## risico, maar *als* een intruder postgres-shell-access mocht krijgen | |
## is er sowieso al een probleem. | |
############################################ | |
mkdir -p ${DUMP_DIR}/${DAG} | |
##################################################################### | |
## -o := maintain oids | |
## -C := create before use | |
## -s := schema only | |
## -a := data only | |
## -D / -d := insert...VALUES() instead of copy...FROM (-D=with column names) | |
## (dit conflicteert met -o --oids !!!!) | |
## --format=c | |
##################################################################### | |
for d in ${DATABASES}; do | |
echo "Database ${d}:${DUMP_DIR}/${DAG}/${d}_..." | |
${PG_DUMP} --disable-triggers --create --schema-only \ | |
-f ${DUMP_DIR}/${DAG}/${d}_schemas.dmp ${d} | |
${PG_DUMP} --disable-triggers --data-only \ | |
-f ${DUMP_DIR}/${DAG}/${d}_dataonly.dmp ${d} | |
echo "Klaar met database $d." | |
done | |
exit 0 | |
##################################################################### | |
## Eof | |
##################################################################### |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment