Java Solutions: April 2016

Friday, April 29, 2016

psql: ERROR: current transaction is aborted, commands ignored until end of transaction block

Once you set autocommit to off at psql and a query failed due to a syntax or other error, you immediately execute another query, the error message " ERROR: current transaction is aborted, commands ignored until end of transaction block" is displayed on the screen.

Do a rollback will fix it.

rollback;

----------------------------------------------------------------------------------------------------

If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.

psql: Set auto commit off or on

Set autocommit off:

SET AUTOCOMMIT = OFF
Commit;

SET AUTOCOMMIT TO OFF
Commit;

Set autocommit on:

SET AUTOCOMMIT = ON
commit;

SET AUTOCOMMIT TO ON
commit;

---------------------------------------------------------------------------------------------

PostgreSQL: convert between date and epoch

1. Convert current time to epoch:

SELECT extract(epoch FROM now());

The following will give you the part before the decimal point.

SELECT round(extract(epoch FROM now()));
SELECT extract (epoch FROM current_date);

2. Convert a particular date to epoch:

SELECT extract (epoch from timestamp '2016-02-28');

SELECT extract (epoch from timestamp '2016-02-28 14:30:00');

3. Convert epoch to date:

SELECT to_timestamp(1461888000);

-------------------------------------------------------------------------------------------

Sunday, April 24, 2016

Resolved - Merge two .idx/.sub video subtitles to display simultaneously

Follow the steps below to merge two .idx/.sub video subtitles.

A. Download and install Subtitle Edit

The Subtitle Edit can be down loaded here. Unzip the file and execute the .exe file to start the installation wizard. Follow the instructions to have the software installed.

B. Convert the .idx/.sub subtitle files to .srt subtitle files

1. Launch the Subtitle Edit by double click the icon on your desktop or the SubtitleEdit.exe file.

2. Click File in the menu bar and choose Open to import one of your .sub file.

3. Choose SubRip (.srt) from the Format drop down list as your output file format.

4. Choose the language in the Language drop down list. If the language is not in the list, click the ... button next to the language drop down list to download the language.

5. Choose the dictionary in the Dictionary drop down list. Make sure your language matches your dictionary. When the images in the .sub file is translated to text in the .srt file, the dictionary is used to check if words are correct.

6. Click the Start OCR button. (OCR stands for Optical Character Recognition.) Fix any errors prompted.

7. Click the OK button once the OCR is finished to save the .srt file.

8. Repeat steps 2 to 7 to convert your other .sub file to .srt file.

C. Merge the tow .srt files into one .aas file.

Go to http://pas-bien.net/2srt2ass/ to merge the two .srt files into one .aas file online.

Reference:

1. Convert .idx Subfile to .srt using subtitle edit

----------------------------------------------------------------------------------------------

Friday, April 22, 2016

SQL: exclude records of a subquery from SELECT resultset; NOT EXISTS, NOT IN, and MINUS - resolved

Lets say you have the following two tables in your database.

FRUIT
fruitId name price

1 Apple 1.89
2 Orange 0.98
3 Pear 1.25
4 Banana 1.23
. . . . . .

SALE
itemId charge employeeId Date
1 55.50 34 2016-03-03

. . . . . .

You want to see which fruits have not had any business in the last 30 days. All the SQLs below shall work.

(* Current date: Oracle is SYSDATE, PostgreSQL CURRENT_DATE or NOW(), MySQL CURRENT_DATE or NOW())

1. SELECT name FROM FRUIT f WHERE NOT EXISTS (SELECT * FROM SALE s WHERE f.fruitId = s.itemId and s.Date > (SYSDATE - 30));

2. SELECT name FROM FRUIT f WHERE f.fruitId not in (SELECT s.itemId FROM SALE s WHERE s.Date > (SYSDATE - 30));

3, SELECT name FROM FRUIT
MINUS
SELECT name FROM FRUIT f INNER JOIN SALE s ON s.itemId = f.fruitId and s.Date > (SYSDATE - 30);

Among these, 2 is the least effective.

---------------------------------------------------------------------------------------

Wednesday, April 20, 2016

