Tag Archives: postgres

>Postgres – Converting Encodings

>I’ve run into a number of problems recently with dealing with old databases of ours which are encoded with LATIN1. Now, with postgres 8.3 (maybe before) you’ll get a message if you try to create LATIN1 saying something like

createdb: database creation failed: ERROR:  encoding LATIN1 does not match server's locale en_US.UTF-8
DETAIL: The server's LC_CTYPE setting requires encoding UTF8.

I got bored trying to work out why – it seems to be that postgres now prevents what it shouldn’t have allowed in the past but did. But if you do want to convert from the old to the new locale – how do you do it? Remarkably simple it turns out.

Do a pg_dump of your existing database. Then take the dump file and run it through iconv – something like this.

iconv -f latin1 -t utf8 original.sql > converted.sql

It’s pretty obvious what the options mean (-f = from, -t = to). iconv comes as standard on Mac and should be available for most linux distos (seems to be installed on ubuntu server by default).

However, before you get too excited – you should ensure that whatever apps are using that database will cope with the new encoding for input and output. That may be ‘non-trivial’ 😉

>pg_top on Ubuntu Hardy – Postgres top utility

>This morning I got pgtop working but had some questions which I forwarded to the author Cosimo Streppone. In his very polite reply he pointed out I should really be using pg_top (note the hyphen) so I then set to getting that to work on Ubuntu Hardy Heron.

Download the latest release from the site above (I downloaded pg_top-3.6.2.tar.gz) and then

tar -zxpvf pg_top-3.6.2.tar.gz
cd pg_top-3.6.2

It’s very possible you might get some errors during this. The three I had were

configure: error: no acceptable C compiler found in $PATH

You don’t have the basic build tools installed. Do.

sudo apt-get install build-essential

Also during config

configure: error: pg_config not found

Get the postgres dev libs – do

sudo apt-get install libpq-dev

During make I got a heap of errors – starting with

gcc  -Wall -g -L/usr/lib -lpq  -o pg_top color.o commands.o display.o getopt.o screen.o sprompt.o pg.o pg_top.o username.o utils.o version.o m_linux.o -ldl -lm 
display.o: In function `display_move':
/home/icottee/pg_top-3.6.2/display.c:257: undefined reference to `tgoto'
/home/icottee/pg_top-3.6.2/display.c:257: undefined reference to `tputs'
display.o: In function `display_write':
/home/icottee/pg_top-3.6.2/display.c:387: undefined reference to `tgoto'
/home/icottee/pg_top-3.6.2/display.c:387: undefined reference to `tputs'

The solution I found was to

sudo apt-get install libncurses5-dev

Then rerun config AGAIN and do a make.

Now I could

pg_top --help

And all was good. Read the web page for info about what you can do with it. But in short you can see all running postgres processes, see what they are doing, examine their query plan, what locks they have and examine table and index statistics of the relevant tables. Full info and screenshots can be found here.

>Cloning/copying postgres databases


createdb newdatabase -T olddatabase

always forget this and end up dumping the old database and loading it into the new one. It takes a lot of time that way. This way is quicker.