PostgreSQL在Linux上的RPM和源码安装

第一章 引言

此文档主要描述Postgre数据库，基于Red Hat Enterprise Linux Server release 6.5 的操作系统上安装Postgre数据库的文档衍生而来。此文档包括Postgre数据库的多种安装方式。

1.1 背景

本文档介绍Postgre 数据库基于linux 6.5平台的三种安装方式。
 
第二章 部署前规划

在部署系统之前，需要对安装存储位置这两方面进行规划。下面分别描述了存储进行规划时，需要注意的地方。

2.1 环境规划

本次环境规划包括postgres用户的家目录，软件安装目录，及数据库目录三大部分。

2.2 软件功能

PostgreSQL is an object-relational database management system (ORDBMS) based on POSTGRES.

PostgreSQL is an open-source descendant of this original Berkeley code. It supports a large part of the SQL standard and offers many modern features:
complex queries
foreign keys
triggers
updatable views
transactional integrity
multiversion concurrency control    

Also, PostgreSQL can be extended by the user in many ways, for example by adding new data types
functions
operators
aggregate functions
index methods
procedural languages

2.3 文档及下载介绍

下面列出了本次实施Postgre可供参考的文档，及软件包的下载路径。

第三章 安装流程

3.1 解压安装

在操作系统安装完成后，上传安转包后按照目录规划安装postgre数据库。

1）安装部署参考文档

下载页：https://www.enterprisedb.com/download-postgresql-binaries
软件包：postgresql-10.7-2-linux-x64-binaries.tar.gz

2）创建postgre安装用户

useradd postgres	
passwd postgres

3）创建postgre安装目录及数据目录

mkdir -p /monchickey/pgsql_data	
chown -R postgres:postgres /monchickey/pgsql_data	
Chmod -R 775 /monchickey/pgsql_data

4）解压安装

tar -zxvf postgresql-10.7-2-linux-x64-binaries.tar.gz -C /monchickey	
 [root@rhel6 monchickey]# ll	
total 12	
drwxrwxrwx  9 root     root     4096 Apr 10 03:24 pgsql	
drwx------ 19 postgres postgres 4096 Apr 15 11:33 pgsql_data

5）初始化数据库

cd /monchickey/pgsql/bin	
/monchickey/pgsql/bin/initdb -D /monchickey/pgsql_data/	
eg:	
[postgres@rhel6 bin]$ ./initdb -D /monchickey/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 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 /monchickey/pgsql_data ... ok	
creating subdirectories ... ok	
selecting default max_connections ... 100	
selecting default shared_buffers ... 128MB	
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 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 /monchickey/pgsql_data/ -l logfile start

6）启动数据库

./pg_ctl -D /monchickey/pgsql_data/ -l logfile start	
eg:	
[postgres@rhel6 bin]$ ./pg_ctl -D /monchickey/pgsql_data/ -l logfile start	
waiting for server to start..../bin/sh: logfile: Permission denied	
 stopped waiting	
pg_ctl: could not start server	
Examine the log output.	
	
解决方法：	
[root@rhel6 monchickey]# chmod -R 777 pgsql	
[root@rhel6 monchickey]# ll	
total 8	
drwxrwxrwx  9 root     root     4096 Apr 10 03:24 pgsql	
	
[postgres@rhel6 bin]$ ./pg_ctl -D /monchickey/pgsql_data/ -l logfile start	
waiting for server to start.... done	
server started

7）停止数据库

./pg_ctl -D /monchickey/pgsql_data/ stop

3.2 使用rpm包安装

使用rpm包安装postgre数据库。需要使用4个rpm包。如果认为系统自带的postgre数据库安装包版本过低，从https://yum.postgresql.org网站上下载。本次安装使用rhel 6.5自带的安装包。

1）安装包信息及顺序

rpm -ivh postgresql92-libs-9.2.4-1PGDG.rhel6.i686.rpm 	
rpm -ivh postgresql92-9.2.4-1PGDG.rhel6.i686.rpm	
rpm -ivh postgresql92-server-9.2.4-1PGDG.rhel6.i686.rpm 	
rpm -ivh postgresql92-contrib-9.2.4-1PGDG.rhel6.i686.rpm

