其他
CentOS6.7安装PostgreSQL10.9详细教程
前 言
PostgreSQL 是一个基于 POSTGRES 的对象关系数据库管理系统（ORDBMS），版本4.2，由加州大学伯克利分校计算机科学系开发。POSTGRES 开创了许多概念，这些概念在很久以后才在某些商业数据库系统中出现。
PostgreSQL 最初设想于 1986 年，当时被叫做 Berkley Postgres Project。该项目一直到 1994 年都处于演进和修改中，直到开发人员 Andrew Yu 和 Jolly Chen 在 Postgres 中添加了一个 SQL（StructuredQuery Language，结构化查询语言）翻译程序，该版本叫做Postgres95，在开放源代码社区发放。1996 年，再次对 Postgres95 做了较大的改动，并将其作为 PostgresSQL6.0 版发布。一般 Linux 系统自带了 Postgresql,但比较旧，不太好用，工欲善其事，必先利其器，那么我们来安装一个比较合适的版本吧。
一、 软件包下载
进入 PostgreSQL 官网 https://www.postgresql.org/
点击 Download 然后选择相对应的安装方式，我这里选择源码安装，点击 Source 选择相应的版本，我这里选择的是 10.9 版本，如下图所示。
当点击版本时会出现两种格式 .bz2 和 .gz 格式，一般会选择 压缩后较小的 .bz2 格式，注意观察，文件下两行有 .md5 和 .sha256 格式的文件，此文件记录了使用对应加密算法加密第一行文件的完整的加密算法值，当下载完文件后用于比对下载文件的完整性。
点击第二个文件查看其中的 MD5 加密值如下：
62f755219b9b05c25f24737405a5aae1 postgresql-10.9.tar.bz2下载完之后，由于服务器一般都属于内网环境，无法上网，则通过 SFTP 上传至服务器 /home/postgresql 目录，然后检验 MD5 值，保证软件下载过程中没有损坏，可正常使用，下面是检验出来的 MD5 值与上图下载中的一致，说明软件可正常使用。
[root@JiekeXupostgresql]# md5sum postgresql-10.9.tar.bz262f755219b9b05c25f24737405a5aae1 postgresql-10.9.tar.bz2[root@JiekeXupostgresql]# ll -lhtotal 19M-rw-r--r-- 1 rootroot 19M Jul 7 23:29postgresql-10.9.tar.bz2二、 操作系统相关配置
1、内核参数设置
vi /etc/sysctl.conf############################forpostgresql###########kernel.shmall =4294967296kernel.shmmax=135497418752kernel.shmmni =4096kernel.sem = 5010064128000 50100 1280fs.file-max =7672460fs.aio-max-nr =1048576net.ipv4.ip_local_port_range= 9000 65000net.core.rmem_default= 262144net.core.rmem_max= 4194304net.core.wmem_default= 262144net.core.wmem_max= 4194304net.ipv4.tcp_max_syn_backlog= 4096net.core.netdev_max_backlog= 10000net.ipv4.netfilter.ip_conntrack_max= 655360net.ipv4.tcp_timestamps= 0net.ipv4.tcp_tw_recycle=1net.ipv4.tcp_timestamps=1net.ipv4.tcp_keepalive_time= 72net.ipv4.tcp_keepalive_probes= 9net.ipv4.tcp_keepalive_intvl= 7vm.zone_reclaim_mode=0vm.dirty_background_bytes= 40960000vm.dirty_ratio =80vm.dirty_expire_centisecs= 6000vm.dirty_writeback_centisecs= 50vm.swappiness=0vm.overcommit_memory= 0vm.overcommit_ratio= 90使用命令 sysctl -p 使其生效
2、操作系统的限制
这些在安装 Oracle 和 MySQL 已经说过，这里就不再说明了。
vi /etc/security/limits.conf * soft nofile 131072 * hard nofile 131072 * soft nproc 131072 * hard nproc 131072 * soft core unlimited * hard core unlimited * soft memlock 500000000 * hard memlock 500000000
3、limits.d 目录
此目录下的文件也是文件限制，上文已经设置了，这里如果还存在的话，则以此文件限制为准，故删除。
more /etc/security/limits.d/90-nproc.conf# Default limit for number of user's processes toprevent# accidental fork bombs.# See rhbz #432903 for reasoning. * soft nproc 1024root soft nproc unlimited
系统其它普通用户最多进程默认是 1024 个，而 root 用户是 unlimited (不受限制）
ls /etc/security/limits.d/*rm -f /etc/security/limits.d/*4、关闭selinux 和 防火墙
vi /etc/sysconfig/selinux SELINUX=disabled SELINUXTYPE=targeted
需要关闭 SELINUX 和 操作系统防火墙 iptables
设置 selinux 为 disable 并重启操作系统；用 chkconfig iptables off 命令关闭防火墙。
5、配置yum 本地源，安装以下依赖包
yum -y install coreutils glib2 lrzsz mpstat dstatsysstat e4fsprogs xfsprogs ntp readline-devel zlib-devel openssl-develpam-devel libxml2-devel libxslt-devel python-devel tcl-devel gcc makesmartmontools flex bison perl-devel perl-Ext Utils* openldap-devel jadetex openjade bzip26、创建普通用户
useradd postgresql passwd postgresql
7、建立数据目录并上传、解压安装包
su – postgresqlmkdir pgdatatar -xvf postgresql-10.9.tar.bz2
做一个软链接方便以后管理与升级
ln -s postgresql-10.9 postgresql8、编译安装
使用如下命令进行编译安装
cd /home/postgresql/postgresql./configure --prefix=/home/postgresql/postgresql--with-perl --with-pythonmake world -j 4 make install-world
编译过程中的部分日志
gcc -Wall -Wmissing-prototypes -Wpointer-arith-Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute-Wformat-security -fno-strict-aliasing -fwrapv -O2 -fPIC -shared -o pgcrypto.sopgcrypto.o px.o px-hmac.o px-crypt.o crypt-gensalt.o crypt-blowfish.o crypt-des.ocrypt-md5.o md5.o sha1.o internal.o internal-sha2.o blf.o rijndael.opgp-mpi-internal.o imath.o mbuf.o pgp.o pgp-armor.o pgp-cfb.o pgp-compress.opgp-decrypt.o pgp-encrypt.o pgp-info.o pgp-mpi.o pgp-pubdec.o pgp-pubenc.opgp-pubkey.o pgp-s2k.o pgp-pgsql.o -L../../src/port -L../../src/common -Wl,--as-needed-Wl,-rpath,'/home/postgresql/pgdata/lib',--enable-new-dtags -lzmake[2]: Leaving directory`/home/postgresql/postgresql-10.9/contrib/pgcrypto'make[1]: Leaving directory `/home/postgresql/postgresql-10.9/contrib'PostgreSQL, contrib, and documentation successfullymade. Ready to install.9、环境变量配置
编辑 postgresql 家目录下的 .bashrc文件，添加如下所示内容，注意这里不推荐使用 .bash_profile 文件或者 .profile 文件，因为有时候在图形化界面中打开终端，这两个文件不生效。如果此配置文件对所有用户生效，则需要配置 /etc/profile 文件。
[postgresql@JiekeXu ~]$ vi .bashrcexport PS1="$USER@`/bin/hostname -s`->" export PGPORT=5432 export PGDATA=/home/postgresql/pgdata export.utf8 export PGHOME=/home/postgresql/postgresqlexportLD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH export PATH=$PGHOME/bin:$PATH:. export DATE=`date +"%Y%m%d%H%M"` export MANPATH=$PGHOME/share/man:$MANPATH export PGHOST=$PGDATA export PGUSER=postgres export PGDATABASE=postgres alias rm='rm -i' alias ll='ls -lh'
使其生效，退出重新登陆或者 source 。
source .bashrc
10、创建数据库簇（即初始化数据库）
initdb -D $PGDATA -E UTF8 --locale=C -U postgrespostgresql@JiekeXu-> initdb -D $PGDATA -E UTF8 --locale=C -U postgresThe files belonging to this database system will beowned by user "postgresql".This user must also own the server process. The database cluster will be initialized with locale"C".The default text search configuration will be set to"english". Data page checksums are disabled. fixing permissions on existing directory /home/postgresql/pgdata... okcreating subdirectories ... okselecting default max_connections ... 100selecting default shared_buffers ... 128MBselecting default timezone ... PRCselecting dynamic shared memory implementation ...posixcreating configuration files ... okrunning bootstrap script ... okperforming post-bootstrap initialization ... oksyncing data to disk ... ok WARNING: enabling "trust" authentication forlocal connectionsYou can change this by editing pg_hba.conf or usingthe option -A, or--auth-local and --auth-host, the next time you runinitdb. Success. You can now start the database server using: pg_ctl -D/home/postgresql/pgdata -l logfile start postgresql@JiekeXu->11、contrib 工具安装
contrib 下面有一些比较使用的工具，我们也可以选择安装一下，基本上直接编译、安装即可。
cd /home/postgresql/postgresql/contribmakemake install12、数据库配置文件相关
在数据目录下编辑两个配置文件 postgresql.conf和pg_hba.conf
（1）配置文件
cd $PGDATA
vi postgresql.conf
listen_addresses = '0.0.0.0' port = 5432 max_connections = 200 unix_socket_directories = '.' tcp_keepalives_idle = 60 tcp_keepalives_interval = 10 tcp_keepalives_count = 10 shared_buffers = 512MB dynamic_shared_memory_type = posix vacuum_cost_delay = 0 bgwriter_delay = 10ms bgwriter_lru_maxpages = 1000 bgwriter_lru_multiplier = 10.0 bgwriter_flush_after = 0 old_snapshot_threshold = -1 backend_flush_after = 0 wal_level = replica synchronous_commit = off full_page_writes = on wal_buffers = 16MB wal_writer_delay = 10ms wal_writer_flush_after = 0 checkpoint_timeout = 30min max_wal_size = 1GB min_wal_size = 128MB checkpoint_completion_target = 0.05 checkpoint_flush_after = 0 random_page_cost = 1.3 log_destination = 'csvlog' logging_collector = on log_truncate_on_rotation = on log_checkpoints = on log_connections = on log_disconnections = on log_error_verbosity = verbose autovacuum = on log_autovacuum_min_duration = 0 autovacuum_naptime = 20s autovacuum_vacuum_scale_factor = 0.05 autovacuum_freeze_max_age = 1500000000 autovacuum_multixact_freeze_max_age = 1600000000 autovacuum_vacuum_cost_delay = 0 vacuum_freeze_table_age = 1400000000 vacuum_multixact_freeze_table_age = 1500000000 datestyle = 'iso, mdy' timezone = 'PRC' lc_messages = 'C' lc_monetary = 'C' lc_numeric = 'C' lc_time = 'C' default_text_search_config = 'pg_catalog.english' shared_preload_libraries='pg_stat_statements'
（2）配置pg_hba.conf
数据库防火墙文件名字 pg_hba.conf，将以下内容追加到文件末尾，表示允许网络用户使用用户密码连接到你的 postgresql 数据库.
host all all 0.0.0.0/0 md5
（3）数据库启动并查看相关状态、进程
postgresql@JiekeXu-> pg_ctl startpostgresql@JiekeXu-> pg_ctl statuspg_ctl: no server running
那么，相应的停止命令则为：pg_ctl stop [-m SHUTDOWN-MODE]
postgresql@JiekeXu-> pg_ctl stopwaiting for server to shut down.... doneserver stopped
注意：-m 参数时指定数据库停止方式，有 smart、fast、immediate，一般这三个都会简写成“-ms”,”-mf”,”-mi”。smart 则是等所有的连接终止后，关闭数据库。如果数据库一直有连接则无法关闭。fast 则是快速关闭数据库，断开客户端连接，让已有的事务回滚，然后正常关闭数据库，这相当于 oracle数据库的 shutdown immediate模式。而 immediate 则是立即关闭数据库，相当于数据库进程立即停止，直接退出，下次启动数据库需要恢复，这则是相当于 Oracle 数据库关闭时的 shutdown abort 模式，慎用。
三、数据库简单操作示例
以上配置好数据库，可以进行简单的操作学习了，使用 psql 直接进入数据库，如同oracle 里的 sqlplus 命令。
首先第一个就是 help 命令，详细信息如下：
postgresql@JiekeXu-> psql --helppsql is the PostgreSQL interactiveterminal. Usage: psql [OPTION]... [DBNAME [USERNAME]] General options: -c,--command=COMMAND run only singlecommand (SQL or internal) and exit -d,--dbname=DBNAME database name toconnect to (default: "postgres") -f,--file=FILENAME execute commandsfrom file, then exit -l,--list list availabledatabases, then exit -v,--set=, --variable=NAME=VALUE set psql variable NAME to VALUE (e.g., -vON_ERROR_STOP=1) -V,--version output versioninformation, then exit -X,--no-psqlrc do not read startupfile (~/.psqlrc) -1("one"), --single-transaction execute as a singletransaction (if non-interactive) -?,--help[=options] show this help, thenexit --help=commands listbackslash commands, then exit --help=variables list specialvariables, then exit Input and output options: -a,--echo-all echo all input fromscript -b,--echo-errors echo failed commands -e,--echo-queries echo commands sentto server -E,--echo-hidden display queries thatinternal commands generate -L,--log-file=FILENAME send session log tofile -n,--no-readline disable enhancedcommand line editing (readline) -o,--output=FILENAME send query resultsto file (or |pipe) -q,--quiet run quietly (nomessages, only query output) -s,--single-step single-step mode(confirm each query) -S,--single-line single-line mode(end of line terminates SQL command) Output format options: -A,--no-align unaligned tableoutput mode -F,--field-separator=STRING field separator forunaligned output (default: "|") -H,--html HTML table outputmode -P,--pset=VAR[=ARG] set printing optionVAR to ARG (see \pset command) -R,--record-separator=STRING recordseparator for unaligned output (default: newline) -t,--tuples-only print rows only -T,--table-attr=TEXT set HTML table tagattributes (e.g., width, border) -x,--expanded turn on expandedtable output -z,--field-separator-zero set field separatorfor unaligned output to zero byte -0,--record-separator-zero set record separatorfor unaligned output to zero byte Connection options: -h,--host=HOSTNAME database server hostor socket directory (default: "/home/postgresql/pgdata") -p,--port=PORT database server port(default: "5432") -U,--username=USERNAME database user name(default: "postgres") -w,--no-password never prompt forpassword -W,--password force passwordprompt (should happen automatically) For more information, type "\?"(for internal commands) or "\help" (for SQLcommands) from within psql, or consult thepsql section in the PostgreSQLdocumentation. Report bugs to<pgsql-bugs@postgresql.org>.
根据以上信息，使用 psql 进入数据库，相当于 Oracle 数据库的 SQL plus 命令。这样也可以查看数据库的版本亦或者使用 pgsql –V 也可以查看数据库版本。
\help --帮助信息\l --列出当前系统所有数据库信息postgres-# \helpAvailable help: ABORT CREATE FOREIGN TABLE DROP SCHEMA ALTER AGGREGATE CREATE FUNCTION DROP SEQUENCE ALTER COLLATION CREATE GROUP DROP SERVER ALTER CONVERSION CREATE INDEX DROP STATISTICS ALTER DATABASE CREATE LANGUAGE DROP SUBSCRIPTION ALTER DEFAULT PRIVILEGES CREATE MATERIALIZED VIEW DROP TABLE ALTER DOMAIN CREATE OPERATOR DROP TABLESPACE ALTER EVENT TRIGGER CREATE OPERATOR CLASS DROP TEXT SEARCH CONFIGURATION ALTER EXTENSION CREATE OPERATOR FAMILY DROP TEXT SEARCH DICTIONARY ALTER FOREIGN DATA WRAPPER CREATE POLICY DROP TEXT SEARCH PARSER ALTER FOREIGN TABLE CREATE PUBLICATION DROP TEXT SEARCH TEMPLATE ALTER FUNCTION CREATE ROLE DROP TRANSFORM ALTER GROUP CREATE RULE DROP TRIGGER ALTERINDEX CREATESCHEMA DROP TYPE ALTER LANGUAGE CREATE SEQUENCE DROP USER ALTER LARGE OBJECT CREATE SERVER DROP USER MAPPING ALTER MATERIALIZED VIEW CREATE STATISTICS DROP VIEW ALTER OPERATOR CREATE SUBSCRIPTION END ALTER OPERATOR CLASS CREATE TABLE EXECUTE ALTER OPERATOR FAMILY CREATE TABLE AS EXPLAIN ALTER POLICY CREATE TABLESPACE FETCH ALTER PUBLICATION CREATE TEXT SEARCH CONFIGURATION GRANT ALTER ROLE CREATE TEXT SEARCH DICTIONARY IMPORT FOREIGN SCHEMA ALTER RULE CREATE TEXT SEARCH PARSER INSERT ALTER SCHEMA CREATE TEXT SEARCH TEMPLATE LISTEN ALTER SEQUENCE CREATE TRANSFORM LOAD ALTER SERVER CREATE TRIGGER LOCK ALTER STATISTICS CREATE TYPE MOVE ALTER SUBSCRIPTION CREATE USER NOTIFY ALTER SYSTEM CREATE USER MAPPING PREPARE ALTER TABLE CREATE VIEW PREPARE TRANSACTION ALTER TABLESPACE DEALLOCATE REASSIGN OWNED ALTER TEXT SEARCH CONFIGURATION DECLARE REFRESHMATERIALIZED VIEW ALTER TEXT SEARCH DICTIONARY DELETE REINDEX ALTER TEXT SEARCH PARSER DISCARD RELEASE SAVEPOINT ALTER TEXT SEARCH TEMPLATE DO RESET ALTER TRIGGER DROP ACCESS METHOD REVOKE ALTER TYPE DROP AGGREGATE ROLLBACK ALTER USER DROP CAST ROLLBACK PREPARED ALTER USER MAPPING DROP COLLATION ROLLBACK TO SAVEPOINT ALTER VIEW DROP CONVERSION SAVEPOINT ANALYZE DROP DATABASE SECURITY LABEL BEGIN DROP DOMAIN SELECT CHECKPOINT DROP EVENT TRIGGER SELECT INTO CLOSE DROP EXTENSION SET CLUSTER DROP FOREIGN DATA WRAPPER SET CONSTRAINTS COMMENT DROP FOREIGN TABLE SET ROLE COMMIT DROP FUNCTION SET SESSION AUTHORIZATION COMMIT PREPARED DROP GROUP SET TRANSACTION COPY DROP INDEX SHOW CREATE ACCESS METHOD DROP LANGUAGE START TRANSACTION CREATE AGGREGATE DROP MATERIALIZED VIEW TABLE CREATE CAST DROP OPERATOR TRUNCATE CREATE COLLATION DROP OPERATOR CLASS UNLISTEN CREATE CONVERSION DROP OPERATOR FAMILY UPDATE CREATE DATABASE DROP OWNED VACUUM CREATE DOMAIN DROP POLICY VALUES CREATE EVENT TRIGGER DROP PUBLICATION WITH CREATE EXTENSION DROP ROLE CREATE FOREIGN DATA WRAPPER DROP RULE
postgres-# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+---------+-------+----------------------- postgres | postgres | UTF8 | C | C | template0 | postgres | UTF8 | C | C | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | C | C | =c/postgres + | | | | | postgres=CTc/postgres(3 rows)创建数据库，查看数据库，删除数据库
postgres=# create database JiekeXu;CREATE DATABASEpostgres=# \l List ofdatabases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+---------+-------+----------------------- jiekexu | postgres | UTF8 | C | C | postgres | postgres | UTF8 | C | C | template0 | postgres | UTF8 | C | C | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | C | C | =c/postgres + | | | | | postgres=CTc/postgres testdb | postgres | UTF8 | C | C |(5 rows) postgres=# drop database TestDB;DROP DATABASEpostgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+---------+-------+----------------------- jiekexu | postgres | UTF8 | C | C | postgres | postgres | UTF8 | C | C | template0 | postgres | UTF8 | C | C | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | C | C | =c/postgres + | | | | | postgres=CTc/postgres(4 rows)
创建表，查看表，删除表
切换数据库
postgres=# \c jiekexuYou are now connected to database"jiekexu" as user "postgres".
创建表
jiekexu=# create table test(id int,namechar(20), age int);CREATE TABLEjiekexu=#jiekexu=# insert into test values(1,'JiekeXu',18);INSERT 0 1jiekexu=# insert into test values(2,'Adventure',25);INSERT 0 1jiekexu=#
查看表信息
jiekexu=# \d List of relations Schema | Name | Type | Owner --------+-------+-------+---------- public | test | table | postgres public | test1 | table | postgres public | test2 | table | postgres(3 rows) jiekexu=# \d test Table"public.test" Column | Type | Collation | Nullable |Default--------+---------------+-----------+----------+--------- id |integer | | | name |character(20) | | | age |integer
查看表内容
jiekexu=# select * from test; id| name | age----+----------------------+----- 1 |JiekeXu | 18 2 |Adventure | 25(2 rows)
修改表内容
jiekexu=# update test set age=26 wherename='JiekeXu';UPDATE 1jiekexu=# commit;WARNING: there is no transaction in progressCOMMITjiekexu=# select * from test; id| name | age----+----------------------+----- 2 |Adventure | 25 1 |JiekeXu | 26(2 rows)
删除表
drop table test2;delete from test;jiekexu=# select * from test; id| name | age----+----------------------+----- 2 |Adventure | 25 1 |JiekeXu | 26 2 |BreatHeat | 22(3 rows) jiekexu=# delete from test where age=22;DELETE 1jiekexu=# select * from test; id| name | age----+----------------------+----- 2 |Adventure | 25 1 |JiekeXu | 26(2 rows)jiekexu=# drop table test2;DROP TABLEjiekexu=#jiekexu=# \d List of relations Schema | Name | Type | Owner --------+-------+-------+---------- public | test | table | postgres public | test1 | table | postgres(2 rows)
最后，使用 \q 或者 Ctrl + d 退出命令行
jiekexu-# \qpostgresql@JiekeXu->断断续续、零零散散、慢慢悠悠的花了一周的时间， 写了这么一点儿东西，希望可以帮助更多的 PGer ，我们一同学习，共同进步，前进的路上不孤单，加油！一起努力向前！如有写的不当之处，还请批评指正，谢谢！最后，容我开一次赞赏，看着你的头像出现在下方，将是我很开心的一件事儿！
推荐阅读：
Linux Oracle 11.2.0.4 单节点数据库升级至最新补丁安装指北
万字详解Oracle架构、原理、进程，学会世间再无复杂架构
Oracle 11GR2 RAC 最新补丁 190416 安装指导
Oracle 11gR2 RAC 集群服务启动与关闭总结
Windows 环境下 Oracle11gR2 安装与卸载
史上最全的 OGG 基础知识整理
资源分享：
5T 技术资源大放送！包括但不限于：Linux，Python，Oracle，MySQL，Java，前端，大数据，具体获取方式可关注本公众号或者添加我微信获取~~~
（长按添加微信公众号，更多精彩内容不错过！）
码字不易，点赞、转发是一种对作者的鼓励！