centos7下postgresql数据库安装及配置

1、安装

#yum install -y postgresql-server

2、postgresql数据库初始化

#service postgresql initdb

3、启动postgresql服务

#systemctl start postgresql

#systemctl enable postgresql

4、查看postgresql状态

#netstat -tlunp|grep 5432

#ss -tlunp|grep 5432

5、连接postgresql数据库

想连接到数据库，需要切换到postgres用户（默认情况下，安装postgresql时会自动生成），使用psql连接到数据库中，在该用户下连接数据库，是不需要密码的。

#查看生成的用户

# tail -1 /etc/passwd

postgres:x:26:26:PostgreSQL Server:/var/lib/pgsql:/bin/bash

#切换用户，登录postgresql

[root@centos7 data]# su - postgres

Last login: Thu Oct 24 16:06:34 CST 2019 on pts/1

-bash-4.2$ psql		#使用psql登录数据库

psql (9.2.24)

Type "help" for help.

postgres=# \l		#列出所有数据库，相当于show databases;

                             List of databases

   Name    |  Owner   | Encoding  | Collate | Ctype |   Access privileges

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

 postgres  | postgres | SQL_ASCII | C       | C     |

 template0 | postgres | SQL_ASCII | C       | C     | =c/postgres          +

           |          |           |         |       | postgres=CTc/postgres

 template1 | postgres | SQL_ASCII | C       | C     | =c/postgres          +

           |          |           |         |       | postgres=CTc/postgres

(3 rows)

postgres=# \q		#退出数据库

-bash-4.2$

6、postgresql的简单配置

postgresql数据库的配置主要是通过修改数据目录下的postgresql.conf文件来实现的

# ll /var/lib/pgsql/data/  #yum安装时路径

total 48

drwx------. 5 postgres postgres    41 Oct 24 15:49 base

drwx------. 2 postgres postgres  4096 Oct 24 15:50 global

drwx------. 2 postgres postgres    18 Oct 24 15:49 pg_clog

-rw-------. 1 postgres postgres  4232 Oct 24 15:49 pg_hba.conf

-rw-------. 1 postgres postgres  1636 Oct 24 15:49 pg_ident.conf

drwx------. 2 postgres postgres    32 Oct 24 15:49 pg_log

drwx------. 4 postgres postgres    36 Oct 24 15:49 pg_multixact

drwx------. 2 postgres postgres    18 Oct 24 15:50 pg_notify

drwx------. 2 postgres postgres     6 Oct 24 15:49 pg_serial

drwx------. 2 postgres postgres     6 Oct 24 15:49 pg_snapshots

drwx------. 2 postgres postgres    25 Oct 24 16:26 pg_stat_tmp

drwx------. 2 postgres postgres    18 Oct 24 15:49 pg_subtrans

drwx------. 2 postgres postgres     6 Oct 24 15:49 pg_tblspc

drwx------. 2 postgres postgres     6 Oct 24 15:49 pg_twophase

-rw-------. 1 postgres postgres     4 Oct 24 15:49 PG_VERSION

drwx------. 3 postgres postgres    60 Oct 24 15:49 pg_xlog

-rw-------. 1 postgres postgres 19805 Oct 24 15:49 postgresql.conf

-rw-------. 1 postgres postgres    57 Oct 24 15:50 postmaster.opts

-rw-------. 1 postgres postgres    92 Oct 24 15:50 postmaster.pid

7、修改监听的Ip和端口

#vim /var/lib/pgsql/data/postgresql.conf

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

 60                               # comma-separated list of addresses;

 61                         # defaults to 'localhost'; use '*' for all

 62                                         # (change requires restart)

 63 #port = 5432                            # (change requires restart)

 修改端口时，默认把前面#去掉，不修改的话默认为5432

 #修改完后重启服务

 #systemctl restart postgresql

8、修改数据库log相关的参数

# - Where to Log -

#log_destination = 'stderr'    # Valid values are combinations of

                               # stderr, csvlog, syslog, and eventlog,

                             # depending on platform.  csvlog

                              # requires logging_collector to be on.

