Upgrading Postgresql from 9.0.4 to 9.2.3 using archlinux
Since archlinux moved to systemd I needed to upgrade postgresql to take advantage of the native systemd startup script. I am using davical, which depends on postgresql, which in turn stopped working after the systemd upgrade due to systemd restricting access to the socket in /tmp. Below you will find a description of how I performed the upgrade, this should work for upgrading all postgresql versions >=7.0 to the most current version. All lines starting with “#” mean you need to be root, “$” means non-root.
First, make sure postgresql is running.
Next, dump the database:
# pg_dumpall --user=postgres -h 127.0.0.1 > postgresqldb.backup.pg_dumpall
Note that I am using the -h 127.0.0.1 option to be able to connect, since local socket connections don’t work anymore.
Then, stop postgresql:
# rc.d stop postgresql
Next, rename the original database data directory for safety:
# mv /var/lib/postgres/data-9.0.4/ /var/lib/postgres/data-9.0.4.backup.renamed
Now you can upgrade postgresql:
# pacman -Sy postgresql postgresql-libs
You need to switch to the postgres user now:
# sudo su postgres -
Create a clean postgresql database as the postgres user:
$ initdb -D /var/lib/postgres/data
If you have a customized pg_hba.conf file, now is the moment to merge your file from the backup with the freshly created file when you created a the clean database:
$ vimdiff /var/lib/postgres/data-9.0.4.backup.renamed/pg_hba.conf /var/lib/postgres/data/pg_hba.conf
I needed to add the following into my file to make davical work:
#for davical according to archwiki local davical davical_app trust local davical davical_dba trust
Now you can start the new postgresql server using systemd under root:
# systemctl start postgresql
Time to import your database dump, note you need to be the postgres user again:
$ psql -d postgres -f /var/lib/postgres/postgresqldb.backup.pg_dumpall
As a final step you will need to restart apache to reload its state and recognize the new socket presented by systemd:
# systemctl restart httpd Congratulations, all done!