PipelineDB安装（CentOS 7）

最新推荐文章于 2025-02-09 15:13:41 发布

gcl_guyunlige

最新推荐文章于 2025-02-09 15:13:41 发布

阅读量721

点赞数

分类专栏： postgresql PipelineDB 系统

本文链接：https://blog.youkuaiyun.com/gaokcl/article/details/95345324

版权

系统同时被 3 个专栏收录

3 篇文章

订阅专栏

postgresql

2 篇文章

订阅专栏

PipelineDB

2 篇文章

订阅专栏

Postgresql 11:PipelineDB安装

Postgresql 10 安装参考：

https://blog.youkuaiyun.com/gaokcl/article/details/95041127

PipelineDB简单使用参考： https://www.cnblogs.com/zhaohuaxishi/p/10155101.html

一，安装：

1, postpredb : pipelinesql 对应的库：

https://github.com/pipelinedb/pipelinedb/releases

在这里插入图片描述

2，zeromq

https://github.com/zeromq/libzmq/releases/

3, CentOS7 + postpredb-11安装对应的 pipelinesql 版本 ( 安装看第 5 条 )

https://github.com/pipelinedb/pipelinedb/releases/download/1.0.0-13/pipelinedb-postgresql-11-1.0.0-13.centos7.x86_64.rpm

4,安装 zeromq（在安装 pipelinedb 前面安装）

[root@VM_6_21_centos ~]# wget  https://github.com/zeromq/libzmq/releases/download/v4.3.2/zeromq-4.3.2.tar.gz
[root@VM_6_21_centos ~]# tar -zxvf zeromq-4.3.2.tar.gz 
[root@VM_6_21_centos ~]# rm -rf zeromq-4.3.2.tar.gz
[root@VM_6_21_centos ~]# cd zeromq-4.3.2/
[root@VM_6_21_centos zeromq-4.3.2]# ./configure   
[root@VM_6_21_centos zeromq-4.3.2]# make && make install

在这里插入图片描述

# 安装依赖
[root@VM_6_21_centos ~]# yum install libkrb5-dev python-psycopg2 libpq-dev

vi /var/lib/pgsql/11/data/postgresql.conf

#max_worker_processes = 8 # (change requires restart)

改为

max_worker_processes = 128 # (change requires restart)

#shared_preload_libraries = ‘’ # (change requires restart)

改为

shared_preload_libraries = ‘pipelinedb’ # (change requires restart)

修改配置后重启postgresql-11

systemctl restart postgresql-11

5, 安装 pipelinedb

[root@VM_6_21_centos ~]# wget  https://github.com/pipelinedb/pipelinedb/releases/download/1.0.0-13/pipelinedb-postgresql-11-1.0.0-13.centos7.x86_64.rpm
[root@VM_6_21_centos ~]#rpm -ivh pipelinedb-postgresql-11-1.0.0-13.centos7.x86_64.rpm 

# 初始化pg
[root@VM_6_21_centos ~]# su - postgres
[postgres@VM_6_21_centos ~]$/usr/pgsql-11/bin/pg_ctl initdb -D /var/lib/pgsql/11/data

在这里插入图片描述

启动pg

1-1,我这里安装 postpresql 初始化过了

[postgres@VM_6_21_centos ~]$ /usr/pgsql-11/bin/pg_ctl initdb -D /var/lib/pgsql/11/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.

initdb: directory "/var/lib/pgsql/11/data" exists but is not empty
If you want to create a new database system, either remove or empty
the directory "/var/lib/pgsql/11/data" or run initdb
with an argument other than "/var/lib/pgsql/11/data".
pg_ctl: database system initialization failed
[postgres@VM_6_21_centos ~]$ cd /var/lib/pgsql/11/

在这里插入图片描述

1-2,解决方法：

1, cd /var/lib/pgsql/11/data # 删除data中的数据

或者 mkdir /var/lib/pgsql/11/pipelinedata ( pipelinedata 自定义 )

2, 我使用的是第二种，创建 /var/lib/pgsql/11/pipelinedata

[postgres@VM_6_21_centos ~]$ cd  /var/lib/pgsql/11/
[postgres@VM_6_21_centos 11]$ mkdir /var/lib/pgsql/11/pipelinedata  
[postgres@VM_6_21_centos ~]$ /usr/pgsql-11/bin/pg_ctl initdb -D /var/lib/pgsql/11/pipelinedata

在这里插入图片描述

[postgres@VM_6_21_centos ~]$ 
[postgres@VM_6_21_centos ~]$ psql
Password for user postgres: 
psql (11.4)
Type "help" for help.

