PostgreSQL 10操作快速入门实验
文章目录
PostgreSQL 10操作快速入门实验一、安装VirtualBox(自行Google)二、在VirtualBox上安装CentOS7(自行Google)三、安装 PostgreSQL on CentOS7四、常用基本操作五、完整的导入数据操作流程
参考 PostgreSQL学习手册：https://www.postgresql.org/docs/10/
实验前提：先在电脑上用VirtualBox安装一台CentOS7虚拟机,然后在CentOS7上安装 PostgreSQL 10
一、安装VirtualBox(自行Google)
二、在VirtualBox上安装CentOS7(自行Google)
三、安装 PostgreSQL on CentOS7
sudo yum update -y
yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm -y
yum install postgresql10-contrib postgresql10-server -y
rpm -qa | grep postgresql
输出结果：
postgresql10-server-10.11-2PGDG.rhel7.x86_64
postgresql10-libs-10.11-2PGDG.rhel7.x86_64
postgresql10-10.11-2PGDG.rhel7.x86_64
postgresql-libs-9.2.24-1.el7_5.x86_64
postgresql10-contrib-10.11-2PGDG.rhel7.x86_64
[root@vl-bg-anaylsis02 ~]# /usr/pgsql-10/bin/postgresql-10-setup initdb
输出结果：
Initializing database ... OK
# 启动postgresql
[root@vl-bg-anaylsis02 ~]# sudo systemctl start postgresql-10
# 设置开机自动启动
[root@vl-bg-anaylsis02 ~]# sudo systemctl enable postgresql-10.service
# postgres账号登录psql
su - postgres
psql
# 在psql中对postgresql进行操作
postgres=# ALTER USER postgres WITH PASSWORD 'post123'
postgres-# \du
postgres=# \l
postgres=# \q
# 切换到系统的root账号，对配置文件进行配置
su - root
vi /var/lib/pgsql/10/data/postgresql.conf
/var/lib/pgsql/10/data/postgresql.conf配置文件更改后的最终内容：
----------------------------------------------------------------------------
listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
port = 5432 # (change requires restart)
max_connections = 100 # (change requires restart)
#superuser_reserved_connections = 3 # (change requires restart)
#unix_socket_directories = '/var/run/postgresql, /tmp' # comma-separated list of directories
# (change requires restart)
#unix_socket_group = '' # (change requires restart)
#unix_socket_permissions = 0777 # begin with 0 to use octal notation
# (change requires restart)
#bonjour = off # advertise server via Bonjour
# (change requires restart)
#bonjour_name = '' # defaults to the computer name
# (change requires restart)
# - Security and Authentication -
#authentication_timeout = 1min # 1s-600s
#ssl = off
#ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers
#ssl_prefer_server_ciphers = on
#ssl_ecdh_curve = 'prime256v1'
#ssl_dh_params_file = ''
#ssl_cert_file = 'server.crt'
#ssl_key_file = 'server.key'
#ssl_ca_file = ''
#ssl_crl_file = ''
password_encryption = md5 # md5 or scram-sha-256
----------------------------------------------------------------------------
vi /var/lib/pgsql/10/data/pg_hba.conf
----------------------------------------------------------------------------
/var/lib/pgsql/10/data/pg_hba.conf配置文件更改后的最终内容：
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::1/128 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all peer
host replication all 127.0.0.1/32 md5
host replication all ::1/128 md5
host all all 0.0.0.0/0 md5
-----------------------------------------------------------------------------
# 重启postgresql
systemctl restart postgresql-10
systemctl status postgresql-10
# 通过以下步骤，禁用防火墙才能从其他主机访问
systemctl stop firewalld.service
firewall-cmd --state
# 失效防火墙开机自动启动
systemctl disable firewalld.service
systemctl restart postgresql-10.service
四、常用基本操作
# 用postgres 管理员账号登录postgresql数据库
psql -U postgres -h 10.20.253.11 -p 5432 -d 'password'
# 创建数据库新用户，如 la_admin
CREATE USER la_admin WITH PASSWORD '*****';
# 创建用户数据库，如:la_test
CREATE DATABASE la_test OWNER la_admin;
# 将la_test数据库的所有权限都赋予la_admin：
GRANT ALL PRIVILEGES ON DATABASE la_test TO la_admin;
# 创建Schema: grade
create schema grade;
# 创建角色组及其membership
CREATE ROLE father LOGIN SUPERUSER CREATEDB CREATEROLE NOINHERIT;
CREATE ROLE son LOGIN INHERIT encrypted password 'son123';
GRANT father To son;
# 参考
https://www.postgresql.org/docs/10/role-membership.html
五、完整的导入数据操作流程
# 创建用于导入数据的用户
CREATE USER la_user WITH PASSWORD 'user123';
# 查看用户及权限
\du
# 将数据库 la_test 权限授权于 user
GRANT ALL PRIVILEGES ON DATABASE la_test TO la_user;
# 但此时用户还是没有读写权限，需要继续授权表
# Note: 该sql语句必须在所要操作的数据库里执行
# 将当前数据库下 grade schema 的表都授权于 user
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA grade TO la_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA grade TO la_user;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA grade TO la_user;
# 赋予角色权限(因为涉及到要创建表，给了superuser等权限，表创建完毕后要将权限收回)
ALTER ROLE la_user WITH SUPERUSER CREATEDB CREATEROLE;
# 将 mytable 这张表的查询权限授予 user（可选操作）
GRANT SELECT ON TABLE mytable TO la_user;
# 再次确认用户及权限
\du
# 查看某用户的表权限
select * from information_schema.table_privileges where grantee='la_user';
# 撤销在某表上的用户权限（可选操作）
REVOKE privileges ON tablename FROM la_user;
# 例如：REVOKE ALL PRIVILEGES ON grade.study_plan_desc FROM la_user;
# 在本地传输导入数据文件至数据库服务器 scp 本机文件路径
scp /本机文件路径/study_plan_desc.csv user@10.20.253.11:/tmp/
# 通过新建的账号进入postgresql
psql -U user -h 10.xx.xx.xx -p 5432 -d la_test
# 在la_test数据库grade schema上创建表
create table grade.study_plan_desc(
acad_plan character varying not null,
descr character varying,
diploma_descr character varying,
descr_chi character varying
);
# 取消角色权限(权限过大不安全)
ALTER ROLE user WITH NOSUPERUSER NOCREATEDB NOCREATEROLE;
# 查看数据表编码：
\encoding
# 修改数据库编码为支持中文导入的编码GBK：
\encoding GBK
或
update pg_database set encoding = pg_char_to_encoding('GBK') where datname='la_test';
# postgresql中导入数据的语句
COPY grade.study_plan_desc FROM '/tmp/study_plan_desc.csv' DELIMITER ',' CSV HEADER;
# 先把编码改回UTF8，查询时才会显示正常的中文
\encoding UTF8
# postgresql中查询数据的语句
select * from grade.study_plan_desc limit 10;
# 使用命令 \q 退出psql：
postgres=# \q
