Fixing PostgreSQL database after automatic upgrade (Fedora)

December 22, 2011

Hi,
I’m posting this here so that you don’t have to work your way through it yourself.

I recently preformed a FC15->16 upgrade, thus upgrading my postgresql-server installation (9.0 -> 9.1). To my surprise, when I tried to start the server, I got the following error:

[root@PC10CIS pgsql]# service postgresql start
Redirecting to /bin/systemctl  start postgresql.service
Job failed. See system logs and 'systemctl status' for details.

So I did. In the logs, I found:

Dec 22 09:56:35 PC10CIS postgres[4536]: [1-1] FATAL:  database files are incompatible with server
Dec 22 09:56:35 PC10CIS postgres[4536]: [1-2] DETAIL:  The data directory was initialized by PostgreSQL version 9.0, which is not compatible with this version 9.1.2.
Dec 22 09:56:35 PC10CIS pg_ctl[4532]: FATAL:  database files are incompatible with server
Dec 22 09:56:35 PC10CIS pg_ctl[4532]: DETAIL:  The data directory was initialized by PostgreSQL version 9.0, which is not compatible with this version 9.1.2.
Dec 22 09:56:40 PC10CIS pg_ctl[4532]: pg_ctl: could not start server
Dec 22 09:56:40 PC10CIS pg_ctl[4532]: Examine the log output.
Dec 22 09:56:40 PC10CIS systemd[1]: postgresql.service: control process exited, code=exited status=1
Dec 22 09:56:40 PC10CIS systemd[1]: Unit postgresql.service entered failed state.

So i thought, OK let’s run pg_upgrade. But since the old bin dir of postgresql-server was gone, I could not do it. So here are the steps:

1. Move the old database files
$sudo mv /var/lib/pgsql/data{,.old} 2. Initialize a new database $ sudo su - postgres -c "initdb -D /usr/local/pgsql/data"
3. Go to http://pkgs.org and find the version you had previously installed (worry only about the first two numbers (as in 9.0.5 – the major version is 9.0)) and download it
4. Then, use rpm2cpioto extract the files, like that:
$mkdir -p /tmp/pgsql-install ; cd /tmp/pgsql-install ; rpm2cpio ~/Downloads/postgresql-server-9.0.4-1.fc15.x86_64.rpm | cpio -idmv 5. Run the upgrade$ su - postgres -c "pg_upgrade -b /tmp/pgsql-install/usr/bin/ -B /usr/bin/ -d /usr/local/pgsql/data.old -D /usr/local/pgsql/data"
6. Copy over old config files
$sudo cp /usr/local/pgsql/data{.old,}/pg_hba.conf ; sudo cp /usr/local/pgsql/data{.old,}/postgresql.conf And now you’re good to go! Creating an IPv6 suffix from MAC address using PL/pgSQL January 13, 2011 So right now I am writing an application for my university – a server application controlling the access to the network by means of VMPS. Anyways, we need to assign IPv4 and IPv6 addresses to registered computers for the use of DHCP. As we all know, IPv6 addresses are generated based on the MAC address of the network adapter. Since PostgreSQL has an excellent support for inet types, we chose to stick with that. And since I believe the best way to design a DB-based application is to put as much logic into the database as possible, I chose to generate the IPv6 addresses on the DB’s side. But when it came to generating them, it turned out that the only thing you can do with a MAC address (PostgreSQL type macaddr) is cast it to text. I mean, what a joke! So I came up with a solution to generate the MAC-depended suffix needed for the generation of IPv6 address. In case it proves useful to anybody, here you go: CREATE OR REPLACE FUNCTION mac_create_suffix(macaddr) RETURNS text AS ' DECLARE mac ALIAS FOR$1;
ret text;
BEGIN
mac_switched := (SELECT mac_switch_7b(mac));
ret := mac_switched::text;
ret := substring(ret from 1 for 2) || substring(ret from 4 for 2) || '':'' ||
substring(ret from 7 for 2) || ''ff'' || '':'' ||
''fe'' || substring(ret from 10 for 2) || '':'' ||
substring(ret from 13 for 2) || substring(ret from 16 for 2);
RETURN ret;
END;'
LANGUAGE 'plpgsql';
DECLARE
mac_orig ALIAS FOR \$1;
mac_orig_txt text;
mac_ret_txt text;
siodmyBit BIT(4);
buf text;
bit_orig BIT(4);
bit_repl BIT(4);
BEGIN
--- we'll use that to switch the 7th bit from the left to 1
siodmyBit := ''0010''::BIT(4);
mac_orig_txt := upper(mac_orig::text);
--- switching the 7th bit from the left will only affect the second letter of the MAC address
buf := (SELECT substring(mac_orig_txt from 2 for 1));
bit_orig := CASE buf
WHEN 0::text  THEN ''0000''::BIT(4)
WHEN 1::text  THEN ''0001''::BIT(4)
WHEN 2::text  THEN ''0010''::BIT(4)
WHEN 3::text  THEN ''0011''::BIT(4)
WHEN 4::text  THEN ''0100''::BIT(4)
WHEN 5::text  THEN ''0101''::BIT(4)
WHEN 6::text  THEN ''0110''::BIT(4)
WHEN 7::text  THEN ''0111''::BIT(4)
WHEN 8::text  THEN ''1000''::BIT(4)
WHEN 9::text  THEN ''1001''::BIT(4)
WHEN ''A''    THEN ''1010''::BIT(4)
WHEN ''B''    THEN ''1011''::BIT(4)
WHEN ''C''    THEN ''1100''::BIT(4)
WHEN ''D''    THEN ''1101''::BIT(4)
WHEN ''E''    THEN ''1110''::BIT(4)
WHEN ''F''    THEN ''1111''::BIT(4)
END;
--- THIS IS WHERE WE SWITCH THE 7th BIT TO 1
bit_repl := bit_orig | siodmyBit;
--- END OF SWITCHING
buf := CASE bit_repl
WHEN ''0000''::BIT(4) THEN 0::text
WHEN ''0001''::BIT(4) THEN 1::text
WHEN ''0010''::BIT(4) THEN 2::text
WHEN ''0011''::BIT(4) THEN 3::text
WHEN ''0100''::BIT(4) THEN 4::text
WHEN ''0101''::BIT(4) THEN 5::text
WHEN ''0110''::BIT(4) THEN 6::text
WHEN ''0111''::BIT(4) THEN 7::text
WHEN ''1000''::BIT(4) THEN 8::text
WHEN ''1001''::BIT(4) THEN 9::text
WHEN ''1010''::BIT(4) THEN ''A''
WHEN ''1011''::BIT(4) THEN ''B''
WHEN ''1100''::BIT(4) THEN ''C''
WHEN ''1101''::BIT(4) THEN ''D''
WHEN ''1110''::BIT(4) THEN ''E''
WHEN ''1111''::BIT(4) THEN ''F''
END;
mac_ret_txt := (SELECT overlay(mac_orig_txt placing buf from 2 for 1));
RETURN mac_ret;
END;'
LANGUAGE 'plpgsql';

And here’s the output of a test run:

myDB=# SELECT mac_create_suffix('00:0d:b9:1a:ed:b8');
mac_create_suffix
---------------------
020d:b9ff:fe1a:edb8
(1 row)

A sidenote: You’d probably want to rewrite the hex-to-bin and bin-to-hex to be in a separate function, probably a hash table, but hey! it’s only a proof of concept :)

Hydro-Gaz.com.pl -> commercial project

July 5, 2010

So right now I’m up to getting my father’s company’s website up and running, trying to achieve that with the newest, bleeding-edge CSS3 techniques. We’ll see what comes out :)

Gnuplot and LaTeX tricks

May 7, 2010

A thing to share – so that you don’t waste your precious time looking for a solution, like I just did.

I wanted to plot the inner (hatched) part of a circle in gnuplot and kept looking for a solution, until i worked out this:

gnuplot> set terminal png font "/usr/share/fonts/dejavu/DejaVuSans.ttf" 9
gnuplot> set output 'a_circle.png'
gnuplot> plot [t=0:2*pi] sin(t)-1,cos(t) lc rgb "gold" with filledcurves title "A circle"


Yeah, I know it’s simple. But I had to google for over 10 mins to get to the “filledcurves” option. I copy&pasted this code from a project I’m working on, so the output is redirected to a png file. Hope you don’t mind.

As for Latex: Beware of FIGURES! I only noticed it by accident. If you insert a figure like that:

\begin{figure}
\centering
\label{fig:MyFigure}
\includegraphics[width=0.8\textwidth]{fig/myFigure.png}\\
\caption{SomeDescription}
\end{figure}


the numbering/referencing won’t work properly. Be sure to insert the \label{} part as the last thing in the figure environment or you’ll get strange results when referencing to that figure.

That’s it for now ;) Hope it’ll help.

The first attempt at constructing a robot

March 20, 2010

Well, I recently got around to building a robot with my friends. For a start, we chose a Line Follower robot.

It’s driven by two transoptors, the output of which is used as the input for PID-based driver. It has a custom-built H bridge for the motors. We are planning on adding additional sensors on the front in order to accomplish greater accuracy. Here are some videos:

The onboard video is freaking me out :) Notice the speed and tightness of cornering ;)

A simple non-linear neuron model

October 12, 2009

Hello everyone,

though delayed (thanks to *indows), here comes my neuron model article. First of all, I want to make clear that none of these can be treated as a 100% truth. There may be mistakes everywhere…

Some bibliography

There is a very good intro to neural networks theory written by some other WordPress blogger. It can be found here.

There is also a great book in Polish, available on-line – by the master and pioneer of Neural Networks in Poland – prof. Tadeusiewicz, available here, which I wholeheartedly recommend.

This is for those who want to learn how to create neural networks but never came across a simple example – for people like me. You get a lot of full-sized neural networks source code available on-line, whole C++ ANN libraries GPL-licensed, but never anything for a simple human being to start with. So I decided it was a high time to change that. From now on, I’ll keep posting NN-related source code, increasing its level of complicatedness. The examples will be based on the programs that come along with the book written by Tadeusiewicz. Those programs were written in QBASIC, which is rather ancient. So i’ll kind of port them to C++.

Source code itself…

can be found here. It’s not complete and idiot-proof so if you want to prove it’s not working, you can easily :P The idea is that you can characterize a flowe by two traits – it can smell good (or bad) and it can be colourfull. Those two traits can be assessed with a value ranging (for example) from -5 to 5. So you can teach the neuron to like flowers which smell very well (4-5) and are quite colourfull (2-3) and after few rounds (7 is usually enough) it can discern well those from the others and tell you how much it likes such flowers.. A pretty straightforward example but it should suffice to illustrate a simple neuron.

The neuron

is non-linear with activation function being f(x) = 1/(1 – exp(-x)). Its derivative is f(x)*(1-f(x)). Well, I guess you’ll get the rest from the source code. If not, comment and ask ;)

Have fun,

dare2be

A simple non-linear neuron model – source code

October 12, 2009
A simple non-linear neuron model - source code

Read the rest of this entry »

Why I actually hate *indows…

October 11, 2009

First post is going to be actually a strong one..

But it is fully justified, since I’ve just recovered my precious Gentoo installation after the mess Windows made to my disk..

Here comes the story: I need to scan a whole book (for translating purposes) so I borrowed a Plustek scanner from my dad. As we all know, Plusteks are dreadful when it comes to Linux support. But not having any other option, what could I do? I had to install *indows on my HDD to get it working.

After I finally got my *indows CD to boot (after changing few parameters in BIOS), I had to face the pure idiotism of *indows not recognizing any partitions other that NTFS/FAT. Thanks God I use LVM so I’m quite flexible and I found enough place for *indows installation. So i hit (from the *indows installer) “Delete partition”, than “Create partition” and got the weird error which can be compressed into “I’m stupid, I can’t create a new partition, although I’ve just successfully removed one.” So I hit whatever it took to exit and decided to give up and return to Gentoo to write a post on WordPress about my new neuron model. This is when I got that beautiful message

This sucker altered my partition table in some wicked way so that I had to search for some old Gentoo LiveCD, boot it and chroot to my old environment and setup GRUB once more.

*indows… Why is it so destructive?!

(Thank God Linux is simple and I solved my problem in a less than two minutes…)

Hey!

October 11, 2009

Hello, everybody ;)

Wanted to say hi to whatever comes to my blog. I’m not that really into blogging which brings me to the question: “What am I doing here, exactly?”

Anyways, I’ll try to post some useful (hopefully) thoughts on whatever I learn ;)

See you around…

dare2be