The FreeBSD Diary

Providing practical examples since 1998

If you buy from Amazon USA, please support us by using this link.

PostgreSQL 7.2 upgrade and crypto 23 February 2002

Need more help on this topic? Click here
This article has 10 comments
Show me similar articles

I upgraded to PostgreSQL 7.2 earlier this week. I had a few problems, but they were really annoying. The lesson is: observe, think, conclude.

Your first port of call should be the HISTORY file which comes with the PostgreSQL source.

The next step should be a backup of your data:

pg_dump dbname > dbname.pgdump

Then, and only then should you even consider installing 7.2.

Configuration

Much of this information is contained in my original PostgreSQL article so I will only briefly describe what I did.

As root, I issued the following command to create a new PostgreSQL database cluster:

# su -l pgsql /usr/local/bin/initdb /usr/local/pgsql/data

Then I started the database daemon:

# /usr/local/etc/rc.d/010.pgsql.sh start

NOTE: in recent versions of PostgreSQL (e.g. 8.2), the command is:

# /usr/local/etc/rc.d/postgresql start

Then I created myself a new PostgreSQL user account:

# su pgsql $ createuser dan

As dan, I issued the following command:

$ createdb dbname

Then I loaded the database:

psql dbname < dbname.pgdump

That's when the problems started.

Timestamps

The first problem occurred during the loading of the data back into a new database:

ERROR: Bad timestamp external representation 'current_timestamp'

The solution is to edit my data and remove the quotes from current_timestamp. I did this using my favorite editor, joe.

This error occurred next:

ERROR: copy: line 2537, Bad timestamp external representation 'current' lost synchronization with server, resetting connection ^C

This was a problem with my data. I had a timestamp field which contained the value 'current'. I modified the data within my 7.1 database, pg_dump'd the data, and then did another import. This time the data import worked.

Relocated files

The PostgreSQL libraries move from /usr/local/lib/ to /usr/local/lib/postgresql/ with this upgrade. This should not cause you any problems. Unless you are using features such as stored procedures. This one had me stumped for quite some time.

The error which occurred during data loading is:

ERROR: stat failed on file '/usr/local/lib/plpgsql.so': No such file or directory

The problem here is that the file actually resides at /usr/local/lib/postgresql/plpgsql.so. Again, as with the timestamp issue, this is a data problem. The fix is to modify my data file to change the path. The line in question was:

$ grep plpgsql.so fp2migration.sql CREATE OR REPLACE FUNCTION "plpgsql_call_handler" () RETURNS opaque AS 'plpgsql', 'plpgsql_call_handler' LANGUAGE 'C';

Note that it is no longer necessary to use a full path to the lib.

I also encountered another error message:

ERROR: SearchSysCache: Bad cache id 27

This was caused by trying to load a 7.1 plpgsql.so into 7.2. I was greeted with this message when I had the incorrect path in the function plpgsql_call_handler (see above). As mentioned above, the path is no longer necessary.

I originally did a delete function, then a create function. That rendered my functions unusable. I drop'd the database and then reloaded it. The error I was getting was:

ERROR: fmgr_info: function 16557: cache lookup failed

Groups

My original pg_dump did not include the groups and users I had created. That was my error. I should have used pg_dumpall to get that data. See man pg_dumpall:

pg_dumpall -g dbname

I think I'll start using pg_dumpall much more often as part of my backup strategy. Remember: users and groups are global and can affect more than one database.

The Crypto

If you did a make clean after you installed PostgreSQL, you'll need to run make again before building this add-on.

There is a cryptography library included with PostgreSQL. To install it, I followed these instructions as found in the README.pgcrypto in the directory indicated below. Note that the 7.2 part of the directory may change depending upon which version you are using.

# cd /usr/ports/databases/postgresql7/work/postgresql-7.2/contrib/pgcrypto
edit Makefile and specify cryptolib = openssl # gmake # gmake install

If you haven't already done so, you should then install PostgreSQL and start it.

The crypto library will install three things:

/usr/local/share/postgresql/contrib/pgcrypto.sql - sql used to create the crypto stored procedures
/usr/local/share/doc/postgresql/contrib/README.pgcrypto - the documentation
/usr/local/lib/postgresql/pgcrypto.so - the library

To access the library functions, you must load the stored procedures into your database. This step will not be necessary if you are setting up a new PostgreSQL server and you will be restoring a dump of a database which already contains these functions.

psql db_name < /usr/local/share/postgresql/contrib/pgcrypto.sql

