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
