Tag Archives: PostgreSQL

PostgreSQL Replication to a Warm-Standby Using WAL Files

Posted on by
Reply

THEORY

Like a good relational database, PostgreSQL maintains a set of transactional log file known as write-ahead-logs (WAL) in the pg_xlog directory.  These logs are written to for every change in the database files and are used to recovery from a crash condition.  If you crash, replay all the WAL files since the last backup and you will be back in business right at the point of failure.

Well, if you have this capability, what about keeping a warm-standby system and feeding it all the WAL files.  If you teach it how to continuously process the incoming write ahead logs from the live system you will have a system ready to go at a moments notice.  When you read about this setup in other places on line the primary server is known as ‘master’ and the secondary the ‘slave’.

NOTE BENE: Both your primary and your secondary need to be running the same major version of the postgreSQL database. Continue reading →

Installing Spacewalk on CentOS 6

Posted on by
8

DISCUSSION
I’m installing Spacewalk (1.5) along with postgreSQL (8.4) on a fresh installation of CentOS 6.  This would be pretty straight forward except the CentOS team did not release updates for the selinux-policy and that leaves you with the error below.  I’m guessing that if youa re reading this you encountered this error while running your yum command to install spacewalk.  If so this should guide you through things:

Finished Dependency Resolution
Error: Package: spacewalk-common-1.5.1-1.el6.noarch (spacewalk)
Requires: selinux-policy-base >= 3.7.19-93
Installed: selinux-policy-targeted-3.7.19-54.el6_0.5.noarch (@updates)
selinux-policy-base = 3.7.19-54.el6_0.5
Available: selinux-policy-minimum-3.7.19-54.el6.noarch (base)
selinux-policy-base = 3.7.19-54.el6
Available: selinux-policy-minimum-3.7.19-54.el6_0.3.noarch (updates)
selinux-policy-base = 3.7.19-54.el6_0.3
Available: selinux-policy-minimum-3.7.19-54.el6_0.5.noarch (updates)
selinux-policy-base = 3.7.19-54.el6_0.5
Available: selinux-policy-mls-3.7.19-54.el6.noarch (base)
selinux-policy-base = 3.7.19-54.el6
Available: selinux-policy-mls-3.7.19-54.el6_0.3.noarch (updates)
selinux-policy-base = 3.7.19-54.el6_0.3
Available: selinux-policy-mls-3.7.19-54.el6_0.5.noarch (updates)
selinux-policy-base = 3.7.19-54.el6_0.5
Available: selinux-policy-targeted-3.7.19-54.el6.noarch (base)
selinux-policy-base = 3.7.19-54.el6
Available: selinux-policy-targeted-3.7.19-54.el6_0.3.noarch (updates)
selinux-policy-base = 3.7.19-54.el6_0.3
You could try using --skip-broken to work around the problem
You could try running: rpm -Va --nofiles --nodigest

HOW DID I GET THE ERROR
Frankly, the instructions for install spacewalk on fedorahosted.org are awesome.  I followed those for the basic install along with the notes for setting up postgresql.  They are repeated here pulling out all of the notes for other OSes and leaving just CentOS 6.  Finally, I found the source code while reading a post from a nice fellow here.

PROCEDURE
Configure you prerequisites, I’m using 120GB for disk space and 4GB of RAM for the buildout, then I’ll reduce the RAM to 2GB for operation. I have a virtual environment so this is very simple for me.  I installed CentOS-6 as a minimal install setting up a DNS record for the system name.  After that I just start up upgrading the system.  The rest is just the facts.

yum upgrade -y
# Install some repos that make the install much easier
 rpm -Uvh http://spacewalk.redhat.com/yum/1.5/RHEL/6/x86_64/spacewalk-repo-1.5-1.el6.noarch.rpm
 rpm -Uvh http://download.fedora.redhat.com/pub/epel/6/i386/epel-release-6-5.noarch.rpm
 rpm -ihv http://spacewalk.redhat.com/yum/1.5-client/RHEL/6/x86_64/spacewalk-client-repo-1.5-1.el6.noarch.rpm
# Get your GPG keys for redhat
 yum install -y wget
 wget -O /etc/pki/rpm-gpg/RPM-GPG-KEY-redhat-release http://www.redhat.com/security/37017186.txt
 rpm --import /etc/pki/rpm-gpg/RPM-GPG-KEY-redhat-release
