Linux系统 源码安装部署postgres-14.0
环境：RedHat7.5 源码包： postgres-14.0.tar.gz
源码包链接：https://www.postgresql.org/ftp/source/
大家可以根据需要自行下载需要的版本，依赖包的安装在部署过程中根据提示安装即可，现在我们开始部署。
1.安装部署
上传源码包至服务器，并进行解压：tar -zxvf postgresql-9.5.4.tar.gz
[root@gpdb238 wangping]# cd postgres-14.0/
[root@gpdb238 postgres-14.0]# ll
总用量 756
-rw-r--r--. 1 root root 490 3月 7 18:20 aclocal.m4
drwxr-xr-x. 2 root root 4096 3月 7 18:20 config
-rw-r--r--. 1 root root 580807 3月 7 18:20 configure
-rw-r--r--. 1 root root 83288 3月 7 18:20 configure.ac
drwxr-xr-x. 58 root root 4096 3月 7 18:20 contrib
-rw-r--r--. 1 root root 1192 3月 7 18:20 COPYRIGHT
drwxr-xr-x. 3 root root 87 3月 7 18:20 doc
-rw-r--r--. 1 root root 4259 3月 7 18:20 GNUmakefile.in
-rw-r--r--. 1 root root 277 3月 7 18:20 HISTORY
-rw-r--r--. 1 root root 63953 3月 7 18:20 INSTALL
-rw-r--r--. 1 root root 1665 3月 7 18:20 Makefile
-rw-r--r--. 1 root root 1213 3月 7 18:20 README
drwxr-xr-x. 16 root root 4096 3月 7 18:20 src
- 编译安装：
[root@gpdb238 postgres-14.0]# ./configure --prefix=/opt/psql \
--with-perl --with-tcl --with-python --with-openssl --with-pam \
--without-ldap --with-libxml --with-libxslt \
--with-wal-blocksize=16 --with-blocksize=16 --enable-dtrace \
--enable-debug --enable-thread-safety
若出现如下错误：说明缺少依赖包
checking build system type… x86_64-pc-linux-gnu
checking host system type… x86_64-pc-linux-gnu
checking which template to use… linux
checking whether NLS is wanted… no
checking for default port number… 5432
checking for dtrace… no
configure: error: dtrace not found
3.安装依赖包：
[root@gpdb238 postgres-14.0]# yum search dtrace
已加载插件：fastestmirror, product-id, search-disabled-repos
Determining fastest mirrors
centos7-base | 3.6 kB 00:00:00
centos7-centosplus | 2.9 kB 00:00:00
centos7-extras | 2.9 kB 00:00:00
centos7-updates | 2.9 kB 00:00:00
docker-ce-stable | 3.5 kB 00:00:00
(1/7): centos7-base/group_gz | 153 kB 00:00:00
(2/7): docker-ce-stable/updateinfo | 55 B 00:00:00
(3/7): centos7-extras/primary_db | 246 kB 00:00:00
(4/7): docker-ce-stable/primary_db | 71 kB 00:00:00
(5/7): centos7-base/primary_db | 6.1 MB 00:00:01
(6/7): centos7-centosplus/primary_db | 6.2 MB 00:00:01
(7/7): centos7-updates/primary_db | 14 MB 00:00:02
================================================= 匹配：dtrace ==================================================
systemtap-sdt-devel.i686 : Static probe support tools
systemtap-sdt-devel.x86_64 : Static probe support tools
根据自身系统安装相应的版本，本人是x86_64版本
在此过程中还会遇到其他错误(缺少依赖包)，大家可以参考以下链接以解决问题： http://blog.csdn.net/luojinbai/article/details/44217551
configure: creating ./config.status
config.status: creating GNUmakefile
config.status: creating src/Makefile.global
config.status: creating src/include/pg_config.h
config.status: creating src/include/pg_config_ext.h
config.status: creating src/interfaces/ecpg/include/ecpg_config.h
config.status: linking src/backend/port/tas/dummy.s to src/backend/port/tas.s
config.status: linking src/backend/port/posix_sema.c to src/backend/port/pg_sema.c
config.status: linking src/backend/port/sysv_shmem.c to src/backend/port/pg_shmem.c
config.status: linking src/include/port/linux.h to src/include/pg_config_os.h
config.status: linking src/makefiles/Makefile.linux to src/Makefile.port
[root@gpdb238 postgres-14.0]#
在此运行 configure命令可以看到如上效果，即表明安装成功。
由于后续想调试跟踪源代码，所以接下来需要修改 postgres-14.0/src/Makefile.global 文件(删除 -O2)：
CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels \
-Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g
上述步骤完成好之后，执行 make && make insatall 指令即可安装成功。切换至/opt/psql目录下即可看到安装成功的文件
[root@gpdb238 postgres-14.0]# cd /opt
[root@gpdb238 opt]# ll
总用量 0
drwxr-xr-x. 6 root root 56 3月 9 12:52 psql
[root@gpdb238 opt]# cd psql
[root@gpdb238 psql]# ll
总用量 12
drwxr-xr-x. 2 root root 4096 3月 9 12:52 bin
drwxr-xr-x. 4 root root 4096 3月 9 12:52 include
drwxr-xr-x. 4 root root 4096 3月 9 12:52 lib
drwxr-xr-x. 3 root root 24 3月 9 12:52 share
[root@gpdb238 psql]# pwd
/opt/psql
其中bin为可执行命令文件，如createdb、pg_ctl、pg_basebackup、vacuumdb等；
include为头文件，如 libpq-fe.h、pgtypes.h、sqlca.h等
lib为库文件，如libpgport.a、libpq.a等
share为相关资源文件,如pg_hba.conf.sample、postgresql.conf.sample、sql_features.txt等
和大多数软件一样，postgres不允许以root用户运行，因此我们创建一个用户：
[root@gpdb238 postgresql]# useradd postgres
[root@gpdb238 postgresql]# passwd postgres
更改用户 postgres 的密码 。
新的 密码：
重新输入新的 密码：
passwd：所有的身份验证令牌已经成功更新。
进入postgres用户，设置环境变量
[root@gpdb238 postgresql]# su - postgres
[postgres@gpdb238 ~]$ vim .bash_profile
export PGHOME=/opt/psql
export PGDATA=/home/postgres/data
export PATH=$PATH:$HOME/bin:$PGHOME/bin
[postgres@gpdb238 ~]$ source .bash_profile
最后进行集群初始化 initdb
[postgres@gpdb238 ~]$ initdb
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 "zh_CN.UTF-8".
The default database encoding has accordingly been set to "UTF8".
initdb: could not find suitable text search configuration for locale "zh_CN.UTF-8"
The default text search configuration will be set to "simple".
Data page checksums are disabled.
creating directory /home/postgres/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 /home/postgres/data -l logfile start
启动数据库：
pg_ctl start
查看系统守护进程和辅助进程：
ps -ef|grep postgres
postgres 158173 1 0 14:16 ? 00:00:00 /opt/psql/bin/postgres
postgres 158175 158173 0 14:16 ? 00:00:00 postgres: checkpointer
postgres 158176 158173 0 14:16 ? 00:00:00 postgres: background writer
postgres 158177 158173 0 14:16 ? 00:00:00 postgres: walwriter
postgres 158178 158173 0 14:16 ? 00:00:00 postgres: autovacuum launcher
postgres 158179 158173 0 14:16 ? 00:00:00 postgres: stats collector
postgres 158180 158173 0 14:16 ? 00:00:00 postgres: logical replication launcher
postgres 161676 95458 0 14:16 pts/0 00:00:00 grep --color=auto postgres
通过psql工具进入数据库：
[postgres@gpdb238 data]$ psql -p 5544
psql (14.0)
Type "help" for help.
postgres=#
postgreSQL数据库默认端口为5432，所以在该端口未被占用的情况下，可以直接使用
psql 进入数据库，作者由于之前安装部署过Greenplum数据库，其5432端口已被占用，所以在 postgresql.conf配置文件中将 port修改成5544。
2 简单实践
2.1 创建数据库
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
template0 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
简单介绍一下，postgres默认的database超级用户为postgres，默认的数据库名为postgres，其中template0和postgres数据库是从template1为模板复制得到。
建一个名为testdb数据库：
postgres=# create database testdb;
CREATE DATABASE
建立一张名为test表,插入1000条数据，并从中选出以 id 从大到小排序的前10项信息
postgres=# create table test(id int primary key, name text);
CREATE TABLE
postgres=# insert into test select generate_series (1,1000),'Seredipity_Shy';
INSERT 0 1000
postgres=# select * from test order by id desc limit 10;
id | name
------+----------------
1000 | Seredipity_Shy
999 | Seredipity_Shy
998 | Seredipity_Shy
997 | Seredipity_Shy
996 | Seredipity_Shy
995 | Seredipity_Shy
994 | Seredipity_Shy
993 | Seredipity_Shy
992 | Seredipity_Shy
991 | Seredipity_Shy
(10 rows)
向上述test表格添加索引
postgres=# create index test_index on test(id);
CREATE INDEX
postgres=# \d+ test
Table "public.test"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
id | integer | | not null | | plain | | |
name | text | | | | extended | | |
Indexes:
"test_pkey" PRIMARY KEY, btree (id)
"test_index" btree (id)
Access method: heap
通过 \d+ test 命令可以查看表test的一些信息：
test为常见的堆表，在字段 id上建立的是btree索引
向上述test表格添加新字段属性birthday,类型为date
postgres=# alter table test add column birthday date;
ALTER TABLE
postgres=# update test set birthday = '2020-03-09';
UPDATE 1000
postgres=# select * from test order by id desc limit 10;
id | name | birthday
------+----------------+------------
1000 | Seredipity_Shy | 2020-03-09
999 | Seredipity_Shy | 2020-03-09
998 | Seredipity_Shy | 2020-03-09
997 | Seredipity_Shy | 2020-03-09
996 | Seredipity_Shy | 2020-03-09
995 | Seredipity_Shy | 2020-03-09
994 | Seredipity_Shy | 2020-03-09
993 | Seredipity_Shy | 2020-03-09
992 | Seredipity_Shy | 2020-03-09
991 | Seredipity_Shy | 2020-03-09
(10 rows)
从test中删除 id = 999的记录
postgres=# delete from test where id = 999;
DELETE 1
postgres=# select * from test order by id desc limit 10;
id | name | birthday
------+----------------+------------
1000 | Seredipity_Shy | 2020-03-09
998 | Seredipity_Shy | 2020-03-09
997 | Seredipity_Shy | 2020-03-09
996 | Seredipity_Shy | 2020-03-09
995 | Seredipity_Shy | 2020-03-09
994 | Seredipity_Shy | 2020-03-09
993 | Seredipity_Shy | 2020-03-09
992 | Seredipity_Shy | 2020-03-09
991 | Seredipity_Shy | 2020-03-09
990 | Seredipity_Shy | 2020-03-09
(10 rows)
查看test表格大小（112 KB）
postgres=# select pg_size_pretty(pg_relation_size('test'));
pg_size_pretty
----------------
112 kB
(1 row)
查看test表对象的 oid (16396)，里面存放的就是上面插入的数据
postgres=# select oid from pg_class where relname = 'test';
oid
-------
16396
查看该表文件所在路径：
postgres=# select pg_relation_filepath('test');
pg_relation_filepath
----------------------
base/13835/16396
(1 row)
这里有一个疑惑，13835 代表什么呢？这个对象下包含 test表，因此猜测其代表一个数据库的oid。我们通过SQL语句查找一些即可
postgres=# select datname from pg_database where oid = 13835;
datname
----------
postgres
(1 row)
看到这里证明了我们的猜想，这到底什么意思呢？
解释： 说明test表位于base目录下数据库名为postgres 中。
使用 truncate命令可以快速清空 test,通过查看表大小进一步证明表数据清除成功。
postgres=# truncate test;
TRUNCATE TABLE
postgres=# select * from test;
id | name | birthday
----+------+----------
(0 rows)
postgres=# select pg_size_pretty(pg_relation_size('test'));
pg_size_pretty
----------------
0 bytes
(1 row)
删除test表
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | test | table | postgres
(1 row)
postgres=# drop table test;
DROP TABLE
postgres=# \d
Did not find any relations.
删除数据库
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
template0 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
testdb | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
(4 rows)
postgres=# drop database testdb;
DROP DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
template0 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
后续会介绍postgres数据库实践的更多内容，希望大家多多关照，一起学习。
该文为本人原创，转载请表明出处。
扫一扫
916
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