PostgreSQL: current transaction is aborted, commands ignored until end of transaction block; 25P02 in_failed_sql_transaction - resolved

Once the Connection is set to auto commit false, all the SQL statement executions using this same Connection are in one transaction block.

In PostgreSQL, if one of the SQL statement execution failed, you ignored it, and continued executing other SQL statements, it throws the error, "org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block." The exception SQL state is 25P02, in_failed_sql_transaction.

For example, you have a FRUIT table.

NAME PRICE LOCATION DESCRIPTION

Apple 1.64 Florida Very popular

Orange 0.89 Florida Winter fruit

The following code will incur such an error.

public void updateFruit(Connection con) {

try {

PreparedStatement ps = con.prepareStatement("Update FRUIT set PRICE = 'Ten dollars' WHERE NAME = 'Apple'");

//It will fail for assigning price a string instead of a number

ps.executUpdate();

} catch (SQLException e) {

System.out.println("Failed to update");

}

try {

PreparedStatement ps2 = con.prepareStatement("Insert into FRUIT values ('Pineapple', 2.15,'Florida', 'Delicious');

ps2.executeUpdate();

} catch(SQLException e2) {

e2.printStackTrace(System.out);

}

The output of the above code is below.

Failed to update

org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block

To Fix it, set save points to rollback when query fails. Only queries after the save point are rolled back.

public void updateFruit(Connection con) {

Savepoint sp = null;

try {

sp = con.setSavepoint();

PreparedStatement ps = con.prepareStatement("Update FRUIT set PRICE = 'Ten dollars' WHERE NAME = 'Apple'");

//It will fail for assign price a string instead of a number

ps.executUpdate();

} catch (SQLException e) {
try {

con.rollback(sp);
} catch(SQLException se) {
System.out.println("Failed to rollback.");
}

System.out.println("Failed to update");

}

try {

sp = con.setSavepoint();

PreparedStatement ps2 = con.prepareStatement("Insert into FRUIT values ('Pineapple', 2.15,'Florida', 'Delicious');

ps2.executeUpdate();

} catch(SQLException e2) {
try {

con.rollback(sp);
con.releaseSavepoint();
}catch(SQLException se) {
System.out.println("Failed to rollback.");
}

e2.printStackTrace(System.out);

}

}

-------------------------------------------------------------------------------------------------

Limit select rows and skip first / top rows in Oracle and PostgreSQL

Oracle

1. To return a limited number of rows in Oracle, you can use the rownum < number of rows . For example,

SELECT NAME, PRICE FROM FRUIT WHERE PLACE = 'FLORIDA' AND rownum < 51 ORDER BY NAME;

This will allow maximum of 50 rows to be returned.

2. To skip the top number of rows in Oracle.

SELECT NAME, PRICE FROM FRUIT WHERE PLACE = 'FLORIDA'

MINUS

SELECT NAME, PRICE FROM FRUIT WHERE PLACE = 'FLORIDA' WHERE rownum < 5;

This will skip the top 4 rows.

To retrieve rows between row 5 and 9

SELECT NAME, PRICE FROM FRUIT WHERE PLACE = 'FLORIDA' WHERE rownum < 10

MINUS

SELECT NAME, PRICE FROM FRUIT WHERE PLACE = 'FLORIDA' WHERE rownum < 5;

PostgreSQL

1. To return a limited number of rows in PostgreSQL, you can use LIMIT number of rows. For example, the following query returns maximum of 50 rows.

SELECT NAME, PRICE FROM FRUIT WHERE PLACE = 'FLORIDA' ORDER BY NAME LIMIT 50;

2. To skip the top number of rows in PostgreSQL

SELECT NAME, PRICE FROM FRUIT WHERE PLACE = 'FLORIDA' ORDER BY NAME OFFSET 5;

This will skip the first 5 rows.

To retrieve rows between row 5 and 9

SELECT NAME, PRICE FROM FRUIT WHERE PLACE = 'FLORIDA' ORDER BY NAME LIMIT 5 OFFSET 4;

---------------------------------------------------------------------------------------------------

Thursday, April 7, 2016

Error 6 initializing SQL*Plus; SP2-0667: Message file sp1.msb not found ; SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

After having installed Oracle Instant Client, you unexpectedly run into this problem when executing the command: sqlplus <oracle user>/<oracle password>@<oracle IP address>:<port>/<oracle database name>.

It returns the following messages:

Error 6 initializing SQL*Plus
SP2-0667: Message file sp1<lang>.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

To fix this problem:

1. Create a lib directory in your Oracle Instant Client directory.

mkdir lib

2. Copy the ojdbc6.jar, ojdbc5.jar, xtreams.jar, and all the .so files to the lib directory.

3. Create a bin directory in your Oracle Instant Client directory.

4. Copy the adrci, genezi, odbc_update_ini.sh, sqlplus, and uidrvci files to the bin directory.

5. Create a sqlplus/admin directory in your Oracle Instant Client directory and copy the glogin.sql to this directory.

6. Create a network/admin directory in your Oracle Instant Client directory.

7. Create the tnsnames.ora file in the network/admin directory.

<Oracle database name> =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <oracle IP address>)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = <Oracle service name>)
)

