1

"PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit" is working in Debian 6.0 Squeeze It contains 12 databases accessed by 100 users from internet. md5 password authentication method is used. Total size of all database compressed backup files in custom format is 10 GB.

I need time range type, so I'm looking for a way to upgrade to 9.3 or 9.4

I installed Postgers 9.3 as parallel in same server.

How to move databases, user names and passwords from 9.1 to 9.3 ?

I found instructions in

http://no0p.github.io/postgresql/2014/03/29/upgrading-pg-ubuntu.html

will this work in Debian Squeeze also ?

This uses pg_upgrade which requires access to 9.1 database during upgrade.

How to upgrade so that old database read access is possible during upgrade ?

Is is possible to create pipe using old psql to new psql or other idea ?

Database access can swithed off at nights for some time is this helps.

  • 1
    Have a look at Debian's pg_upgradecluster, it also uses pg_upgrade but is even easier to use, as it is the onlycommand you will have to execute. It will do the upgrade, the config, everything. The 9.1 cluster will be down during the upgrade though, so you wont have read only access. – zhenech Nov 2 '14 at 8:40
  • I found that pg_dumpall -p 5432 | psql -d postgres -p 5433 works. It takes less than 30 minutes , 12 databases, total compressed backup files files size is 10 GB, is this possible or is there something missing? I have already create one database with different name in new cluster, will this keep new database? Is it OK to use this method since it doesn't require to take database offline? – Andrus Nov 2 '14 at 16:33
  • Your dump (from pg_dumpall) contains only a point-in-time view of the database. As you did not turn of write-access, the old database could have changed during the upgrade. pg_upgradecluster -m upgrade ---link would be faster, yet give a short downtime. Not sure what you mean about the new database, though. – zhenech Nov 2 '14 at 20:35
  • pg_dumpall is running in late evening, it can assumed that data is not changed by users in this time. Destination cluster in 5433 port has new database with different name containing data. Will this method keep this new database in destination cluster, will it add only existing databases from source cluster ? – Andrus Nov 4 '14 at 6:46
  • You should not assume when it comes to data(bases). pg_upgradecluster is designed to work on clusters, as such it will create a new one for the upgrade and wont update data in you existing 9.3 one. – zhenech Nov 4 '14 at 7:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.