PostgreSQL : migration d’un cluster 9.1 vers 9.3

Boris HUISGEN January 12, 2014

administration base de données postgresql

Installation des dépôts

root@zabbix:~# wget --quiet -O - http://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc | apt-key add -
root@zabbix:~# add-apt-repository 'deb http://apt.postgresql.org/pub/repos/apt/ wheezy-pgdg main'

Mise à jour du serveur

root@zabbix:~# apt-get update && apt-get install postgresql-9.3

Migration du cluster existant

root@zabbix:/var/lib/postgresql# pg_upgradecluster 9.1 main

Disabling connections to the old cluster during upgrade...
Restarting old cluster with restricted connections...
Creating new cluster 9.3/main ...
config /etc/postgresql/9.3/main
data /var/lib/postgresql/9.3/main
locale en_US.UTF-8
port 5433
Disabling connections to the new cluster during upgrade...
Roles, databases, schemas, ACLs...
Fixing hardcoded library paths for stored procedures...
Upgrading database zabbix...
Analyzing database zabbix...
Fixing hardcoded library paths for stored procedures...
Upgrading database postgres...
Analyzing database postgres...
Fixing hardcoded library paths for stored procedures...
Upgrading database template1...
Analyzing database template1...
Re-enabling connections to the old cluster...
Re-enabling connections to the new cluster...
Copying old configuration files...
Copying old start.conf...
Copying old pg_ctl.conf...
Copying old server.crt...
Copying old server.key...
Stopping target cluster...
Stopping old cluster...
Disabling automatic startup of old cluster...
Configuring old cluster to use a different port (5433)...
Starting target cluster on the original port...
Success. Please check that the upgraded cluster works. If it does,
you can remove the old cluster with

pg_dropcluster 9.1 main

Suppression de l’ancien cluster

root@zabbix:~# pg_dropcluster 9.1 main

Désinstallation des anciens packages

root@zabbix:~# apt-get --purge remove postgresql-9.1 postgresql-client-9.1

See also

PostgreSQL : créer un utilisateur avec droits en lecture seule
Read more
PostgreSQL : afficher les requêtes SQL en cours d’exécution
Read more
FreeBSD : jail PostgreSQL
Read more