Create the sqlnet.ora file in the network/admin directory.

TCP.CONNECT_TIMEOUT=10

8. Set the environmental variables.

export ORACLE_HOME=/usr/oracle/instantclient_12_1

export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export SQLPATH=$ORACLE_HOME/sqlplus/admin

export TNS_ADMIN=$ORACLE_HOME/network/admin

export NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252

9. Logout and log back in.

Reference:

1. oracle instant client 12.1 SP2-0667: Message file sp1<lang>.msb not found

-----------------------------------------------------------------------------------------------------------------

Watch the blessing and loving online channel: SupremeMasterTV live

ERROR: could not load library "/usr/pgsql-9.5/lib/oracle_fdw.so": libclntsh.so.12.1: cannot open shared object file: No such file or directory - resolve

While trying to connect from PostgreSQL to Oracle via oracle_fdw foreign data wrapper, you execute the command, CREATE EXTENSION oracle_fdw; after you have installed the Oracle Instant Client and the Foreign Data Wrapper for Oracle, you receive this message: ERROR: could not load library "/usr/pgsql-9.5/lib/oracle_fdw.so": libclntsh.so.12.1: cannot open shared object file: No such file or directory.

To fix this problem:

1. Copy the oracle_few.so from the foreign data wrapper directory to the /usr/pgsql-9.5/lib directory.

2. Create symbolic links in the /usr/pgsql-9.5/lib directory for all the .so files in the oracle instant client directory.

ln -s /usr/pgsql-9.5/oracle/instantclient_12_1/lib/libclntshcore.so.12.1 /usr/pgsql-9.5/lib/libclntshcore.so.12.1

ln -s /usr/pgsql-9.5/oracle/instantclient_12_1/lib/libipc1.so /usr/pgsql-9.5/lib/libipc1.so

ln -s /usr/pgsql-9.5/oracle/instantclient_12_1/lib/libmql1.so /usr/pgsql-9.5/lib/libmql1.so

ln -s /usr/pgsql-9.5/oracle/instantclient_12_1/lib/libnnz12.so /usr/pgsql-9.5/lib/libnnz12.so

ln -s /usr/pgsql-9.5/oracle/instantclient_12_1/lib/libocci.so /usr/pgsql-9.5/lib/libocci.so

ln -s /usr/pgsql-9.5/oracle/instantclient_12_1/lib/libociei.so /usr/pgsql-9.5/lib/libociei.so

ln -s /usr/pgsql-9.5/oracle/instantclient_12_1/lib/libocijdbc12.so /usr/pgsql-9.5/lib/libocijdbc12.so

ln -s /usr/pgsql-9.5/oracle/instantclient_12_1/lib/libons.so /usr/pgsql-9.5/lib/libons.so

ln -s /usr/pgsql-9.5/oracle/instantclient_12_1/lib/liboramysql12.so /usr/pgsql-9.5/lib/liboramysql12.so

ln -s /usr/pgsql-9.5/oracle/instantclient_12_1/lib/libsqlplus.so /usr/pgsql-9.5/lib/libsqlplus.so

