postgresql 常用命令

创建用户

CREATE USER davide WITH PASSWORD 'jw8s0F4';
CREATE ROLE admin WITH CREATEDB CREATEROLE;

修改用户密码

ALTER ROLE davide WITH PASSWORD 'hu8jmn3';
ALTER ROLE davide WITH PASSWORD NULL;

创建数据库

CREATE DATABASE sales OWNER salesapp

设置数据库只读权限

create user test_readonly with password 'password';
grant connect on DATABASE test to test_readonly;
grant select on all tables in schema public to test_readonly;

导出导入

pg_dump -F t -h 127.0.0.1 -U postgres dbname > dbname.tar
pg_restore -d dbname dbname.tar