Java Solutions: 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

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

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.

Java Solutions

Tuesday, April 5, 2016

Install PostgreSQL on Linux and Windows