Installing native pg gem on Mountain Lion

I tried to install the native pg gem on Mac OS X Mountain Lion (10.8.2):

gem install pg

and got this exception:

Building native extensions. This could take a while...
ERROR: Error installing pg:
 ERROR: Failed to build gem native extension.
/Users/robertreiz/.rvm/rubies/ruby-1.9.3-p327/bin/ruby extconf.rb
checking for pg_config... yes
Using config values from /usr/bin/pg_config
checking for libpq-fe.h... yes
checking for libpq/libpq-fs.h... yes
checking for pg_config_manual.h... yes
checking for PQconnectdb() in -lpq... yes
checking for PQconnectionUsedPassword()... yes
checking for PQisthreadsafe()... yes
checking for PQprepare()... yes
checking for PQexecParams()... yes
checking for PQescapeString()... yes
checking for PQescapeStringConn()... yes
checking for PQescapeLiteral()... yes
checking for PQescapeIdentifier()... yes
checking for PQgetCancel()... yes
checking for lo_create()... yes
checking for pg_encoding_to_char()... yes
checking for pg_char_to_encoding()... yes
checking for PQsetClientEncoding()... yes
checking for PQlibVersion()... yes
checking for PQping()... yes
checking for rb_encdb_alias()... yes
checking for rb_enc_alias()... yes
checking for PGRES_COPY_BOTH in libpq-fe.h... no
checking for PGRES_SINGLE_TUPLE in libpq-fe.h... no
checking for struct pgNotify.extra in libpq-fe.h... yes
checking for unistd.h... yes
checking for ruby/st.h... yes
creating extconf.h
creating Makefile
compiling pg.c
pg.c: In function ‘Init_pg_ext’:
pg.c:384: error: ‘PQPING_OK’ undeclared (first use in this function)
pg.c:384: error: (Each undeclared identifier is reported only once
pg.c:384: error: for each function it appears in.)
pg.c:386: error: ‘PQPING_REJECT’ undeclared (first use in this function)
pg.c:388: error: ‘PQPING_NO_RESPONSE’ undeclared (first use in this function)
pg.c:390: error: ‘PQPING_NO_ATTEMPT’ undeclared (first use in this function)
make: *** [pg.o] Error 1

I could fix it by installing postgres via Homebrew.

brew update
brew install postgresql

and after that this here worked perfect:

gem install pg

Brew is awesome!

Access PostgreSQL from outside

After you installed PostgreSQL on a debian server, by default it is not accessible from outside. From other servers! It is just accessible from localhost. To change that you need to make it listen to a special ip address and port. On Debian 6 you have to switch to this directory:


Here are the config files for PostgreSQL. You have to edit the “postgresql.conf” file. There is one line called “listen_addresses”. This line you have to enhance:

listen_addresses = 'localhost, <YOUR_SERVER_IP>' # what IP address(es) to listen on;
 # comma-separated list of addresses;
 # defaults to 'localhost', '*' = all
 # (change requires restart)

And with this lines you are controlling the port and the connections.

port = 5432 # (change requires restart)
max_connections = 100 # (change requires restart)

Now you have to restart the postgres service:

/etc/init.d/postgresql restart

That alone is not enough. Now PostgresSQL is listen but you can anyway not access it 🙂

For security reasons you have to edit the “pg_hba.conf” file, too. Here you say which IP address is trustful enough to access the service. Here you have to add a line like this:

host    all         all        md5

That means that all ip from your network can access all databases and all users.

Setting password for PostgreSQL

After a standard installation of PostgreSQL on Debian linux, there is no password set for the default user postgres. You can set the password by login in as postgres user. If you are root, just type in that:

su postgres

Than you can start the postgres client:


And now execute this command here to set up a password for user postgres.

ALTER USER Postgres WITH PASSWORD '<password>';

That’s it.

Showing Indexes in Postgresql

OK. If you want to see the indexes to a table in Postgresql you can do this here:


If that is not showing what you expected you can do this short sql statement.

 t.relname as table_name,
 i.relname as index_name,
 a.attname as column_name
 pg_class t,
 pg_class i,
 pg_index ix,
 pg_attribute a
 t.oid = ix.indrelid
 and i.oid = ix.indexrelid
 and a.attrelid = t.oid
 and a.attnum = ANY(ix.indkey)
 and t.relkind = 'r'
 and t.relname like 'not%'
order by

That shows all indexes which are beginning with “not”.

I heard from other database where you just have to type in “show indexes”. But why make it so easy if it can be so difficult.

The usability of postgresql is incredible bad!

Running VACUUM

If you are working with PgAdmin from PostgreSQL, I am sure you know this dialog window already:

And in the long text is written:

In most cases, autovacuum will be the best choice.

Really? And if it is in the most cases the best choice, why it isn’t turned on by default ???????????????

PSQLException: The column index is out of range: 1, number of columns: 0

I tried to write a insert statement with Spring JDB Template for an PostgreSQL Database. My insert string looked like this:

final String sql = "INSERT INTO products (prod_name, prod_key, artifact_id, group_id, link, src, prod_type) VALUES ('?','?','?','?','?','?','?')";

And I got this Exception:

PreparedStatementCallback; SQL []; The column index is out of range: 1, number of columns: 0.; nested exception is org.postgresql.util.PSQLException: The column index is out of range: 1, number of columns: 0.

The Problem was that I used ” ‘ ” inside of the sql string close to the “?”. After I removed the ” ‘ ” strings it worked perfectly.

psql client for PostgreSQL

psql is a command line client for the PostgreSQL Database Engine. If you install postgreSQL on Debian psql is installed by default. After you switched to the postgteSQL user:

su postgres

you can access the client with this simpel command:


and you can exit with this command:


You can set a password with this command inside of the psql client:


you can call the help with this command:


It’s a mess! I think more “NOT SEXY” is not possible! MySQL is maybe not as good as PostgreSQL, but from the usability it is 10 times better. In MySQL the command for exit is “exit” not “\q” and for showing the databases the command is “show databases” and for showing tables “show tables”. Much more intuitive than PostgreSQL!

Installing PostgreSQL 8.3 on Debian Linux (leny)

Installing PostgreSQL 8.3 on Debian Linux is pretty easy. With this command it is done.

apt-get install postgresql-8.3

Now the DB system is installed under:


You can see the status of the RDBMS with this command:

/etc/init.d/postgresql-8.3 status

You can start it

/etc/init.d/postgresql-8.3 start

and you can stop it

/etc/init.d/postgresql-8.3 stop

After the successful installation there is also a “postgres” user on your system. You can set the password of this user with this command:

passwd postgres

Now you can login as postgres user with:

su postgres

And with this command you can create a database:

createdb testdatabase

Your DB ist up and running.

MySQL to PostgresSQL migration

Today I migrated a 10 MB database from MySQL to PostgresSQL. I used the “ESF Database Migration Toolkit – Standard 6.5.06”. It works pretty good for me.

I access the database with a spring/hibernate based java application. After some litle changes on the applicationContext.xml I could successfully start my app and access the postgresSQL database.

When I tried to enter new data through the java application I got some errors:

Caused by: java.sql.BatchUpdateException: Batch-Eintrag 0 insert into

The reason for this error is that postgresSQL uses sequences to generate IDs. The migration toolkit from ESF created for every table a separate sequence. By default hibernate creates one sequence for all tables.

After I customized the hibernate mappings to use the sequences created by the migration toolkit from ESF, it works fine for me. Here is the right mapping for a custom sequence.

<id name="id" type="long" column="`ID`">
 <generator >
 <param name="sequence">work_unit_id_seq</param>

It is absolute important that the sequence name is spelled in small letters, in the postgresSQL database.