本文主要介绍一下在linux(CentOS7.1)下postgres的安装,后续的一篇博文会着重的介绍一下SQL操作。

注:

  • #:超级用户提示符
  • $:普通用户提示符

安装

1
2
3
4
5
$ sudo yum install postgresql
$ sudo yum install pgadmin3

$ su postgres #切换psql用户下
exit #退出

修改用户postgres密码

1
$ sudo passwd postgres #重置新密码

配置

初始化数据库

1
2
3
4
5
6
$ sudo mkdir -p /var/lib/pgsql/data      #创建pgsql的数据库目录
# cd /var/lib/pgsql
# chown postgres.postgres data #改变目录的所属用户用组
# su postgres #切换到postgres,不然初始化不了

initdb -E UTF-8 -D /var/lib/pgsql/data --locale=en_US.UTF-8 -U postgres -W #在postgres下,初始化数据库(初始化过程中,需要输入root密码)

修改配置文件

修改/var/lib/pgsql/data/postgresql.conf文件,修改postgresql.conf的目的是修改连接权限;

1
# vim /var/lib/pgsql/data/postgresql.conf
1
2
listen_addresses = '*'     #监听所有ip的连接,默认是本机 ip,当然也可以设置局域网ip
port = 5432 #这个不开也行,默认就是5432端口

修改/var/lib/pgsql/data/pg_hba.conf文件,修改pg_hba.conf的目的是设置谁才可以操作数据服务器

1
# vim /var/lib/pgsql/data/pg_hba.conf
1
2
3
4
5
6
7
8
# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 true
host all all 0.0.0.0/0 true #这一行我加的,所有IP和用户,密码对都可以连接
# IPv6 local connections:
host all all ::1/128 true

psql使用

启动

命令:

1
2
3
# systemctl start postgresql.service
# or
# service postgresql start

但是出现了一个问题:

startFailed

使用systemctl status postgresql.service查看日志信息:

fail

检查:

  • 配置防火墙,在/etc/sysconfig/iptables中添加

    1
    2
    -A INPUT -p tcp -m state --state NEW -m tcp --dport 5432 -j ACCEPT //postgresql的端口是5432
    -A INPUT -p tcp -m state --state NEW -m tcp --dport 80 -j ACCEPT
  • 然后设置systemctl enable postgresql.service 来设置/usr/lib/systemd/system/postgresql.service文件。

成功启动的psql如下所示:

status

通过netstat -tpnl | grep 5432来查看5432端口的情况。

port

添加用户和数据库

推荐两个学习的网站:

首先,在linux下进入到postgres用户名下,然后进入到postgres的控制台:

1
2
# su postgres
psql

这样就进入了数据库的控制台

1
2
3
4
5
6
7
8
9
#不要忘记每句话后的";",建立数据库用户时,首先linux系统下要有user用户(adduser user ),然后才可以再把user用户指定为数据库的用户。

CREATE USER matt WITH PASSWORD '123456';#创建数据库用户matt,并设置密码

CREATE DATABASE bank OWNER matt;#创建数据库bank,并指定该数据库的所有者为matt

GRANT ALL PRIVILEGES ON DATABASE bank to matt;#将test数据库的所有权限都赋给用户matt,否则用户matt只能登陆控制台,没有任何数据库操作权限

\q #退出控制台(ctrl+D)

控制台命令:

  • \h:查看SQL命令的解释,比如\h select。
  • \?:查看psql命令列表。
  • \l:列出所有数据库。
  • \c [database_name]:连接其他数据库。
  • \d:列出当前数据库的所有表格。
  • \d [table_name]:列出某一张表格的结构。
  • \du:列出所有用户。
  • \e:打开文本编辑器。
  • \conninfo:列出当前数据库和连接的信息

建立数据表

1
2
3
psql -U matt -d bank -h 127.0.0.1 -p 5432#上面命令的参数含义如下:-U指定用户,-d指定数据库,-h指定服务器,-p指定端口。
# or
psql -U matt -d bank
1
2
3
4
5
-- 建立四张表,不要忘记最后面的“;”号
create table customer(customer_name char(20), customer_street char(30), customer_city char(30), primary key(customer_name));
create table branch(branch_name char(15), branch_city char(30), assets numeric(16,2), primary key(branch_name));
create table account(account_number char(10), branch_name char(15), balance numeric(12,2), primary key(account_number));
create table depositor(customer_name char(20), account_number char(10), primary key(customer_name, account_number));

其他的SQL命令会后续的博客中详细介绍。

遇到的问题

连接数据库失败

错误为org.postgresql.util.PSQLException: FATAL: password authentication failed for user "postgres"
打开Postgresql安装目录下的data文件夹,找到pg_hba.conf文件并打开。修改认证方式,将md5改为trust,然后保存。

1
2
3
4
# TYPE DATABASE USER CIDR-ADDRESS METHOD
# IPv4 local connections:
host all all 127.0.0.1/32 trust#md5改为trust
host all all 0.0.0.0/0 trust

参考Postgresql常见问题

开启端口

有可能是电脑的端口5432的TCP/IP允许没有开启。

JAVA接口

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class table_insert {
public static void main(String args[]) {
Connection c = null;
Statement stmt = null;

long time= 201503292255L;
int client_id=4;
int server_id=3;
int server_location=110000;
String WebName="other";
int count=1000;

try {
Class.forName("org.postgresql.Driver");
c = DriverManager.getConnection("jdbc:postgresql://192.168.81.136:5432/postgres","postgres","psql");
c.setAutoCommit(false);
System.out.println("Opened database successfully");

stmt = c.createStatement();

//String sql = "INSERT INTO traffic (time, client_id, server_id, server_location, web_name, count) VALUES( 201503292255, 1, 3, 110000,'others', 2);";

//String sql="INSERT INTO traffic (time, client_id, server_id, server_location, web_name, count) VALUES( "+time+", "+client_id+", "+server_id+", "+server_location+", '"+WebName+"'"+", "+count+");";

String sql="UPDATE traffic SET count="+count+" WHERE time="+time+" AND client_id="+client_id+" AND server_id="+server_id+" AND server_location="+server_location+" AND web_name= '"+WebName+"';";
stmt.executeUpdate(sql);

stmt.close();//必须从要有对应关闭数据库的操作,否则会出现问题
c.commit();
//c.close();
} catch (Exception e) {
System.err.println( e.getClass().getName()+": "+ e.getMessage() );
System.exit(0);
}
System.out.println("Records created successfully");
}
}

这里必须要有数据库关闭的操作。

连接数过多

So many clients alreay!.
查看的命令:

1
2
3
4
5
6
7
8
--控制台下输入

--当前总共正在使用的连接数
select count(1) from pg_stat_activity;
--显示系统允许的最大连接数
show max_connections;
--显示系统保留的用户数
show superuser_reserved_connections ;

修改/var/lib/pgsql/data/postgresql.conf文件的max_connections,默认为100.但是更多的时候需要考虑的是为什么数据库的连接会那么多,一般情况下是自己的程序出错了。


参考: