Thread: pg_upgrade from 8.3 to 9.1 and Flag --disable-integer-datetimes
Hello all, we want to upgrade our database from Postgres 8.3.23 to 9.1.12 using pg_upgrade. The documentation on pg_upgrade (http://www.postgresql.org/docs/9.1/static/pgupgrade.html) states the following: "Also, the default datetime storage format changed to integer after PostgreSQL 8.3. pg_upgrade will check that the datetime storage format used by the old and new clusters match. Make sure your new cluster is built with the configure flag --disable-integer-datetimes." We have a SLES 11 system. We installed Postgres 9.1.12 using Yast. We assume that our installation was built WITHOUT --disable-integer-datetimes. The pg_upgrade is running without any complaints. Since we assume that our 9.1-server is built without disable-integer-datetimes, we expect pg_upgrade to fail or giving some kind of notice. What is the expected behavior of pg_upgrade in the case that 9.1-server is not built with with disable-integer-datetimes? How do we determine, whether or not a server is built with disable-integer-datetimes? Best regards Meik Weißbach
On 05/27/2014 07:29 AM, Meik Weißbach wrote: > Hello all, > > we want to upgrade our database from Postgres 8.3.23 to 9.1.12 using > pg_upgrade. The documentation on pg_upgrade > (http://www.postgresql.org/docs/9.1/static/pgupgrade.html) states the > following: > > "Also, the default datetime storage format changed to integer after > PostgreSQL 8.3. pg_upgrade will check that the datetime storage format > used by the old and new clusters match. Make sure your new cluster is > built with the configure flag --disable-integer-datetimes." > > We have a SLES 11 system. We installed Postgres 9.1.12 using Yast. We > assume that our installation was built WITHOUT --disable-integer-datetimes. > > The pg_upgrade is running without any complaints. Since we assume that > our 9.1-server is built without disable-integer-datetimes, we expect > pg_upgrade to fail or giving some kind of notice. > > What is the expected behavior of pg_upgrade in the case that 9.1-server > is not built with with disable-integer-datetimes? > > How do we determine, whether or not a server is built with > disable-integer-datetimes? As the postgres user do something like: pg_controldata /usr/local/pgsql/data/ where the path is $DATA/ for your Postgres install In the output should be: Date/time type storage: 64-bit integers > > Best regards > > Meik Weißbach > > -- Adrian Klaver adrian.klaver@aklaver.com
On Tue, May 27, 2014 at 04:29:19PM +0200, Meik Weißbach wrote: > Hello all, > > we want to upgrade our database from Postgres 8.3.23 to 9.1.12 using > pg_upgrade. The documentation on pg_upgrade > (http://www.postgresql.org/docs/9.1/static/pgupgrade.html) states > the following: > > "Also, the default datetime storage format changed to integer after > PostgreSQL 8.3. pg_upgrade will check that the datetime storage > format used by the old and new clusters match. Make sure your new > cluster is built with the configure flag > --disable-integer-datetimes." > > We have a SLES 11 system. We installed Postgres 9.1.12 using Yast. > We assume that our installation was built WITHOUT > --disable-integer-datetimes. > > The pg_upgrade is running without any complaints. Since we assume > that our 9.1-server is built without disable-integer-datetimes, we > expect pg_upgrade to fail or giving some kind of notice. > > What is the expected behavior of pg_upgrade in the case that > 9.1-server is not built with with disable-integer-datetimes? > > How do we determine, whether or not a server is built with > disable-integer-datetimes? pg_upgrade --check will definitely complain about a timestamp storage mismatch. Odds are your packager built 8.3 with integer timestamps. Run pg_controldata on the 8.3 cluster and look at: Date/time type storage: 64-bit integers This shows integer timestamps. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +
Bruce Momjian <bruce@momjian.us> wrote: > On Tue, May 27, 2014 at 04:29:19PM +0200, Meik Weißbach wrote: >> How do we determine, whether or not a server is built with >> disable-integer-datetimes? > > pg_upgrade --check will definitely complain about a timestamp storage > mismatch. Odds are your packager built 8.3 with integer timestamps. > Run pg_controldata on the 8.3 cluster and look at: > > Date/time type storage: 64-bit integers > > This shows integer timestamps. All good information; but just for the sake of completeness, if you can get a database connection, you can execute: SHOW integer_datetimes; -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company