PostgreSQL的使用
PG控制台常用的命令
设置密码
\password
退出
\q
查看SQL命令的解释，比如 \h select
\h
查看psql命令列表
\?
列出所有数据库
\l
连接其他数据库
\c [database_name]
列出当前数据库的所有表格
\d
列出某一张表格的结构
\d [table_name]
列出所有用户
\du
显示所有表的权限
\dp
数据库操作
登录数据库
psql -h 服务器 -U 用户名 -d 数据库 -p 端口地址 // -U 是大写
psql -U dbuser -d example -h 127.0.0.1 -p 5432
psql （连接数据库，默认用户和数据库都是postgres)
相当于系统用户postres以同名数据库用户的身份，登录数据库，这是不用输入密码的，如果一切正常，系统提示符会变为“postgres=#", 表示这时已经进入了数据库控制台。
创建数据库
create database mydb;
在linux 命令行下创建数据库
createdb mydb;
删除数据库
drop database mydb;
数据库表操作
创建表格时每列必须使用数据类型，PostgreSQL中主要有三类数据类型：
数值数据类型
字符串数据类型
日期/时间数据类型
创建表
create table test(id serial primary key, name varchar(255));
插入数据
insert into test(name) values('jack');
显示表结构
\d test;
查询数据
select * from test;
更新数据
update test set name='mayun' where id = 1;
删除数据
delete from test where id = 1;
Schema
创建schema
create schema myschema;
创建表在schema下面
create table myschema.test(id int, name varchar(255));
如何备份PostgreSQL数据库
如果您在生产环境中使用PostgreSQL, 请务必采取预防措施以确保用户的数据不会丢失。
单数据库
PostgreSQL提供了pg_dump实用程序来简化备份单个数据库的过程。必须以对要备份的数据库具有读取权限的用户身份运行次命令。
以postgres用户身份登录：
sudo su - postgres
生成的备份文件dbname.bak可以使用scp传输到另一台主机，也可以存储在本地供以后使用。
要演示恢复丢失的数据，请删除示例数据库并在其位置创建一个空数据库：
使用psql恢复数据库
psql test < dbname.bak
备份格式有几种选择：
*.bak: 压缩二进制格式
*.sql: 明文转储
*.tar: tarball
注意：默认情况下，PostgreSQL将忽略备份过程中发生的任何错误。这可能导致备份不完整。要防止这种情况，您可以使用-l选项运行pg_dump命令。这会将整个备份过程视为单个事务，这将在发生错误时阻止部分备份。
所有数据库
由于pg_dump一次只创建一个数据库的备份，因此它不会存储有关数据库角色或其他群集范围配置的信息，要存储此信息并同时备份所有数据库，可以使用pg_dumpall。
创建备份文件：
pg_dumpall > pg_backup.bak
从备份还原所有数据库：
psql -f pg_backup.bak postgres
示例：
#备份数据库
pg_dump -U postgres -f /tmp/postgres.sql postgres (导出postgres数据库保存为postgres.sql)
pg_dump -U postgres -f /tmp/postgres.sql -t test postgres (导出postgres数据库中表test的数据)
pg_dump -U postgres -F t -f /tmp/postgres.tar postgres (导出postgres数据库以tar形式压缩保存为postgres.tar)
#恢复数据库
psql -U postgres -f /tmp/postgres.sql bk01 (恢复postgres.sql数据到bk01数据库)
#pg_restore -- 从pg_dump创建的备份文件中恢复PostgresSQL数据库，用于恢复由pg_dump转储的任何非纯文本格式中的PostgreSQL数据库。
pg_restore -U postgres -d bk01 /tmp/postgres.tar (恢复postgres.tar数据到bk01数据库)
用户操作
数据库授权，赋予指定账户指定数据库权限
GRANT ALL PRIVILEGES ON DATABASE 'dbname' TO 'username';
将数据库mydb权限授予test
GRANT ALL PRIVILEGES ON DATABASE mydb TO test;
但此时用户还是没有读写权限，需要继续授权表。 （注意：该SQL语句必须在所要操作的数据库里执行）
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO xxx;
使用username登录myDatabase数据库
psql -U username -d myDatabase;
移除username在myDatabase数据库上的所有权限
revoke all privileges on database myDatabase from username;
移除username在表上的所有权限
revoke all privileges on all tables in schema public from username;
删除用户
drop user username;
创建角色
create role david; -- (Attributes: Cannot login)
创建用户
create user sandy; -- (Attributes: Superuser, Create role, Create DB, Replication, Bypass RLS)
查询角色
select * from pg_roles;
查询用户
select * from pg_user;
允许用户/角色登录
alter role david with LOGIN;
创建用户并设置密码
CREATE USER 'username' WITH PASSWORD 'password';
eg: create role renee password 'abc123' LOGIN;
修改用户密码
ALTER USER 'username' WITH PASSWORD 'password';
给已存在的用户赋予各种权限
赋予登录权限
ALTER ROLE rolename WITH LOGIN;
赋予renee创建角色权限
ALTER ROLE renee WITH CREATEROLE;