# Setup to use available java packages (paste into the command line)
 cat > /etc/yum.repos.d/jpackage-generic.repo << EOF
 [jpackage-generic]
 name=JPackage generic
 baseurl=http://mirrors.dotsrc.org/jpackage/5.0/generic/free/
 enabled=1
 gpgcheck=1
 gpgkey=http://www.jpackage.org/jpackage.asc
 EOF
# Work on PostgreSQL 8.4 or better
 yum install -y 'postgresql-server > 8.4'
 chkconfig postgresql on
 service postgresql initdb
 service postgresql start
# Setup the PostgreSQL DB
 su - postgres -c 'PGPASSWORD=spacepw; createdb spaceschema ; createlang plpgsql spaceschema ; yes $PGPASSWORD | createuser -P -sDR spaceuser'
#Setup the security in the file below adding the lines show _before_ the lines that mention 'all' users
 vi /var/lib/pgsql/data/pg_hba.conf
# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
 local   spaceschema     spaceuser                       md5
 host    spaceschema     spaceuser       127.0.0.1/8     md5
 host    spaceschema     spaceuser       ::1/128         md5
# Reload the server
 service postgresql reload
# Test the connection (exit with \q)
 PGPASSWORD=spacepw psql -a -U spaceuser spaceschema
 PGPASSWORD=spacepw psql -h localhost -a -U spaceuser spaceschema
# Make sure you don't have any conflicting packages
 yum remove cobbler-web
 vi /etc/yum.conf and make sure you exclude=cobbler-web
# Build the RPM that is missing from CentOS
 # This means we need some tools for the build environment and
 # specifically, some tools for the RPM (policycoreutils-python)
 yum install rpm-build redhat-rpm-config make gcc policycoreutils-python
wget ftp://ftp.redhat.com/pub/redhat/linux/enterprise/6Server/en/os/SRPMS/selinux-policy-3.7.19-93.el6.src.rpm
 rpm -ivv selinux-policy-3.7.19-93.el6.src.rpm
 cd ~/rpmbuild
 rpmbuild --rebuild ../selinux-policy-3.7.19-93.el6.src.rpm
 yum localinstall --nogpgcheck /root/rpmbuild/RPMS/noarch/*.rpm
# Install Spacewalk
 yum install spacewalk-postgresql

I have some other notes about setting up Spacewalk on this site.  I found that more complicated than working through the above steps and hope they may be useful to you.

 

NOTE BENE:

If you upgrade cobbler above version 2.0 and you are running Spacewalk 1.5 (and probably 1.6) then you can expect it to break.  You should probably disable the epel-testing repo and exclude cobbler from the peel repo “exclude=cobbler*, jabberpy”

About Jay Farschman - Jay currently works as a Senior Systems Administrator for an asset management company in Colorado where he works with companies that produce hardware, telecommunications software and financial services.  Jay previously owned a consulting company and provided training and consulting services for three Fortune 500 companies and numerous small businesses where he leveraged Linux to provided exceptional value.

Upgrading PostgreSQL from 8.2 to 8.4

Posted on by
Reply

This article takes you through upgrading from PostgreSQL 8.2 to 8.4.  If you are familiar with upgrading databases like MySQL then you probably think this is a trivial task.  It’s not trivial, but it’s not all that complicated either.  You just need a bit of understanding of how it all works and to think it through before you lose any data.

MY SETUP
A couple of years ago I installed PostgreSQL 8.2 using a yum repository at http://yum.pgsqlrpms.org/8.2/ .   This allows you to get all the updates to the 8.2 line for patching purposes, but does not introduce any new features.  My notes on this install process are available here.

THE PROBLEM
So, if you have a repo. clearly marked 8.2  why not just change your baser to show 8.4 and then yum clean all and yum update?  I tried this and it all seemed to work very well until I tested the database.  Apparently there are some fundamental data structure changes.

THEORY
In theory you can use a tool called pg_migrator (http://pg-migrator.projects.postgresql.org/)  to do an ‘in-place’ incremental upgrade, but if you need to skip a version then you may have an issue with the migrator and it my understanding that you can end up with a problem with the migrator simply because it’s not a mature application yet..  So do a dump and restore.

DUMP AND RESTORE
As a Mac user I’m a fan of the tool pgadmin III which allows you to manage databases in a more visual manner.  This is best for me because I’m not a full-time dedicated DBA.  Seeing things in a graphical interface makes all the difference.  You may want to grab a copy, especially if you are a casual PostgreSQL user.  However, all the commands below are designed to be used on the command line.

Continue reading →