PostgreSQL创建数据库及修改参数文件

基础操作之___创建数据库及修改参数文件：

1、使用psql客户端连接数据库

（1）启动数据库

$ pg_ctl start -D /pgdata12/
waiting for server to start....2022-08-18 18:50:01.227 CST [9880] LOG:  starting PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
2022-08-18 18:50:01.228 CST [9880] LOG:  listening on IPv6 address "::1", port 5432
2022-08-18 18:50:01.228 CST [9880] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2022-08-18 18:50:01.230 CST [9880] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2022-08-18 18:50:01.240 CST [9881] LOG:  database system was interrupted; last known up at 2022-08-18 00:06:21 CST
2022-08-18 18:50:01.260 CST [9881] LOG:  database system was not properly shut down; automatic recovery in progress
2022-08-18 18:50:01.260 CST [9881] LOG:  redo starts at 0/163B5C0
2022-08-18 18:50:01.260 CST [9881] LOG:  invalid record length at 0/163B5F8: wanted 24, got 0
2022-08-18 18:50:01.260 CST [9881] LOG:  redo done at 0/163B5C0
2022-08-18 18:50:01.263 CST [9880] LOG:  database system is ready to accept connections
 done
server started
1
2
3
4
5
6
7
8
9
10
11
12
13

（2）登录数据库

$ psql -Upostgres postgres
psql (12.3)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/tmp" at port "5432".
postgres=#
1
2
3
4
5
6
7

（3）创建数据库

postgres=# create database htdb;
CREATE DATABASE

#查看已有数据库
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 htdb      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 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
1
2
3
4
5
6
7
8
9
10
11
12
13
14

（4）创建用户

postgres=# create user htuser with password '********' superuser;
CREATE ROLE

#查看已有角色
htdb=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 htuser    | Superuser                                                  | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
1
2
3
4
5
6
7
8
9
10

（5）使用新建用户登录数据库

$ psql -Uhtuser htdb
psql (12.3)
Type "help" for help.

htdb=# \c
You are now connected to database "htdb" as user "htuser".
1
2
3
4
5
6

2、测试使用IP登录

（1）尝试使用ip登录

$ psql -h192.168.27.40 p5432 -Uhtuser htdb
psql: warning: extra command-line argument "htdb" ignored
psql: error: could not connect to server: could not connect to server: Connection refused
	Is the server running on host "192.168.27.40" and accepting
	TCP/IP connections on port 5432?
1
2
3
4
5

（2）查看监听端口及IP

[postgres@junzi log]$ netstat -anpl|grep 5432|grep -w LISTEN
(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 127.0.0.1:5432          0.0.0.0:*               LISTEN      9960/postgres       
tcp6       0      0 ::1:5432                :::*                    LISTEN      9960/postgres
1
2
3
4
5

（3）编辑配置文件

vi /pgdata12/postgresql.conf
1

将localhost替换为本机IP或“ * ”

listen_addresses = '192.168.27.40'
1

（4）再次尝试登录

$ psql -h192.168.27.40 -p5432 -Uhtuser htdb
psql: warning: extra command-line argument "htdb" ignored
psql: error: could not connect to server: FATAL:  no pg_hba.conf entry for host "192.168.27.40", user "htuser", database "p5432"
1
2
3

还是提示登录失败，因pg_hba.conf未添加IP登录规则，请看如下操作

3、添加用户登录规则

（1）修改pg_hba.conf

vi /pgdata12/pg_hba.conf
1

添加规则：

# IPv6 local connections:
host    htdb            htuser          192.168.27.40/32        md5
1
2

（2）重新加载规则文件

$ pg_ctl reload -D /pgdata12/
server signaled
1
2

（3）再次尝试登录数据库

$ psql -h192.168.27.40 -p5432 -Uhtuser htdb
Password for user htuser: 
psql (12.3)
Type "help" for help.

htdb=# \c
You are now connected to database "htdb" as user "htuser". 
1
2
3
4
5
6
7

4、修改参数

（1）修改最大连接数

修改参数max_connections为618

vi /pgdata12/postgresql.conf
1

max_connections = 618
1

（2）重启数据库

pg_ctl restart -D /pgdata12
1

（初次学习、诸多不足、请多指教）