Please read the documentation supplied. But here's an example which you can use to confirm that the functions and library are functioning:

fp2migration=# select DIGEST('adfsd', 'md5');
                   digest
---------------------------------------------
 \374\373J|Nm\365\2314\224'\243m\026\314\035
(1 row)

PHP problems 27 February 2002

Today a PHP exploit was announced. After implementing the recommendations within that advisory (i.e. a change to php.ini), I restarted Apache, which refused to restart:

Syntax error on line 240 of /usr/local/etc/apache/httpd.conf: Cannot load /usr/local/libexec/apache/libphp4.so into server: /usr/local/libexec/apache/libphp4.so: Undefined symbol "pg_encoding_to_char"

The line in question contained:

LoadModule php4_module libexec/apache/libphp4.so

The clue to what needs to be done is in the undefined symbol (i.e. pg_). I recompiled and installed php4:

/usr/ports/www/mod_php4 make pkg_delete -f mod_php4-4.1.1 make install make clean

I could have used portupgrade. I did the make first to ensure the port built successfully. Then, and only then did I delete the existing php installation.

Soundex/Fuzzy matching 18 March 2002

If you did a make clean after you installed PostgreSQL, you'll need to run make again before building this add-on.

There is a SOUNDEX/fuzzy matching library included with PostgreSQL. It allows you to search for values which sound similar to a given value. To install it, I followed these instructions as found in the README.fuzzystrmatch in the directory indicated below:

# cd /usr/ports/databases/postgresql7/work/postgresql-7.2/contrib/fuzzystrmatch # gmake # gmake install

If you haven't already done so, you should then install PostgreSQL.

The crypto library will install three things:

/usr/local/share/postgresql/contrib/fuzzystrmatch.sql- sql used to create the SOUNDEX/fuzzy match stored procedures
/usr/local/share/doc/postgresql/contrib/README.fuzzystrmatch - the fuzzy match documentation
/usr/local/share/doc/postgresql/contrib/README.soundex - the soundex documentation
/usr/local/lib/postgresql/fuzzystrmatch.so - the library

psql db_name < /usr/local/share/postgresql/contrib/fuzzystrmatch.sql

Please read the documentation supplied. But here's an example which you can use to confirm that the functions and library are functioning:

freshports=# select levenshtein('MacDonald', 'McDonald');
 levenshtein
-------------
           1
(1 row)

freshports=# select levenshtein('MacDonald', 'Smith');
 levenshtein
-------------
           9
(1 row)

freshports=#

pgAdmin II - Accessing the database remotely 13 August 2002

I have finally given in. I'm going to try a Windows GUI for my database. After looking at the list of related PostgreSQL projects, I decided to try pgAdmin II. But in order to use this application, I need to tell PostgreSQL to accept connections from other boxes. I will do this in two steps:

Allow remote connections
Restrict remote connections to a specific IP address

According to the Run-time configuration information, I need to specify TCPIP_SOCKET = true in my ~pgsql/data/postgresql.conf file. After making this change, I stopped, then restarted the postmaster:

# /usr/local/etc/rc.d/010.pgsql.sh stop # /usr/local/etc/rc.d/010.pgsql.sh start

NOTE: in recent versions of PostgreSQL (e.g. 8.2), the command is:

# /usr/local/etc/rc.d/postgresql stop # /usr/local/etc/rc.d/postgresql start

I verified that incoming connections were being accepted:

[dan@bast:~] $ telnet undef 5432
Trying 192.168.0.56...
Connected to undef.unixathome.org.
Escape character is '^]'.
lkjadf
EFATAL 1:  invalid length of startup packet
Connection closed by foreign host.

This takes care of the first part of step 1. The next step is to ensure that the user can log in. For this, I read Client Authentication. I discovered I need to alter ~pgsql/data/pg_hba.conf. I added the following line:

host       all         192.168.0.99  255.255.255.255    trust

This means to trust all incoming connections from 192.168.0.99. This may not be what you want. Please read the documentation. In my situation, that IP address can be trusted. You decide what is right for you. After making this change, I had to restart the postmaster (see above for how I did it before).

If you see the error:

No pg_hba.conf entry for host 192.168.0.99, user dan, database template1

...then you either forget to add the entry to the pg_hba.conf or restart the postmaster.

Installing pgAdmin II was pretty simple. I then started it and did a File->Connect. I supplied the IP address of the box on which PostgreSQL was running, and the username. Then I clicked connect. So pgAdmin II seems like a good tool.

Need more help on this topic? Click here
This article has 10 comments
Show me similar articles