Tuesday, August 9, 2016

How to rotate PgBouncer logs in Linux/Windows ?

Before doing a deep dive into the subject, a short outline about PgBouncer, its a lightweight connection pooler for PostgreSQL that dramatically reduces the processing time and resources for maintaining a large number of client connections to one or more databases. Typically used to increase the number of user connections that can be handled in a high performance environment. For more details on Installing/Configuring PgBouncer refer to the documentation here.
Like other tools, PgBouncer has a  stderr/syslog logging architecture to record connection, disconnection, and  pooler_errors with different verbosity levels. As of now, the greater part of logging go to one single file "pgbouncer.log" and grows endlessly. Sometimes, it might be a potential risk of making a system unresponsive due to lack of disk space on the log file location. At present, PgBouncer logging has no in-built configuration to rotate logs on the basis of age or size, hence it forces users to choose alternative methods. IMO, there are two approaches to handle it :-
  1. Configure PgBouncer in "syslog" method to rely on OS log rotation or
  2. Configure log rotation using OS utilities on "pgbouncer.log" file.

Method 1:

Its pretty straightforward to configure syslog in PgBouncer, set "syslog" to 1 (default 0); give a name to begin the log line in OS logs in "syslog_ident" (default 'pgbouncer') and specify the facility details in "syslog_facility" (default daemon). A sample output from my OS logs(/var/log/messages):
Aug 5 16:54:27 raghavt pgbouncer[62549]: C-0x1cdfe60: postgres/postgres@unix(62621):6432 login attempt: db=postgres user=postgres tls=no
Aug 5 16:54:27 raghavt pgbouncer[62549]: S-0x1ce4b10: postgres/postgres@127.0.0.1:5432 new connection to server (from 127.0.0.1:38947)
Aug 5 16:54:27 raghavt pgbouncer[62549]: C-0x1cdfe60: postgres/postgres@unix(62621):6432 closing because: client close request (age=0)
Note: If "syslog" enabled, comment or blank out the "logfile" parameter, else it will be additional logging. 

Method 2:

Logrotate is one of the OS utility that has an ability to rotate logs systematically and archive to reduce an operating system's disk space requirement. Each log file may be handled daily, weekly, monthly, or when it grows too large. A default configuration file "/etc/logrotate.conf" defines the log rotation age/size/interval.  Using this tool logs can be kept longer with less disk space. Many people have articulated about the usage of the utility which you can discover it over net anyway, thus am jumping directly into the implementation phase.
First, create a configuration file in /etc/logrotate.d/ directory for pgbouncer logs. I have named it as "/etc/logrotate.d/pgbouncer" with below details:
/var/log/pgbouncer/pgbouncer.log {
      rotate 10
      missingok
      sharedscripts
      notifempty
      nocompress
      size 10m
      daily
      create 0640 postgres postgres
      postrotate
           /bin/kill -HUP `cat /var/pgbouncer-postgres/pgbouncer.pid 2> /dev/null` 2>/dev/null ||true
      endscript
}
About the configuration file, first line indicate the pgbouncer log file location("logfile" parameter values in pgbouncer.ini file) and next are the parameters that work on rotation thresholds like; how many log files to maintain (rotate); issue no error and go on to next log (missingok); what script should be executed pre/post rotation (prerotate/postrotate); run once or multiple times pre/post scripts (sharedscripts); do not rotate the log if it is empty (notifempty); after rotation an old log file should be compressed with gzip utility (compress/nocompress); on how much size log rotation should be performed (size); how often to rotate a particular log (daily); and what permission new log file should be (create).

Now we can see new log files rotated with 10M size. (We can even force the rotation with command "logrotate -f /etc/logrotate.conf")

[root@172.16.210.202 pgbouncer]# ls -lrth
total 16K
-rw-r-----. 1 postgres postgres 10M Jul 27 15:30 pgbouncer.log-20160727
-rw-r-----. 1 postgres postgres 11K Jul 27 18:32 pgbouncer.log
That was simple right (smile), now lets check the same on Windows environment.

On Windows:

