Re: pg_upgrade

Hello,

while using pg_upgrade I receive an error:

pg_restore: dropping DATABASE template1
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 1955; 1262 1 DATABASE
template1 postgres
pg_restore: [archiver (db)] could not execute query: ERROR: cannot drop a
template database
Command was: DROP DATABASE "template1";

I have seen, that template-databases can not be dropped. Is it a bug in
pg_upgrade or am I doing something not properly?

regards
Thomas Szypowski

Tomasz Szypowski <tomasz(dot)szypowski(at)gmail(dot)com> writes:
> while using pg_upgrade I receive an error:

> pg_restore: dropping DATABASE template1
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> pg_restore: [archiver (db)] Error from TOC entry 1955; 1262 1 DATABASE
> template1 postgres
> pg_restore: [archiver (db)] could not execute query: ERROR: cannot drop a
> template database
> Command was: DROP DATABASE "template1";

Hmmm ... I could believe this would happen if you've removed the
is_template marking from template1 in the source installation.

That doesn't seem like a terribly good idea though, so I'm not
inclined to try to figure a way for pg_dump to work around it.

regards, tom lane

Hi Tom,

thanks for the response. After initdb tempate1 has datistemplate as true
and in the pg_upgrade.c is:

/*
* template1 and postgres databases will already exist in the target
* installation, so tell pg_restore to drop and recreate them;
* otherwise we would fail to propagate their database-level
* properties.
*/
create_opts = "--clean --create";

regards
Thomas Szypowski

pon., 18 mar 2019 o 15:33 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> napisał(a):

> Tomasz Szypowski <tomasz(dot)szypowski(at)gmail(dot)com> writes:
> > while using pg_upgrade I receive an error:
>
> > pg_restore: dropping DATABASE template1
> > pg_restore: [archiver (db)] Error while PROCESSING TOC:
> > pg_restore: [archiver (db)] Error from TOC entry 1955; 1262 1 DATABASE
> > template1 postgres
> > pg_restore: [archiver (db)] could not execute query: ERROR: cannot drop
> a
> > template database
> > Command was: DROP DATABASE "template1";
>
> Hmmm ... I could believe this would happen if you've removed the
> is_template marking from template1 in the source installation.
>
> That doesn't seem like a terribly good idea though, so I'm not
> inclined to try to figure a way for pg_dump to work around it.
>
> regards, tom lane
>

Tomasz Szypowski <tomasz(dot)szypowski(at)gmail(dot)com> writes:
> thanks for the response. After initdb tempate1 has datistemplate as true

Right, initdb leaves it set that way, but I'm guessing that it's not
like that in the database you're trying to pg_upgrade.

regards, tom lane

So what set it to false?
Suppose I have created cluster in 9.5. Template1 has set template flag. Now
I upgrade it to 11.2, so I created cluster on 11.2 by initdb,. Here
template1 has set template flag as well. Now I do perform pg_upgrade and
both template1 are template. Should I manually unset the flag? If yes how
would it be possible to perform pg_upgrade without unsetting the flag -
there is no such point in instruction

Regards
Thomas Szypowski

W dniu pon., 18.03.2019 o 22:58 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> napisał(a):

> Tomasz Szypowski <tomasz(dot)szypowski(at)gmail(dot)com> writes:
> > thanks for the response. After initdb tempate1 has datistemplate as true
>
> Right, initdb leaves it set that way, but I'm guessing that it's not
> like that in the database you're trying to pg_upgrade.
>
> regards, tom lane
>
--
pozdrawiam
Tomek

Tomasz Szypowski <tomasz(dot)szypowski(at)gmail(dot)com> writes:
> So what set it to false?

I was hoping you'd tell me ;-). If it's not false in the old cluster,
though, that theory is all wet.

I wonder if you're somehow using the wrong version of pg_upgrade or
pg_dump. There are cross-checks about that in pg_upgrade, but it
looks like they only check the major version number --- if we'd changed
anything about this in a minor release (which I think we did), it might
be possible to get burnt if you were using pg_upgrade or pg_dump from a
prior minor release. What are all the versions involved, exactly?

regards, tom lane

As far as I remember pg_upgrade is from 11.2 rest is from 9.5. This is due
to the fact, that the version is secured, only md5, md5 hashed and so on. I
compared the code and didn’t see much difference in pg_upgrade core, but
give me some days and I will test it using binaries from 11.2

Regards
Thomas

W dniu pon., 18.03.2019 o 23:37 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> napisał(a):

> Tomasz Szypowski <tomasz(dot)szypowski(at)gmail(dot)com> writes:
> > So what set it to false?
>
> I was hoping you'd tell me ;-). If it's not false in the old cluster,
> though, that theory is all wet.
>
> I wonder if you're somehow using the wrong version of pg_upgrade or
> pg_dump. There are cross-checks about that in pg_upgrade, but it
> looks like they only check the major version number --- if we'd changed
> anything about this in a minor release (which I think we did), it might
> be possible to get burnt if you were using pg_upgrade or pg_dump from a
> prior minor release. What are all the versions involved, exactly?
>
> regards, tom lane
>
--
pozdrawiam
Tomek

Tomasz Szypowski <tomasz(dot)szypowski(at)gmail(dot)com> writes:
> As far as I remember pg_upgrade is from 11.2 rest is from 9.5.

If you're not certain, run each program with the --version switch
to find out. But pg_upgrade should be refusing to use pg_dump
et al that aren't from its own major version.

regards, tom lane

Yes, generalny i use 11.4 bins, but libpq, dump, dumpall, restore and psql
are old ones.
Nów I have made the test with original 9.5.16 to 10.2 and it succeeded. If
it is the binary version problem, I´m a little bit surprised, because I
havent´s seen it in a code.
If it is the only bug, that you have, so please do not change code, due to
my problem :-). I ´ll do my test one more time, this time with 11.2
binaries. Will let you then know.

regards
Thomas

W dniu pon., 18.03.2019 o 23:54 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> napisał(a):

> Tomasz Szypowski <tomasz(dot)szypowski(at)gmail(dot)com> writes:
> > As far as I remember pg_upgrade is from 11.2 rest is from 9.5.
>
> If you're not certain, run each program with the --version switch
> to find out. But pg_upgrade should be refusing to use pg_dump
> et al that aren't from its own major version.
>
> regards, tom lane
>