I found a way to do this, but I'm not sure if it's the proper/recommended way. My steps are below:
1. Save the existing configuration
Run sudo vim /var/lib/pgsql9/data/postgresql.conf
and sudo vim /var/lib/pgsql9/data/pg_hba.conf
and copy all uncommented settings somewhere. You'll later have to manually set them in the new PostgreSQL version.
2. Install the new PostgreSQL version
Use yum list
to find the correct package names and install them. In my case I needed to sudo yum install postgresql94.x86_64
and sudo yum install postgresql94-server.x86_64
Run postgres --version
, pg_dumpall --version
and psql --version
to ensure that the version has been updated to the new one.
3. Save existing data and shut down the database
Stop any processes that may change the database (e.g. your web app). Run sudo pg_dumpall >db.out
, followed by sudo service postgresql stop
right after that to stop the server and prevent further data changes. Examine the resulting data dump with vim db.out
- it should have all your DB schema and data in plain SQL format. You may wish to back up this file before proceeding.
4. Save the old database directory
sudo mv /var/lib/pgsql9 /var/lib/pgsql9.old
5. Create a new database directory for the new PostgreSQL version
sudo mkdir /var/lib/pgsql9
sudo chown postgres /var/lib/pgsql9
sudo su postgres
In the sudo su
prompt:
initdb -D /var/lib/pgsql9/data
Then press Ctrl-D
6. Copy certificates to the new database directory
If you are using certificates, copy them to the new directory and give them the proper ownership, e.g:
sudo cp /var/lib/pgsql9.old/data/server.key /var/lib/pgsql9/data
sudo cp /var/lib/pgsql9.old/data/server.crt /var/lib/pgsql9/data
sudo chown postgres /var/lib/pgsql9/data/server.crt
sudo chown postgres /var/lib/pgsql9/data/server.key
7. Configure the new database
Use sudo vim /var/lib/pgsql9/data/postgresql.conf
and sudo vim /var/lib/pgsql9/data/pg_hba.conf
to configure the database according to the settings you saved back in step 1.
8. Fix the postgresql service to point to the new version
Run ls -ila /etc/rc.d/init.d
- notice how you have a script for every version (postgresql92
, postgresql94
), but postgresql
itself is linked to the old version. Fix this by running sudo ln -sf /etc/rc.d/init.d/postgresql94 /etc/rc.d/init.d/postgresql
and run the ls
command again to verify the link is correct.
9. Run the service
Run sudo service postgresql start
. If the service fails to start, chances are it's either a permissions error (you forgot a chown
step above) or a configuration syntax error. You can look at logs (/var/lib/pgsql94/pgstartup.log
and the files in /var/lib/pgsql9/data/pg_log
) to look for the error. If the service has started correctly, you can run sudo su postgres
, then psql
to verify your database is running (use \q
to quit and press Ctrl-D to get out of sudo su
).
10. Restore your old data
sudo cp db.out /var/lib/pgsql9/data
sudo chown postgres /var/lib/pgsql9/data/db.out
sudo su postgres
And in that prompt:
psql -d postgres -f /var/lib/pgsql9/data/db.out
You should see a bunch of database commands scroll by. At that point your database is upgraded and running. You can verify by running psql
. Use \list
command to see a list of databases, \connect (database_name)
to connect to your database, \dt
to see a list of tables once connected, or run semicolon-terminated SQL commands. Type \q
to quit.
Once done verifying, press Ctrl-D
to exit sudo su
.
11. Clean up
At this point, once you're sure everything is working, you may remove /var/lib/pgsql9.old
, /var/lib/pgsql9/db.out
, db.out
and any other backups.