I know very less about windows utilities, consequently I did some googling and found a Windows version utility called "LogRotateWin"  which works same like Linux version of logrotate. For more details refer to detailed documentation available on Installation/Configuration/Usage here
Let's see how it works, first download ".msi" version of LogRotateWin available on the site as "logrotateSetup*.zip" file. Extract and execute the ".msi" file, it will install the utility to "c:\Program Files (x86)\LogRotate" location. You can find the default configuration file(logrotate.conf) under "c:\Program Files (x86)\LogRotate\Content". 
Next, edit the "c:\Program Files (x86)\LogRotate\Content\logrotate.conf" file and specify the full path of "pgbouncer.log" file with same rotation parameters. A sample copy of my configuration file tested on Windows 10. (Note: Below parameter values are used to test the utility)
c:\Program Files (x86)\LogRotate\Content>more logrotate.conf
"c:\Program Files (x86)\PgBouncer\log\pgbouncer.log" {
rotate 10
copytruncate
create
missingok
sharedscripts
nocompress
size 200k
daily
}
To verify, I have forced the log rotation with "-f" option
c:\Program Files (x86)\LogRotate>logrotate.exe -f Content\logrotate.conf
logrotate: Force option set to true
Here's the result:
C:\Program Files (x86)\PgBouncer\log>dir
Volume in drive C has no label.
Volume Serial Number is F226-9FFB

Directory of C:\Program Files (x86)\PgBouncer\log

08/08/2016 01:31 PM <DIR> .
08/08/2016 01:31 PM <DIR> ..
08/08/2016 01:31 PM 0 pgbouncer.log
08/08/2016 01:31 PM 6,626 pgbouncer.log.1
08/08/2016 01:31 PM 13,252 pgbouncer.log.2
3 File(s) 19,878 bytes
2 Dir(s) 26,905,051,136 bytes free
Nice right !!!.
On most Linux distributions, logrotate runs daily using "logrotate.conf" as part of cronjob, similarly on Windows, we can schedule a task in Windows Task Scheduler to rotate the logs daily. FYI, I have not explored much on "LogRotateWin" utility just a basic level. In case, if you encounter any issue please post it on logrotate General Discussion forum.
Thank you for reading. 
--Raghav

Wednesday, May 25, 2016

Ways to access Oracle Database in PostgreSQL

Today, organizations stores information(data) in different database systems. Each database system has a set of applications that run against it. This data is just bits and bytes on a file system - and only a database can turn the bits and bytes of data into business information. Integration and consolidation of such information(data) into one database system is often difficult. Because many of the applications that run against one database may not have an equivalent application that runs against another. To consolidate the information into one database system, we need a heterogeneous database connection.  In this post, I'll demo on how you may connect PostgreSQL to one of heterogeneous database Oracle using different methods.

Below are few methods to make connection to Oracle database in PostgreSQL.
  • Using ODBC Driver
  • Using Foreign DataWrappers
  • Using Oracle Call Interface(OCI) Driver
Softwares used in demo(included download links) - CentOS 7 64bit, PostgreSQL 9.5, EDB Postgres Advanced Server 9.5, ODBC-Link 1.0.4, unixODBC-2.3.4, Oracle Instant Client 11.x Drivers & Oracle_FDW 

Using ODBC Driver

Open DataBase Connectivity(ODBC) is a standard software API for using DBMS. The ODBC driver/ODBC Data source(API) is a library that allows applications to connect to any database for which an ODBC driver is available. It's a middle layer translates the application's data queries into commands that the DBMS understands. To use this method, an open source unixODBC and Oracle ODBC driver(Basic/ODBC/Devel) packages required. Along with a module in PostgreSQL that can communicate to DSN created using unixODBC and Oracle ODBC driver. Few years back CyberTec has released a module ODBC-Link, at present it is obsolete, however, it has a dblink-style implementation for PostgreSQL to connect to any other ODBC compliant database. We can use this module for basic connectivity to Oracle. Let's see.

Install unixODBC
tar -xvf unixODBC-2.3.4.tar.gz
cd unixODBC-2.3.4/
./configure --sysconfdir=/etc
make
make install
Binary/Libraries/configuration files location: /usr/local/bin,/usr/local/lib,/etc(odbc.ini,odbcinst.ini)

Install Oracle ODBC Driver
rpm -ivh oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm
rpm -ivh oracle-instantclient11.2-odbc-11.2.0.4.0-1.x86_64.rpm
rpm -ivh oracle-instantclient11.2-devel-11.2.0.4.0-1.x86_64.rpm
Binary/Libraries location: /usr/lib/oracle/11.2/client64

