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.
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
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 install postgresql
and after that this here worked perfect:
gem install pg
Brew is awesome!
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:
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.
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:
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>';
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
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%'
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!
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 ???????????????
Note for me. Usefull PostgreSQL commands.
switch to the postgres user on the linux machine
create a postgres database
enter the database with psql
show all databases
show all tables in “myFirstDb”
Delete your first DB.
drop database myFirstDb;
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.