Archive for the ‘Postgresql’ Category.

Backing up PostgreSQL databases nightly on FreeBSD

I didn’t write this post, a blogger named Keith did, but I am definitely going to link to his work on his blog.

Backing up PostgreSQL databases nightly on FreeBSD

He did a good job editing this script to backup postgresql on FreeBSD. While he does say he got some of it from another site he found on using Google…

…Using Google I found a generic script, after some custom tweaks for FreeBSD I had the script I wanted.

…so some of the work should be attributed elsewhere. I want to thank him for the work he did. This was something that was on my to do list and now it is pretty much done for me.

Here is the script he posted.

# Location of binaries
# Location of the backup logfile.
# Location to place backups.
touch $logfile
timeslot=`date +%H-%M`
databases=`$bin/psql -h localhost -d $database -U $username -q -c "\l" | sed -n 4,/\eof/p | grep -v rows\) | awk {'print $1'}`

for i in $databases; do
        echo "Backup and Vacuum complete at $timeinfo for time slot $timeslot on database: $i " >> $logfile
        $bin/vacuumdb -z -h localhost -U $username $i >/dev/null 2>&1
        while [ $j -ge 0 ]
                temp0=`expr $j - 1`
                j=`expr $j - 1`
                if [ $temp0 -lt 0 ]
                if [ -f $backup_dir/postgresql-$i-database.gz$suffix0 ]
                        echo "Renaming postgresql-$i-database.gz$suffix0 to postgresql-$i-database.gz$suffix1"
                        mv $backup_dir/postgresql-$i-database.gz$suffix0 $backup_dir/postgresql-$i-database.gz$suffix1
        if [ $i != 'template0' ]
                $bin/pg_dump -U $username $i -h | gzip > "$backup_dir/postgresql-$i-database.gz"

Here is what I like about his script:

  1. Variables are used at the top of the script that are easily modifiable.
  2. It backs up all databases without having to list the databases, so you don’t have to update the list with every new database.
  3. It does compress using gzip the databases, so uses as little space as possible.

Here are some ideas for future enhancements to the script

  1. The gzip compression level as a variable up top. The default gzip compression level is 6 and 9 could make the file significantly smaller with large databases, while 1 would be faster for machines with weaker processors.
  2. Database to exclude from the backup.  I like the idea to get them all by default, but sometimes you have a test database you just don’t care about and you don’t want a nightly backup of it.
  3. I don’t see the password in the script, and I am not sure how it is authenticating without it, maybe a password feature needs to be added.

Drupal, Joomla, WordPress, SilverStripe, or other, which Content Management System (CMS) is best for a BSD user?

Choosing a Content Management System

Ok, so as today, August 26, 2010, my blog is hosted by  It will be this way for some time.

However, I am probably going to eventually have my own server hosted somewhere. Probably with my brother’s company, Fusion Networks.

So I will probable build a FreeBSD server that will host my site.  At this point, I think I will stop using WordPress and use a different Content Management System.

Content Management Systems Lists

So I went into this having done reasearch before, so I knew what I was getting into.  There are plenty of different Content Management Systems and unfortunately there is no way I could find time to test them all.

Wikipedia had a great list of Content Management Systems.

Ok, now that  you have seen the very large list of Content Management Systems, you realize that I have to narrow my list down without really testing them all.

Narrowing down the list

First, let me document what I would like and see if which fit into the list.  These are not in order of priority, and some are important and some are not.

I want it to

  1. use PHP
  2. have a large community
  3. use a BSD License
  4. use Postgresql (as it is BSD licensed and MySQL is not)
  5. import my existing WordPress blog
  6. have great feature sets
  7. have common plugins
  8. support multiple Blogs with one install
  9. manages website as well as blog
  10. manages multiple sites
  11. have a descent editor
  12. be well documenated


They have their lists separated  into tables by programming language.  The PHP table has the most options and since I for sure want to use PHP, it is the only table I will focus on.  These tables even lists the license.

However, I did look at the other tables, just in case one was mostly PHP-based but didn’t show up in the PHP list.  For the full PHP list, go here:

Large community

So the big three Content Management Systems seems to be these.  I don’t have any data to back this up.  I think this is just because these three seem to be more mentioned when doing Google searches.

  • Drupal
  • Joomla
  • WordPress

BSD Licensed

However, none of those are BSD Licensed, which I prefer. I am not opposed to GPL or LGPL, but I feel more comfortable with BSD licenses software.

The Content Management Systems that are PHP-based and use the BSD License are these:

Had one of the Big 3 been BSD licensed, my decision would have probably been easy.  However, obviously I am going to have to test some of the three BSD licensed Content Management Systems.

Supports Postgresql

Of the six Content Management Systems listed above, the following support Postgresql.

  • Drupal
  • Serendipity
  • SilverStripe

