About replacing PostgreSQL instance

Hi Oscar Calderon,

Replacing instance is not good approach in major version (from PG9.1 to
9.3).

Yes,your approach is correct it should be upgrade,since it is production
need to take some extra care.

On Thu, May 16, 2013 at 11:49 PM, Oscar Calderon <
ocalderon@solucionesaplicativas.com> wrote:

Hi to all, i wanna ask you a piece of advice. The company where i work is
bringing maintenance service of PostgreSQL to another company, and
currently they have installed PostgreSQL 9.1.1, and they want to move to
9.3 version when it will come out. So, because the difference of versions,
and because it was installed by compiling it (using source code), and
because the 9.1.1 installation is in a different directory than the
default, they decided to replace 9.1.1 version with 9.3 (no upgrade, but
replace it).

Currently, they only have one database in production of 2.2 GB with some
procedures and triggers. So, my plan to execute this database installation
is the next:

1. Install PostgreSQL 9.3 from postgresql repository (
yum.postgresql.org) with a different port to avoid interrupt the
production PostgreSQL instance operation
2. Tune the database parameters in postgresql.conf, also create the
same rules in pg_hba as the production instance, configure log and so on.
3. At the end of the operations day, create a backup of the production
database and then restore it into the new instance.

It seems you are shutting down Application here. If not please shutdown

the application or you need to keep production databases in read only mode
on PG9.1 in-order to protect your database from users write queries and
maintain consistency at the time of upgrade.

The below command will protect your PG9.1 production database.
postgres=# ALTER DATABASE PRDB SET default_transaction_read_only to on;
ALTER DATABASE

A.Take the global dump(pg_dumpall) of PG91 and restore in PG9.3.
B.Take the pg_dump of required production databases from PG9.1 and restore
in PG9.3.