2）或者使用yum源进行安装

yum install -y postgresql-libs	
yum install -y postgresql	
Yum install -y postgresql-server	
Yum install -y postgresql-contrib

3）初始化数据库

[root@rhel6 Packages]# service postgresql initdb	
Initializing database:                                     [  OK  ]

4）启动数据库服务

[root@rhel6 Packages]# service postgresql start	
Starting postgresql service:                               [  OK  ]

5）查看默认的psql

[postgres@rhel6 ~]$ psql	
psql (8.4.18)	
Type "help" for help.

6）停止数据库服务

[postgres@rhel6 ~]$ exit	
logout	
[root@rhel6 Packages]# service postgresql stop	
Stopping postgresql service:                               [  OK  ]

7）卸载数据库

rpm -e postgresql-server	
rpm -e postgresql-contrib	
rpm -e postgresql	
rpm -e postgresql-libs 

3.3 编译安装

使用二进制文件进行编译方法，安装postgre数据库。

1）安装包信息及下载地址

Pg软件下载地址：http://ftp.postgresql.org/pub/source
Pg安装包名称  ：postgresql-11.0.tar.bz2
参考文档地址   ：https://www.postgresql.org/docs/10/installation.html

2）安装前系统检查,参照官方文档的要求，安装软件包

必须的安装包检查：
1：make --version检查
Eg:
[root@rhel6 ~]# make --version
GNU Make 3.81
Copyright (C) 2006  Free Software Foundation, Inc.
This is free software; see the source for copying conditions.
There is NO warranty; not even for MERCHANTABILITY or FITNESS FOR A
PARTICULAR PURPOSE.

2：gcc检查
[root@rhel6 ~]# rpm -aq gcc
gcc-4.4.7-4.el6.x86_64

可选软件包安装
1：Perl
2：Python
3：OpenSSL
......

3）解压安装包

tar -xjvf postgresql-11.0.tar.bz2 -C /usr/local	
Eg：	
[root@rhel6 local]# cd postgresql-11.0/	
[root@rhel6 postgresql-11.0]# ll	
total 744	
-rw-r--r--  1 1107 1107    486 Oct 16 05:12 aclocal.m4	
drwxrwxrwx  2 1107 1107   4096 Oct 16 05:14 config	
-rwxr-xr-x  1 1107 1107 558874 Oct 16 05:12 configure	
-rw-r--r--  1 1107 1107  83596 Oct 16 05:12 configure.in	
drwxrwxrwx 56 1107 1107   4096 Oct 16 05:14 contrib	
-rw-r--r--  1 1107 1107   1192 Oct 16 05:12 COPYRIGHT	
drwxrwxrwx  3 1107 1107   4096 Oct 16 05:14 doc	
-rw-r--r--  1 1107 1107   3664 Oct 16 05:12 GNUmakefile.in	
-rw-r--r--  1 1107 1107    284 Oct 16 05:12 HISTORY	
-rw-r--r--  1 1107 1107  72717 Oct 16 05:15 INSTALL	
-rw-r--r--  1 1107 1107   1682 Oct 16 05:12 Makefile	
-rw-r--r--  1 1107 1107   1212 Oct 16 05:12 README	
drwxrwxrwx 16 1107 1107   4096 Oct 16 05:15 src

4）编译

./configure

问题：
configure: error: readline library not found
If you have readline already installed, see config.log for details on the
failure.  It is possible the compiler isn't looking in the proper directory.
Use --without-readline to disable readline support.
原因：
The GNU Readline library is used by default. It allows psql (the PostgreSQL command line SQL interpreter) to remember each command you type, and allows you to use arrow keys to recall and edit previous commands. This is very helpful and is strongly recommended. If you don't want to use it then you must specify the --without-readline option to configure. As an alternative, you can often use the BSD-licensed libedit library, originally developed on NetBSD. The libedit library is GNU Readline-compatible and is used if libreadline is not found, or if --with-libedit-preferred is used as an option to configure. If you are using a package-based Linux distribution, be aware that you need both the readline and readline-devel packages, if those are separate in your distribution.
解决方法：
1：./configure --without-readline
2：安装readline包
rpm -ivh redline
rpm -ivh redline-devel

