Public | Automated Build

Last pushed: 3 days ago
Short Description
PostgreSQL 9.5 with plpython3u support and some foreign data wrappers.
Full Description

Based on postgres:9.5 with multicorn.sqlalchemyfdw, mysql_fdw and cstore_fdw extensions installed.
See Dockerfile for more details.
For Oracle support - see draft notes at the bottom.
More about Postgres foreign data wrappers
Sample of run script: run_dev_postgres.sh

Test new features

Enable plpython3u:
create extension plpython3u;

Test plpython3u:
create or replace function pytest()
returns text
as $$
import pandas
return pandas.__version__
$$ language 'plpython3u';
select pytest();

Enable mysql_fdw:
create extension mysql_fdw;

Create server:
create server mysql_db foreign data wrapper mysql_fdw options (
host 'localhost',
port '3306'
);

Store credentials:
create user mapping for current_user
server mysql_db
options (username 'mysql_user', password 'mysql_password');

Create a postgres foreign table pg_tbl1 that is linked to mysql table (or subset of columns) mysql_tbl1:
create foreign table pg_tbl1
(
cust_id bigint not null,
subsidiary varchar(10) not null,
email_addr varchar(120)
...
)
server mysql_db options (
dbname 'retail',
table_name 'mysql_tbl1'
);

Draft Notes for adding Oracle client and installing cx_Oracle python driver.

This step is only required if you need support for Oracle tables as foreign tables in your Postgres.
Oracle client download requires authentication so I had to do this manually by bashing into a running container:

**bash into running container:**
$ docker exec -t -i dev-postgres bash

**Inside container:**
    ## nstall required packages:
apt-get install alien libaio1 wget 
    ## go to Oracle download page, login, accept agreement and start downloading.
    ## get download URLs with AuthParam from your browser download history and use them with wget
    ## http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html
wget oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm?AuthParam=....
wget oracle-instantclient12.1-devel-12.1.0.2.0-1.x86_64.rpm?AuthParam=....
wget oracle-instantclient12.1-sqlplus-12.1.0.2.0-1.x86_64.rpm?AuthParam=....
    ## rename downloaded files so they would have proper .rpm extension and then install:
alien -i oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm oracle-instantclient12.1-devel-12.1.0.2.0-1.x86_64.rpm oracle-instantclient12.1-sqlplus-12.1.0.2.0-1.x86_64.rpm
rm *.rpm
export LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib:$LD_LIBRARY_PATH
export ORACLE_HOME=/usr/lib/oracle/12.1/client64
echo 'export LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib:$LD_LIBRARY_PATH' >> /etc/environment
echo 'export ORACLE_HOME=/usr/lib/oracle/12.1/client64' >> /etc/environment
## install cx_oracle:
pip3 install cx_oracle
    ## clenup:
apt-get -y clean autoclean
apt-get -y autoremove --purge
rm -rf /var/lib/{apt,dpkg,cache,log}
    ## exit container
exit

    ## commit container changes to a new image (you may want to name it differently):
docker commit dev-postgres yurz/postgres-plpy-fwd-ora
Docker Pull Command
Owner
yurz
Source Repository