# This is used when logging to stderr:

logging_collector = on       # Enable capturing of stderr and csvlog

#日志默认为打开

                             # into log files. Required to be on for

                                        # csvlogs.

                              # (change requires restart)

# These are only used if logging_collector is on:

#log_directory = 'pg_log'    # directory where log files are written,

                             # can be absolute or relative to PGDATA

log_filename = 'postgresql-%a.log'      # log file name pattern,

                              # can include strftime() escapes

#log_file_mode = 0600                   # creation mode for log files,

                              # begin with 0 to use octal notation

log_truncate_on_rotation = on   # If on, an existing log file with the

                              # same name as the new log file will be

                             # truncated rather than appended to.

                             # But such truncation only occurs on

                            # time-driven rotation, not on restarts

                            # or size-driven rotation.  Default is

                            # off, meaning append to existing files

                            # in all cases.

log_rotation_age = 1d       # Automatic rotation of logfiles will

#日志只保存一天

                            # happen after that time.  0 disables.

log_rotation_size = 0       # Automatic rotation of logfiles will

                             # happen after that much log output.

                              # 0 disables.

9、内存参数的设置

# - Memory -

shared_buffers = 32MB                   # min 128kB

#共享内存的大小，用于共享数据块

10、添加用户、创建数据库、并登录

#授权用户远程登录

#vim /var/lib/pgsql/data/pg_hba.conf	#在最后一行添加

host	all		all		0.0.0.0/0 		md5

#vim /var/lib/pgsql/data/postgresql.conf

listen_addresses='*'		#把localhost更改为*

#systemctl restart postgresql

#创建用户和密码

postgres=# create user wang01 with password '123456';

CREATE ROLE

#创建数据库，基于用户wang01

postgres=# create database test01 owner wang01;

CREATE DATABE

#授权用户

postgres=# grant all privileges on database test01 to wang01;

GRANT

#查看结果

postgres=# \l

                             List of databases

   Name    |  Owner   | Encoding  | Collate | Ctype |   Access privileges

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

 postgres  | postgres | SQL_ASCII | C       | C     |

 template0 | postgres | SQL_ASCII | C       | C     | =c/postgres          +

           |          |           |         |       | postgres=CTc/postgres

 template1 | postgres | SQL_ASCII | C       | C     | =c/postgres          +

           |          |           |         |       | postgres=CTc/postgres

 test      | wang     | SQL_ASCII | C       | C     | =Tc/wang             +

           |          |           |         |       | wang=CTc/wang

 test01    | wang01   | SQL_ASCII | C       | C     | =Tc/wang01           +

           |          |           |         |       | wang01=CTc/wang01

 test02    | postgres | SQL_ASCII | C       | C     |

(6 rows)

#登录

[root@centos7 data]# psql -h 10.0.0.3 -p 5432 -U wang -d test

Password for user wang:

psql (9.2.24)

Type "help" for help.

test=> \l

                             List of databases

   Name    |  Owner   | Encoding  | Collate | Ctype |   Access privileges

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

 postgres  | postgres | SQL_ASCII | C       | C     |

 template0 | postgres | SQL_ASCII | C       | C     | =c/postgres          +

           |          |           |         |       | postgres=CTc/postgres

 template1 | postgres | SQL_ASCII | C       | C     | =c/postgres          +

           |          |           |         |       | postgres=CTc/postgres

 test      | wang     | SQL_ASCII | C       | C     | =Tc/wang             +

           |          |           |         |       | wang=CTc/wang

(4 rows)

11、postgresql数据库基本应用

1、列出所有数据库

mysql: show databases

psql:    \l或\list

2、切换数据库

mysql:  use dbname

psql:     \c dbname

3、列出当前数据库下的所有表

mysql:  show tables

psql:       \d

4、列出指定表的所有字段

mysql:   shwo columns from table_name

psql:    \d table_name

5、查看表的基本情况

mysql:  describe table_name

psql:    \d+ table_name

巴特西

centos7下postgresql数据库安装及配置

最新文章

热门文章