PostgreSQL isn’t the most friendly when it comes to creating access to various databases or tables within those databases however with a couple easy shell scripts it becomes much easier. I previously wrote this article regarding a shell script created to provide SELECT access, which is essentially read only access, to every table located within a specified database. The problem now might be if you want to remove a user from having access to that database you will be required to manually remove each access privilege for the role you created. Below is the reverse of the initial shell script which will remove SELECT access from each table within a specified database.

Read the rest of this entry »

When attempting to create a new database on a PostgreSQL server running walmgr, which is part of SkyTools by Skype Developers, for real time failover I received an error. Initially I was a bit baffled by the error however I remembered after racking my brain for a little bit that you must stop walmgr’s syncdaemon when adding a new database. Below are more details about the error as well as how to stop walmgr’s syncdaemon and then create the new database.

Read the rest of this entry »

As noted in a previous article I have been working on a couple new Linux servers with a minimal install of CentOS on them. The /var, /usr, and / directories each were configured with 2GB of space within a logical volume group that has 1TB of space available. I first expanded the /var and /usr directory from 2GB to 20GB and then expanded the root, or /, directory from 2GB to 30GB. Once all three of these directories were expanded I next needed to create a new logical volume group and a partition to hold PostgreSQL data. Use the information below to create a new logical volume, format it with the ext3 file system, mount it, and configure it to be mounted automatically upon the next boot of the server.

Read the rest of this entry »

When connecting to a PostgreSQL database using pgAdmin you may receive an error letting you know that the server instrumentation is not installed. This is a warning to let you know that you can install the adminpack contribution which will provide more functionality to pgAdmin including the ability to modify the pg_hba.conf and postgresql.conf configuration files. Below is information on the pop up warning you may receive when logging into a Postgres database using pgAdmin III as well as how to install the adminpack to not receive the error any longer.

Read the rest of this entry »

Earlier a client with a fairly complex network setup had a power outage in one of their data centers in Dallas, TX. The power outage caused three servers and a firewall to lose power causing all sorts of other issues on the servers themselves. One of the primary issues was getting the PostgreSQL cluster back in 100% operational.

Read the rest of this entry »