2018-05-16 CentOS 6.9下安装配置Postgr
2018-05-16 本文已影响0人
张大志的博客
1、安装
操作系统：CentOS6.9_x64
PostgreSQL官方网址： https://www.postgresql.org/
安装过程参考：https://www.postgresql.org/download/linux/redhat/
yum install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-6-x86_64/pgdg-centos96-9.6-3.noarch.rpm
yum install postgresql96 #安装客户端程序包
yum install postgresql96-server #安装服务端程序包
[root@host yum.repos.d]# service postgresql-9.6 initdb #初始化数据库
Initializing database: [ OK ]
[root@host yum.repos.d]# chkconfig postgresql-9.6 on #设置开机启动
[root@host yum.repos.d]# service postgresql-9.6 start #启动服务
Starting postgresql-9.6 service: [ OK ]
[root@host yum.repos.d]# psql --version
psql (PostgreSQL) 9.6.9
2、配置数据库
修改用户密码
[root@host yum.repos.d]# su - postgres #切换用户
[postgres@host ~]$ psql -U postgres #登陆数据库，不用指明-U也可以登陆，默认是以 postgres用户登陆
psql (9.6.9)
Type "help" for help.
postgres=# ALTER USER postgres WITH PASSWORD '123' #修改登陆到数据库的用户postgres的登陆密码
postgres-# \q #退出数据库
修改配置
[root@host yum.repos.d]# vim /var/lib/pgsql/9.6/data/postgresql.conf
listen_addresses = '*' #定义监听的端口
[root@host yum.repos.d]# vim /var/lib/pgsql/9.6/data/pg_hba.conf #定义允许哪些主机访问
# "local" is for Unix domain socket connections only
# IPv4 local connections:
host all all 0.0.0.0/0 trust #设置成trust表示不用密码就可以远程登录
host all all 0.0.0.0/0 md5 #设置成md5表示远程登录时需要密码
[root@host yum.repos.d]# service postgresql-9.6 restart
3、使用数据库
[root@host yum.repos.d]# su - postgres
[postgres@host ~]$ psql #本机登陆数据库时不用用户名和密码
psql (9.6.9)
Type "help" for help.
postgres=# CREATE USER useradmin WITH PASSWORD '123456';
CREATE ROLE
postgres=# CREATE DATABASE testdb OWNER useradmin;
CREATE DATABASE
postgres=# GRANT ALL PRIVILEGES ON DATABASE testdb to useradmin;
GRANT
postgres=# \du #显示用户
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
useradmin | | {}
postgres=# \c testdb #切换数据库
You are now connected to database "testdb" as user "postgres".
create table students (
id bigserial primary key,
name varchar(20) NOT NULL
); #创建表
insert into students values (1,'stu1'); #插入数据
select * from students ;
drop table students ;
testdb=# \l #列出所有数据库
testdb=# \c testdb #切换数据库
You are now connected to database "testdb" as user "postgres".
testdb=# \d #查看此数据库下的所有表
List of relations
Schema | Name | Type | Owner
--------+-----------------+----------+----------
public | students | table | postgres
public | students_id_seq | sequence | postgres
testdb=# \d students #查看指定表的所有字段
Table "public.students"
Column | Type | Modifiers
--------+-----------------------+-------------------------------------------------------
id | bigint | not null default nextval('students_id_seq'::regclass)
name | character varying(20) | not null
Indexes:
"students_pkey" PRIMARY KEY, btree (id)
testdb=# \d+ students #查看指定表的基本情况
Table "public.students"
Column | Type | Modifiers | Storage | Stats target | Description
--------+-----------------------+-------------------------------------------------------+----------+--------------+-------------
id | bigint | not null default nextval('students_id_seq'::regclass) | plain | |
name | character varying(20) | not null | extended | |
Indexes:
"students_pkey" PRIMARY KEY, btree (id)
4、远程连接
yum install postgresql #安装客户端程序包
[root@huabei ~]#psql --help
[root@huabei ~]#psql -h ip地址 -U postgres #会提示输入密码
Password for user postgres:
[root@huabei ~]#psql -h p地址 -U useradmin -d testdb #因为此用户只对testdb数据库有权限，所以远程连接时要指明连接到哪个数据库
Password for user useradmin:
