PostgreSQL 维护

服务管理

docker container start postgres-server
docker container stop postgres-server
docker container restart postgres-server
docker logs postgres-server

查看状态

SELECT version();
SELECT current_database();
SELECT current_user;

psql 常用命令

\l
\c app_db
\dt
\d users
\du
\q

用户和权限

CREATE ROLE app_user WITH LOGIN PASSWORD 'vfanlee';
GRANT CONNECT ON DATABASE app_db TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

生产环境不要直接使用超级用户连接应用。

备份和恢复

pg_dump -U postgres -d app_db > app_db.sql
psql -U postgres -d app_db < app_db.sql