5）编译并安装

make && make install

6）创建postgre用户及相应数据目录

Useradd postgres	
Su - postgres	
mkdir -P /monkeys/pgsql11_data

7）数据库初始化：

/usr/local/pgsql/bin/initdb -D /monchickey/pgsql11_data	
Eg:	
[postgres@rhel6 bin]$ ./initdb -D /monchickey/pgsql11_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 /monchickey/pgsql11_data ... ok	
creating subdirectories ... ok	
selecting default max_connections ... 100	
selecting default shared_buffers ... 128MB	
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 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 /monchickey/pgsql11_data -l logfile start

8）启动postgre数据库

./pg_ctl -D /monchickey/pgsql11_data -l logfile start	
Eg：	
[postgres@rhel6 bin]$ ./pg_ctl -D /monchickey/pgsql11_data -l logfile start	
waiting for server to start.... done	
server started	
	

9）创建数据库

[postgres@rhel6 bin]$ ./createdb test	
[postgres@rhel6 bin]$ ./psql	
psql (11.0)	
Type "help" for help.	
	
postgres=# 		
Tuples only is on.	
postgres=# l	
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 	
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +	
           |          |          |             |             | postgres=CTc/postgres	
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +	
           |          |          |             |             | postgres=CTc/postgres	
 test      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

第四章 Postgre数据库简单实用操作

在postgre数据库安装完成后，查看postgre数据库的后台进程，及端口信息来判断postgrep数据库的运行情况。进行简单操作，来尝试连接并使用数据库。

4.1 检查postgre数据库的进程及端口信息

1）查看postgre数据库进程

[postgres@rhel6 bin]$ ps -ef | grep post	
root       1946      1  0 08:15 ?        00:00:00 /usr/libexec/postfix/master	
postfix    1954   1946  0 08:15 ?        00:00:00 pickup -l -t fifo -u	
postfix    1955   1946  0 08:15 ?        00:00:00 qmgr -l -t fifo -u	
postgres   3914      1  0 09:39 pts/0    00:00:00 /monchickey/pgsql/bin/postgres -D /monchickey/pgsql_data	
postgres   3916   3914  0 09:39 ?        00:00:00 postgres: checkpointer process                          	
postgres   3917   3914  0 09:39 ?        00:00:00 postgres: writer process                                	
postgres   3918   3914  0 09:39 ?        00:00:00 postgres: wal writer process                            	
postgres   3919   3914  0 09:39 ?        00:00:00 postgres: autovacuum launcher process                   	
postgres   3920   3914  0 09:39 ?        00:00:00 postgres: stats collector process                       	
postgres   3921   3914  0 09:39 ?        00:00:00 postgres: bgworker: logical replication launcher    

2）查看postgre端口，默认端口号是5432

[postgres@rhel6 ~]$ lsof -i:5432	
COMMAND   PID     USER   FD   TYPE DEVICE SIZE/OFF NODE NAME	
postgres 3914 postgres    3u  IPv6  74137      0t0  TCP localhost:postgres (LISTEN)	
postgres 3914 postgres    4u  IPv4  74138      0t0  TCP localhost:postgres (LISTEN)

3）查看psql的帮助信息

[postgres@rhel6 ~]$ psql --help	
psql is the PostgreSQL interactive terminal.	
	
Usage:	
  psql [OPTION]... [DBNAME [USERNAME]]	
	
General options:	
  -c, --command=COMMAND    run only single command (SQL or internal) and exit	
  -d, --dbname=DBNAME      database name to connect to (default: "postgres")	
  -f, --file=FILENAME      execute commands from file, then exit	
  -l, --list               list available databases, then exit	
  -v, --set=, --variable=NAME=VALUE	
                           set psql variable NAME to VALUE	
                           (e.g., -v ON_ERROR_STOP=1)	
  -V, --version            output version information, then exit	
  -X, --no-psqlrc          do not read startup file (~/.psqlrc)	
  -1 ("one"), --single-transaction	
                           execute as a single transaction (if non-interactive)	
  -?, --help[=options]     show this help, then exit	
      --help=commands      list backslash commands, then exit	
      --help=variables     list special variables, then exit	
	
