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

一、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 postgrespostgresql12-12.4-1PGDG.rhel7.x86_64postgresql12-libs-12.4-1PGDG.rhel7.x86_64postgresql12-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 ... okcreating subdirectories ... okselecting dynamic shared memory implementation ... posixselecting default max_connections ... 100selecting default shared_buffers ... 128MBselecting default time zone ... Asia/Shanghaicreating configuration files ... okrunning bootstrap script ... okperforming post-bootstrap initialization ... oksyncing data to disk ... okinitdb: warning: enabling "trust" authentication for local connectionsYou 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/psqlpsql (12.4)Type "help" for help.postgres=# create user sansi with password '123';CREATE ROLEpostgres=# create database db1 with encoding='utf8' owner='sansi';CREATE DATABASE#验证登录$ /usr/pgsql-12/bin/psql -U sansi -d db1psql (12.4)

7、环境变量配置

$ cat /home/postgres/.bash_profile# .bash_profile# Get the aliases and functionsif [ -f ~/.bashrc ]; then        . ~/.bashrcfi# User specific environment and startup programsPATH=$PATH:$HOME/.local/bin:$HOME/bin:$PGHOME/binexport PATHexport 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/profileexport PGHOME=/usr/local/pgsqlexport PGDATA=/data/pgsql12/dataexport PATH=$PGHOME/bin:$PATHexport LD_LIBRARY_PATH=/usr/local/pgsql/lib# source /etc/profile

7、初始化数据库

# su - postgresLast 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 ... okcreating subdirectories ... okselecting dynamic shared memory implementation ... posixselecting default max_connections ... 100selecting default shared_buffers ... 128MBselecting default time zone ... Asia/Shanghaicreating configuration files ... okrunning bootstrap script ... okperforming post-bootstrap initialization ... oksyncing data to disk ... okinitdb: warning: enabling "trust" authentication for local connectionsYou 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 startwaiting for server to start.... doneserver started

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

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

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

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