Repair mongodb on a bad shutdown

On my Debian box i run, and that should work also on Ubuntu :

sudo rm /var/lib/mongodb/mongod.lock
sudo mongod –dbpath /var/lib/mongodb –repair
sudo chown mongodb /var/lib/mongodb/*
sudo service mongodb start

Converting MySQL to PostgreSQL

It has been a long time that i did not write a post into my blog, well it’s time, working with bacula, i got a need to upgrade my database from MySQL to PostgreSQL. If you have to do so, well you can do it simpley using command line and tools given with these database applications.

First of all dump your tables with

$ mysqldump –compatible=postgresql databasename > outputfile.sql

but even then you will have to change quote escaping:

$ sed “s/\\\’/\’\'/g” outputfile.sql

You also have to manually modify the data types when /int.* unsigned/ “bigint” when /bigint/ “bigint” when “bit(1)” “boolean” when “tinyint(1)” “boolean” when /tinyint/ “tinyint” when /int/ “integer” when /varchar/ “varchar” when /char/ “char” when /(float|decimal)/ “decimal” when /double/ “double precision”

After you convert your tables, import them the same way you were used to in MySQL, that is

$ psql -h server -d databasename -U username -W < data.sql

Upgrading PostgreSQL

To upgrade a PostgreSQL installation start by dumping all the existing databases. You should do this before stopping the current database server
$ PGUSER=postgres /usr/bin/pg_dumpall > dump.sql
$ sudo /etc/init/d/postgresql stop
$ sudo mv /usr/local/pgsql /usr/local/pgsql.old

Build and install your new version. Be sure to create a PGDATA directory that is owned by the postgres user. Initialize the PGDATA directory with initdb
$ sudo mkdir /usr/local/pgsql/data
$ sudo chown postgres:postgres /usr/local/pgsql/data
$ sudo -u postgres /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data

Once you have created your new database, start the new database server and populate the new database with the data you dumped from your old system:

$ sudo -u postgres /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data
$ sudo -u postgres /usr/local/pgsql/bin/psql -d postgres -f dump.sql

How to find the PostgreSQL database size

PostgreSQL is a powerful, open source relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness.
If you want to find the size of a database, in our example the sqview database, please tape:

sqview-# SELECT pg_database_size(‘sqview’);
pg_database_size
——————
111415944
(1 ligne)

The result in octal, for a pretty print we will use the pg_size_pretty function which converts the size in bytes to human understandable format.
sqview-# SELECT pg_size_pretty(pg_database_size(‘sqview’));
pg_size_pretty
—————-
106 MB
(1 ligne)

Well if we need to get the size of all databases
sqview-# SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database;
datname | size
———–+———
postgres | 3537 kB
template1 | 3537 kB
template0 | 3480 kB
freedom | 25 MB
anakeen | 6081 kB
sqview | 106 MB
rt | 8201 kB
(7 lignes)

PostgreSQL can also give the size of a table ‘users’ like this

sqview-# SELECT pg_size_pretty(pg_relation_size(‘users’));
pg_size_pretty
—————-
64 kB
(1 ligne)

This value exclude indexes and some auxiliary data.
If you want to include them use pg_total_relation_size instead of pg_relation_size as shown below.
sqview-# SELECT pg_size_pretty(pg_total_relation_size(‘users’));
pg_size_pretty
—————-
152 kB
(1 ligne)

to find the largest table in the postgreSQL database.
sqview-# SELECT relname, relpages FROM pg_class ORDER BY relpages DESC limit 1;
relname | relpages
———+———-
sqview | 9666
(1 ligne)

Install Drizzle on Debian lenny

Drizzle is a Free Software/Open Source database management system (DBMS)
that was forked from version 6.0 of the MySQL DBMS. Drizzle is a re-thought and re-worked version of the MySQL kernel designed specifically for high-performance, high-concurrency environments.
Like MySQL, Drizzle has a client/server architecture and uses SQL as its primary command language.
Drizzle is distributed under version 2 of the GNU General Public License.

Drizzle is written in the C++ programming language, and stores its string data in an UTF-8 format.
It is being developed for modern Unix-like operating systems, including Linux,
Mac OS X, and Solaris – in general, any OS that conforms to POSIX.

I decided to try to get drizzle up on my Debian Lenny, normally you can install it with apt but
Drizzle is not yet present in the Debian repository.
Well, to install it please follow this steps, first make sure that all build dependencies are met:

protobuf : Protocol Buffers – Google’s data interchange format
Protocol Buffers are a way of encoding structured data in an efficient yet extensible format.

libdrizzle : is the client and protocol library that is required to build Drizzle.

libuuid : generates and parses 128-bit universally unique ids (UUIDs).
See RFC 4122 for more information.

libreadline5 : The GNU readline library aids in the consistency of user interface
across discrete programs that need to provide a command line interface.

libpcre3 : This is a library of functions to support regular expressions whose syntax
and semantics are as close as possible to those of the Perl 5 language.

You can get them with aptitude like:
$ sudo aptitude install libprotobuf-dev protobuf-compiler uuid-dev libdrizzle0 libdrizzle-dev libreadline5-dev libpcre3-dev

Download the last source code archives for the Drizzle database server:
$ wget http://launchpad.net/drizzle/trunk/aloha/+download/drizzle-2009.07.1088.tar.gz
$ tar xzf drizzle-2009.07.1088.tar.gz
$ cd drizzle-2009.07.1088
$ ./configure –with-libprotobuf-prefix=/usr/local/drizzle
$ make
$ sudo make install

Create a non-privileged group and user for the database server, as below:
$ sudo groupadd drizzle
$ sudo useradd -g drizzle drizzle

Create data directory and set the right permission on it:
$ sudo mkdir /usr/local/drizzle/data
$ sudo chown -R drizzle:drizzle /usr/local/drizzle

Then, Start up the Drizzle server by this command
$ sudo ./usr/local/drizzle/sbin/drizzled –user=drizzle –datadir=/usr/local/drizzle/data/ &

Run drizzle client, which is very similar to the MySQL client.
Start it up and access the Drizzle server as below:

$ ./usr/local/drizzle/bin/drizzle
Welcome to the Drizzle client.. Commands end with ; or \g.
Your Drizzle connection id is 2
Server version: 2009.07.1088 Source distribution (trunk)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

drizzle> \h
List of all Drizzle commands:
Note that all text commands must be first on line and end with ‘;’
? (\?) Synonym for `help’.
clear (\c) Clear command.
connect (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter. NOTE: Takes the rest of the line as new delimiter.
ego (\G) Send command to drizzle server, display result vertically.
exit (\q) Exit drizzle. Same as quit.
go (\g) Send command to drizzle server.
help (\h) Display this help.
nopager (\n) Disable pager, print to stdout.
notee (\t) Don’t write into outfile.
pager (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print (\p) Print current command.
prompt (\R) Change your drizzle prompt.
quit (\q) Quit drizzle.
rehash (\#) Rebuild completion hash.
source (\.) Execute an SQL script file. Takes a file name as an argument.
status (\s) Get status information from the server.
tee (\T) Set outfile [to_outfile]. Append everything into given outfile.
use (\u) Use another database. Takes database name as argument.
warnings (\W) Show warnings after every statement.
nowarning (\w) Don’t show warnings after every statement.

drizzle> select version();
+————–+
| version() |
+————–+
| 2009.07.1088 |
+————–+
1 row in set (0 sec)

drizzle> exit;

have fun!