1.本地登陆postgresql,建库授权，设置密码

服务器本地登陆postgresql数据库（默认是不需要密码的）

postgres@localhost ~]$ psql

psql.bin (9.5.9)

Type "help" for help.

创建角色，并且给角色设置密码:

postgres=# create user testwjw with password 'Zykj@5&^%996';

CREATE ROLE

修改数据库用户和密码：

postgres=# alter user testwjw with password '558996';

ALTER ROLE

指定字符集创建数据库testdb1，并且授权给testwjw

postgres=# create database testdb1 with encoding='utf8' owner=testwjw;

CREATE DATABASE

授权：

postgres=# grant all privileges on database testdb1 to testwjw;

GRANT

2.修改postgresql.conf文件中的端口和监听主机:

postsql默认安装后是监听本机127.0.0.1 默认端口是5432,是不能够远程登陆的，所以要修改监听主机地址，同时修改默认的端口为:36985

postgresql数据库的配置文件是:postgresql.conf,所在位置是：postgresql初始化时所指定的data数据目录下：

[postgres@localhost ~]$ ll /data/postgresql/data/postgresql.conf

-rw------- 1 postgres postgres 21305 Oct 3 11:18 /data/postgresql/data/postgresql.conf

[postgres@localhost ~]$ egrep "listen_addresses|5432"/data/postgresql/data/postgresql.conf

listen_addresses = 'localhost'# what IP address(es) to listen on;

port = 5432# (change requires restart)

修改监听主机为*，端口为:36985

[postgres@localhost ~]$ egrep "listen_addresses|36985" /data/postgresql/data/postgresql.conf

listen_addresses = '*'# what IP address(es) to listen on;

port = 36985# (change requires restart)

修改配置文件pg_hba.conf ，允许远程ip访问本地数据库，以及设置服务器本地登陆postgresql数据库要求输入密码才可以登陆

[postgres@localhost ~]$ egrep "60.223.153.25|127.0.0.1" /data/postgresql/data/pg_hba.conf

hostallall60.223.153.25/32trust

hostallall127.0.0.1/32password

#hostreplicationpostgres127.0.0.1/32trust

允许60.223.153.25ip访问服务器postgresql数据库

psql -Utestwjw -dpostgres -p36985 -h 127.0.0.1 这样访问数据库127.0.0.1数据库必须输入密码才可以

3.重启postgresql服务生效：

[postgres@localhost ~]$ pg_ctl -D /data/postgresql/data -l /data/postgresql/log/postgres.log restart

waiting for server to shut down....LOG: received fast shutdown request

LOG: aborting any active transactions

LOG: autovacuum launcher shutting down

LOG: shutting down

LOG: database system is shut down

done

server stopped

server starting

[postgres@localhost ~]$ netstat -lntup|grep postgres

(Not all processes could be identified, non-owned process info

will not be shown, you would have to be root to see it all.)

tcp0 0 0.0.0.0:36985 0.0.0.0:* LISTEN 6472/postgres

4.登陆数据库:

[postgres@localhost ~]$ psql

psql.bin: could not connect to server: No such file or directory

Is the server running locally and accepting

connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

[postgres@localhost ~]$ psql -Utestwjw -dpostgres -p36985 -h 127.0.0.1

Password for user testwjw:

psql.bin: fe_sendauth: no password supplied

[postgres@localhost ~]$ psql -Utestwjw -dpostgres -p36985 -h 127.0.0.1

Password for user testwjw:

psql.bin (9.5.9)

Type "help" for help.

postgres=> \q

[postgres@localhost ~]$ psql -Utestwjw -dtestdb1 -p36985 -h 127.0.0.1

Password for user testwjw:

psql.bin (9.5.9)

Type "help" for help.

testdb1=> select * from t;

id | nan | note

----+-----+-------

1 | t | TRUE

2 | f | FALSE

3 | t | tRue

4 | f | fAlse

11 || null

11 || NULL

7 | t | 't'

8 | f | 'f'

9 | t | 'yes'

10 | f | '0'

(10 rows)

testdb1=>

[postgres@localhost ~]$ psql -Utestwjw -dtestdb1 -p36985

psql.bin (9.5.9)

Type "help" for help.

testdb1=> \q

[postgres@localhost ~]$ psql -Utestwjw -dtestdb2 -p36985

psql.bin (9.5.9)

Type "help" for help.

testdb2=> \q