OS: Ubuntu 12.04
PostgreSQL: above v9.0
To upgrade Postgresql 9.1 to 9.6:
1. Install Postgresql 9.6
sudo add-apt-repository "deb http://apt.postgresql.org/pub/repos/apt/ precise-pgdg main" wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - sudo apt-get update sudo apt-get install postgresql-9.6
2. If pg_createcluster fails, do it manually
sudo pg_createcluster 9.6 main --start
3. If doing a on-line in-place cluster upgrade, do the following:
sudo pg_dropcluster --stop 9.6 main sudo service postgresql stop sudo pg_upgradecluster 9.1 main
4. If doing a backup/restore cluster upgrade, do the following
sudo su postgresql psql -p 5433 -c "create user apoint superuser createdb password 'apoint';" exit pg_dump -h 127.0.0.1 -p 5432 -U apoint -W -F custom -f db_dump.backup database_name pg_restore -h 127.0.0.1 -p 5433 -U apoint -W -C -v db_dump.backup
5. Stop old version from starting up on boot
sudo service postgresql stop 9.1 sudo nano /etc/postgresql/9.1/main/start.conf # change auto to disable
6. Change connection settings of the new version at pg_hba.conf
7. Change the default binary files used at /etc/postgresql-common/user_clusters as follows:
* * {new_version} main *
If you encounter the SHMMAX or SHMMNI errors when trying to start the new cluster, do the following:
1. Open /etc/sysctl.conf and increase the kernel.shmmax and kernel.shmall values. shmmax is a memory value in bytes, and shmall is the number of pages (4096 bytes/page), thus shmall = shmmax / 4096.
2. Apply the new settings with sudo sysctl -p
You can check the kernel memory settings with the following commands:
Segments: sudo ipcs -m
Process: sudo ipcs -pm
Limit: sudo ipcs -lm
Parameters (shmmax, shmall, shmmni, etc): cat /proc/sys/kernel/xxx