PostgreSQL 操作命令

cat  /etc/redhat-release
 CentOS Linux release 7.6.1810 (Core)

#安装 postgresql rpm 
yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm
#安装postgresql客户端
yum install postgresql10
#安装postgresql-server
yum install postgresql10-server
#初始化数据库
/usr/pgsql-10/bin/postgresql-10-setup  initdb
#启动并开机启动
systemctl   start  postgresql-10 &&  systemctl  enable  postgresql-10

#yum安装postgresql，默认会建一个名为”postgres”的数据库管理员账号和同名的系统用户，用于执行PostgreSQL；
sudo -u postgres psql -U postgres
#修改密码
postgres=# ALTER USER postgres WITH PASSWORD '123456';

#默认只能本机访问postgresql；
#修改listen_addresses = 'localhost'为listen_addresses = '*'，允许所有远程访问；
sed -i "s|#listen_addresses = 'localhost'|listen_addresses = '*'|g" /var/lib/pgsql/10/data/postgresql.conf

systemctl    restart  postgresql-10

firewall-cmd  --add-port=5432/tcp --permanent
firewall-cmd  --reload

netstat -tuntlp |grep 5432
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      16169/postmaster 
tcp6       0      0 :::5432                 :::*                    LISTEN      16169/postmaster

默认的数据库路径是/var/lib/pgsql/10/data/
创建新的数据存储目录mkdir -p /data/postgresql/data
更改目录属主属组
chown -R postgres:postgres  /data/postgresql/data
更改目录权限
chmod 700 /data/postgresql/data
停止服务
systemctl stop postgresql-10
修改启动文件/usr/lib/systemd/system/postgresql-10.service
修改 
PGDATA=/var/lib/pgsql/10/data/
将其修改为新的数据目录路径：PGDATA=/data/postgresql/data
重载配置
sudo systemctl daemon-reload
切换postgres用户
su – postgres
拷贝数据至新的目录下
cp -rf  /var/lib/pgsql/10/data/* /data/pgdata/data
exit //退出
systemctl start postgresql-10
登录数据库查收数据存储路径 
psql  -U postgres
show data_directory;

数据的导出
pg_dump -U postgres(用户名)  (-t 表名)  数据库名(缺省时同用户名)  > 路径/文件名.sql
示例：pg_dump -h 192.168.99.221 -U zabbix zabbix >/opt/zabbix.sql
数据的导入
psql -d newdatabase -U postgres -f mydatabase.sql   // sql 文件在当前路径下
psql -d databaename(数据库名) -U username(用户名) -f < 路径/文件名.sql  // sql 文件不在当前路径下
示例：psql  -U zabbix zabbix -h 192.168.99.221 </opt/zabbix.sql

#登陆数据库
psql -U postgres -h 192.168.99.201\
#退出
\q
#列举数据库，相当于mysql的show databases
\l
#切换数据库,相当于mysql的use dbname
\c zabbix
#列举表，相当于mysql的show tables
\dt
#查看表结构，相当于desc tblname
\d users
#查看索引 
\di 
#查看当前数据存储目录
show data_directory;
#select 查询
select  userid,alias,lang from users;
#update 更新#
更新 users 表中 id 为 2 的 refresh 字段值
update  users  set refresh = 60 WHERE userid = 2;
#更新 users 表中 id 为1的passwd 字段
update  users set passwd = md5('zabbix') where  userid = 1;
select * from config \x\g\x;update  config set authentication_type=1;

#修改 postgres 用户密码
ALTER USER postgres WITH PASSWORD '123456';
设置访问权限
#语法：GRANT permission_type ON table_name TO role_name;
#示例：#赋予zabbix用户 users表的update权限
GRANT UPDATE ON users TO zabbix; 
#赋予zabbix用户所有表的SELECT权限
GRANT SELECT ON ALL TABLES IN SCHEMA PUBLIC to zabbix; 
#特殊符号:ALL代表所访问权限，PUBLIC代表所有用户
GRANT ALL ON users TO zabbix; --赋给zabbix用户users表所有权限
GRANT SELECT ON users TO PUBLIC; --将users表的SELECT权限赋给所有用户
#撤销用户访问权限#语法格式如下:
REVOKE permission_type ON table_name FROM user_name;
#其中permission_type和table_name含义与GRANT指令中相同。
\z或\dp   #指令显示用户访问权限
\h        #GRANT显示所有可设置的访问权限
#切换用户
SET ROLE role_name; #切换到role_name用户
RESET ROLE; #切换回最初的role
示例
#创建zabbix用户 create user zabbix with password 'zabbixpwd123';
create database zabbix owner zabbix;
grant all privileges on database zabbix to zabbix;