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
make
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:

/etc/postgresql/8.4/main

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         192.168.0.19/24        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:

psql

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:

\d <TABLENMAE>

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


select
 t.relname as table_name,
 i.relname as index_name,
 a.attname as column_name
from
 pg_class t,
 pg_class i,
 pg_index ix,
 pg_attribute a
where
 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
 t.relname,
 i.relname;

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.