常用 DataBase 相关操作和资源

记录一些常用的 DataBase 相关的东西。

查询
权限
备份
读写

查询

1
2
3

-- 查询所有的 schema
select schema_name from information_schema.schemata;
select nspname from pg_catalog.pg_namespace;

权限

--- 创建 Read-Only 用户
GRANT CONNECT ON DATABASE [db_name] TO [user_name];
GRANT USAGE ON SCHEMA public TO [user_name];
-- special table
GRANT SELECT ON [table_name] TO [user_name];
-- all tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO [user_name];
-- grant access to the new tables automatically
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO [user_name];

-- 创建正常用户
CREATE USER [user_name] WITH PASSWORD 'xxxxxxx';
-- only public schema
GRANT ALL PRIVILEGES ON DATABASE [db_name] TO [user_name];
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO [use_rname];

备份

### psql dump ###
pg_dump -h [host] -Fc -o -U [user_name] [db_name] > backup.dump
sudo -u postgres pg_restore -C [backup.dump]
# Example
pg_dump -h 67.8.78.10 -Fc -o -U myuser mydb > mydb_backup.dump
pg_restore -h 67.8.78.10 -p 5432 -d mydb -U myuser mydb_backup.dump

### psql sql ###
pg_dump -h [host] -U [user_name] [db_name] > mydb_backup.sql
psql -h host -d mydb -U myuser -p 5432 -a -q -f mydb_backup.sql
-h PostgreSQL server IP address
-d database name
-U user name
-p port which PostgreSQL server is listening on
-f path to SQL script
-a all echo
-q quiet 
-f file

// complete (all db and objects)
pg_dumpall -U myuser -h 67.8.78.10 --clean --file=backup.dump
// restore from pg_dumpall --clean
psql -f backup.dump postgres #it doesn't matter which db you select here


### mysql ###
mysqldump --host [host] -P [port] -u [user_name] -p[password] [db_name] > backup.sql
# Example
mysqldump --host 192.168.1.15 -P 3306 -u myuser -pmypassword mydb > mydb.sql