When you wish to upgrade the version of PostgreSQL running in an EC2 instance, do so by creating a new instance with the new version of PostgreSQL and installing the database cluster in the new instance, following the procedure described below.
There are two major types of PostgreSQL version upgrade: major and minor. In a major upgrade, you will create an instance with 2 new volumes, archive the database from the existing server, restore it on the new server, and then shift responsibility for the database service from the old to the new server. In a minor upgrade, you will create an instance with only 1 new volume and move the other, database, volume from the old server to the new server. A major upgrade is generally an upgrade in which the first or second segment of the 3-segment identifier of the PostgreSQL version changes.
To begin upgrading PostgreSQL on AWS, create an AMI from the root volume of the current instance, limited to the root volume (remove the database volume from the AMI during definition).
If you want to assure the integrity of the file system, leave unchanged the default rebooting behavior. This will cause AWS to stop the instance before making an AMI and then reboot it. The AWS documentation does not expressly state that this leaves PostgreSQL running, the database volume attached and mounted, and the IP address associated, so check on all these after the reboot is completed, which typically takes “a few minutes”.
Launch a new instance from the AMI with the desired instance type. If this is a major upgrade, include in the new instance a new EBS volume large enough for the database in addition to the root volume. Launching the instance does not start the old version of PostgreSQL.
Add the new instance’s URL (called “public DNS”) to the first line of the “.ssh/config” file in your home directory on your client host. This is necessary for SSH connections to the new server instance.
Connect by SSH as the “ubuntu” user to the new instance, using its URL, since it doesn’t yet have an IP address.
If the new version of PostgreSQL is not yet available from the official Ubuntu repository but instead from a Personal Package Archive (PPA) such as PostgreSQL backports for stable Ubuntu releases, add that PPA to the instance’s list of APT repositories with a command such as sudo add-apt-repository ppa:pitti/postgresql.
Download and install the new version of PostgreSQL with commands such as sudo apt-get update, sudo apt-get upgrade, and sudo apt-get --fix-missing install postgresql-9.2 postgresql-plperl-9.2.
While still connected as the “ubuntu” user to the new instance, update the environment configuration for the new version of PostgreSQL by changing “9.1” to “9.2” in the first lines of /etc/environment and /var/lib/postgresql/.profile.
Stop the running processes of the just-installed new version of PostgreSQL, and delete its default cluster, with a command such as sudo pg_dropcluster --stop 9.2 main. This leaves the /etc/postgresql and /var/log/postgresql directories empty. Configuration files remain in /var/lib/postgresql, which is the “postgres” user’s home directory. The new version’s executable files in /usr/lib/postgresql remain in existence.
While still connected to the new instance as the “ubuntu” user, verify that the /mnt/db directory exists. If it doesn’t, create and configure it with the commands sudo mkdir /mnt/db, sudo chown ubuntu:postgres /mnt/db, and chmod 770 /mnt/db.
Make the (empty new) database volume that directory’s storage device with the command sudo mount /dev/xvdf /mnt/db. The database cluster will be installed inside this directory.
Before capturing the state of the database in a dump file, prevent the public from making further changes in the data with the PanLem interface. Informational queries will continue to be permitted.
While still connected to the new instance as the “ubuntu” user, acquire a dump of the database from the old instance with the command pg_dumpall -h db.panlex.net -U superuser -f pgsql/temp/yyyymmdd-pgdump.sql, replacing “superuser” with your own cluster user name or the name of another login-capable database superuser. PostgreSQL on the old server will demand your password. Executing this command will take between 15 minutes and an hour.
While still connected to the new instance as the “ubuntu” user, navigate to the /mnt/db directory and create a directory for the new database cluster in it with the commands mkdir data, sudo chown postgres data, and sudo chmod 700 data.
Create a new database cluster in that directory, with the command sudo -iu postgres initdb -D /mnt/db/data -E UTF8 --no-locale --text-search-config=simple.
While still connected to the new instance as the “ubuntu” user, launch a shell as the “postgres” user with the command sudo -iu postgres bash.
Navigate to the “ubuntu” user’s PostgreSQL directory with the command cd /home/ubuntu/pgsql.
Install the custom main configuration with the commands mv /mnt/db/data/postgresql.conf custom/postgresql-std.conf and cp -p custom/postgresql-ec2x.conf /mnt/db/data/postgresql.conf, replacing “postgresql-ec2x.conf” with the name of the instance-appropriate main configuration file.
Install the custom connection configuration with the commands mv /mnt/db/data/pg_hba.conf custom/pg_hba-std.conf and cp -p custom/pg_hba-ec2.conf /mnt/db/data/pg_hba.conf.
Set the maximum size of a shared memory segment in bytes (SHMMAX) as specified in the main configuration file that you just installed. To do this, first edit /etc/sysctl.d/30-postgresql-shm.conf to make this amount the value of “kernel.shmmax”. Then execute the command sudo sysctl -w kernel-shmmax=n, replacing “n” with that amount.
Start the new version of PostgreSQL with the command sudo -iu postgres pg_ctl -D /mnt/db/data start.
While still connected to the new instance as the “ubuntu” user, restore the database with the command sudo -iu postgres psql -f /home/ubuntu/pgsql/temp/yyyymmdd-pgdump.sql postgres. The restoration will take about an hour.
On the EC2 management console, disassociate the server’s IP address from the old server and associate it with the new server.
Restore the ability of the public to modify the database with the PanLem interface.
On your workstation, delete the server’s line from the .ssh/known_hosts file in your home directory. Otherwise, SSH will report a change in the IP address’s machinery and refuse to run.
On the EC2 management console, remove termination protection from the instance on which the old server is running, and terminate that instance.
While still connected to the new instance as the “ubuntu” user, delete the cluster dump with the command rm /home/pgsql/temp/temp/yyyymmdd-pgdump.sql or, to reclaim space while keeping it, compress it with the command bzip2 /home/pgsql/temp/temp/yyyymmdd-pgdump.sql.
