sudo apt-get install postgresql

netstat -ap | grep 5432

切换用户并登录

sudo su postgres
psql postgres

创建用户

CREATE USER username WITH PASSWORD 'password';

创建数据库 和 赋权

\c databasename #切换数据库

CREATE DATABASE databasename OWNER username;

GRANT ALL PRIVILEGES ON DATABASE databasename TO username;

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO username;

退出 \q 登录

psql -U username -d databasename -h 127.0.0.1 -p 5432

设置远程访问

  1. postgresql.conf
配置文件路径
/etc/postgresql/{{version}}/main/postgresql.conf
listen_addresses = 'localhost' 修改成 '*'
  1. pg_hba.conf
TYPE  DATABASE        USER            ADDRESS                 METHOD
下面添加
host  all  all 0.0.0.0/0 md5
  1. 重启
sudo /etc/init.d/postgresql start
sudo service postgresql restart
sudo /etc/init.d/postgresql status//查看状态

查询

##列出所有数据库

\l
列出所有数据库

查看列

\d tablename
查看列的类型

uuid

CREATE TABLE tablename(
   id uuid DEFAULT uuid_generate_v4(),
   name           TEXT    NOT NULL,
   time TIMESTAMPTZ
);

删除表

REVOKE CONNECT ON DATABASE db_name from db_user;

DROP TABLE tablename;

增加字段

ALTER TABLE table_name
ADD COLUMN new_column_name data_type;

修改字段类型

ALTER TABLE "tablename" ALTER COLUMN "columnname" TYPE typename(int);

ALTER TABLE "tablename" ALTER COLUMN "columnname" TYPE int USING "columnname"::integer;

导入/数据库恢复

psql -d databasename -U username -f mydatabase.sql

导出/数据库备份

pg_dump -U username -d databasename -f dump.sql

export PATH=/Library/PostgreSQL/11/bin:$PATH