How to install PostgreSQL in Debian

PostgreSQL is an object-relational database management system.

Server

Installation in Debian is straight forward:

apt-get install postgresql

The installation adds a linux administrative user account postgres. You will need to set password (consider take a look how to generate a strong password here):

passwd postgres

There is also user postgres in database. The passwords for both should be different. Let change securely password for database user postgres (you will need this password to connect to database):

psql01:~# su - postgres
psql01:~$ psql 
psql (9.1.3)
Type "help" for help.

postgres=# \password 
Enter new password: 
Enter it again: 
postgres=# \q

Server Network Access

The installation configures the server to be available for local connections only. If you need this server to be accessible from other computers in your network follow these:

• Ensure the server connection settings (file/etc/postgresql/9.1/main/postgresql.conf):

  # - Connection Settings -
  listen_addresses = '*'
  

• Allow incoming network connections (file /etc/postgresql/9.1/main/pg_hba.conf):

  # Allow remote connections to any database, 
  # for any user from 192.168.10.0/24 network.
  host  all   all  192.168.10.0/24     md5
  

• Restart server so your changes take place:

  /etc/init.d/postgresql restart
  

At this point you should get your PostgreSQL server installation finished.

Client

pgAdmin III is a database design and management application for use with PostgreSQL (graphical tool). Let install it:

apt-get install pgadmin3

Try connect to the server you installed with user postgres and password set for database user.

How to Renew Certificate with OpenSSL

SSL certificates are valid for certain period of time, usually 365 days. If you are using self signed certificates at some point of time you will need renew them, otherwise services that utilize them "unexpectedly" stop working. That actually greatly depends on client configuration, so if client demand valid server certificate it will not proceed any further.

Suppose your certificate private key (original request) is in file my-key.pem and signed certificate in my-cert.pem.

Validate Certificate

Validate certificate by issuing the following command:

openssl verify my-cert.pem

Here is a sample output of checking valid cerificate:

my-cert.pem: OK

Expired:

my-cert.pem: ...
error 10 at 0 depth lookup:certificate has expired
OK

If verification of certificate shows it expired, you need renew it.

Renew Certificate

Renewal of expired certificate consists of two steps: revoke old one, sign certificate request.

1. Revoke expired certificate (you will be asked for Certificate Authority password):

   ca1:~/ca# openssl ca -revoke my-cert.pem 
   Using configuration from /usr/lib/ssl/openssl.cnf
   Enter pass phrase for ./demoCA/private/cakey.pem:
   Revoking Certificate EFDAF4493BC3D5BB.
   Data Base Updated
   

2. Rename you certificate key (request) file to newreq.pem.

   ca1:~/ca# mv my-key.pem newreq.pem
   ca1:~/ca# /usr/lib/ssl/misc/CA.sh -sign
   ...
   Signed certificate is in newcert.pem
   

At this point renewed certificate is in newcert.pem.

Troubleshooting

If you get error like this one below:

failed to update database
TXT_DB error number 2

You must revoke previous certificate from CA database.

Python Imaging Library Compiled

The compiled Python Imaging Library (version 1.1.7 for python 2.4-2.7 i386 and python 2.7 x86_64) can be found here. See the other post how to get it compiled in Debian.

How to Install Python Imaging Library in Debian

If you need install Python Imaging Library into virtual environment under Debian you will need set location of several libraries (libfreetype, libjpeg). First of all let setup pre-requirements so we can compile PIL.

apt-get -y install build-essential python-dev \
  python-virtualenv libbz2-dev zlib1g-dev \
  libfreetype6-dev libjpeg8-dev

The script below simplify PIL configuration/setup procedure.

# Download PIL and uzip it
wget http://effbot.org/downloads/Imaging-1.1.7.tar.gz
tar xzf Imaging-1.1.7.tar.gz

# create virtual environment
virtualenv env

ARCH=i386
#ARCH=x86_64

