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.
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:
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:
ALTER ROLE postgres WITH ENCRYPTED PASSWORD 'postgres';
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 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:
You can start it
and you can stop it
After the successful installation there is also a “postgres” user on your system. You can set the password of this user with this command:
Now you can login as postgres user with:
And with this command you can create a database:
Your DB ist up and running.
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`">
It is absolute important that the sequence name is spelled in small letters, in the postgresSQL database.