Ruby on Rails – Multiple DB Connections

Seems Magic Multi-Connections does what it says and provides simple support for multiple databases in a single Rails model.

SQL to add/revoke permissions

To add/revoke permissions for all tables in a database:


-- to create the role
CREATE ROLE read_only_role_foo LOGIN PASSWORD 'password' NOINHERIT
VALID UNTIL 'infinity';
grant usage on schema public to read_only_role_foo;
select 'GRANT SELECT ON ' || c.relname || ' TO read_only_role_foo;'
from pg_class AS c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
where c.relkind = 'r'
and n.nspname NOT IN('pg_catalog', 'pg_toast')
and pg_table_is_visible(c.oid);



-- to drop the role
select 'REVOKE SELECT ON ' || c.relname || ' FROM read_only_role_foo;'
from pg_class AS c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
where c.relkind = 'r'
and n.nspname NOT IN('pg_catalog', 'pg_toast')
and pg_table_is_visible(c.oid);
REVOKE usage ON schema public FROM read_only_role_foo;
DROP ROLE read_only_role_foo;


MySQL Dump and Restore

BACKUP
dumps out and compresses:
mysqldump –all-databases | bzip2 -c >databasebackup.sql.bz2

or you can just dump out the mantis db:
mysqldump -uusername -ppassword mantisdbname   > mantisbackup.sql

or cron this script with date:
#!/bin/sh
date=`date -I`
mysqldump –all-databases | gzip > /var/backup/backup-$date.sql.gz

RESTORE:
To test restore right from a gz:
gunzip < mantisbackup.sql.gz | mysql -uusername -ppassword mantisdbname

or mysql -uusername -ppassword  mantisdbname < mantisbackup.sql

Postgres – Dumping and restoring a database

To dump a database

pg_dump -Fc -Uuser -d mydb > db.dump

To create schema and make insert statements

pg_dump  -Uuser -d mydb –create –inserts > db.sql

To restore a database

pg_restore -Uuser -d mydb db.dump