postgresql

Mysql + PostgreSQL 'all databases' backup script

Backup script to find and backup all databases (postgres and mysql)

#!/bin/bash
 
today=$(date +%y%m%d)
 
# local dir where the backups go
myDir='/data/dbbackups'
# remote dir where we'll send them offsite
myRemoteDir='/data/backup/li35-166/dbbackups'
 
 
# this is for PostgreSQL. If you don't need it, you
# could leave it here, but remove the 'backup_pgsql'
# function call at the end of the script
 
function backup_pgsql {
  for db in `su postgres -c "psql -U postgres -qAtc '\l'" | cut -f1 -d\|
| grep -v '^template[01]'`; do
    mkdir -p $myDir/${db};

How to see size of postgres databases / tables

Tables:


SELECT pg_tables.tablename, pg_tables.schemaname, pg_size_pretty(pg_relation_size((pg_tables.schemaname::text || '.'::text) || pg_tables.tablename::text)) AS pg_size_pretty FROM pg_tables ORDER BY pg_relation_size((pg_tables.schemaname::text || '.'::text) || pg_tables.tablename::text) DESC;

Database entirely (I swear I had this sites statistics script somewhere that contained all this, including a MySQL version.. where did that go?):

Check number of postgresql connections 

psql template1;

SELECT COUNT(*) FROM pg_stat_activity;

You can see max allowed connections with SHOW max_connections; - but this is just as easily read from the postgresql.conf in /etc/postgresql/(version).. etc

Hourly database backup 

#!/bin/bash
DATE=`date +%H`
FILENAME=$1/$1_hourly_$DATE.sql
mkdir -p $1
pg_dump -cxO -f $FILENAME $1
gzip -f $FILENAME
echo $FILENAME

Daily database backup 

#!/bin/bash
DATE=`date +%Y%m%d`
FILENAME=$1/$1_daily_$DATE.sql
mkdir -p $1
pg_dump -cxO -f $FILENAME $1
gzip -f $FILENAME
echo $FILENAME
scp -i /home/user/.ssh/password_less_ssh_key $FILENAME.gz user@remote.backup:/data/backup/dbbackups/$1/