postgres=# create database pipeline;
CREATE DATABASE
postgres=# \c pipeline;
You are now connected to database "pipeline" as user "postgres".
pipeline=# create extension pipelinedb;
WARNING:  pipelinedb must be loaded via shared_preload_libraries
CREATE EXTENSION
pipeline=# grant all on database pipeline to postgres;
GRANT
pipeline=# create extension pipelinedb;
ERROR:  extension "pipelinedb" already exists
pipeline=# \d
Did not find any relations.
pipeline=# CREATE FOREIGN TABLE wiki_stream (
pipeline(# hour timestamp,
pipeline(# project text,
pipeline(# title text,
pipeline(# view_count bigint,
pipeline(# size bigint)
pipeline-# SERVER pipelinedb;
CREATE FOREIGN TABLE
pipeline=# \d
                List of relations
 Schema |    Name     |     Type      |  Owner   
--------+-------------+---------------+----------
 public | wiki_stream | foreign table | postgres
(1 row)

pipeline=# \q
[postgres@VM_6_21_centos ~]$

在这里插入图片描述

在这里插入图片描述 ## 最后不用 postgres 了，返回 root

[postgres@VM_6_21_centos ~]$ su -
Password: 
su: Authentication failure
[postgres@VM_6_21_centos ~]$ su -
Password: 
Last login: Wed Jul 10 14:58:49 CST 2019 from 210.13.67.94 on pts/0
Last failed login: Wed Jul 10 16:01:26 CST 2019 on pts/0
There were 3 failed login attempts since the last successful login.
[root@VM_6_21_centos ~]#

二，参考：

Ubuntu参考: https://blog.youkuaiyun.com/u012551524/article/details/86705632

https://www.jianshu.com/p/863675a4372e

rpm安装pipelinedb，参考： https://blog.51cto.com/lee90/2369753

部分pipelinedb操作：参考：https://blog.51cto.com/lee90/2369753

# 这里还可以有些创建账号、添加pg_hba地址授权等操作，不在本文讨论的范畴内。

/usr/pgsql-11/bin/psql pipeline # 登录后，执行如下的2个SQL： # 查看数据表：

注意： /usr/pgsql-11/bin/psql pipeline 相当于 su - postgres psql

pipeline=# \d
List of relations
Schema |       Name       |     Type      |  Owner
--------+------------------+---------------+----------
public | wiki_stats       | view          | postgres
public | wiki_stats_def   | view          | postgres
public | wiki_stats_mrel  | table         | postgres
public | wiki_stats_osrel | foreign table | postgres
public | wiki_stats_seq   | sequence      | postgres
public | wiki_stream      | foreign table | postgres
(6 rows)

# 现在我们将数据集解压缩为流并将其写入stdin，它可以用作COPY的输入

curl -sL http://pipelinedb.com/data/wiki-pagecounts | gunzip | /usr/pgsql-11/bin/psql pipeline -c “COPY wiki_stream (hour, project, title, view_count, size) FROM STDIN”

# 查看测试的数据集：

/usr/pgsql-11/bin/psql pipeline -c “SELECT * FROM wiki_stats ORDER BY total_views DESC”;

# 要查看系统中当前的连续视图及其定义，可以运行以下查询：

pipeline=# SELECT * FROM pipelinedb.views;
id | schema |    name    | active |                                                              query
----+--------+------------+--------+----------------------------------------------------------------------------------------------------------------------------------
3 | public | wiki_stats | t      |  SELECT wiki_stream.hour,                                                                                                       +
|        |            |        |     wiki_stream.project,                                                                                                        +
|        |            |        |     count(*) AS total_pages,                                                                                                    +
|        |            |        |     sum(wiki_stream.view_count) AS total_views,                                                                                 +
|        |            |        |     min(wiki_stream.view_count) AS min_views,                                                                                   +
|        |            |        |     max(wiki_stream.view_count) AS max_views,                                                                                   +
|        |            |        |     avg(wiki_stream.view_count) AS avg_views,                                                                                   +
|        |            |        |     percentile_cont((0.99)::double precision) WITHIN GROUP (ORDER BY ((wiki_stream.view_count)::double precision)) AS p99_views,+
|        |            |        |     sum(wiki_stream.size) AS total_bytes_served                                                                                 +
|        |            |        |    FROM wiki_stream                                                                                                             +
|        |            |        |   GROUP BY wiki_stream.hour, wiki_stream.project
(1 row)