安装postgreSQL数据库

postgreSQL

---

环境信息

软件	版本
CentOS	7.9.2009
PostgreSQL	13

安装postgreSQL

CentOS 7 自带了9.2.24版本，本文安装最新的13版本

yum search postgresql-s

================================================================= N/S matched: postgresql-s ==================================================================
postgresql-server.x86_64 : The programs needed to create and run a PostgreSQL server
postgresql-static.i686 : Statically linked PostgreSQL libraries
postgresql-static.x86_64 : Statically linked PostgreSQL libraries

  Name and summary matches only, use "search all" for everything.
[root@FC data]# yum info postgresql-server.x86_64
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
Available Packages
Name        : postgresql-server
Arch        : x86_64
Version     : 9.2.24
Release     : 4.el7_8
Size        : 3.8 M
Repo        : base/7/x86_64
Summary     : The programs needed to create and run a PostgreSQL server
URL         : http://www.postgresql.org/
License     : PostgreSQL
Description : PostgreSQL is an advanced Object-Relational database management system (DBMS).
            : The postgresql-server package contains the programs needed to create
            : and run a PostgreSQL server, which will in turn allow you to create
            : and maintain PostgreSQL databases.

1. 安装RPM仓库

sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

2. 安装PostgreSQL

sudo yum install -y postgresql13-server

3. 安装完成后，使用以下命令初始化PostgreSQL数据库：

sudo /usr/pgsql-13/bin/postgresql-13-setup initdb

4. 安装完成后会建立一个名叫postgres的linux用户（默认密码为空），和自带3个数据库postgres、template0、template1

配置postgreSQL

1. 启动PostgreSQL服务，并配置为其能够在系统启动时启动

sudo systemctl enable postgresql-13
sudo systemctl start postgresql-13
sudo systemctl status postgresql-13

2. 查看PostgreSQL运行状态

sudo systemctl status postgresql-13

会输出一下内容

● postgresql-13.service - PostgreSQL 13 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-13.service; enabled; vendor preset: disabled)
   Active: active (running) since Sun 2021-05-09 15:03:33 CST; 24min ago
     Docs: https://www.postgresql.org/docs/13/static/
  Process: 20039 ExecStartPre=/usr/pgsql-13/bin/postgresql-13-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 20045 (postmaster)
   CGroup: /system.slice/postgresql-13.service
           ├─20045 /usr/pgsql-13/bin/postmaster -D /var/lib/pgsql/13/data/
           ├─20047 postgres: logger 
           ├─20049 postgres: checkpointer 
           ├─20050 postgres: background writer 
           ├─20051 postgres: walwriter 
           ├─20052 postgres: autovacuum launcher 
           ├─20053 postgres: stats collector 
           └─20054 postgres: logical replication launcher 

May 09 15:03:33 FC systemd[1]: Starting PostgreSQL 13 database server...
May 09 15:03:33 FC postmaster[20045]: 2021-05-09 15:03:33.677 CST [20045] LOG:  redirecting log output to logging collector process
May 09 15:03:33 FC postmaster[20045]: 2021-05-09 15:03:33.677 CST [20045] HINT:  Future log output will appear in directory "log".
May 09 15:03:33 FC systemd[1]: Started PostgreSQL 13 database server.

开启远程访问

1. 修改postgresql.conf

sudo vi /var/lib/pgsql/data/postgresql.conf

在最上面位置添加listen_addresses = '*'

2. 修改pg_hba.conf

sudo nano /var/lib/pgsql/data/pg_hba.conf

在文件末尾添加host all all 0.0.0.0/0 md5

# TYPE  DATABASE  USER  CIDR-ADDRESS  METHOD
host  all  all 0.0.0.0/0 md5

3. 解决解决root用户下无法通过psql -U postgres登录的问题

修改pg_hba.conf

# 修改前
local    all    all    peer
# 修改后
local    all    all    trust

基本使用

1. 登录postgres用户，并登录数据库终端

# 登录postgres使用
su postgres
psql
# 直接使用 
psql -U postgres

2. 修改密码

# 方法一
alter user postgres with password 'NewPassword'; 
# 方法二
\password

3. 查看版本

select version();
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 13.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit

4. 数据库相关语法示例

# 创建数据库
CREATE DATABASE mydb;

#查看所有数据库
\l

# 切换当前数据库
\c mydb

# 创建表
CREATE TABLE test(id int,body varchar(100));

# 查看当前数据库下所有表
\d

5. 用户与访问授权语法示例

#新建用户
CREATE USER test WITH PASSWORD 'test';

#赋予指定账户指定数据库所有权限
GRANT ALL PRIVILEGES ON DATABASE mydb TO test;

#移除指定账户指定数据库所有权限
REVOKE ALL PRIVILEGES ON DATABASE mydb TO test

权限代码：SELECT、INSERT、UPDATE、DELETE、TRUNCATE、REFERENCES、TRIGGER、CREATE、CONNECT、TEMPORARY、EXECUTE、USAGE