su postgres
psql galaxy
where galaxy is the name of databse.Basic psql help
\?
List of tables
\dt
definition of the table galaxy_user
\d galaxy_user
To create automatic backup (after -U is the username)
pg_dump -U galaxy galaxy > galaxy.db
To fill database from the backup
psql galaxy < galaxy.db
Script for running in the crontab
#!/bin/bash
BACKUP_DIR=/home/galaxyelixir/storage/dbbackups
DAYS_TO_KEEP=30
FILE_SUFFIX=_pg_backup_galaxielixir.sql
DATABASE=galaxy
FILE=`date +"%Y%m%d"`${FILE_SUFFIX}
OUTPUT_FILE=${BACKUP_DIR}/${FILE}
pg_dump ${DATABASE} -F p -f ${OUTPUT_FILE}
gzip $OUTPUT_FILE
# prune old backups
find $BACKUP_DIR -maxdepth 1 -mtime +$DAYS_TO_KEEP -name "*${FILE_SUFFIX}.gz" -exec rm -rf '{}' ';'
crontab line for making backup twice a week
5 * * * 3,6 /home/galaxyelixir/storage/dbbackups/backupPostGre.sh
