PostgreSQL 入门手记

PostgreSQLpostgresql 创建数据库

sudo -u postgres createdb postgres openbravopos

创建一个openbravopos库,指定所有者为postgres

导入数据库

psql -U postgres openbravopos<D:\apattributeadd.sql

错误解决
1) . 错误：

psql: FATAL: Peer authentication failed for user "postgres"

解决办法如下:

1）. 运行下面的命令编辑pg_hba.conf文件

sudo vim /etc/postgresql/9.1/main/pg_hba.conf

2）. 将

# Database administrative login by Unix domain socket

local all postgres peer

改为

# Database administrative login by Unix domain socket

local all postgres trust #这个不需要密码登录

#or

local all postgres md5 #这个需要输入密码登录

3）. 保存后执行下面的命令重新加载配置文件:

 sudo /etc/init.d/postgresql reload

4) . 设置选项说明
1.trust

Allow the connection unconditionally. This method allows anyone that can connect to the PostgreSQL database server to login as any PostgreSQL user they like, without the need for a password.

2.reject

Reject the connection unconditionally. This is useful for “filtering out” certain hosts from a group.

3.md5

Require the client to supply an MD5-encrypted password for authentication.

4.password

Require the client to supply an unencrypted password for authentication. Since the password is sent in clear text over the network, this should not be used on untrusted networks.

5.gss

Use GSSAPI to authenticate the user. This is only available for TCP/IP connections.

6.sspi

Use SSPI to authenticate the user. This is only available on Windows.

7.krb5

Use Kerberos V5 to authenticate the user. This is only available for TCP/IP connections.

8.ident

Obtain the operating system user name of the client (for TCP/IP connections by contacting the ident server on the client, for local connections by getting it from the operating system) and check if it matches the requested database user name.

9.ldap

Authenticate using an LDAP server.

10.cert

Authenticate using SSL client certificates.

11.pam

Authenticate using the Pluggable Authentication Modules (PAM) service provided by the operating system.

2、错误：

conn = psycopg2.connect(database="testdb", user="postgres", password="nopasswd", host="127.0.0.1", port="5432")
File "/usr/lib/python2.7/dist-packages/psycopg2/__init__.py", line 179, in connect
connection_factory=connection_factory, async=async)
psycopg2.OperationalError: FATAL: password authentication failed for user "postgres"
FATAL: password authentication failed for user "postgres"

用jdbc连接Postgresql数据库时经常出现这个错误，这主要是由于用户密码认证方式引起的，Postgresql数据库安装好后默认采用md5密码加密认证方式。
解决方法：

1）. 运行下面的命令编辑pg_hba.conf文件 sudo vim /etc/postgresql/9.1/main/pg_hba.conf

2）. 将

# IPv4 local connections:
host all all 127.0.0.1/32 md5
#更改为
# IPv4 local connections:
host all all 127.0.0.1/32 trust

3）. 保存后执行下面的命令重新加载配置文件: sudo /etc/init.d/postgresql reload

3、错误:

create index for some fields
psql: FATAL: Ident authentication failed for user "root"

解决方法：

1）. 运行下面的命令编辑pg_hba.conf文件

sudo vim /etc/postgresql/9.1/main/pg_hba.conf

2）. 将

# "local" is for Unix domain socket connections only
local all all peer //旧版本该处为ident
#更改为
# "local" is for Unix domain socket connections only
local all all trust

3）. 保存后执行下面的命令重新加载配置文件:

sudo /etc/init.d/postgresql reload

PostgreSQL 修改端口

Windows 下修改

在安装目录下的data文件夹下有个postgresql.conf文件,如:

C:\Program Files\PostgreSQL\9.3\data\postgresql.conf

修改完需要重启一下服务就可以了.

Ubuntu 下修改

在目录/etc/postgresql/9.3/main下有postgresql.conf文件,如:

~$ vi /etc/postgresql/9.3/main/postgresql.conf
#修改完重启一下服务器
~$ sudo service postgresql restart

未经允许不得转载：窗外天空 » PostgreSQL 入门手记