Postgresql Study 笔记

Postgresql 安装

Windows, MAC Install

Postgresql 下载地址： https://www.enterprisedb.com/downloads/postgres-postgresql-downloads

Linux Install

1. sudo apt-get update
2. sudo apt-get install postgresql postgresql-client
3. sudo -i -u postgres
4. psql
5. systemctl start postgresql.service # 开启
6. systemctl stop postgresql.service # 关闭
7. systemctl restart postgresql.service # 重启
8. \password postgres # 修改密码
9. \q # 退出

Postgresql 命令

1. psql -h localhost -p 5432 -U postgres chitchat
2. \h # 帮助
3. \l # 显示数据库
4. \c # 选择数据库
5. \d # 显示数据表
6. \? # 命令帮助

Postgresql 权限

1. CREATE USER root WITH PASSWORD 'root';
2. GRANT ALL PRIVILEGES ON DATABASE chitchat TO root; # 赋值数据库权限
3. GRANT ALL PRIVILEGES ON users TO root; # 赋值数据表权限
4. ALTER USER myuser WITH SUPERUSER; # 可选的，权限赋值太大（创建用户和数据库等）
5. GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public to root; # 所有表
6. GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public to root; # 所有表
7. GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public to root; # 所有表

Postgresql Golang 连接

1. vim /etc/postgresql/10/main/pg_hba.conf # 以下修改
2. systemctl restart postgresql.service

...... 
host all all 127.0.0.1/32 md5 
## fix to 
host all all 127.0.0.1/32 trust 
......

Postgresql 操作

drop table users;
create table users (
  id         serial primary key,
  uuid       varchar(64) not null unique,
  name       varchar(255),
  email      varchar(255) not null unique,
  password   varchar(255) not null,
  created_at timestamp not null   
);
insert into users (uuid, name, email, password, created_at) values (1, 'slagga', 'slagga@qq.com', 'slagga', '2007-12-13');
select * from users;

Postgresql Golang 使用

代码运行

1. git clone https://github.com/sausheong/gwp
2. mv Chapter_2_Go_ChitChat src
3. export GOPATH=$GOPATH:/root/code/gwp
4. cd src && go install chitchat
5. cp -R config.json public templates ../bin/
6. cd ../bin && nohup ./chitchat &
7. curl localhost:8080
8. curl http://47.244.126.85:8080

db, err = sql.Open("postgres", "port=5432 user=postgres password=postgres dbname=chitchat sslmode=disable")

参考地址

https://www.runoob.com/postgresql/linux-install-postgresql.html