系统
[root@reg dev-flows-api-golang]# cat /etc/redhat-release
CentOS Linux release 7.4.1708 (Core)
安装
1.安装rpm包,打开链接https://yum.postgresql.org/repopackages.php,找到符合自己系统的包,例如CentOS 7 - x86_64
yum install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm -y
yum install -y postgresql96-server postgresql96-contrib -y
初始化数据库
[root@i-zq8y5vs8 ~]# /usr/pgsql-9.6/bin/postgresql96-setup initdb
Initializing database ... OK
启动服务
systemctl start postgresql-9.6
systemctl enable postgresql-9.6
(如果未安装firewalld防火墙可跳过这一步)
firewall-cmd --add-service=postgresql --permanent
firewall-cmd --reload
修改默认PostgreSQL用户密码
[root@i-zq8y5vs8 ~]# su - postgres
-bash-4.2$ psql -U postgres
psql (9.6.9)
Type "help" for help.
postgres=#
postgres=# ALTER USER postgres with encrypted password 'xboU58vQbAbN';
ALTER ROLE
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
创建用户,数据库,以及赋予权限给特定的用户
-bash-4.2$ psql
psql (9.6.9)
Type "help" for help.
postgres=# CREATE USER tcepaas WITH PASSWORD 'xboU58vQbAbN';
CREATE ROLE
postgres=# CREATE DATABASE tenxcloud_2_0 OWNER tcepaas;
CREATE DATABASE
postgres=# GRANT ALL PRIVILEGES ON DATABASE tenxcloud_2_0 TO tcepaas;
GRANT
postgres=# \q
-bash-4.2$ exit
logout
[root@i-zq8y5vs8 ~]# sudo adduser tcepaas
[root@i-zq8y5vs8 ~]# sudo passwd tcepaas
Changing password for user tcepaas.
New password:
Retype new password:
passwd: all authentication tokens updated successfully.
[root@i-zq8y5vs8 ~]# su - tcepaas
[tcepaas@i-zq8y5vs8 ~]$ ps
ps ps2pdf ps2pdf14 ps2ps2 psfgettable psktool pstree
ps2ascii ps2pdf12 ps2pdfwr psed psfstriptable psql pstree.x11
ps2epsi ps2pdf13 ps2ps psfaddtable psfxtable pstack pstruct
[tcepaas@i-zq8y5vs8 ~]$ psql -d tenxcloud_2_0
psql (9.6.9)
Type "help" for help.
tenxcloud_2_0=> \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
---------------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
tenxcloud_2_0 | tcepaas | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/tcepaas +
| | | | | tcepaas=CTc/tcepaas
(4 rows)
开启远程访问权限以及赋给特定IP的访问权限
要不然会报相关的错误如下图
cd /var/lib/pgsql/9.6/data
vi pg_hba.conf
....
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host tenxcloud_2_0 tcepaas 10.39.0.119/32 trust
host all all 0.0.0.0/32 md5
# IPv6 local connections:
host all all ::1/128 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local replication postgres peer
#host replication postgres 127.0.0.1/32 ident
#host replication postgres ::1/128 ident
vi /var/lib/pgsql/9.6/data/postgresql.conf
...
# - Connection Settings -
listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
...
安装完成
用法
列出当前数据库所有表
\dt
列出表名
SELECT tablename FROM pg_tables;
WHERE tablename NOT LIKE ‘pg%’
AND tablename NOT LIKE ‘sql_%’
ORDER BY tablename;
列出数据库名
\l
或
SELECT datname FROM pg_database;
切换数据库
\c 数据库名
1、通过命令行查询
\d 数据库 —— 得到所有表的名字
\d 表名 —— 得到表结构
2、通过SQL语句查询
"select * from pg_tables" —— 得到当前db中所有表的信息(这里pg_tables是系统视图)
"select tablename from pg_tables where schemaname='public'" —— 得到所有用户自定义表的名字(这里"tablename"字段是表的名字,"schemaname"是schema的名字。用户自定义的表,如果未经特殊处理,默认都是放在名为public的schema下)
当 DROP DATABAS your_target_database; 提示有session占用时, 用此方法 删除
参考:
CentOS7安装并配置PostgreSQL
命令行方式登录PostgreSQL、创建用户和数据库并赋权
PostgreSQ 连接问题 FATAL: no pg_hba.conf entry for host