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 加密值如下：
代码语言：javascript复制62f755219b9b05c25f24737405a5aae1 postgresql-10.9.tar.bz2
下载完之后，由于服务器一般都属于内网环境，无法上网，则通过 SFTP 上传至服务器 /home/postgresql 目录，然后检验 MD5 值，保证软件下载过程中没有损坏，可正常使用，下面是检验出来的 MD5 值与上图下载中的一致，说明软件可正常使用。
代码语言：javascript复制[root@JiekeXupostgresql]# md5sum postgresql-10.9.tar.bz2
62f755219b9b05c25f24737405a5aae1 postgresql-10.9.tar.bz2
[root@JiekeXupostgresql]# ll -lh
total 19M
-rw-r--r-- 1 rootroot 19M Jul 7 23:29postgresql-10.9.tar.bz2
二、 操作系统相关配置
1、内核参数设置
代码语言：javascript复制vi /etc/sysctl.conf############################forpostgresql###########
kernel.shmall =4294967296
kernel.shmmax=135497418752
kernel.shmmni =4096
kernel.sem = 5010064128000 50100 1280
fs.file-max =7672460
fs.aio-max-nr =1048576
net.ipv4.ip_local_port_range= 9000 65000
net.core.rmem_default= 262144
net.core.rmem_max= 4194304
net.core.wmem_default= 262144
net.core.wmem_max= 4194304
net.ipv4.tcp_max_syn_backlog= 4096
net.core.netdev_max_backlog= 10000
net.ipv4.netfilter.ip_conntrack_max= 655360
net.ipv4.tcp_timestamps= 0
net.ipv4.tcp_tw_recycle=1
net.ipv4.tcp_timestamps=1
net.ipv4.tcp_keepalive_time= 72
net.ipv4.tcp_keepalive_probes= 9
net.ipv4.tcp_keepalive_intvl= 7
vm.zone_reclaim_mode=0
vm.dirty_background_bytes= 40960000
vm.dirty_ratio =80
vm.dirty_expire_centisecs= 6000
vm.dirty_writeback_centisecs= 50
vm.swappiness=0
vm.overcommit_memory= 0
vm.overcommit_ratio= 90使用命令 sysctl -p 使其生效
2、操作系统的限制
这些在安装 Oracle 和 MySQL 已经说过，这里就不再说明了。
代码语言：javascript复制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 5000000003、limits.d 目录
此目录下的文件也是文件限制，上文已经设置了，这里如果还存在的话，则以此文件限制为准，故删除。
代码语言：javascript复制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 1024
root soft nproc unlimited系统其它普通用户最多进程默认是 1024 个，而 root 用户是 unlimited (不受限制）
代码语言：javascript复制ls /etc/security/limits.d/*
rm -f /etc/security/limits.d/*
4、关闭selinux 和 防火墙
代码语言：javascript复制vi /etc/sysconfig/selinux SELINUX=disabled
SELINUXTYPE=targeted需要关闭 SELINUX 和 操作系统防火墙 iptables
设置 selinux 为 disable 并重启操作系统；用 chkconfig iptables off 命令关闭防火墙。
5、配置yum 本地源，安装以下依赖包
代码语言：javascript复制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 bzip2
6、创建普通用户
代码语言：javascript复制useradd postgresql
passwd postgresql7、建立数据目录并上传、解压安装包
代码语言：javascript复制su – postgresql
mkdir pgdata
tar -xvf postgresql-10.9.tar.bz2做一个软链接方便以后管理与升级
代码语言：javascript复制ln -s postgresql-10.9 postgresql
8、编译安装
使用如下命令进行编译安装
代码语言：javascript复制cd /home/postgresql/postgresql
./configure --prefix=/home/postgresql/postgresql--with-perl --with-python
make world -j 4
make install-world编译过程中的部分日志
代码语言：javascript复制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 -lz
make[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 文件。
代码语言：javascript复制[postgresql@JiekeXu ~]$ vi .bashrc
export PS1="$USER@`/bin/hostname -s`->"
export PGPORT=5432
export PGDATA=/home/postgresql/pgdata
export.utf8
export PGHOME=/home/postgresql/postgresql
exportLD_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、创建数据库簇（即初始化数据库）
代码语言：javascript复制initdb -D $PGDATA -E UTF8 --locale=C -U postgrespostgresql@JiekeXu-> initdb -D $PGDATA -E UTF8 --locale=C -U postgres
The 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... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default timezone ... PRC
selecting dynamic shared memory implementation ...posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
WARNING: enabling "trust" authentication forlocal connections
You 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 下面有一些比较使用的工具，我们也可以选择安装一下，基本上直接编译、安装即可。
代码语言：javascript复制cd /home/postgresql/postgresql/contrib
make
make install12、数据库配置文件相关
在数据目录下编辑两个配置文件 postgresql.conf和pg_hba.conf
（1）配置文件
代码语言：javascript复制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 数据库.
代码语言：javascript复制host all all 0.0.0.0/0 md5（3）数据库启动并查看相关状态、进程
代码语言：javascript复制postgresql@JiekeXu-> pg_ctl start
postgresql@JiekeXu-> pg_ctl status
pg_ctl: no server running
那么，相应的停止命令则为：pg_ctl stop [-m SHUTDOWN-MODE]
代码语言：javascript复制postgresql@JiekeXu-> pg_ctl stop
waiting for server to shut down.... done
server stopped注意：-m 参数时指定数据库停止方式，有 smart、fast、immediate，一般这三个都会简写成“-ms”,”-mf”,”-mi”。smart 则是等所有的连接终止后，关闭数据库。如果数据库一直有连接则无法关闭。fast 则是快速关闭数据库，断开客户端连接，让已有的事务回滚，然后正常关闭数据库，这相当于 oracle数据库的 shutdown immediate模式。而 immediate 则是立即关闭数据库，相当于数据库进程立即停止，直接退出，下次启动数据库需要恢复，这则是相当于 Oracle 数据库关闭时的 shutdown abort 模式，慎用。
三、数据库简单操作示例
以上配置好数据库，可以进行简单的操作学习了，使用 psql 直接进入数据库，如同oracle 里的 sqlplus 命令。
首先第一个就是 help 命令，详细信息如下：
代码语言：javascript复制postgresql@JiekeXu-> psql --help
psql 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 SQL
commands) from within psql, or consult thepsql section in the PostgreSQL
documentation.
Report bugs to<pgsql-bugs@postgresql.org>.根据以上信息，使用 psql 进入数据库，相当于 Oracle 数据库的 SQL plus 命令。这样也可以查看数据库的版本亦或者使用 pgsql –V 也可以查看数据库版本。
help --帮助信息
l --列出当前系统所有数据库信息
postgres-# help
Available 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)创建数据库，查看数据库，删除数据库
代码语言：javascript复制postgres=# create database JiekeXu;
CREATE DATABASE
postgres=# 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 DATABASE
postgres=# 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)创建表，查看表，删除表
切换数据库
代码语言：javascript复制postgres=# c jiekexu
You are now connected to database"jiekexu" as user "postgres".创建表
代码语言：javascript复制jiekexu=# create table test(id int,namechar(20), age int);
CREATE TABLE
jiekexu=#
jiekexu=# insert into test values(1,'JiekeXu',18);
INSERT 0 1
jiekexu=# insert into test values(2,'Adventure',25);
INSERT 0 1
jiekexu=#查看表信息
代码语言：javascript复制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查看表内容
代码语言：javascript复制jiekexu=# select * from test;
id| name | age
---- ---------------------- -----
1 |JiekeXu | 18
2 |Adventure | 25
(2 rows)修改表内容
代码语言：javascript复制jiekexu=# update test set age=26 wherename='JiekeXu';
UPDATE 1
jiekexu=# commit;
WARNING: there is no transaction in progress
COMMIT
jiekexu=# select * from test;
id| name | age
---- ---------------------- -----
2 |Adventure | 25
1 |JiekeXu | 26
(2 rows)删除表
代码语言：javascript复制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 1
jiekexu=# select * from test;
id| name | age
---- ---------------------- -----
2 |Adventure | 25
1 |JiekeXu | 26
(2 rows)
jiekexu=# drop table test2;
DROP TABLE
jiekexu=#
jiekexu=# d
List of relations
Schema | Name | Type | Owner
-------- ------- ------- ----------
public | test | table | postgres
public | test1 | table | postgres
(2 rows)最后，使用 q 或者 Ctrl d 退出命令行
代码语言：javascript复制jiekexu-# q
postgresql@JiekeXu->断断续续、零零散散、慢慢悠悠的花了一周的时间， 写了这么一点儿东西，希望可以帮助更多的 PGer ，我们一同学习，共同进步，前进的路上不孤单，加油！一起努力向前！如有写的不当之处，还请批评指正，谢谢！最后，容我开一次赞赏，看着你的头像出现在下方，将是我很开心的一件事儿！