Install ODBC-Link
tar -zxvf ODBC-Link-1.0.4.tar.gz
cd ODBC-Link-1.0.4
export PATH=/opt/PostgreSQL/9.5/bin:$PATH
which pg_config 
make USE_PGXS=1
make USE_PGXS=1 install
Libraries and SQL files location: /opt/PostgreSQL/9.5/share/postgresql/contrib

Installation will create a ODBC-Link module SQL file in $PGHOME/contrib directory. Load the SQL file, which will create a schema by name "odbclink" with necessary functions in it.
psql -p 5432 -d oratest -U postgres -f /opt/PostgreSQL/9.5/share/postgresql/contrib/odbclink.sql
At this point, we have installed unixODBC Drirver, Oracle ODBC driver and ODBC-Link module for PostgreSQL. As a first step, we need to create a DSN using Oracle ODBC.

Edit /etc/odbcinst.ini file and pass the drivers deifinition
## Driver for Oracle
[MyOracle]
Description     =ODBC for oracle
Driver          =/usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1
UsageCount=1
FileUsage = 1
Driver Logging = 7
Edit /etc/odbc.ini file and create the DSN with driver mentioned in /etd/odbcinst.ini
## Host: pg.raghav-node1.com, PORT: 1521
## Oracle Instance Name: ORA11G, Username: mmruser, Password: mmruser
## ODBC Data source: Ora

[Ora]
Description = myoracledb database
Driver = MyOracle
Trace = yes
TraceFile = /tmp/odbc_oracle.log
Database = //pg.raghav-node1.com:1521/ORA11G
UserID = mmruser
Password = mmruser
Port = 1521
After creating DSN, load all Oracle & unix ODBC driver libraries by setting environment variables and test the connectivity using OS command line tool "dltest" & "iSQL"
[root@172.16.210.161 ~]# export ORACLE_HOME=/usr/lib/oracle/11.2/client64
[root@172.16.210.161 ~]# export LD_LIBRARY_PATH=/usr/local/unixODBC-2.3.4/lib:/usr/lib/oracle/11.2/client64/lib
[root@172.16.210.161 ~]# export ODBCINI=/etc/odbc.ini
[root@172.16.210.161 ~]# export ODBCSYSINI=/etc/
[root@172.16.210.161 ~]# export TWO_TASK=//pg.raghav-node1.com:1521/ORA11G
[root@172.16.210.161 ~]# dltest /usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1
SUCCESS: Loaded /usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1
[root@172.16.210.161 ~]# isql ora -v
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>
Now, set the same environment variables for postgres user for loading the libraries and restart the PostgreSQL cluster to take effect. Connect to PostgreSQL and call odbclink functions to connect to Oracle database.
[root@172.16.210.163 ~]#su - postgres
[postgres@172.16.210.163 ~]$ export ORACLE_HOME=/usr/lib/oracle/11.2/client64
[postgres@172.16.210.163 ~]$ export LD_LIBRARY_PATH=/usr/local/unixODBC-2.3.4/lib:/usr/lib/oracle/11.2/client64/lib
[postgres@172.16.210.163 ~]$ export ODBCINI=/etc/odbc.ini
[postgres@172.16.210.163 ~]$ export ODBCSYSINI=/etc/
[postgres@172.16.210.163 ~]$ export TWO_TASK=//pg.raghav-node1.com:1521/ORA11G
[postgres@172.16.210.163 ~]$ dltest /usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1
SUCCESS: Loaded /usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1
[postgres@172.16.210.163 ~]$ /opt/PostgreSQL/9.5/bin/pg_ctl -D /opt/PostgreSQL/9.5/data/ stop -mf
[postgres@172.16.210.163 ~]$ /opt/PostgreSQL/9.5/bin/pg_ctl -D /opt/PostgreSQL/9.5/data/ start
[postgres@172.16.210.163 ~]$ psql
psql.bin (9.5.2)
Type "help" for help.

postgres=# select odbclink.connect('DSN=Ora');
 connect
---------
       1
(1 row)
Cool right...!!!. For retrieving and manipulating data refer to ODBC-Link README file.

Using Foreign DataWrappers

An SQL/MED(SQL Management of External Data) extension to the SQL Standard allows managing external data stored outside the database. SQL/MED provides two components Foreign data wrappers and Datalink. PostgreSQL introduced Foreign Data Wrapper(FDW) in 9.1 version with read-only support and in 9.3 version write support of this SQL Standard. Today, the latest version has a number of features around it and many varieties of FDW available to access different remote SQL databases.