Input and output options:	
  -a, --echo-all           echo all input from script	
  -b, --echo-errors        echo failed commands	
  -e, --echo-queries       echo commands sent to server	
  -E, --echo-hidden        display queries that internal commands generate	
  -L, --log-file=FILENAME  send session log to file	
  -n, --no-readline        disable enhanced command line editing (readline)	
  -o, --output=FILENAME    send query results to file (or |pipe)	
  -q, --quiet              run quietly (no messages, 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 table output mode	
  -F, --field-separator=STRING	
                           field separator for unaligned output (default: "|")	
  -H, --html               HTML table output mode	
  -P, --pset=VAR[=ARG]     set printing option VAR to ARG (see pset command)	
  -R, --record-separator=STRING	
                           record separator for unaligned output (default: newline)	
  -t, --tuples-only        print rows only	
  -T, --table-attr=TEXT    set HTML table tag attributes (e.g., width, border)	
  -x, --expanded           turn on expanded table output	
  -z, --field-separator-zero	
                           set field separator for unaligned output to zero byte	
  -0, --record-separator-zero	
                           set record separator for unaligned output to zero byte	
	
Connection options:	
  -h, --host=HOSTNAME      database server host or socket directory (default: "local socket")	
  -p, --port=PORT          database server port (default: "5432")	
  -U, --username=USERNAME  database user name (default: "postgres")	
  -w, --no-password        never prompt for password	
  -W, --password           force password prompt (should happen automatically)	
	
For more information, type "?" (for internal commands) or "help" (for SQL	
commands) from within psql, or consult the psql section in the PostgreSQL	
documentation.	
	
Report bugs to <pgsql-bugs@postgresql.org>.

5）psql中sql语法的帮助信息

[postgres@rhel6 bin]$ ./psql 	
psql (11.0)	
Type "help" for help.	
	
postgres=# help	
Available help:	
  ABORT                            ALTER TEXT SEARCH TEMPLATE       CREATE PUBLICATION               DROP FUNCTION                    IMPORT FOREIGN 	
......

6）查看当前数据库列表：

postgres-# l	
                                  List of databases	
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   	
-----------+----------+----------+-------------+-------------+-----------------------	
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 	
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +	
           |          |          |             |             | postgres=CTc/postgres	
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +	
           |          |          |             |             | postgres=CTc/postgres	
(3 rows)

7）创建数据库

postgres=# CREATE DATABASE dbname WITH OWNER=postgres ENCODING='UTF-8';	
CREATE DATABASE

8）切换数据库

postgres=# c dbname	
You are now connected to database "dbname" as user "postgres".

9）查看数据库下所有表

dbname=# create table tab1 (id int,name char(20));	
CREATE TABLE	
dbname=# d	
        List of relations	
 Schema | Name | Type  |  Owner   	
--------+------+-------+----------	
 public | tab1 | table | postgres

10）查看表信息

dbname-# d student	
                 Table "public.student"	
 Column |     Type      | Collation | Nullable | Default 	
--------+---------------+-----------+----------+---------	
 id     | integer       |           | not null | 	
 name   | character(32) |           |          | 	
 number | character(5)  |           |          | 	
Indexes:	
    "student_pkey" PRIMARY KEY, btree (id)

原创：张红妮，云和恩墨服务部技术顾问；Oracle 运维DBA，对开源数据库也很有研究。

编辑：尹文敏

公司简介  | 招聘 | DTCC | 数据技术嘉年华 | 免费课程 | 入驻华为严选商城

zCloud | SQM | Bethune Pro2 | zData一体机 | MyData一体机 | ZDBM 备份一体机

Oracle技术架构 | 免费课程 | 数据库排行榜 | DBASK问题集萃 | 技术通讯 

升级迁移 | 性能优化 | 智能整合 | 安全保障 |  架构设计 | SQL审核 | 分布式架构 | 高可用容灾 | 运维代维

云和恩墨大讲堂 | 一个分享交流的地方

长按，识别二维码，加入万人交流社群

请备注：云和恩墨大讲堂