cd Imaging-1.1.7
# Set location of libjpeg8-dev
sed -e "s,JPEG_ROOT = None,JPEG_ROOT = '/usr/lib/$ARCH-linux-gnu',g" \
  setup.py > /tmp/x && mv /tmp/x setup.py

# Set location of zlib1g-dev
sed -e "s,ZLIB_ROOT = None,ZLIB_ROOT = '/usr/lib/$ARCH-linux-gnu',g" \
  setup.py > /tmp/x && mv /tmp/x setup.py

# Set location of libfreetype6-dev
sed -e "s,FREETYPE_ROOT = None,FREETYPE_ROOT = '/usr/lib/$ARCH-linux-gnu',g" \
  setup.py > /tmp/x && mv /tmp/x setup.py

# Proceed with installation into virtual environment
../env/bin/python setup.py install

The virtual environment is ready to be used with projects dependent on PIL, e.g. captcha, etc.

You can download pre-compiled version of PIL per this post.

How to Revert Broken Package in Debian

You just made upgrade of your Debian testing box and noticed something went wrong, some daemon not starting and you have no idea what to do until the bug will be fixed. Fortunately, you are able to revert broken package in Debian. Here we are going revert broken bind9 package version 1:9.8.1.dfsg.P1-2 and replace it with last known to work.

1. We need to find last known to work version of the broken package. Take a look at /var/log/apt/history.log. You should be able to find information about the package failed to install/configure:

   Upgrade: ..., bind9:i386 (9.7.3.dfsg-1+b1, 
   9.8.1.dfsg.P1-2)...
   

   So here version 9.7.3.dfsg-1+b1 is replaced by 9.8.1.dfsg.P1-2. Thus we found last working version.
2. Since we know version (9.7.3.dfsg-1+b1) of the bind9 package we can install it from snapshot.debian.org. Go to that site and search for your package. You will get a list of various versions available. Follow link for with version that you found previously. You will get a list of various options, including source, architecture specific files, etc.

   bind9_9.7.3.dfsg-1+b1_i386.deb
   Seen in debian on 2011-04-20 22:16:02 in 
   /pool/main/b/bind9.
   

   Follow link /pool/main/b/bind9. In my case it was:

   http://snapshot.debian.org/archive/debian/20110420T221602Z/pool/main/b/bind9/
   

3. Add snapshot url to file /etc/apt/sources.list:

   deb http://snapshot.debian.org/archive/debian/20110420T221602Z testing main
   

4. Update your apt repository with the following command:

   apt-get -o Acquire::Check-Valid-Until=false update
   

5. Have a look at updated package information:

   apt-cache showpkg bind9
   

   You should be able to see something like this:

   Versions: 
   1:9.8.1.dfsg.P1-2 ...
   1:9.7.3.dfsg-1 ...
   

   1:9.7.3.dfsg-1 is the version we need.
6. Remove broken package and related dependencies:

   apt-get remove bind9
   apt-get autoremove
   

7. Install version we need:

   apt-get install bind9=1:9.7.3.dfsg-1
   

   Since it complains:

   The following packages have unmet dependencies:
    bind9 : Depends: bind9utils (= 1:9.7.3.dfsg-1) but 1:9.8.1.dfsg.P1-2 is to be installed
   

   ... let add that one dependency for bind9utils as well.

   apt-get install bind9=1:9.7.3.dfsg-1 bind9utils=1:9.7.3.dfsg-1
   

At this point you have reverted broken bind9 package to last known to be working.

Package pinning

We will use apt pinning feature to prevent packages from being upgraded. Just create a file /etc/apt/preferences.d/bind9 and add the following:

Package: bind9                                                                 
Pin: version 1:9.7.3*                                                          
Pin-Priority: 1001                                                             
                                                                               
Package: bind9utils                                                            
Pin: version 1:9.7.3*                                                          
Pin-Priority: 1001

The next time you run upgrade these two packages remain untouched.