PostgreSQL部署和基础操作

一、安装部署

#下载postgresql数据库的yum源
yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

#查看当前yum源支持哪些版本
yum list | grep postgre

#yum安装数据库
yum install postgresql94 postgresql94-server

二、数据库初始化

/usr/pgsql-9.4/bin/postgresql94-setup initdb

三、配置数据库远程访问

在/var/lib/pgsql/data/pg_hba.conf 文件中添加host all all 0.0.0.0/0 md5

在/var/lib/pgsql/data/postgresql.conf添加 listen_addresses='*'

配置完之后就可以从其他地方使用ip连接数据库了

启动数据库
systemctl start postgresql-9.4

关闭数据库
systemctl stop postgresql-9.4

四、创建用户和数据库

#连接数据库进入命令行
su postgres

#启动SQL Shell
psql

#创建数据库用户
CREATE USER databaseuser WITH PASSWORD 'password';

#给用户创建一个数据库
CREATE DATABASE database-name OWNER databaseuser;

#查看数据库，以及数据库的所有者
/l 

#退出SQL Shell
\q

#退出命令行
exit

下面介绍数据库的常用操作

# 以人性化的方式显示数据库大小
select pg_size_pretty(pg_database_size('数据库名字'));

# 查看所有数据库大小
select pg_database.datname,
pg_database_size(pg_database.datname) AS size from pg_database;

# 创建用户数据库
CREATE DATABASE dbname OWNER dbuser;

# 创建数据库新用户
CREATE USER dbname WITH PASSWORD 'password';

# 使用命令 \q 退出psql：
postgres=# \q

# 创建数据库 
create database dbname;

# 删除数据库 
drop database dbname;

#  赋给用户数据库的所有权限 
GRANT ALL PRIVILEGES ON DATABASE dbname TO dbuser;

# 撤销用户权限 
REVOKE privileges ON tablename FROM user;

# 显示所有用户 
\du

#查看所有数据库
\l

#切换当前数据库
\c mydb

#创建test表，
CREATE TABLE test(id int,body varchar(100));

#查看当前数据库下所有表
\d



#新建用户
CREATE USER test WITH PASSWORD 'test';

#赋予指定账户指定数据库所有权限
GRANT ALL PRIVILEGES ON DATABASE mydb TO test;

#移除指定账户指定数据库所有权限
REVOKE ALL PRIVILEGES ON DATABASE mydb TO test

权限代码：SELECT、INSERT、UPDATE、DELETE、TRUNCATE、REFERENCES、TRIGGER、CREATE、CONNECT、TEMPORARY、EXECUTE、USAGE


#断开当前数据库连接，在PostgreSQL 9.2及更高版本中，要断开除会话外的所有连接，请断开与数据库的连接：


SELECT pg_terminate_backend(pg_stat_activity.pid)

FROM pg_stat_activity

WHERE datname = current_database()

  AND pid <> pg_backend_pid();

在旧版本中，相同，只是更改pid为procpid。要断开与其他数据库的连接，只需更改current_database()为要断开用户连接的数据库的名称即可。

在断开用户连接之前，您可能希望从数据库用户那里REVOKE获得CONNECT正确的权限，否则用户将保持重新连接状态，而您将永远没有机会删除数据库。请参阅此注释以及与之相关的问题，如何将所有其他用户从数据库中分离出来。