Thread: pg_upgrade failing from 9.3 to 9.4 because "template0" already exists
Attempting to upgrade a large (>3TB) postgressql database from 9.3 to 9.4 on Ubuntu 14.04 LTS, but the process fails fairly early on. The error message instructs me to look at the last few lines of pg_upgrade_utility.log for more info, and the last two lines there (the only ones that don't succeed) are: CREATE DATABASE "template0" WITH TEMPLATE = template0 OWNER = "postgres"; psql:pg_upgrade_dump_globals.sql:44: ERROR: database "template0" already exists For reference, the pg_upgrade command as I'm running it is: /usr/lib/postgresql/9.4/bin/pg_upgrade -k -b /usr/lib/postgresql/9.3/bin -B /usr/lib/postgresql/9.4/bin -d /DB/postgres/data/9.3/main -D /DB/postgres/data/9.4/main -o '-c config_file=/etc/postgresql/9.3/main/postgresql.conf' -O '-c config_file=/etc/postgresql/9.4/main/postgresql.conf' Searching google for useful advice yields...little. (Mostly articles about problems upgrading from 8.4 to 9.2, whose solutions -- when they're described -- don't help here. Also, there are a number of sources saying I should just do a pg_dumpall and then a pg_restore...which would be fine, probably, if I could afford to take my production database offline for a week.) I do have a pg_dumpall backup done, and at the moment I'm doing all this on a test instance, so I can afford to experiment without risking data corruption or excessive downtime on the production server, but the whole point of the experimenting is to come up with a procedure that will work in production when it's time to do it there. Dump-and-restore definitely does not meet that requirement, even if it does eventually recreate a working database. Any ideas? I'm sure I'm missing something obvious, here, but I can't seem to find any reference that tells me what it would be.
On 03/06/2015 10:11 AM, Matt Landry wrote: > Attempting to upgrade a large (>3TB) postgressql database from 9.3 to > 9.4 on Ubuntu 14.04 LTS, but the process fails fairly early on. The > error message instructs me to look at the last few lines of > pg_upgrade_utility.log for more info, and the last two lines there (the > only ones that don't succeed) are: > > CREATE DATABASE "template0" WITH TEMPLATE = template0 OWNER = "postgres"; > psql:pg_upgrade_dump_globals.sql:44: ERROR: database "template0" > already exists Looks to me like someone created their own template0 database in the original cluster. You might do in psql a \l in the original cluster to see if there is more than one template0. > > For reference, the pg_upgrade command as I'm running it is: > > /usr/lib/postgresql/9.4/bin/pg_upgrade -k -b /usr/lib/postgresql/9.3/bin > -B /usr/lib/postgresql/9.4/bin -d /DB/postgres/data/9.3/main -D > /DB/postgres/data/9.4/main -o '-c > config_file=/etc/postgresql/9.3/main/postgresql.conf' -O '-c > config_file=/etc/postgresql/9.4/main/postgresql.conf' > > Searching google for useful advice yields...little. (Mostly articles > about problems upgrading from 8.4 to 9.2, whose solutions -- when > they're described -- don't help here. Also, there are a number of > sources saying I should just do a pg_dumpall and then a > pg_restore...which would be fine, probably, if I could afford to take my > production database offline for a week.) > > I do have a pg_dumpall backup done, and at the moment I'm doing all this > on a test instance, so I can afford to experiment without risking data > corruption or excessive downtime on the production server, but the whole > point of the experimenting is to come up with a procedure that will work > in production when it's time to do it there. Dump-and-restore definitely > does not meet that requirement, even if it does eventually recreate a > working database. > > Any ideas? I'm sure I'm missing something obvious, here, but I can't > seem to find any reference that tells me what it would be. > > -- Adrian Klaver adrian.klaver@aklaver.com
Matt, In your existing environment, do you have template0 set to allow connections (datallowconn)? That's not a good idea in general, but I suspect that's why pg_dumpall is including it based on a quick look at the code. Thanks! * Matt Landry (lelnet.matt@gmail.com) wrote: > Attempting to upgrade a large (>3TB) postgressql database from 9.3 > to 9.4 on Ubuntu 14.04 LTS, but the process fails fairly early on. > The error message instructs me to look at the last few lines of > pg_upgrade_utility.log for more info, and the last two lines there > (the only ones that don't succeed) are: > > CREATE DATABASE "template0" WITH TEMPLATE = template0 OWNER = "postgres"; > psql:pg_upgrade_dump_globals.sql:44: ERROR: database "template0" > already exists > > For reference, the pg_upgrade command as I'm running it is: > > /usr/lib/postgresql/9.4/bin/pg_upgrade -k -b > /usr/lib/postgresql/9.3/bin -B /usr/lib/postgresql/9.4/bin -d > /DB/postgres/data/9.3/main -D /DB/postgres/data/9.4/main -o '-c > config_file=/etc/postgresql/9.3/main/postgresql.conf' -O '-c > config_file=/etc/postgresql/9.4/main/postgresql.conf' > > Searching google for useful advice yields...little. (Mostly articles > about problems upgrading from 8.4 to 9.2, whose solutions -- when > they're described -- don't help here. Also, there are a number of > sources saying I should just do a pg_dumpall and then a > pg_restore...which would be fine, probably, if I could afford to > take my production database offline for a week.) > > I do have a pg_dumpall backup done, and at the moment I'm doing all > this on a test instance, so I can afford to experiment without > risking data corruption or excessive downtime on the production > server, but the whole point of the experimenting is to come up with > a procedure that will work in production when it's time to do it > there. Dump-and-restore definitely does not meet that requirement, > even if it does eventually recreate a working database. > > Any ideas? I'm sure I'm missing something obvious, here, but I can't > seem to find any reference that tells me what it would be. > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
Attachment
Adrian,
* Adrian Klaver (adrian.klaver@aklaver.com) wrote:
> On 03/06/2015 10:11 AM, Matt Landry wrote:
> >Attempting to upgrade a large (>3TB) postgressql database from 9.3 to
> >9.4 on Ubuntu 14.04 LTS, but the process fails fairly early on. The
> >error message instructs me to look at the last few lines of
> >pg_upgrade_utility.log for more info, and the last two lines there (the
> >only ones that don't succeed) are:
> >
> >CREATE DATABASE "template0" WITH TEMPLATE = template0 OWNER = "postgres";
> >psql:pg_upgrade_dump_globals.sql:44: ERROR: database "template0"
> >already exists
>
> Looks to me like someone created their own template0 database in the
> original cluster. You might do in psql a \l in the original cluster
> to see if there is more than one template0.
template0 is created during initdb and it shouldn't be possible to have
more than one database named 'template0' in a given cluster (though if
there is, that could certainly be a problem...).
Now, if someone renamed the original template0 and created a new one,
that could be an issue but I think that's more because the newer
template0 would be dumped by pg_dumpall as it'd be set to allow
connections (which is the default).
If I'm reading the code correctly, I think we have an entirely different
issue here which is that databases with 'datallowconn' set to false
won't be included in the upgrade and, while all the files will be there,
the catalog won't be. That's a serious problem as people do actually
modify that flag in some environments and if it happens to be set
incorrectly when the pg_dumpall from pg_upgrade runs then those
databases will disappear across the upgrade.. I've not checked to see
if something else in the pg_upgrade process will catch this, but if not,
we should definitely add something.
Thanks,
Stephen
Attachment
On 03/06/2015 10:35 AM, Stephen Frost wrote: > Adrian, > > * Adrian Klaver (adrian.klaver@aklaver.com) wrote: >> On 03/06/2015 10:11 AM, Matt Landry wrote: >>> Attempting to upgrade a large (>3TB) postgressql database from 9.3 to >>> 9.4 on Ubuntu 14.04 LTS, but the process fails fairly early on. The >>> error message instructs me to look at the last few lines of >>> pg_upgrade_utility.log for more info, and the last two lines there (the >>> only ones that don't succeed) are: >>> >>> CREATE DATABASE "template0" WITH TEMPLATE = template0 OWNER = "postgres"; >>> psql:pg_upgrade_dump_globals.sql:44: ERROR: database "template0" >>> already exists >> >> Looks to me like someone created their own template0 database in the >> original cluster. You might do in psql a \l in the original cluster >> to see if there is more than one template0. > > template0 is created during initdb and it shouldn't be possible to have > more than one database named 'template0' in a given cluster (though if > there is, that could certainly be a problem...). Agreed, I am just trying to figure out how you get: CREATE DATABASE "template0" WITH TEMPLATE = template0 .. Seems to be a snake eating its tail:) > > Now, if someone renamed the original template0 and created a new one, > that could be an issue but I think that's more because the newer > template0 would be dumped by pg_dumpall as it'd be set to allow > connections (which is the default). > > If I'm reading the code correctly, I think we have an entirely different > issue here which is that databases with 'datallowconn' set to false > won't be included in the upgrade and, while all the files will be there, > the catalog won't be. That's a serious problem as people do actually > modify that flag in some environments and if it happens to be set > incorrectly when the pg_dumpall from pg_upgrade runs then those > databases will disappear across the upgrade.. I've not checked to see > if something else in the pg_upgrade process will catch this, but if not, > we should definitely add something. > > Thanks, > > Stephen > -- Adrian Klaver adrian.klaver@aklaver.com
On 03/06/2015 12:37 PM, Adrian Klaver wrote: > Agreed, I am just trying to figure out how you get: > > CREATE DATABASE "template0" WITH TEMPLATE = template0 .. > > Seems to be a snake eating its tail:) Yes. It does. And it's pretty obvious why having this would be a problem...not quite so obvious how to not have it. For the record, I inherited this DB configuration, but I can say with certainty that we have _one_ actual database with data in it, and it's not "template0". If it turns out that, in order to do what I need to do, we need to either drop (or rename, or whatever) template0 or somehow manually exclude the attempt to upgrade template0, and that for some reason we have local modifications to that template that will then have to be re-done, then that's a totally acceptable solution, from my perspective, as long as we (that is, me and all the people who read the pg_upgrade documentation in the future) know how to do that. But figuring out how it managed to get into this condition in the first place, and how pg_upgrade can be made to programmatically cope with that, is probably more important for all the users who aren't me. :)
On 03/06/2015 11:22 AM, Matt Landry wrote: > On 03/06/2015 12:37 PM, Adrian Klaver wrote: >> Agreed, I am just trying to figure out how you get: >> >> CREATE DATABASE "template0" WITH TEMPLATE = template0 .. >> >> Seems to be a snake eating its tail:) > > Yes. It does. And it's pretty obvious why having this would be a > problem...not quite so obvious how to not have it. > > For the record, I inherited this DB configuration, but I can say with > certainty that we have _one_ actual database with data in it, and it's > not "template0". If it turns out that, in order to do what I need to do, > we need to either drop (or rename, or whatever) template0 or somehow > manually exclude the attempt to upgrade template0, and that for some > reason we have local modifications to that template that will then have > to be re-done, then that's a totally acceptable solution, from my > perspective, as long as we (that is, me and all the people who read the > pg_upgrade documentation in the future) know how to do that. So on the original cluster, log in using psql and do \l and post the results here. Thanks. > > But figuring out how it managed to get into this condition in the first > place, and how pg_upgrade can be made to programmatically cope with > that, is probably more important for all the users who aren't me. :) > > -- Adrian Klaver adrian.klaver@aklaver.com
On 03/06/2015 11:22 AM, Matt Landry wrote: > On 03/06/2015 12:37 PM, Adrian Klaver wrote: >> Agreed, I am just trying to figure out how you get: >> >> CREATE DATABASE "template0" WITH TEMPLATE = template0 .. >> >> Seems to be a snake eating its tail:) > > Yes. It does. And it's pretty obvious why having this would be a > problem...not quite so obvious how to not have it. > > For the record, I inherited this DB configuration, but I can say with > certainty that we have _one_ actual database with data in it, and it's > not "template0". If it turns out that, in order to do what I need to do, > we need to either drop (or rename, or whatever) template0 or somehow > manually exclude the attempt to upgrade template0, and that for some > reason we have local modifications to that template that will then have > to be re-done, then that's a totally acceptable solution, from my > perspective, as long as we (that is, me and all the people who read the > pg_upgrade documentation in the future) know how to do that. > > But figuring out how it managed to get into this condition in the first > place, and how pg_upgrade can be made to programmatically cope with > that, is probably more important for all the users who aren't me. :) > > Meant to add to previous post, to check with issue that Stephen mentioned do: select datname, datallowconn from pg_database ; -- Adrian Klaver adrian.klaver@aklaver.com
On 03/06/2015 01:55 PM, Adrian Klaver wrote:
>
> So on the original cluster, log in using psql and do \l and post the
> results here. Thanks.
[...]
> Meant to add to previous post, to check with issue that Stephen
> mentioned do:
>
> select datname, datallowconn from pg_database ;
>
>
>
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access
privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
reporting | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
=c/postgres +
| | | | |
postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
=c/postgres +
| | | | |
postgres=CTc/postgres
(4 rows)
postgres=# select datname, datallowconn from pg_database ;
datname | datallowconn
-----------+--------------
template1 | t
template0 | t
postgres | t
reporting | t
(4 rows)
postgres=#
Matt,
* Matt Landry (lelnet.matt@gmail.com) wrote:
> postgres=# select datname, datallowconn from pg_database ;
> datname | datallowconn
> -----------+--------------
> template1 | t
> template0 | t
> postgres | t
> reporting | t
> (4 rows)
Right, as I mentioned, template0 shouldn't have datallowconn as 'true'.
That's why it's being included in the pg_dumpall.
On your test setup, run (as superuser):
update pg_database set datallowconn = false where datname = 'template0';
Then re-run the pg_upgrade.
Thanks!
Stephen
Attachment
On 03/06/2015 02:43 PM, Stephen Frost wrote: > Right, as I mentioned, template0 shouldn't have datallowconn as 'true'. > That's why it's being included in the pg_dumpall. > > On your test setup, run (as superuser): > > update pg_database set datallowconn = false where datname = 'template0'; > > Then re-run the pg_upgrade. Glory and halleleujah, it works. 9.4 now confirmed working, and all is well. Thanks much, for your help.
Stephen Frost <sfrost@snowman.net> writes:
> * Matt Landry (lelnet.matt@gmail.com) wrote:
>> postgres=# select datname, datallowconn from pg_database ;
>> datname | datallowconn
>> -----------+--------------
>> template1 | t
>> template0 | t
>> postgres | t
>> reporting | t
>> (4 rows)
> Right, as I mentioned, template0 shouldn't have datallowconn as 'true'.
> That's why it's being included in the pg_dumpall.
Perhaps pg_upgrade should deliberately ignore template0 regardless of
datallowconn? And/or we should hard-wire that into pg_dumpall?
I feel no compulsion whatsoever to preserve any user-initiated changes
in template0 across an upgrade.
regards, tom lane
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Stephen Frost <sfrost@snowman.net> writes:
> > * Matt Landry (lelnet.matt@gmail.com) wrote:
> >> postgres=# select datname, datallowconn from pg_database ;
> >> datname | datallowconn
> >> -----------+--------------
> >> template1 | t
> >> template0 | t
> >> postgres | t
> >> reporting | t
> >> (4 rows)
>
> > Right, as I mentioned, template0 shouldn't have datallowconn as 'true'.
> > That's why it's being included in the pg_dumpall.
>
> Perhaps pg_upgrade should deliberately ignore template0 regardless of
> datallowconn? And/or we should hard-wire that into pg_dumpall?
My thinking would be that pg_dumpall should be hard-wired for template0
(just like it is for template1..) and that we should *not* be excluding
databases that are marked as datallowconn = false.. That said, it's not
clear to me what to do there instead. Maybe throw an error or a
warning? The point of pg_dumpall is to dump *all* the databases and at
least the manpage doesn't appear to say anything about "but ignores
databases with datallowconn = false".
> I feel no compulsion whatsoever to preserve any user-initiated changes
> in template0 across an upgrade.
I agree with this, with regard to template0.
Thanks,
Stephen
Attachment
Stephen Frost <sfrost@snowman.net> writes:
> * Tom Lane (tgl@sss.pgh.pa.us) wrote:
>> Perhaps pg_upgrade should deliberately ignore template0 regardless of
>> datallowconn? And/or we should hard-wire that into pg_dumpall?
> My thinking would be that pg_dumpall should be hard-wired for template0
> (just like it is for template1..) and that we should *not* be excluding
> databases that are marked as datallowconn = false.. That said, it's not
> clear to me what to do there instead. Maybe throw an error or a
> warning? The point of pg_dumpall is to dump *all* the databases and at
> least the manpage doesn't appear to say anything about "but ignores
> databases with datallowconn = false".
I think pg_upgrade and pg_dumpall may be two different use-cases.
pg_upgrade should definitely throw a hard error if there are any
non-template0 databases that it can't connect to, because the alternative
is losing such databases during the upgrade. I'm not sure that the
argument is so black-and-white for pg_dumpall, though. Nobody's ever
complained about it skipping unconnectable databases, and that behavior
has been there since we invented datallowconn (cf commit 2cf48ca04bf599).
regards, tom lane
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Stephen Frost <sfrost@snowman.net> writes:
> > * Tom Lane (tgl@sss.pgh.pa.us) wrote:
> >> Perhaps pg_upgrade should deliberately ignore template0 regardless of
> >> datallowconn? And/or we should hard-wire that into pg_dumpall?
>
> > My thinking would be that pg_dumpall should be hard-wired for template0
> > (just like it is for template1..) and that we should *not* be excluding
> > databases that are marked as datallowconn = false.. That said, it's not
> > clear to me what to do there instead. Maybe throw an error or a
> > warning? The point of pg_dumpall is to dump *all* the databases and at
> > least the manpage doesn't appear to say anything about "but ignores
> > databases with datallowconn = false".
>
> I think pg_upgrade and pg_dumpall may be two different use-cases.
Perhaps..
> pg_upgrade should definitely throw a hard error if there are any
> non-template0 databases that it can't connect to, because the alternative
> is losing such databases during the upgrade. I'm not sure that the
> argument is so black-and-white for pg_dumpall, though. Nobody's ever
> complained about it skipping unconnectable databases, and that behavior
> has been there since we invented datallowconn (cf commit 2cf48ca04bf599).
Technically, there haven't been any complaints about either pg_dumpall's
behavior in this regard, or pg_upgrade's, but pg_upgrade's post-upgrade
scripts would happily remove any databases which were marked as
'datallowconn = false' and that scares the daylights out of me. To that
end, I'd suggest patching (and back-patching) pg_upgrade to check early
on that:
template0 is set to 'datallowconn = false'
AND
all databases except template0 are set to 'datallowconn = true'
The first is required or anyone who has done that will get the funny
error that started this thread and things won't work anyway, but I
believe the latter is also necessary to patch and back-patch as it could
lead to data loss. It's not a high potential as, hopefully, people will
check first, but I can imagine a hosting provider or environments where
there are lots of independent clusters not catching this issue in their
testing, only to discover someone set their database to 'datallowconn =
false' for whatever reason and now that database is gone...
Thanks!
Stephen
Attachment
Stephen Frost <sfrost@snowman.net> writes: > * Tom Lane (tgl@sss.pgh.pa.us) wrote: > >> Stephen Frost <sfrost@snowman.net> writes: >> > * Tom Lane (tgl@sss.pgh.pa.us) wrote: >> >> Perhaps pg_upgrade should deliberately ignore template0 regardless of >> >> datallowconn? And/or we should hard-wire that into pg_dumpall? >> >> > My thinking would be that pg_dumpall should be hard-wired for template0 >> > (just like it is for template1..) and that we should *not* be excluding >> > databases that are marked as datallowconn = false.. That said, it's not >> > clear to me what to do there instead. Maybe throw an error or a >> > warning? The point of pg_dumpall is to dump *all* the databases and at >> > least the manpage doesn't appear to say anything about "but ignores >> > databases with datallowconn = false". >> >> I think pg_upgrade and pg_dumpall may be two different use-cases. > > Perhaps.. > >> pg_upgrade should definitely throw a hard error if there are any >> non-template0 databases that it can't connect to, because the alternative >> is losing such databases during the upgrade. I'm not sure that the >> argument is so black-and-white for pg_dumpall, though. Nobody's ever >> complained about it skipping unconnectable databases, and that behavior >> has been there since we invented datallowconn (cf commit 2cf48ca04bf599). > > Technically, there haven't been any complaints about either pg_dumpall's > behavior in this regard, or pg_upgrade's, but pg_upgrade's post-upgrade > scripts would happily remove any databases which were marked as > 'datallowconn = false' and that scares the daylights out of me. To that > end, I'd suggest patching (and back-patching) pg_upgrade to check early > on that: > > template0 is set to 'datallowconn = false' > > AND > > all databases except template0 are set to 'datallowconn = true' > > The first is required or anyone who has done that will get the funny > error that started this thread and things won't work anyway, but I > believe the latter is also necessary to patch and back-patch as it could > lead to data loss. It's not a high potential as, hopefully, people will > check first, but I can imagine a hosting provider or environments where > there are lots of independent clusters not catching this issue in their > testing, only to discover someone set their database to 'datallowconn = > false' for whatever reason and now that database is gone... Good thinking! Not lately but several times in the past I've done just that to lock down some odd DB that was to be untouched but preserved nonetheless. > Thanks! > > Stephen -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consulting@comcast.net p: 312.241.7800
On Fri, Mar 6, 2015 at 06:10:15PM -0500, Stephen Frost wrote: > Technically, there haven't been any complaints about either pg_dumpall's > behavior in this regard, or pg_upgrade's, but pg_upgrade's post-upgrade > scripts would happily remove any databases which were marked as > 'datallowconn = false' and that scares the daylights out of me. To that > end, I'd suggest patching (and back-patching) pg_upgrade to check early > on that: > > template0 is set to 'datallowconn = false' > > AND > > all databases except template0 are set to 'datallowconn = true' > > The first is required or anyone who has done that will get the funny > error that started this thread and things won't work anyway, but I > believe the latter is also necessary to patch and back-patch as it could > lead to data loss. It's not a high potential as, hopefully, people will > check first, but I can imagine a hosting provider or environments where > there are lots of independent clusters not catching this issue in their > testing, only to discover someone set their database to 'datallowconn = > false' for whatever reason and now that database is gone... Agreed. I will work on a patch for this. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +
On Mon, Mar 9, 2015 at 12:43:05PM -0400, Bruce Momjian wrote: > On Fri, Mar 6, 2015 at 06:10:15PM -0500, Stephen Frost wrote: > > Technically, there haven't been any complaints about either pg_dumpall's > > behavior in this regard, or pg_upgrade's, but pg_upgrade's post-upgrade > > scripts would happily remove any databases which were marked as > > 'datallowconn = false' and that scares the daylights out of me. To that > > end, I'd suggest patching (and back-patching) pg_upgrade to check early > > on that: > > > > template0 is set to 'datallowconn = false' > > > > AND > > > > all databases except template0 are set to 'datallowconn = true' > > > > The first is required or anyone who has done that will get the funny > > error that started this thread and things won't work anyway, but I > > believe the latter is also necessary to patch and back-patch as it could > > lead to data loss. It's not a high potential as, hopefully, people will > > check first, but I can imagine a hosting provider or environments where > > there are lots of independent clusters not catching this issue in their > > testing, only to discover someone set their database to 'datallowconn = > > false' for whatever reason and now that database is gone... > > Agreed. I will work on a patch for this. Oh, also, thanks for the analysis on this --- you are spot-on. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +
On Mon, Mar 9, 2015 at 12:43:05PM -0400, Bruce Momjian wrote: > On Fri, Mar 6, 2015 at 06:10:15PM -0500, Stephen Frost wrote: > > Technically, there haven't been any complaints about either pg_dumpall's > > behavior in this regard, or pg_upgrade's, but pg_upgrade's post-upgrade > > scripts would happily remove any databases which were marked as > > 'datallowconn = false' and that scares the daylights out of me. To that > > end, I'd suggest patching (and back-patching) pg_upgrade to check early > > on that: > > > > template0 is set to 'datallowconn = false' > > > > AND > > > > all databases except template0 are set to 'datallowconn = true' > > > > The first is required or anyone who has done that will get the funny > > error that started this thread and things won't work anyway, but I > > believe the latter is also necessary to patch and back-patch as it could > > lead to data loss. It's not a high potential as, hopefully, people will > > check first, but I can imagine a hosting provider or environments where > > there are lots of independent clusters not catching this issue in their > > testing, only to discover someone set their database to 'datallowconn = > > false' for whatever reason and now that database is gone... > > Agreed. I will work on a patch for this. Attached is a patch that implements this, and it should be backpatch to all versions. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +
Attachment
Bruce,
* Bruce Momjian (bruce@momjian.us) wrote:
> On Mon, Mar 9, 2015 at 12:43:05PM -0400, Bruce Momjian wrote:
> > On Fri, Mar 6, 2015 at 06:10:15PM -0500, Stephen Frost wrote:
> > > The first is required or anyone who has done that will get the funny
> > > error that started this thread and things won't work anyway, but I
> > > believe the latter is also necessary to patch and back-patch as it could
> > > lead to data loss. It's not a high potential as, hopefully, people will
> > > check first, but I can imagine a hosting provider or environments where
> > > there are lots of independent clusters not catching this issue in their
> > > testing, only to discover someone set their database to 'datallowconn =
> > > false' for whatever reason and now that database is gone...
> >
> > Agreed. I will work on a patch for this.
>
> Attached is a patch that implements this, and it should be backpatch to
> all versions.
Excellent and agreed. Just looked through the patch and didn't do a
full review, but it looks good to me.
Thanks!
Stephen
Attachment
On Fri, May 15, 2015 at 10:49:43AM -0400, Stephen Frost wrote: > Bruce, > > * Bruce Momjian (bruce@momjian.us) wrote: > > On Mon, Mar 9, 2015 at 12:43:05PM -0400, Bruce Momjian wrote: > > > On Fri, Mar 6, 2015 at 06:10:15PM -0500, Stephen Frost wrote: > > > > The first is required or anyone who has done that will get the funny > > > > error that started this thread and things won't work anyway, but I > > > > believe the latter is also necessary to patch and back-patch as it could > > > > lead to data loss. It's not a high potential as, hopefully, people will > > > > check first, but I can imagine a hosting provider or environments where > > > > there are lots of independent clusters not catching this issue in their > > > > testing, only to discover someone set their database to 'datallowconn = > > > > false' for whatever reason and now that database is gone... > > > > > > Agreed. I will work on a patch for this. > > > > Attached is a patch that implements this, and it should be backpatch to > > all versions. > > Excellent and agreed. Just looked through the patch and didn't do a > full review, but it looks good to me. OK, thanks. I will apply it all branches later today as it is a data loss bug. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +
On Fri, May 15, 2015 at 10:51:15AM -0400, Bruce Momjian wrote: > On Fri, May 15, 2015 at 10:49:43AM -0400, Stephen Frost wrote: > > Bruce, > > > > * Bruce Momjian (bruce@momjian.us) wrote: > > > On Mon, Mar 9, 2015 at 12:43:05PM -0400, Bruce Momjian wrote: > > > > On Fri, Mar 6, 2015 at 06:10:15PM -0500, Stephen Frost wrote: > > > > > The first is required or anyone who has done that will get the funny > > > > > error that started this thread and things won't work anyway, but I > > > > > believe the latter is also necessary to patch and back-patch as it could > > > > > lead to data loss. It's not a high potential as, hopefully, people will > > > > > check first, but I can imagine a hosting provider or environments where > > > > > there are lots of independent clusters not catching this issue in their > > > > > testing, only to discover someone set their database to 'datallowconn = > > > > > false' for whatever reason and now that database is gone... > > > > > > > > Agreed. I will work on a patch for this. > > > > > > Attached is a patch that implements this, and it should be backpatch to > > > all versions. > > > > Excellent and agreed. Just looked through the patch and didn't do a > > full review, but it looks good to me. > > OK, thanks. I will apply it all branches later today as it is a data > loss bug. Patch applied back through 9.0. Thanks for the report and analysis. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +