{"id":8916,"date":"2016-09-27T13:03:49","date_gmt":"2016-09-27T11:03:49","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/connecting-your-postgresql-instance-to-an-oracle-database-debian-version\/"},"modified":"2016-09-27T13:03:49","modified_gmt":"2016-09-27T11:03:49","slug":"connecting-your-postgresql-instance-to-an-oracle-database-debian-version","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/connecting-your-postgresql-instance-to-an-oracle-database-debian-version\/","title":{"rendered":"Connecting your PostgreSQL instance to an Oracle database – Debian version"},"content":{"rendered":"

Some time ago I blogged about attaching your PostgreSQL instance to an Oracle database<\/a> by using the oracle_fdw<\/a> foreign data wrapper. This resulted in a comment which is the reason for this post: Doing the same with a Debian system where you can not use the rpm versions of the Oracle Instant Client (at least not directly). Lets go …<\/p>\n

<\/p>\n

What I did to start with is to download the Debian 8 netinstall ISO<\/a> and started from there with a minimal installation (see the end of this post for the screen shots of the installation if you are not sure on how to do it).<\/p>\n

As I will compile PostgreSQL from source I’ll need to install the required packages:<\/p>\n

\nroot@debianpg:~ apt-get install libldap2-dev libpython-dev libreadline-dev libssl-dev bison flex libghc-zlib-dev libcrypto++-dev libxml2-dev libxslt1-dev tcl tclcl-dev bzip2 wget screen ksh git unzip\n<\/pre>\n
Create the directory structure for my PostgreSQL binaries and instance:<\/p>\n
\nroot@debianpg:~ mkdir -p \/u01\/app\/postgres\/product\nroot@debianpg:~ chown -R postgres:postgres \/u01\/app \nroot@debianpg:~ mkdir -p \/u02\/pgdata\nroot@debianpg:~ mkdir -p \/u03\/pgdata\nroot@debianpg:~ chown -R postgres:postgres \/u0*\/pgdata \n<\/pre>\n
Compile and install PostgreSQL from source:<\/p>\n
\npostgres@debianpg:~$ PGHOME=\/u01\/app\/postgres\/product\/95\/db_4\npostgres@debianpg:~$ SEGSIZE=2\npostgres@debianpg:~$ BLOCKSIZE=8\npostgres@debianpg:~$ WALSEGSIZE=16\npostgres@debianpg:~$ wget https:\/\/ftp.postgresql.org\/pub\/source\/v9.5.4\/postgresql-9.5.4.tar.bz2\npostgres@debianpg:~$ tar -axf postgresql-9.5.4.tar.bz2\npostgres@debianpg:~$ cd postgresql-9.5.4\/\n.\/configure --prefix=${PGHOME} \\\n            --exec-prefix=${PGHOME} \\\n            --bindir=${PGHOME}\/bin \\\n            --libdir=${PGHOME}\/lib \\\n            --sysconfdir=${PGHOME}\/etc \\\n            --includedir=${PGHOME}\/include \\\n            --datarootdir=${PGHOME}\/share \\\n            --datadir=${PGHOME}\/share \\\n            --with-pgport=5432 \\\n            --with-perl \\\n            --with-python \\\n            --with-openssl \\\n            --with-ldap \\\n            --with-libxml \\\n            --with-libxslt \\\n            --with-segsize=${SEGSIZE} \\\n            --with-blocksize=${BLOCKSIZE} \\\n            --with-wal-segsize=${WALSEGSIZE}  \\\n            --with-extra-version=\" dbi services build\"\npostgres@debianpg:~$ make world\npostgres@debianpg:~$ make install\npostgres@debianpg:~$ cd contrib\npostgres@debianpg:~$ make install\npostgres@debianpg:~$ cd ..\/..\npostgres@debianpg:~$ rm -rf postgres*\n<\/pre>\n
Initialize a new cluster:<\/p>\n
\npostgres@debianpg:~$ \/u01\/app\/postgres\/product\/95\/db_4\/bin\/initdb -D \/u02\/pgdata\/PG1 -X \/u02\/pgdata\/PG1 --locale=en_US.UTF-8\nThe files belonging to this database system will be owned by user \"postgres\".\nThis user must also own the server process.\n\nThe database cluster will be initialized with locale \"en_US.UTF-8\".\nThe default database encoding has accordingly been set to \"UTF8\".\nThe default text search configuration will be set to \"english\".\n\nData page checksums are disabled.\n\ncreating directory \/u02\/pgdata\/PG1 ... ok\nfixing permissions on existing directory \/u02\/pgdata\/PG1 ... ok\ncreating subdirectories ... ok\nselecting default max_connections ... 100\nselecting default shared_buffers ... 128MB\nselecting dynamic shared memory implementation ... posix\ncreating configuration files ... ok\ncreating template1 database in \/u02\/pgdata\/PG1\/base\/1 ... ok\ninitializing pg_authid ... ok\ninitializing dependencies ... ok\ncreating system views ... ok\nloading system objects' descriptions ... ok\ncreating collations ... ok\ncreating conversions ... ok\ncreating dictionaries ... ok\nsetting privileges on built-in objects ... ok\ncreating information schema ... ok\nloading PL\/pgSQL server-side language ... ok\nvacuuming database template1 ... ok\ncopying template1 to template0 ... ok\ncopying template1 to postgres ... ok\nsyncing data to disk ... ok\n\nWARNING: enabling \"trust\" authentication for local connections\nYou can change this by editing pg_hba.conf or using the option -A, or\n--auth-local and --auth-host, the next time you run initdb.\n\nSuccess. You can now start the database server using:\n\n    \/u01\/app\/postgres\/product\/95\/db_4\/bin\/pg_ctl -D \/u02\/pgdata\/PG1 -l logfile start\n<\/pre>\n
Adjust the logging_collector<\/a> parameter and startup the instance:<\/p>\n
\npostgres@debianpg:~$ sed -i 's\/#logging_collector = off\/logging_collector = on\/g' \/u02\/pgdata\/PG1\/postgresql.conf\npostgres@debianpg:~$ mkdir \/u02\/pgdata\/PG1\/pg_log\npostgres@debianpg:~$ \/u01\/app\/postgres\/product\/95\/db_4\/bin\/pg_ctl start -D \/u02\/pgdata\/PG1\/\npostgres@debianpg:~$ \/u01\/app\/postgres\/product\/95\/db_4\/bin\/psql\npsql (9.5.4 dbi services build)\nType \"help\" for help.\n\npostgres= select version();\n                                                   version                                                   \n-------------------------------------------------------------------------------------------------------------\n PostgreSQL 9.5.4 dbi services build on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit\n(1 row)\n\npostgres=\n<\/pre>\n
Download the Oracle Instant Client zip file from here<\/a>. You’ll need these:<\/p>\n