Thread: BUG #13911: pg_upgrade from 8.4 to 9.5 broken

The following bug has been logged on the website:

Bug reference:      13911
Logged by:          Jerome Paul
Email address:      jpaul@aconex.com
PostgreSQL version: 9.5.0
Operating system:   Ubuntu 14.04
Description:

Hi,

I've been attempting to run "pg_upgrade" to move data from Postgresql 8.4.22
to 9.5

The command I've been running is:

/usr/lib/postgresql/9.5/bin/pg_upgrade --old-datadir
'/etc/postgresql/8.4/main' --new-datadir '/etc/postgresql/9.5/main'
--old-bindir '/usr/lib/postgresql/8.4/bin' --new-bindir
'/usr/lib/postgresql/9.5/bin'


This results in the following error within "pg_upgrade_internal.log"

-----------------------------------------------------------------
  pg_upgrade run on Thu Feb  4 13:30:13 2016
-----------------------------------------------------------------

Finding the real data directory for the old cluster         Could not get
data directory using "/usr/lib/postgresql/8.4/bin/postgres" -D
"/etc/postgresql/8.4/main" -C data_directory: No such file or directory


It looks like the -C option was only added to the postgres command in 9.2.
The documentation for pg_upgrade states: "pg_upgrade supports upgrades from
8.4.X and later to the current major release of PostgreSQL, including
snapshot and alpha releases."


Is the upgrade script broken or the documentation incorrect?

sincerely
Jerome

On 2/3/2016 7:06 PM, jpaul@aconex.com wrote:
> /usr/lib/postgresql/9.5/bin/pg_upgrade --old-datadir
> '/etc/postgresql/8.4/main' --new-datadir '/etc/postgresql/9.5/main'

I don't use Ubuntu, but the data directories shouldn't be in /etc, thats
where the powergresql.conf files are located in their wrappered
systems.  The data directories should be in /var/something.



--
john r pierce, recycling bits in santa cruz

On 04/02/16 05:21, John R Pierce wrote:
> On 2/3/2016 7:06 PM, jpaul@aconex.com wrote:
>> /usr/lib/postgresql/9.5/bin/pg_upgrade --old-datadir
>> '/etc/postgresql/8.4/main' --new-datadir '/etc/postgresql/9.5/main'
>
> I don't use Ubuntu, but the data directories shouldn't be in /etc, thats
> where the powergresql.conf files are located in their wrappered
> systems.  The data directories should be in /var/something.
>
>
>
Correct
The default schema is
/var/lib/postgresql/<major_version>/<cluster_name>/

the --new-datadir should be

/var/lib/postgresql/9.5/main

--
Federico Campoli
Brandwatch | Senior Database Administrator
federico@brandwatch.com |

New York  | San Francisco |  *Brighton*  |  Berlin  |  Stuttgart

John R Pierce wrote:

> I don't use Ubuntu, but the data directories shouldn't be in /etc, thats=
=20
> where the powergresql.conf files are located in their wrappered=20
> systems.  The data directories should be in /var/something.

Yes, the data directory is presumably /var/lib/postgresql/8.4/main,
or the pg_lsclusters command can be used to clear up any doubt.

Besides, the pg_upgrade process is wrapped too, so what's
recommended is not to use pg_upgrade directly from
/usr/lib/postgresql/X.Y/... (just like any other command from
these directories), but rather invoke:

# pg_upgradecluster -v 9.5 8.4 main

which would let the wrapper deal with data directories and other
options by itself.

See=20
http://manpages.ubuntu.com/manpages/trusty/man8/pg_upgradecluster.8.html

If a main/9.5 cluster already exists beforehand, it should be
dropped first with
# pg_dropcluster --stop 9.5 main

Best regards,
--=20
Daniel V=E9rit=E9
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

jpaul@aconex.com writes:
> I've been attempting to run "pg_upgrade" to move data from Postgresql 8.4.22
> to 9.5

> The command I've been running is:
> /usr/lib/postgresql/9.5/bin/pg_upgrade --old-datadir
> '/etc/postgresql/8.4/main' --new-datadir '/etc/postgresql/9.5/main'
> --old-bindir '/usr/lib/postgresql/8.4/bin' --new-bindir
> '/usr/lib/postgresql/9.5/bin'

> This results in the following error within "pg_upgrade_internal.log"
> Finding the real data directory for the old cluster         Could not get
> data directory using "/usr/lib/postgresql/8.4/bin/postgres" -D
> "/etc/postgresql/8.4/main" -C data_directory: No such file or directory

Before 9.2, pg_upgrade never worked at all for setups with config-only
data directories, since it couldn't figure out where the real data
directory was.  Now it does work, but only if the old postmaster supports -C.

            regards, tom lane

On 2/4/2016 10:03 AM, Tom Lane wrote:
> Before 9.2, pg_upgrade never worked at all for setups with config-only
> data directories, since it couldn't figure out where the real data
> directory was.  Now it does work, but only if the old postmaster supports -C.

would a (hacky) workaround be to copy the 8.4 postgresql.conf and
pg_hba.conf to the actual data directory, then point to that as the source ?



--
john r pierce, recycling bits in santa cruz

John R Pierce <pierce@hogranch.com> writes:
> On 2/4/2016 10:03 AM, Tom Lane wrote:
>> Before 9.2, pg_upgrade never worked at all for setups with config-only
>> data directories, since it couldn't figure out where the real data
>> directory was.  Now it does work, but only if the old postmaster supports -C.

> would a (hacky) workaround be to copy the 8.4 postgresql.conf and
> pg_hba.conf to the actual data directory, then point to that as the source ?

Yeah, something like that is the solution.  You need to make it look like
a traditional data directory setup.

            regards, tom lane