ln -s /usr/pgsql-9.5/oracle/instantclient_12_1/lib/libsqlplusic.so /usr/pgsql-9.5/lib/libsqlplusic.so

ln -s /usr/pgsql-9.5/oracle/instantclient_12_1/lib/libclntsh.so /usr/pgsql-9.5/lib/libclntsh.so

3. Ensure the libclntsh.so file is on your PATH

4. If the problem remains, run command: ldconfig as root to config the dynamic linker run time binding.

--------------------------------------------------------------------------------------

Wednesday, April 6, 2016

WARNING: no Oracle character set for database encoding "SQL_ASCII" - Resolved

During migration from Oracle to PostgreSQL, the problem arises in the situation below.

CREATE EXTENSION oracle_fdw;
CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//<oracle database IP address>:<port>/<oracle database name>');
  GRANT USAGE ON FOREIGN SERVER oradb TO postgres;
  CREATE USER MAPPING FOR postgres SERVER oradb OPTIONS (user '<oracle user>', password '<oracle password>')");
  CREATE FOREIGN TABLE f_table (. . . . . .) SERVER oradb OPTIONS (schema '<oracle schema name>', table '<oracle table name>');
SELECT * FROM f_table;

Returns:

WARNING: no Oracle character set for database encoding "SQL_ASCII"
DETAIL: All but ASCII characters will be lost.
HINT: You can set the option "nls_lang" on the foreign data wrapper to force an Oracle character set.

To fix this problem: set the nls_lang option for the foreign data wrapper.

CREATE EXTENSION oracle_fdw;
  CREATE FOREIGN DATA WRAPPER oracle_fdw2 HANDLER oracle_fdw_handler VALIDATOR oracle_fdw_validator OPTIONS (nls_lang 'AMERICAN_AMERICA.WE8MSWIN1252');

(The value of the nls_lang must be in constant with the charset of the oracle database. Use the following SQL statement to find out the charset of your oracle database: SELECT * FROM V$NLS_PARAMETERS; A list of the nls_lang values can be found at https://docs.oracle.com/cd/B19306_01/install.102/b14317/gblsupp.htm#NTDBX382.)

CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw2 OPTIONS (dbserver '//<oracle database IP address>:<port>/<oracle database name>');
GRANT USAGE ON FOREIGN SERVER oradb TO postgres;
  CREATE USER MAPPING FOR postgres SERVER oradb OPTIONS (user '<oracle user>', password '<oracle password>')");
  CREATE FOREIGN TABLE f_table (. . . . . .) SERVER oradb OPTIONS (schema '<oracle schema name>', table '<oracle table name>');
SELECT * FROM f_table;

Returns the data from the table.

References:

1. How to set nls_lang?

---------------------------------------------------------------------------------------------

Tuesday, April 5, 2016

Install PostgreSQL on Linux and Windows

Linux

A. Figure out the OS and modify the yum files.

1. Before installing, use command (uname -m) to decide the bit of your linux machine; and command (lsb_release -a or cat /etc/redhat-release ) to find the Distributor ID and release of your linux machine. In the following steps, download the corresponding software accordingly.

2. Edit both /etc/yum.repos.d/epel.repo and /etc/yum.repos.d/epel-testing.repo files. Comment out all entries starting with mirrorlist= and uncoment all the entries starting with baseurl=.

Change all https to http.

Add a line sslverify=0

B. Install PostgreSQL

Installing PostgreSQL 9.5 from Yum repository

1. Download the proper postgresql package right for your machine from http://yum.postgresql.org/repopackages.php for record.

1. Find the correct postgresql from its Official Website. Hover the cursor over the link to find out the rpm link.

For example, you download this package for you CentOS 6 (64bit) machine

pgdg-centos95-9.5-2.noarch.rpm

2. Yum install postgresql using the link. For example, on a CentOS 6 (64bit) platform, execute the following command.

yum install http://yum.postgresql.org/9.5/redhat/rhel-6-x86_64/pgdg-centos95-9.5-2.noarch.rpm

3. Edit /etc/yum.repos.d/pgdg-95-centos.repo. Change all https to http.

Add line sslverify=0 to the main and update sessions.

4. List all the packages in pgdg95.

yum list | grep pgdg95

5. Install the packages interested

yum install postgresql95 postgresql95-server postgresql95-libs postgresql95-contrib postgresql95-devel

When prompted: Is this ok [Y/N]: enter Y.

6. Modify the /root/.bash_profile. Add the :/usr/pgsql-9.5/bin:/usr/pgsql-9.5/lib to the PATH.

Creating PostgreSQL data cluster

1. Changing password of system postgres account

Installing postgres creates a default postgres database and a user account with username set to postgres. If you want to change the password to something you know do this:

      passwd postgres

You'll be prompted for a new password.

enter superuser.

su - postgres

2. Init the database

/usr/pgsql-9.5/bin/initdb

3. Modify the pg_hba.conf file (host-based authentication)

vi /var/lib/pgsql/9.5/data/pg_hba.conf

change IPv4 and IPv6 local connections to: host all all all trust

4. Modify the postgresql.conf file

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

Uncomment and change listen_addresses = 'localhost' to listen_addresses = '*'.
Uncomment the line port=5432 and make sure the port is correct.

Starting the database server

su - postgres

1. Start the database server:

/usr/pgsql-9.5/bin/pg_ctl -D /var/lib/pgsql/9.5/data -l logfile start

2. Check if server is running:
pg_ctl status

3. Config to automatically start at boot:

chkconfig postgresql-9.5 on

The data cluster is created by default in /var/lib/pgsql/9.5/data and the extensions folder is in /usr/pgsql-9.5/share/extension/ and the binaries are in/usr/pgsql-9.5/bin.

Launch the psql prompt

su - administrator

1. Download and install the following packages

lib64openssl1.0.0-1.0.2g-4.mga6.x86_64.rpm from https://rpmfind.net/linux/rpm2html/search.php?query=libcrypto.so.1.0.0()(64bit)

lib64openssl-engines1.0.0-1.0.2g-4.mga6.x86_64.rpm from https://www.rpmfind.net/linux/rpm2html/search.php?query=lib64openssl-engines1.0.0(x86-64)

lib64pq5-9.5.1-2.mga6.x86_64.rpm from https://www.rpmfind.net/linux/rpm2html/search.php?query=lib64pq5(x86-64)

yum installall lib64openssl1.0.0-1.0.2g-4.mga6.x86_64.rpm lib64openssl-engines1.0.0-1.0.2g-4.mga6.x86_64.rpm

yum installall yum install lib64pq5-9.5.1-2.mga6.x86_64.rpm

2. Start the pgsql command line:

su - postgres

psql

3. Use the following commands to control the database

/usr/pgsql-9.5/bin/pg_ctl start - start the database server
/usr/pgsql-9.5/bin/pg_ctl stop - stop the database server
/usr/pgsql-9.5/bin/pg_ctl restart - stop/start
/usr/pgsql-9.5/bin/pg_ctl reload - reread its configuration files (postgresql.conf, pg_hba.conf, etc.

To immediately shut down and kick everyone off, run the command below.

pg_ctl -D /var/lib/pgsql/9.5/data -m immediate stop

4. If for some reason, you need to uninstall the postgresql, run the following command.
yum remove postgresql95.x86_64

C. Install JDBC

1. Create the pgJDBC directory at /usr/pgsql-9.5

mkdir pgJDBC

2. Download the jar of pgJDBC according to the java version installed on your machine from https://jdbc.postgresql.org/download.html to the pgJDBC directory.

(e.g. postgresql-9.4.1208.jre7.jar)

3. Set the path by running the command below.

export PATH=$PATH:/usr/pgsql-9.5/pgJDBC.

Or modify the /root/.bash_profile. Add the :/usr/pgsql-9.5/pgJDBC to the end of PATH.

Restart posgresql server.

su - postgres
/usr/pgsql-9.5/bin/pg_ctl restart

4. JDBC parameters

The pgJDBC URL: jdbc:postgresql://<host IP address>:<port>/<database name>

(e.g. jdbc:postgresql://10.10.0.112:5432/postgres)

The pgJDBC driver: org.postgresql.Driver

D. Install ODBC

Login as postgres

1. Run the command below to find the psqlODBC.

yum search postgre | grep odbc

2. Run the command below to install psqlODBC.

  yum install postgresql95-odbc.x86_64 postgresql95-odbc-debuginfo.x86_64 unixODBC

Check to ensure that /usr/pgsql-9.5/lib/psqlodbc.so exists.

3. Modify the /etc/odbcinst.ini file. Set the following parameters for PostgreSQL.

  Description = ODBC for PostgreSQL
Driver = /usr/pgsql-9.5/lib/psqlodbc.so
Setup = /usr/lib64/libodbcpsqlS.so
Driver64 = /usr/pgsql-9.5/lib/psqlodbc.so
Setup64 = /usr/lib64/libodbcpsqlS.so
FileUsage = 1

4. Modify the /etc/odbc.ini file. Set the following parameters.

Description = PostgreSQL connection to postgres database
Driver = PostgreSQL
Database = postgres
Servername = 10.10.0.112
UserName = postgres
Password = superuser
Port = 5432
Protocol = 9.5
ReadOnly = No
RowVersioning = No
ShowSystemTables = No
ConnSettings =

5. Test psqlODBC.

   odbcinst -q -d
It should print on screen: [PostgreSQL]
  [MySQL]

Windows

1. Download PostgreSQL from its Official Website.

2. For installing on Windows, launch the GUI installer wizard by executing the .exe file. The installer will create a default PostgreSQL superuser, postgres. Give it a password when asked for. A detailed installation guide can be obtained from Install PostgreSQL.

After you finished installing PostgreSQL, add full path of its bin and lib directories to the PATH environmental variable.

3. Modify the postgresql.conf. The file is located in the data directory. Open it with a text editor. Change the listen_addresses to '*' (listen_address = '*') and the port to the port number you used during installation

4. Create a new database in pgAdmin III by right clicking Databases and selecting New Database.

5. Create a new schema in pgAdmin III by right clicking the Schemas and selecting New Schema.

6. Install the pgJDBC and pgODBC. Click Start/All Programs/PostgreSQL/Application Stack Builder to launch the Application Stack Builder.

Select your PostgreSQL installation. Click Next.

Unfold Database Drivers. Check pgJDBC and psqlODBC. And click Next

On following screens, click Next or Install to install the pgJDBC and pgODBC.

The pgJDBC URL: jdbc:postgresql://<host IP address>:<port>/<database name>
The pgJDBC driver: org.postgresql.Driver

Reference:

1. An almost idiot's guide to install PostgreSQL 9.5, PostGIS 2.2 and pgRouting 2.1.0 with Yum
2. Install PostgreSQL

-------------------------------------------------------------------------------

Migration from Oracle to PostgreSQL using oracle_fdw Foreign Data Wrapper on Linux - step by step guide

A. Figure out the OS and modify the yum files.

B. Install PostgreSQL

Installing PostgreSQL 9.5 from Yum repository

1. Download the proper postgresql package right for your machine from http://yum.postgresql.org/repopackages.php for record.

Find the correct postgresql from its Official Website. Hover the cursor over the link to find out the rpm link.

For example, you download this package for you CentOS 6 (64bit) machine

pgdg-centos95-9.5-2.noarch.rpm

2. Yum install postgresql using the link. For example, on a CentOS 6 (64bit) platform, execute the following command.

yum install http://yum.postgresql.org/9.5/redhat/rhel-6-x86_64/pgdg-centos95-9.5-2.noarch.rpm

or if you have downloaded the rpm file:
yum install rhel-6-x86_64/pgdg-centos95-9.5-2.noarch.rpm

3. Edit /etc/yum.repos.d/pgdg-95-centos.repo.

Change all https to http.
Add line sslverify=0 to the main and update sessions.

4. List all the packages in pgdg95.

yum list | grep pgdg95

5. Install the packages interested

yum install postgresql95 postgresql95-server postgresql95-libs postgresql95-contrib postgresql95-devel

When prompted: Is this ok [Y/N]: enter Y.

6. Modify the /root/.bash_profile. Add the :/usr/pgsql-9.5/bin:/usr/pgsql-9.5/lib to the PATH.

Creating PostgreSQL data cluster

1. Changing password of system postgres account

Installing postgresql creates a default postgres database and a user account with username set to postgres. If you want to change the password to something you know do this:

      passwd postgres

You'll be prompted for a new password.

enter <your password>.

su - postgres

2. Init the database

Empty the /var/lib/pgsql/9.5/data directory. Delete anything there.

/usr/pgsql-9.5/bin/initdb

3. Modify the pg_hba.conf file (host-based authentication)

vi /var/lib/pgsql/9.5/data/pg_hba.conf

change IPv4 and IPv6 local connections to: host all all all trust

4. Modify the postgresql.conf file

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

Uncomment and change listen_addresses = 'localhost' to listen_addresses = '*'.
Uncomment the line port=5432 and make sure the port is correct.

Starting the database server

su - postgres

1. Start the database server:

  /usr/pgsql-9.5/bin/pg_ctl -D /var/lib/pgsql/9.5/data -l logfile start

2. Check if server is running:
  /usr/pgsql-9.5/bin/pg_ctl status

3. Config to automatically start at boot:

  su - administrator
chkconfig postgresql-9.5 on

The data cluster is created by default in /var/lib/pgsql/9.5/data and the extensions folder is in /usr/pgsql-9.5/share/extension/ and the binaries are in/usr/pgsql-9.5/bin.

Launch the psql prompt

1. Start the pgsql command line:

su - postgres

psql

2. Use the following commands to control the database

/usr/pgsql-9.5/bin/pg_ctl start - start the database server
/usr/pgsql-9.5/bin/pg_ctl stop - stop the database server
/usr/pgsql-9.5/bin/pg_ctl restart - stop/start
/usr/pgsql-9.5/bin/pg_ctl reload - reread its configuration files (postgresql.conf, pg_hba.conf, etc.

To immediately shut down and kick everyone off, run the command below.

pg_ctl -D /var/lib/pgsql/9.5/data -m immediate stop

3. If for some reason, you need to uninstall the postgresql, run the following command.
yum remove postgresql95.x86_64

C. Install JDBC

1. Create the pgJDBC directory at /usr/pgsql-9.5

mkdir pgJDBC

2. Download the jar of pgJDBC according to the java version installed on your machine from https://jdbc.postgresql.org/download.html to the pgJDBC directory.

(e.g. postgresql-9.4.1209.jre7.jar)

3. Set the path by running the command below.

export PATH=$PATH:/usr/pgsql-9.5/pgJDBC.

Or modify the /root/.bash_profile. Add the :/usr/pgsql-9.5/pgJDBC to the PATH.

Restart posgresql server. (It is very important to restart the postgresql server, otherwise it will throw “ERROR: could not load library "/usr/pgsql-9.5/lib/oracle_fdw.so": libmql1.so: cannot open shared object file: No such file or directory” when you try to create the Oracle Foreign Data Wrapper later.)

su - postgres
/usr/pgsql-9.5/bin/pg_ctl restart

4. JDBC parameters

The pgJDBC URL: jdbc:postgresql://<host IP address>:<port>/<database name>

(e.g. jdbc:postgresql://10.04.0.342:5432/postgres)

The pgJDBC driver: org.postgresql.Driver

D. Install ODBC

Login as postgres

1. Run the command below to find the psqlODBC.

yum search postgre | grep odbc

2. Run the command below to install psqlODBC.

  yum install postgresql95-odbc.x86_64 postgresql95-odbc-debuginfo.x86_64 unixODBC

Check to ensure that /usr/pgsql-9.5/lib/psqlodbc.so exists.

3. Modify the /etc/odbcinst.ini file. Set the following parameters for PostgreSQL.

  Description = ODBC for PostgreSQL
Driver = /usr/pgsql-9.5/lib/psqlodbc.so
Setup = /usr/lib64/libodbcpsqlS.so
Driver64 = /usr/pgsql-9.5/lib/psqlodbc.so
Setup64 = /usr/lib64/libodbcpsqlS.so
FileUsage = 1

4. Modify the /etc/odbc.ini file. Set the following parameters.

Description = PostgreSQL connection to postgres database
Driver = PostgreSQL
Database = postgres
Servername = 10.04.0.342
UserName = postgres
Password = superuser
Port = 5432
Protocol = 9.5
ReadOnly = No
RowVersioning = No
ShowSystemTables = No
ConnSettings =

5. Test psqlODBC.

   odbcinst -q -d
It should print on screen: [PostgreSQL]
  [MySQL]

E. Install Oracle Instant Client

Refer Install Oracle Instant Client on Windows and linux to install Oracle Instant Client.

F. Install the Oracle Data Wrapper

Login as administrator.

1. Download the oracle_fdw (Souce code) from https://github.com/laurenz/oracle_fdw/releases/tag/ORACLE_FDW_1_3_0 to the /usr/pgsql-9.5/oracle directory.

unzip oracle_fdw-ORACLE_FDW_1_3_0.zip

2. Compile the code.

cd /usr/pgsql-9.5/oracle/fdw-ORACLE_FDW_1_3_0

make
make install

3. Copy the three files (oracle_fdw.control, oracle_fdw--1.0--1.1.sql, and oracle_fdw--1.1.sql) from /usr/pgsql-9.5/oracle/oracle_fdw/share/extension to /usr/pgsql-9.5/share/extension.

Copy the three sql files (oracle_fdw.sql, oracle_gis.sql, and oracle_import.sql) in the sql directory to /usr/pgsql-9.5/share/extension.

4. Copy the /usr/pgsql-9.5/oracle/oracle_fdw-ORACLE_FDW_1_3_0/oracle_fdw.so to the /usr/pgsql-9.5/lib/ and the /usr/pgsql-9.5/oracle/instantclient_12_1/lib directory.

log out and log back in as administrator.

Ensure that the environmental variables are set correctly.

env

6. Test the oracle_fdw.

su - postgres

psql

CREATE EXTENSION oracle_fdw;

CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//11.15.0.112:1521/ORACLE');

DROP SERVER oradb;

DROP EXTENSION IF EXISTS oracle_fdw;

G. Migration from Oracle to PostgreSQL

Login as postgres
psql

1. Create the Foreign Data Wrapper server.

CREATE EXTENSION oracle_fdw;

  CREATE FOREIGN DATA WRAPPER oracle_fdw_char HANDLER oracle_fdw_handler VALIDATOR oracle_fdw_validator OPTIONS (nls_lang 'AMERICAN_AMERICA.WE8MSWIN1252')

CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw_char OPTIONS (dbserver '//<oracle database host IP address>:<port>/<database name>');

  GRANT USAGE ON FOREIGN SERVER oradb TO postgres;

2. Create user mapping by execute the following statement in the SQL Editor.

CREATE USER MAPPING FOR postgres SERVER oradb OPTIONS (user '<oracle database user>', password '<oracle database password>');

3. Migrating tables.

i) Create the foreign table. A foreign table is a stub to access corresponding remote oracle database table as if it is local.

  CREATE FOREIGN TABLE <foreign table name> SERVER oradb OPTIONS (table '<oracle database table name>');

ii) Migrating data to PostgreSQL.

CREATE TABLE <table name> AS (SELECT * FROM <foreign table name>);

E. Drop the User Mapping, Server, and oracle_fdw Extension.

DROP EXTENSION IF EXISTS oracle_fdw CASCADE;

With CASCADE option at the end, it will drop all objects that depend on the oracle_fdw extension.

Or you can drop each one of them separately.

  DROP USER MAPPING for postgres server oradb;

  DROP SERVER oradb;
  DROP FOREIGN DATA WRAPPER IF EXISTS oracle_fdw_char

  DROP EXTENSION IF EXISTS oracle_fdw;

-----------------------------------------------------------------------------------------------------------------

Watch the blessing and loving online channel: SupremeMasterTV live