关于数据库:PostgreSQL开发与实战1安装部署

作者：太阳
PostgreSQL 是一款开源的高度可扩大、跨平台的关系型数据库管理系统，反对简单数据类型、ACID 事务、触发器、存储过程等个性，具备弱小的查问优化器和完整性束缚，领有宏大的寰球社区反对。它实用于各种规模和类型的利用，提供稳固牢靠的数据库解决方案。上面将别离介绍 PostgreSQL 的 rpm 包装置和源码装置部署的具体步骤。

1、装置 RPM 包

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

# rpm -qa | grep postgres
postgresql12-12.4-1PGDG.rhel7.x86_64
postgresql12-libs-12.4-1PGDG.rhel7.x86_64
postgresql12-server-12.4-1PGDG.rhel7.x86_64

2、增加用户

# groupadd postgres
# useradd -g postgres postgres

3、配置数据、日志目录

# mkdir -p /data/pgsql/{data,logs}
# chown -R postgres:postgres /data/pgsql

4、初始化数据库

# su - postgres
$
$  /usr/pgsql-12/bin/initdb -E utf8 -D /data/pgsql/data/
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /data/pgsql/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Shanghai
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
    /usr/pgsql-12/bin/pg_ctl -D /data/pgsql/data/ -l logfile start

5、启动数据库

$ /usr/pgsql-12/bin/postgres -D /data/pgsql/data/ > /data/pgsql/logs/postgres.log &
[1] 18534

6、创立用户和数据库

$ /usr/pgsql-12/bin/psql
psql (12.4)
Type "help" for help.
postgres=# create user sansi with password '123';
CREATE ROLE
postgres=# create database db1 with encoding='utf8' owner='sansi';
CREATE DATABASE

#验证登录
$ /usr/pgsql-12/bin/psql -U sansi -d db1
psql (12.4)

7、环境变量配置

$ cat /home/postgres/.bash_profile
# .bash_profile
# Get the aliases and functions
if [-f ~/.bashrc]; then
        . ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/.local/bin:$HOME/bin:$PGHOME/bin
export PATH
export PGHOME=/usr/local/pgsql          #软件装置目录
export PGDATA=/data/pgsql12/data        #PG 数据目录

$ source /home/postgres/.bash_profile

1、装置源码软件包

# yum install lbzip2
# wget https://ftp.postgresql.org/pub/source/v12.2/postgresql-12.2.tar.bz2
# tar xf postgresql-12.2.tar.bz2 -C /usr/local/

2、装置依赖包

# yum install gcc gcc-c++ readline-devel readline readline-dev zlib-devel

3、编译装置

# cd /usr/local/postgresql-12.2/
# ./configure --prefix=/usr/local/pgsql
# make && make install

编译时一些必要的参数：

-with-perl : 编译时增加该参数才可能应用 perl 语法的 PL/Perl 过程语言写自定义函数，个别都须要。须要提前装置好相干的 perl 开发包：libperl-dev
-with-python : 编译时增加该参数才可能应用 python 语法的 PL/Perl 过程语言写自定义函数，个别都须要。须要提前装置好相干的 python 开发包：python-dev
-with-blocksize=128 –with-wal-blocksize=128 : 默认状况下 PG 数据库的数据页大小为 8KB，若数据库用来做数仓业务，可在编译时将数据页进行调整，以进步磁盘 IO
-enable-thread-safety : 保障客户端线程平安，该参数在 PG 9.0 以上默认线程平安，如果装置的是 8.0 版本须要手动指定该参数

4、增加用户

# groupadd postgres
# useradd -g postgres postgres

5、配置数据、日志目录

# mkdir -p /data/pgsql12/{data,logs}
# chown -R postgres:postgres /data/pgsql12/

6、配置环境变量

# vi /etc/profile
export PGHOME=/usr/local/pgsql
export PGDATA=/data/pgsql12/data
export PATH=$PGHOME/bin:$PATH
export LD_LIBRARY_PATH=/usr/local/pgsql/lib

# source /etc/profile

7、初始化数据库

# su - postgres
Last login: Tue Sep  1 22:52:40 CST 2020 on pts/0

$ initdb -D /data/pgsql12/data/
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /data/pgsql12/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Shanghai
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections

You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D /data/pgsql12/data/ -l logfile start

8、启动数据库

$ pg_ctl -D /data/pgsql12/data/ -l logfile start
waiting for server to start.... done
server started

9、验证登录并批改用户验证形式

在对数据库做初始化时会创立一个与操作系统同名的用户在数据库的超级用户，所以在改 OS 用户下登录数据库默认不须要应用账户和明码，当然也能够通过批改 pg_hba.conf 配置文件进行管制。

## 通过 postgres 用户免密登录数据库，并批改该数据库用户明码
$ psql
psql (12.2)
Type "help" for help.
postgres=# alter user postgres password '123';
ALTER ROLE
postgres=# \q
## 批改认证配置文件，要求全副进行 md5 认证 (将 trust 改为 md5)
local   all             all                                     md5
host    all             all             127.0.0.1/32            md5
## 批改配置文件后 reload 从新载入配置文件
$ pg_ctl reload
## 再次验证是否必须明码登录
$ psql
Password for user postgres:                 // 输出明码
psql (12.2)
Type "help" for help.

更多技术信息请查看云掣官网 https://yunche.pro/?t=yrgw

关于数据库:PostgreSQL开发与实战1安装部署

一、rpm 包装置部署

二、源码装置