Category Archives: PostgreSQL

PostgreSQL 8.2: Recovering from a Corrupted Database

Today I encountered my second database corruption or crash on my development machine. The problem is that my IDE drive sucks, an old IBM 75GXP. For those of you who don’t remember, these drives were notorious for having all sorts of problems when they were released. Most of the problems revolved around dying after six months. Regardless, I’m still using this thing for another month.

You can tell if your database became corrupted by restarting the postmaster or typing:

sudo /etc/init.d/posrgresql-8.2 restart

The result of the command will look similar to the following:

2007-11-26 13:14:48 CST LOG:  server process (PID 16312) was terminated by signal 11
2007-11-26 13:14:48 CST LOG: terminating any other active server processes
2007-11-26 13:14:48 CST LOG: all server processes terminated; reinitializing
2007-11-26 13:14:48 CST LOG: database system was interrupted at 2007-11-26 13:14:47 CST
2007-11-26 13:14:48 CST LOG: checkpoint record is at 0/965E4130
2007-11-26 13:14:48 CST LOG: redo record is at 0/965E4130; undo record is at 0/0; shutdown TRUE
2007-11-26 13:14:48 CST LOG: next transaction ID: 0/73629; next OID: 10952256
2007-11-26 13:14:48 CST LOG: next MultiXactId: 1; next MultiXactOffset: 0
2007-11-26 13:14:48 CST LOG: database system was not properly shut down; automatic recovery in progress
2007-11-26 13:14:48 CST LOG: record with zero length at 0/965E4178

Before initiating any recovery options, I recommend backing up your $PGDATA directory. The following directions will attempt to reset the transaction log, and you may loose the latest changes to your databases. The first option for recovering from a crash is to open your ‘/etc/postgresql/8.2/main/postgresql.conf’ file and turn on the following option:

...
zero_damaged_pages = true

After saving the changes, type the following to restart postgresql:

sudo /etc/init.d/postgresql-8.2 restart

If you still see error messages, you may need to restart the entire computer in order to get postgresql to start.

After restarting postgresql, reopen the ‘/etc/postgresql/8.2/main/postgresql.conf’ file and comment out the zero_damaged_pages option. The line should look similar to the following:

#zero_damaged_pages = true

After reading a few posts, it seems that IDE drives in general should not be used when working with databases because of corruption issues. If you are using an IDE drive, I recommend adding the following options to the ‘/etc/postgresql/8.2/main/postgresql.conf’ file.

fsync = on				# turns forced synchronization on or off
wal_sync_method = fsync

The problem with turning on fsync is that it slows down the performance of Postgresql, however, it should prevent further corruption issues with your database.

You can find more information about these configuration options in the Postgresql 8.2 documentation.

Reference:

Killing processes and PostgreSQL Queries

I’ve been working on issues related to processes and PostgreSQL these past few days. Minor as they were, I think these are common issues that aren’t documented very well. I recently had a problem canceling queries issued from PGAdmin. I decided to cancel the queries by looking up the process id and canceling the process thats running the query.

You can look up the process id, or the ‘pid’ column, by executing the following query in psql or PGAdmin:

select * from pg_stat_activity;

This query will return a list of all processes currently being run by your server. After finding the query you want to cancel, go to the BASH prompt and type:

sudo kill pid_number

Another problem that I ran into the other day was figuring out which processes a java server was running on. I did this by querying processes by name or browsing through all of the server’s processes. To query processes by a name, I typed:

ps -o user,pid,ppid,command -ax | grep java

If you still can’t find the process, you can browse all processes by typing:

procinfo

New Article

PostgreSQL and its History

As I’ve played around with PostgreSQL, I’ve found it to be quite a capable database. I’ve begun to wonder how common PostgreSQL is used in production systems. After a little bit of research, apparently quite a few organizations use this database.

PostgreSQL was originally developed at the University of California at Berkley by Profesor M. Stonebraker in 1986. Postgres, as the database was originally called, was later commercialized by the company Informix and renamed Illustra. Informix was later purchased by IBM for 1 billion dollars. In 1996, Postgres95 – the open source version of Postgres – was created. This was later renamed to PostgreSQL 6.0.

After over a decade of development, I think its safe to say that this database is mature and commands a large install base due to corporate support by established companies (Sun, Fujitsu, Afilias, etc.) and the large development community behind it. I just find it weird that I don’t see a whole lot of PostgreSQL fanboys running around telling everyone and their mothers about this software. This is how obscure OSS projects gets well known – kind of like how MySQL and Linux got popular.

If you would like more information on the topic, please check the postgresql.org’s history page. I’ve also put up an article on PostgreSQL pertaining to domains.

New Articles

Database vs Database and the Linux PATH variable

Last night I was doing some research on PostgreSQL and got as far as configuring the server, getting a Schema up, and creating my first functions. In terms of capability between SQL Server 2005 and PostgreSQL, so far PostgreSQL seems to offer a comparable alternative, at least with the requirements for our system. A lot of features that were recently introduced into SQL Server 2005 such as custom data types, language support outside of T-SQL, and support for new SQL syntax has been around in Postgre for awhile, but that doesn’t mean PostgreSQL is better. As usual, you have to look at your requirements for your system before deciding which database servers out there are a better match for your project.

For our project, the two crucial requirements that counted out SQL Server 2005 were the ability to run on Linux and low licensing fees. The licensing costs of SQL Server 2005 does not scale well. Even with volume license deals, the licensing costs still encourage companies to scale up their hardware to save on licensing fees. Part of our strategy involves scaling out over cheap redundant boxes, its the exact same strategy that most successful tech startups have chosen because its more economical in the beginning of the system’s or company’s life.

Last night while I was configuring Postgre on my local Ubuntu machine, I ran into a small problem regarding editing the ‘system’ PATH in the bash shell. The PATH variable in Bash is similar to the PATH entry in the Window’s System Variables. This variable allows a user to execute files in any location located in the directories referenced by the PATH variable. Like the Windows counter part, there is a global file that contains PATH entries for all users and a local file for the current user. The global file is located at ‘/etc/environment’. You may have to login as ‘su’ in order to access the file. The local file is located at ‘~/.bashrc’, which is actually in the user’s home directory.

To add an entry to the PATH variable for the local user, just add the following lines

First – Type: gedit ~/.bashrc

PATH=$PATH:/path_to_program_is/bin
export PATH

To add an entry to the PATH variable for the environment, just add your entry with a colon and the new entry to the end of the PATH definition.

First – Type:  sudo gedit  /etc/environment

PATH="XXXXX:XXXXX/XXXX:XXXX:XXX/XXXX/XXXX:XXXXX:/path_to_program_is/bin"

For more information, consult this thread in the ubuntuforums.