Web Patrika

If you get this error while connecting to a postgres database, most likely you are missing an entry in pg_hba.conf file to allow users from your machine to access the database. You will normally find the pg_hba.conf file in /var/lib/pgsql/data directory. If you don't find it there, look for PGDATA environment variable by doing a login to postgres server as a postgres user and typing env at command prompt. Then edit the file to add following entry ; host all all XYZ 255.255.255.255 trust just below the following entry host all all 127.0.0.1 255.255.255.255 trust and restart the postgres process by typing pg_ctl restart . It should now allow all user from your machine to connect to the database.

I was trying to use tomcat connection pooling to establish a connection to MySQL database. I ran into "Bad handshake" SQLException. After few minutes on internet, I figured out that it was a problem with the driver. However, when I checked WEB-INF/lib directory I found latest mysql driver already placed in that directory. However, I found another older MySQL driver in <tomcat-dir>/common/lib . So tomcat was loading the old driver from classpath ahead of latest drivers in WEB-INF/lib directory. Once I updated the old driver with the latest version, everything worked fine. Note: removing the driver from <tomcat-dir>/common/lib did not fix the problem since tomcat connection pool needs to load the driver from common library location.

I noticed that a web application deployed on tomcat application server kept throwing JDBC exceptions after every couple of days. So I guessed that it must be a problem with mysql database connection timing out. I did quick search on the issue and found this article explaining the problem. You need to switch from the default Hibernate connection pooling to one of the production grade database connection pooling technology and set appropriate timeout settings. You will also need to create a c3p0.properties file containing following values; # a good mysql test query c3p0.preferredTestQuery=SELECT 1 c3p0.testConnectionOnCheckout=true

I was very comfortable using MySQL query and admin GUI tools to connect to MySQL database. However, recently I had to manage a database that was behind a firewall. I found these steps very useful to enable ssh tunneling (or forwarding) to access MySQL database behind the firewall using GUI tools. It is very easy to setup and only took couple of minutes.

Following command can be used to take a dump of an existing MySQL database. mysqldump db-name --host=host-name --user=user-id --password=user-pass > init-db.sql This will create a file called init-db.sql containing sql queries to reinitialize the database.

To create a database; createdb [db-name] To get a psql prompt; psql [db-name] To export table data; pg_dump --data-only --table=table-name db-name > file-name Following commands can be executed at psql prompt. list all databases in postgres; \l list all tables in a database; \d execute a sql script; \i [sql-script]

Recently I had to install postgresql on a linux machine. I ended up referring to more than one article on the web. So here's an attempt to capture all in one document. 1. Download latest postgresql-XXX.tar.gz (where XXX is a release number) distribution of postgres database. At the time of writing this document 8.2 is the latest release. 2. Due to built-in security reasons, postgres cannot run as a root user. Therefore, become a superuser and create a postgres group. Then add a user postgres to the group as follows; sudo su - root (become a superuser) groupadd postgres (create a postgres group) useradd postgres –g postgres (create a postgres user) 3. Extract the tar file into /usr/local directory. This should create a postgresql-XXX subdirectory in /usr/local. cd /usr/local gunzip -c /path-to-file/postgresql-XXX.tar.gz | tar xvf - 4. create a /usr/local/pgsql directory where postgres database will be installed. mkdir /usr/local/pgsql 5. Assign ownership of di...