CentOS7下安装和配置PostgreSQL11

安装

$ yum install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm
$ yum install postgresql96 postgresql96-server postgresql96-contrib postgresql96-devel postgresql96-libs

$ yum install https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7.7-x86_64/pgdg-centos11-11-2.noarch.rpm
$ yum install postgresql11.x86_64 postgresql11-devel.x86_64 postgresql11-plpython.x86_64 postgresql11-server.x86_64 postgresql11-contrib.x86_64 postgresql11-libs.x86_64 postgresql11-llvmjit.x86_64

初始化 PostgreSQL 数据库

$ mkdir /data/pgsql
$ chown -R postgres:postgres /data/pgsql
$ su postgres
$ /usr/pgsql-11/bin/initdb -D /data/pgsql/data
$ vi /usr/lib/systemd/system/postgresql-11.service
Environment=PGDATA=/data/pgsql/data
$ systemctl daemon-reload
$ systemctl enable postgresql-11
$ systemctl start postgresql-11

配置默认 postgres 用户及密码并创建新的用户和数据库

$ cd data
$ su postgres
$ createuser gadfly
$ createdb gadfly_db
$ psql
psql (11.2)
Type "help" for help.
Postgres=# 
postgres=# \password postgres
Enter new password:
Enter it again:
postgres=# CREATE EXTENSION adminpack;
CREATE EXTENSION
postgres=# alter user gadfly with encrypted password 'centos';
ALTER ROLE 
postgres=# grant all privileges on database gadfly_db to gadfly;
GRANT
postgres=# \q
$ exit

配置 PostgreSQL

配置 PostgreSQL-MD5 认证

$ vi /data/pgsql/data/pg_hba.conf
# "local" is for Unix domain socket connections only
local        all      all                     md5
# IPv4 local connections:
host        all      all    192.168.1.0/24    md5
host        all      all    127.0.0.1/32      md5
# IPv6 local connections:
host        all      all    ::1/128           md5

配置 PostgreSQL-Configure TCP/IP

$ vi /data/pgsql/data/postgresql.conf 
[...]
listen_addresses = '*’
[...]
port = 5432
[...]

安装 redis_fdw 外部表插件

$ git clone -b REL_10_STABLE https://github.com/pg-redis-fdw/redis_fdw.git
$ cd redis_fdw
$ export PATH=/usr/pgsql-9.6//bin:$PATH
$ make USE_PGXS=1
$ make USE_PGXS=1 install
$ su postgres
bash-4.2$ psql -U postgres -d postgres
postgres=# create extension redis_fdw;
CREATE EXTENSION
postgres=# CREATE SERVER redis_server
postgres-#     FOREIGN DATA WRAPPER redis_fdw
postgres-#     OPTIONS (address '127.0.0.1', port '6379');
CREATE SERVER
postgres=# CREATE USER MAPPING FOR PUBLIC
postgres-#     SERVER redis_server
postgres-#     OPTIONS (password '');
CREATE USER MAPPING

redis_fdw 详细的用法介绍

CREATE SERVER 支持的 option(指定地址和端口)

address: The address or hostname of the Redis server. Default: 127.0.0.1
port: The port number on which the Redis server is listening. Default: 6379

CREATE USER MAPPING 支持的 option (指定密码)

password: The password to authenticate to the Redis server with. Default:

CREATE FOREIGN TABLE 支持的 option

指定数据库ID
表类型(hash,list,set,zset或scalar)
key 前缀 key 集合 singleton_key 指定 KEY

database: The numeric ID of the Redis database to query. Default: 0
tabletype: can be 'hash', 'list', 'set' or 'zset' Default: none, meaning only look at scalar values.
tablekeyprefix: only get items whose names start with the prefix Default: none
tablekeyset: fetch item names from the named set Default: none
singleton_key: get all the values in the table from a single named object. Default: none, meaning don't just use a single object.

安装www_fdw扩展

$ git clone https://github.com/cyga/www_fdw.git
$ cd www_fdw
$ export USE_PGXS=1
$ make && make install

安装pgsql-http扩展

 $ git clone https://github.com/pramsey/pgsql-http.git
$ cd pgsql-http
$ export PATH=/usr/local/Cellar/postgresql/11.2/bin:$PATH
$ make USE_PGXS=1
$ make USE_PGXS=1 install
$ su postgres
bash-4.2$ psql -U postgres -d postgres
postgres=# create extension http;

安装mysql-fdw扩展

git clone https://github.com/EnterpriseDB/mysql_fdw.git
cd mysql_fdw
$ export PATH=/usr/local/Cellar/postgresql/11.2/bin:$PATH
$ make USE_PGXS=1
$ make USE_PGXS=1 install
$ su postgres
bash-4.2$ psql -U postgres -d postgres
postgres=# create extension mysql_fdw;