Thread: Upgrading old server

Hi All,

We've decided to upgrade our PostgreSQL production servers. First task 
is remove an old v7.14 version. It was supposed to be upgraded to a v8.4 
server. The server was installed, several databases where released here 
but v7.4 was never migrated. The plan is pg_dump this database and psql 
it to existing 8.4 server. After this, we'll pg_upgrade. In order to 
make some tests to be ready for production server I'd like to know what 
would be the best approach: upgrade to v10 or maybe v11? Or start 
upgrading to 9.6 and if everything goes fine migrate to v10/v11?

If is useful: server is CentOS 6.8.

I've installed succesfully a backup of the old v7.14 version in my v8.4 
test server (with some help of psql-admin list), and now that I've 
studied a bit about pg_upgrade-ing I'm going to upgrade installed PG 
version to the one that's the best for this situation.


Thank you all,

Ekaterina

Re: Ekaterina Amez 2019-09-25 <8818b028-bd2d-412e-d4e3-e29c49ffee17@zunibal.com>
> We've decided to upgrade our PostgreSQL production servers. First task is
> remove an old v7.14 version. It was supposed to be upgraded to a v8.4
> server. The server was installed, several databases where released here but
> v7.4 was never migrated. The plan is pg_dump this database and psql it to
> existing 8.4 server. After this, we'll pg_upgrade.

If you doing dump-restore anyway, why not restore into v11 rightaway?

Christoph

On 9/25/19 9:29 AM, Christoph Berg wrote:
> Re: Ekaterina Amez 2019-09-25 <8818b028-bd2d-412e-d4e3-e29c49ffee17@zunibal.com>
>> We've decided to upgrade our PostgreSQL production servers. First task is
>> remove an old v7.14 version. It was supposed to be upgraded to a v8.4
>> server. The server was installed, several databases where released here but
>> v7.4 was never migrated. The plan is pg_dump this database and psql it to
>> existing 8.4 server. After this, we'll pg_upgrade.
> If you doing dump-restore anyway, why not restore into v11 rightaway?

Since it's recommend to run the newer pg_dump on the older database, I've 
got to wonder if v11 pg_dump can read the v7.4 on-disk structures.

-- 
Angular momentum makes the world go 'round.

Ron <ronljohnsonjr@gmail.com> writes:
> On 9/25/19 9:29 AM, Christoph Berg wrote:
>> Re: Ekaterina Amez 2019-09-25 <8818b028-bd2d-412e-d4e3-e29c49ffee17@zunibal.com>
>>> We've decided to upgrade our PostgreSQL production servers. First task is
>>> remove an old v7.14 version. It was supposed to be upgraded to a v8.4
>>> server. The server was installed, several databases where released here but
>>> v7.4 was never migrated. The plan is pg_dump this database and psql it to
>>> existing 8.4 server. After this, we'll pg_upgrade.

>> If you doing dump-restore anyway, why not restore into v11 rightaway?

> Since it's recommend to run the newer pg_dump on the older database, I've
> got to wonder if v11 pg_dump can read the v7.4 on-disk structures.

We dropped support for pre-8.0 source servers in pg_dump sometime
recently, though I forget if v11 is affected by that or not.
You could try just dumping with 7.4's pg_dump and seeing if the
output will load into v11 --- ideally it would, but I'd not be
surprised if there are issues that have to be resolved manually.
Or, if you have 8.4's pg_dump at hand, try using that.

7.4 to 11 is a big jump to be doing in one step.  There's definitely
something to be said for porting to an intermediate release, just to
break down the work into smaller chunks.  But I'd go for halfway between,
which if I counted releases correctly would be about 9.1, not 8.4.

            regards, tom lane