Can import my WordPress blog

It appears that they all can do this.

I am worried about links, however.  My blog entries often link to each other.  I question whether they do it perfectly.  For example, a link to one of my WordPress blogs might be this:

So the link has this format:


So if I go with something other than WordPress, then after importing my blogs, are all the links going to be broken?

Making the Decision

So here is the rating, after a very small overview of the different CMS web sites, and before verifying their marketing claims.

I am giving 0 to 3 points for each feature. (This is subject to change over time, should I want to come back after the decision and report on something).

Features Worpress Drupal Joomla Serendipity SilverStripe Pimcore
1. PHP-Based 3 3 3 3 3 3
2. Large community 3 3 3 1 1 1
3.BSD License 0 0 0 3 3 3
4. Postgresql Support 0 3 0 3 3 0
5. Imports WordPress 3 1 ? 1 1 ?
6. Great Feature set 3 3 3 1 3 ?
7. Has common plugins 3 3 3 3 2 ?
8. Multiple Blogs 3 3 3 3 2 ?
9. Manages Website as well as blog 3 3 3 0 3 ?
10. Multiple Sites 2 3 ? 0 3 ?
11. Editor 3 3 ? 2 3 ?
12. Documenation 3 3 ? 1 3 ?
Score: 29 31 ? 21 30 ?

Here is what I am going to do:

I am going to build a BSD, Apache, PHP, Postgresl virtual machine and test Drupal and Serendipity to see how the import goes.

Perhaps I will have a follow-up some time because:

  1. I have to test these different CMSes
  2. I am sure there are a dozen other items that should be scored 1-3 that I haven’t thought of.


I started out looking at Serendipity first.

There was a FreeBSD port.

The install was painless.


Their web site didn’t seem very good, which is a big turn off for a company that is a CMS.

The links to their Forum on their web site and other pages were broken.  I did find that the forum does exist, so the links are just broken.

I tried to sign up for the Forum to report the broken links.  I registered, but was supposed to be sent a confirmation email, which I never received, even after re-requesting it, so I have never reported the broken links.  They are still broken a day later. Also, there appear to only be a handful of people on their forums at any given time. Both suggest they have a small community.

I couldn’t figure out how to design my home page. It seemed the blog was my home page.  I couldn’t figure out how to move my blog entries to a different page.


If you want one blog and nothing else, this would work for you.

I need more. I stopped trying and have dropped Serendipity from the list.


I looked at SilverStripe second and I way more pleased with its feature set.


Though I had some install problems, I found solutions on their forums.

There are a number of theme available for download.

The default web site had some common pages premade:

  • Home
  • About Us
  • Contact Us
  • Page not found

They had both Site Management and Blog management.

There are many extensions: modules, themes, widgets.

Modules include a Blog Modules and Sub-site module, so I could manage multiple sites and multiple blogs.

It is simple to download and install modules.

The user interface is simple and easy to use.

The documentation is sufficient.


I had install/setup problems (that were resolved) by the Forum.  Supposedly they only occur with Postgresql.

Not a lot is included in the default install.

The community is smaller than I would like.

The documentation is only sufficient.


I really like the SilverStripe interface.

It had almost everything I am looking for.  It is BSD Licensed.  It supports Postgresql.

It is a Content Management System first, and has modules to add to it.  There a blog module, a multi-site module, which really interested me.

However, while it has a module to import a WordPress xml, I have yet to get it to work.  That is something I can live with, but I would prefer it to work.



It has many features, and any feature it lacks is pretty much filled by an add-on or widget.

The documentations seems thorough.

The community looks to be large.


I had to install a different version of wordpress to support multiple blogs.

It is GPL instead of BSD Licensed.

The admin site seemed rather overwhelming. There were almost too many options.


If this were BSD licensed, it would be my pick.  It seems great.



It is popular and familiar.  The most common platform of the blogging world.

It has many features, and any feature it lacks is pretty much filled by an add-on or widget.

Migrating from my existing WordPress blog is flawless.

The community is huge.

The documentation is a plenty.


It doesn’t support Postgresql.

It is GPL instead of BSD Licensed.


I almost feel like apologizing to Joomla.  I had two BSD servers, one with FreeBSD, Apache, PHP, MySQL where I installed WordPress a year ago for testing.  I have another brand new server with FreeBSD, Apache, PHP, and Postgresql where I tested all the postgresql capable CRMs.  However, I installed Joomla to the wordpress server and it just didn’t work.  The page was blank.  I actually think there was a php error, not a Joomla error.

Unfortunately, Joomla doesn’t support postgresql, it isn’t BSD licensed.  I did research and it appears like it would score high with WordPress, Drupal, and Silverstripe, but I can’t really see how it would be any better than Drupal, or Silverstripe so I really had to cut it loose.  Lets be honest.  As a BSD users, it was beaten out by Drupal and Silverstripe for the same reason I am looking to replace WordPress.  It doesn’t support Postgresql.


There are two other Content Management Systems that I feel are worth mentioning.

Django is also a CMS that is BSD licensed and supports Postgresql. However, it runs on Python, a language which I have occasionally touched but never really developed in.  If you know Python, this might be the choice for you.

Bricolage is also a CMS that is BSD licensed and supports Postgresql. However, it runs on Perl, which I have written a few things with, but not as much as PHP.  If you know Python, this might be the choice for you.

My Decision

I know, you all want to know which I chose to use.

Drumb roll please….

I have chosen…

But don’t expect my blog to move to Silverstripe tomorrow.  It move slowly with things like this.  It could take months or get delayed for a year.

Anyone who might choose a CMS differently would be completely happy with any of the others they evaluate and feel meets their needs.  I am not saying I chose the best.  I am saying I chose the best for my needs.  Hopefully, you know your needs and this will help you choose the best Content Management System for your needs.

Installing and Using Postgresql on FreeBSD

Postrgesql is an excellent alternate to MySQL.

It is BSD Licensed instead of GPL, which is especially more attractive if you need to distribute the database software.  In fact, if you are reselling a product, and paying a license fee to MySQL you probably should save your company the money and move to Postgresql.  LANDesk, the company I work for, had just such an experience with our Management Gateway device.

Setting up FreeBSD

Ok, so I already have a post on this here:

How do I install FreeBSD?

Also, make sure to download ports.

What are the first commands I run after installing FreeBSD

Installing Postgresql on FreeBSD

As always, installing software on FreeBSD is simple using the ports system.

cd /usr/ports/databases/postgresql84-server
make WITH_OPTIMIZED_CFLAGS=true BATCH=yes install clean

Post-installation Setup

There a few post-installation steps.

Initialize the database

# /usr/local/etc/rc.d/postgresql initdb

Or starting with FreeBSD 8.1, you can now run this command:

# service postgresql initdb

Make any changes to the postgresql.conf

The postgresql.conf is located in /usr/local/pgsql/data.

Open the file and read through it and make any desired changes.

Common Changes

Here are two common changes.

Enabled remote connections

If the database is to be accessed by the network, then you should at least uncomment the setting #listen_addresses = 'localhost' and change it to listen_addresses = '*'.

Changing the default TCP Port

Uncomment the setting #port = 5432 and change the port number to the desired value.

Configure password authentication

  1. Change to the /usr/local/etc/pgsql/data directory.
  2. Edit the pg_hba.conf and change the default authentication method to something more secure, such as md5.
    # TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD# "local" is for Unix domain socket connections only
    local   all         all                               md5
    # IPv4 local connections:
    host    all         all          md5
    # IPv6 local connections:
    host    all         all         ::1/128               md5

Configure the postgresql server service to start on reboot

Add the string postgresql_enable="YES" to /etc/rc.conf.

# echo ‘postgresql_enable=”YES”‘ >> /etc/rc.conf

Creating a Database

To create a database, su to pgsql and run createdb.

su pgsql
createdb MyDBName

Note: Similarly, use dropdb to drop a database.

Creating a User or Role

To create a user, su to pgsql and run createuser.

su pgsql
createuser -P

Enter name of role to add: MyUserOrRoleName
Enter password for new role:
Enter it again:
Shall the new role be a superuser? (y/n) y

If you are not using password authentication, then you can exclude the -P.

Using psql client

A simple way to connect to postgresql is using the shell-based client, psql.


To connect, use the following command.

$ psql MyDBName


You are now at the psql prompt.

Connecting as a different user

To connect, use the following command.

$ psql MyDBName MyUserOrRoleName


You are now at the psql prompt.

Changing a a User or Role Password

To change a password, use the following syntax.

MyDBName-# alter role pgsql with password ‘pw’;

Show Databases

To show database, simply type \l, (which is the lowercase letter L not the number 1) which is short hand in psql for this query:
SELECT datname FROM pg_database;

MyDBName-# \l

Show Tables

To show tables in the current database, simply type \d, which is short hand in psql for this query:
SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

MyDBName-# \d

Show Tables

To show columns of a table in the current database, simply type \d table, which is short hand in psql for this query:
SELECT column_name FROM information_schema.columns WHERE table_name ='table';

MyDBName-# \d MyTableName

Creating a Table

To create a table, use the following syntax:

FirstName text,
LastName text,
DateOfBirth date

Inserting data into a Table

To insert data into a table, use the following syntax:

UserId serial,
FirstName text,
LastName text,
DateOfBirth date

Ok, from here you should be able to start figuring everything out on your own.


Copyright ® – Linking to this page is allowed without permission and as many as ten lines of this page can be used along with this link. Any other use of this page is allowed only by permission of