CREATE USER WITH ENCRYPTED PASSWORD '';
GRANT CONNECT ON DATABASE database_name TO username;
GRANT USAGE ON SCHEMA schema_name TO username;
GRANT SELECT ON table_name TO username;
GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO username;
grant select on all tables in schema public to user1;
CREATE DATABASE dbname WITH OWNER = postgres TEMPLATE = template0 ENCODING = 'UTF8';
GRANT ALL PRIVILEGES ON DATABASE dbname to username;
pg_dumpall > db.out
pg_dump -h localhost -U postgres(用户名) 数据库名(缺省时同用户名) > /data/dum.sql
psql -U postgres(用户名) 数据库名(缺省时同用户名) < /data/dum.sql
pg_dump -h localhost -U postgres(用户名) 数据库名(缺省时同用户名) | gzip > /data/dum.sql.gz
gunzip < /data/dum.sql.gz | psql -h localhost -U postgres(用户名) 数据库名(缺省时同用户名)
pg_dump -U postgres -h localhost -p 5432 -t staff -f staff.sql yjl(表示数据库名称)-U 表示用户-h 表示主机-p 表示端口号-t 表示表名-f 表示备份后的sql文件的名字-d 表示要恢复数据库名称
psql -U postgres -h localhost -p 5432 -d product -f staff.sql
select count(1) from pg_stat_activity;
show max_connections;
SELECT d.datname AS Name, pg_catalog.pg_get_userbyid(d.datdba) AS Owner,CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))ELSE 'No Access'END AS SIZEFROM pg_catalog.pg_database dORDER BYCASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')THEN pg_catalog.pg_database_size(d.datname)ELSE NULLEND DESC -- nulls firstLIMIT 20
腾图小抄 SCWY.net v0.03 小抄561条 自2022-01-02访问317591次