Shulou(Shulou.com)06/01 Report--

1. Log in locally to postgresql, create database authorization, set password

Server Local login postgresql database (default is no password required)

postgres@localhost ~]$ psql

psql.bin (9.5.9)

Type "help" for help.

Create a character and set a password for the character:

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

CREATE ROLE

Modify database user and password:

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

ALTER ROLE

Specify character set to create database testdb1 and license to testwjw

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

CREATE DATABASE

Authorized:

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

GRANT

2. Modify the ports and listening hosts in the postgresql.conf file:

postsql default after installation is to listen to the local machine 127.0.0.1 default port is 5432, is not able to log in remotely, so to modify the listening host address, and modify the default port is:36985

The postgresql database configuration file is:postgresql.conf, located in: data directory specified when postgresql is initialized:

[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)

Change the listening host to * and port to: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)

Modify the configuration file pg_hba.conf to allow remote ip access to the local database, and set the server to log in locally. The postgresql database requires a password to log in.

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

host all all 60.223.153.25/32 trust

host all all 127.0.0.1/32 password

#host replication postgres 127.0.0.1/32 trust

Allow 60.223.153.25ip to access server postgresql database

psql -Utestwjw -dpostgres -p36985 -h 127.0.0.1 This way to access database 127.0.0.1 database you must enter a password to be able to

3. Restart postgresql service takes effect:

[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.)

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

4. Login database:

[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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.