Dias atrás precisei migrar um PGSQL 9.2 para um 9.3. Mais detalhes da evolução da versão podem ser visualizados em http://www.postgresql.org/about/news/1481/.
Para meu desespero o servidor era um Ubuntu, coisa medonha, Deus meu! Mas em três comandos realizei o procedimento. Primeiro tu precisas dos binários do 9.3 instalados, não vou mostrar aqui como fazer isto, mas use o repositório do PGDG que é muito bom.
Binários instalados e contrib também, pois lá é que está o binário do pg_upgrade, basta fazer o seguinte:
1 - Parar ambas as instâncias, 9.2 e 9.3;
2 - Executar o comando: sudo -H -u postgres /usr/lib/postgresql/9.3/bin/pg_upgrade -b /usr/lib/postgresql/9.2/bin -B /usr/lib/postgresql/9.3/bin -d /var/lib/postgresql/9.2/main -D /var/lib/postgresql/9.3/main -o ' -c config_file=/etc/postgresql/9.2/main/postgresql.conf' -O ' -c config_file=/etc/postgresql/9.3/main/postgresql.conf'
Detalhes podem ser observados no help do comando:
/usr/lib/postgresql/9.3/bin/pg_upgrade -h
pg_upgrade upgrades a PostgreSQL cluster to a different major version.
Usage:
pg_upgrade [OPTION]...
Options:
-b, --old-bindir=OLDBINDIR old cluster executable directory
-B, --new-bindir=NEWBINDIR new cluster executable directory
-c, --check check clusters only, don't change any data
-d, --old-datadir=OLDDATADIR old cluster data directory
-D, --new-datadir=NEWDATADIR new cluster data directory
-j, --jobs number of simultaneous processes or threads to use
-k, --link link instead of copying files to new cluster
-o, --old-options=OPTIONS old cluster options to pass to the server
-O, --new-options=OPTIONS new cluster options to pass to the server
-p, --old-port=OLDPORT old cluster port number (default 50432)
-P, --new-port=NEWPORT new cluster port number (default 50432)
-r, --retain retain SQL and log files after success
-u, --user=NAME cluster superuser (default "root")
-v, --verbose enable verbose internal logging
-V, --version display version information, then exit
-?, -h, --help show this help, then exit
Before running pg_upgrade you must:
create a new database cluster (using the new version of initdb)
shutdown the postmaster servicing the old cluster
shutdown the postmaster servicing the new cluster
When you run pg_upgrade, you must provide the following information:
the data directory for the old cluster (-d OLDDATADIR)
the data directory for the new cluster (-D NEWDATADIR)
the "bin" directory for the old version (-b OLDBINDIR)
the "bin" directory for the new version (-B NEWBINDIR)
For example:
pg_upgrade -d oldCluster/data -D newCluster/data -b oldCluster/bin -B newCluster/bin
or
$ export PGDATAOLD=oldCluster/data
$ export PGDATANEW=newCluster/data
$ export PGBINOLD=oldCluster/bin
$ export PGBINNEW=newCluster/bin
$ pg_upgrade
Report bugs to
Log da execução:
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
Checking database user is a superuser ok
Checking for prepared transactions ok
Checking for reg* system OID user data types ok
Checking for contrib/isn with bigint-passing mismatch ok
Creating dump of global objects ok
Creating dump of database schemas
ok
Checking for presence of required libraries ok
Checking database user is a superuser ok
Checking for prepared transactions ok
If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.
Performing Upgrade
------------------
Analyzing all rows in the new cluster ok
Freezing all rows on the new cluster ok
Deleting files from new pg_clog ok
Copying old pg_clog to new server ok
Setting next transaction ID for new cluster ok
Setting oldest multixact ID on new cluster ok
Resetting WAL archives ok
Setting frozenxid counters in new cluster ok
Restoring global objects in the new cluster ok
Adding support functions to new cluster ok
Restoring database schemas in the new cluster
ok
Removing support functions from new cluster ok
Copying user relation files
ok
Setting next OID for new cluster ok
Sync data directory to disk ok
Creating script to analyze new cluster ok
Creating script to delete old cluster ok
Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
analyze_new_cluster.sh
Running this script will delete the old cluster's data files:
delete_old_cluster.sh
analyze_new_cluster.sh : Atualiza estatísticas de todas as bases
delete_old_cluster.sh : Cuidado, esse cara remove o pg_data do 9.2.
---
This script will generate minimal optimizer statistics rapidly
so your system is usable, and then gather statistics twice more
with increasing accuracy. When it is done, your system will
have the default level of optimizer statistics.
If you have used ALTER TABLE to modify the statistics target for
any tables, you might want to remove them and restore them after
running this script because they will delay fast statistics generation.
If you would like default statistics as quickly as possible, cancel
this script and run:
vacuumdb --all --analyze-only
Generating minimal optimizer statistics (1 target)
------------------------------
vacuumdb: limpando banco de dados "postgres"
vacuumdb: limpando banco de dados "template1"
The server is now available with minimal optimizer statistics.
Query performance will be optimal once this script completes.
Generating medium optimizer statistics (10 targets)
------------------------------
vacuumdb: limpando banco de dados "postgres"
vacuumdb: limpando banco de dados "template1"
Generating default (full) optimizer statistics (100 targets?)
------------------------------
vacuumdb: limpando banco de dados "postgres"
vacuumdb: limpando banco de dados "template1"
Done
./delete_old_cluster.sh
4 - Valide teu 'pg_hba' e 'postgresql.conf' da nova instância e era isso.