Oracle_fdw provides an easy and efficient way to access Oracle Database. IMO,its one of the coolest method to access the remote database. To compile Oracle_FDW with PostgreSQL 9.5, we need Oracle Instant Client libraries and pg_config set in PATH. We can use the same Oracle Instant Client libraries used for ODBC-Link. Let's see how it works.

First, set environment variables with OIC libraries and pg_config
export PATH=/opt/PostgreSQL/9.5/bin:$PATH
export ORACLE_HOME=/usr/lib/oracle/11.2/client64
export LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib
Unzip the oracle_fdw module and compile it with PostgreSQL 9.5
unzip oracle_fdw-1.4.0.zip
cd oracle_fdw-1.4.0/
make 
make install
Now switch as 'postgres' user and restart the cluster by loading Oracle Instant Client libraries required for oracle_fdw extension and create the extension inside the database.
[postgres@172.16.210.161 9.5]$ export ORACLE_HOME=/usr/lib/oracle/11.2/client64/lib
[postgres@172.16.210.161 9.5]$ export LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib:$LD_LIBRARY_PATH
[postgres@172.16.210.161 9.5]$ /opt/PostgreSQL/9.5/bin/pg_ctl -D /opt/PostgreSQL/9.5/data/ stop -mf
[postgres@172.16.210.161 9.5]$ /opt/PostgreSQL/9.5/bin/pg_ctl -D /opt/PostgreSQL/9.5/data/ start
[postgres@172.16.210.161 9.5]$ psql
Password:
psql.bin (9.5.2)
Type "help" for help.

postgres=# create extension oracle_fdw;
CREATE EXTENSION
Now you can access the Oracle database.
postgres=# CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//pg.raghav-node1.com/ORA11G');
CREATE SERVER
postgres=# GRANT USAGE ON FOREIGN SERVER oradb TO postgres;
GRANT
postgres=# CREATE USER MAPPING FOR postgres SERVER oradb OPTIONS (user 'scott', password 'tiger');
CREATE USER MAPPING
postgres=#
postgres=# CREATE FOREIGN TABLE oratab (ecode integer,name char(30)) SERVER oradb OPTIONS(schema 'SCOTT',table 'EMP');
CREATE FOREIGN TABLE
postgres=# select * from oratab limit 3;
 ecode |              name
-------+--------------------------------
  7369 | SMITH
  7499 | ALLEN
  7521 | WARD
(3 rows)

Using Oracle Call Interface(OCI) Drivers

Oracle Call Interface(OCI) a type-2 driver freely available on Oracle site which allows the client to connect to Oracle database. EDB Postgres Advanced Server (also called EPAS) a proprietary product has built-in OCI-based database link module called dblink_ora, which connects to Oracle database using Oracle OCI drivers. All you have to do to use dblink_ora module, install EPAS(not covering installation) and tell EPAS where it can find Oracle OCI driver libraries. We can make use of same Oracle Instant Client by specifying its libraries location in LD_LIBRARY_PATH environment variable and to take effect restart the EPAS cluster.

First, switch as "enterprisedb" user, load the libraries and restart the cluster. That's all, we are good to access Oracle database.
[enterprisedb@172.16.210.129 ~]$ export LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib
[enterprisedb@172.16.210.129 bin]$ /opt/PostgresPlus/9.5AS/bin/pg_ctl -D /opt/PostgresPlus/9.5AS/data/ restart
[enterprisedb@172.16.210.129 bin]$ psql
psql.bin (9.5.0.5)
Type "help" for help.

edb=# select dblink_ora_connect('oraconn','localhost','edbora','edbuser','edbuser',1521);
 dblink_ora_connect
--------------------
 OK
(1 row)
Note: EPAS connects to the Oracle Database using Oracle Instant Client library "libclntsh.so". If you won't find the library in Oracle Client Library location then create the symbolic link with libclntsh.so pointing to the libclntsh.so.version.number. Refer to documentation.

In the example, dblink_ora_connect establishes a connection to an Oracle database with the user-specified connection information. Later using link name('oraconn' in my case) we can perform operations like SELECT,INSERT,DELETE,UPDATE & COPY using dblink_ora* functions. All functions you can refer from the EnterpriseDB documentation here.

All the above methods will be very handy if you are working on migration projects. Hope its helpful. Thank you

--Raghav