On 9/25/19 9:00 AM, Tom Lane wrote:
> Ron <ronljohnsonjr@gmail.com> writes:
>> On 9/25/19 9:29 AM, Christoph Berg wrote:
>>> Re: Ekaterina Amez 2019-09-25 <8818b028-bd2d-412e-d4e3-e29c49ffee17@zunibal.com>
>>>> We've decided to upgrade our PostgreSQL production servers. First task is
>>>> remove an old v7.14 version. It was supposed to be upgraded to a v8.4
>>>> server. The server was installed, several databases where released here but
>>>> v7.4 was never migrated. The plan is pg_dump this database and psql it to
>>>> existing 8.4 server. After this, we'll pg_upgrade.
> 
>>> If you doing dump-restore anyway, why not restore into v11 rightaway?
> 
>> Since it's recommend to run the newer pg_dump on the older database, I've
>> got to wonder if v11 pg_dump can read the v7.4 on-disk structures.
> 
> We dropped support for pre-8.0 source servers in pg_dump sometime
> recently, though I forget if v11 is affected by that or not.

Version 10.0:

https://www.postgresql.org/docs/10/release-10.html


> You could try just dumping with 7.4's pg_dump and seeing if the
> output will load into v11 --- ideally it would, but I'd not be
> surprised if there are issues that have to be resolved manually.
> Or, if you have 8.4's pg_dump at hand, try using that.
> 
> 7.4 to 11 is a big jump to be doing in one step.  There's definitely
> something to be said for porting to an intermediate release, just to
> break down the work into smaller chunks.  But I'd go for halfway between,
> which if I counted releases correctly would be about 9.1, not 8.4.
> 
>             regards, tom lane
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 9/25/19 9:00 AM, Tom Lane wrote:

Ron <ronljohnsonjr@gmail.com> writes:

On 9/25/19 9:29 AM, Christoph Berg wrote:

Re: Ekaterina Amez 2019-09-25 <8818b028-bd2d-412e-d4e3-e29c49ffee17@zunibal.com>

We've decided to upgrade our PostgreSQL production servers. First task is
remove an old v7.14 version. It was supposed to be upgraded to a v8.4
server. The server was installed, several databases where released here but
v7.4 was never migrated. The plan is pg_dump this database and psql it to
existing 8.4 server. After this, we'll pg_upgrade.

If you doing dump-restore anyway, why not restore into v11 rightaway?

I won't use v11 because the existing server where de DB is going to be re-allocated is v8.4. Our Postgres servers are "a bit" out-dated.

Since it's recommend to run the newer pg_dump on the older database, I've
got to wonder if v11 pg_dump can read the v7.4 on-disk structures.

We dropped support for pre-8.0 source servers in pg_dump sometime
recently, though I forget if v11 is affected by that or not.

Version 10.0:

https://www.postgresql.org/docs/10/release-10.html

Ok, v10 release notes says it explicitly:

• Remove pg_dump/pg_dumpall support for dumping from pre-8.0 servers (Tom Lane)

  Users needing to dump from pre-8.0 servers will need to use dump programs from PostgreSQL 9.6 or earlier. The resulting output should still load successfully into newer servers.

You could try just dumping with 7.4's pg_dump and seeing if the
output will load into v11 --- ideally it would, but I'd not be
surprised if there are issues that have to be resolved manually.
Or, if you have 8.4's pg_dump at hand, try using that.

Yes, that's what I have for my tests: 8.4's pg_dump.

7.4 to 11 is a big jump to be doing in one step.  There's definitely
something to be said for porting to an intermediate release, just to
break down the work into smaller chunks.  But I'd go for halfway between,
which if I counted releases correctly would be about 9.1, not 8.4.

            regards, tom lane

v8.4 is mandatory middle step, because we'd like to remove v7.14 ASAP and the only available server is 8.4. After that upgrade is what I'm talking about. I was thinking as you, Tom: upgrading to v11 is really a big jump. v10 is also a big jump that scares me less, but maybe going first to 9.6 (which gives us a couple of years) would be a better solution that could let us experiment with some of the new performance features we're interested in.