Re: [GENERAL] Problems with pg_upgrade after change of unix user running db.

[Benedikt Grundmann]

On 4 October 2016 at 09:28, Benedikt Grundmann 
wrote:

>
>
> On 4 October 2016 at 08:17, Benedikt Grundmann 
> wrote:
>
>>
>> On 3 October 2016 at 21:01, Tom Lane  wrote:
>>
>>> Benedikt Grundmann  writes:
>>> > proddb_testing=# SELECT
>>> > conname,convalidated,conislocal,coninhcount,connoinherit
>>> > proddb_testing-# FROM pg_constraint WHERE conrelid =
>>> > 'js_activity_20110101'::regclass;
>>> >conname   | convalidated |
>>> conislocal |
>>> > coninhcount | connoinherit
>>> > -+--
>>> ++-+--
>>> >  seqno_not_null  | f| t
>>>   |
>>> >   1 | f
>>>
>>> After some tracing through the code, I think it's the combination of all
>>> three of coninhcount>0, conislocal, and !convalidated that is producing
>>> the problem, and even then possibly only in binary-upgrade mode.  pg_dump
>>> is jumping through some hoops to try to restore that state, and evidently
>>> not getting it entirely right.
>>>
>>> Is there a reason you've left all these constraints in NOT VALID state?
>>> They're kinda useless that way.
>>
>>
>> Not at all.  I consider the ability to add constraints in not validated
>> form one of the 10 best things that happened in postgres in recent years.
>> They helped us a lot when slowly improving our schemas.
>>
>> Often just preventing any new or modified rows to validate the constraint
>> is really all we need or most that is needed.  Which is the only thing I
>> really care about in this case. And given the size of these tables and
>> their importance validating the constraints during production hours is
>> tricky.  Which means to validate them one of us has to sacrifice part of
>> their Saturday to do these and the marginal utility of having the
>> constraint validated was just never worth it.  But if that is what's
>> required to do the upgrade we will do so (the upgrade itself we will have
>> to do on a Saturday anyway).
>>
>>
>> Probably if you updated them to be valid
>>> (see ALTER TABLE ... VALIDATE CONSTRAINT), the upgrade would go through
>>> without difficulty.
>>>
>>> I'm running all the upgrade attempts on our testing instance (which is
>> nightly restored from the latest backup), it's not a problem to run the
>> validate command there so I'll do that now and find out if you are right.
>>
>
> It looks like you might be right but I don't know for sure yet.  And it
> will take me a long time to find out.  Rationale: After validating
> seqno_not_null I could proceed a bit further but failed at another
> constraint like that (valid_counterparty).  However that constraint
> actually is violated by lots of rows in the past and we had no plans (or
> easy way) to fix this.  The constraint was put in like this to prevent
> future rows.
>
> I guess I could drop the constraint do the restore and then put the
> constraint in again.  Sigh.  This is all relatively sad.
>
>

Yep I can confirm that after dropping a few more constraints and then doing
the checkpoint_segments vs min_wal_size/max_wal_size foo in postgresql.conf
 I got the database up.  So far everything seems otherwise fine.


>
>> I'll look into fixing this, but depending on how messy it turns out to be,
>>> it might be something we choose to fix only in HEAD.
>>>
>>> regards, tom lane
>>>
>>
>>
>

Re: [GENERAL] Problems with pg_upgrade after change of unix user running db.

[Benedikt Grundmann]

On 4 October 2016 at 08:17, Benedikt Grundmann 
wrote:

>
> On 3 October 2016 at 21:01, Tom Lane  wrote:
>
>> Benedikt Grundmann  writes:
>> > proddb_testing=# SELECT
>> > conname,convalidated,conislocal,coninhcount,connoinherit
>> > proddb_testing-# FROM pg_constraint WHERE conrelid =
>> > 'js_activity_20110101'::regclass;
>> >conname   | convalidated |
>> conislocal |
>> > coninhcount | connoinherit
>> > -+--
>> ++-+--
>> >  seqno_not_null  | f| t
>>   |
>> >   1 | f
>>
>> After some tracing through the code, I think it's the combination of all
>> three of coninhcount>0, conislocal, and !convalidated that is producing
>> the problem, and even then possibly only in binary-upgrade mode.  pg_dump
>> is jumping through some hoops to try to restore that state, and evidently
>> not getting it entirely right.
>>
>> Is there a reason you've left all these constraints in NOT VALID state?
>> They're kinda useless that way.
>
>
> Not at all.  I consider the ability to add constraints in not validated
> form one of the 10 best things that happened in postgres in recent years.
> They helped us a lot when slowly improving our schemas.
>
> Often just preventing any new or modified rows to validate the constraint
> is really all we need or most that is needed.  Which is the only thing I
> really care about in this case. And given the size of these tables and
> their importance validating the constraints during production hours is
> tricky.  Which means to validate them one of us has to sacrifice part of
> their Saturday to do these and the marginal utility of having the
> constraint validated was just never worth it.  But if that is what's
> required to do the upgrade we will do so (the upgrade itself we will have
> to do on a Saturday anyway).
>
>
> Probably if you updated them to be valid
>> (see ALTER TABLE ... VALIDATE CONSTRAINT), the upgrade would go through
>> without difficulty.
>>
>> I'm running all the upgrade attempts on our testing instance (which is
> nightly restored from the latest backup), it's not a problem to run the
> validate command there so I'll do that now and find out if you are right.
>

It looks like you might be right but I don't know for sure yet.  And it
will take me a long time to find out.  Rationale: After validating
seqno_not_null I could proceed a bit further but failed at another
constraint like that (valid_counterparty).  However that constraint
actually is violated by lots of rows in the past and we had no plans (or
easy way) to fix this.  The constraint was put in like this to prevent
future rows.

I guess I could drop the constraint do the restore and then put the
constraint in again.  Sigh.  This is all relatively sad.


>
> I'll look into fixing this, but depending on how messy it turns out to be,
>> it might be something we choose to fix only in HEAD.
>>
>> regards, tom lane
>>
>
>

Re: [GENERAL] Problems with pg_upgrade after change of unix user running db.

[Benedikt Grundmann]

On 3 October 2016 at 21:01, Tom Lane  wrote:

> Benedikt Grundmann  writes:
> > proddb_testing=# SELECT
> > conname,convalidated,conislocal,coninhcount,connoinherit
> > proddb_testing-# FROM pg_constraint WHERE conrelid =
> > 'js_activity_20110101'::regclass;
> >conname   | convalidated | conislocal
> |
> > coninhcount | connoinherit
> > -+--
> ++-+--
> >  seqno_not_null  | f| t
> |
> >   1 | f
>
> After some tracing through the code, I think it's the combination of all
> three of coninhcount>0, conislocal, and !convalidated that is producing
> the problem, and even then possibly only in binary-upgrade mode.  pg_dump
> is jumping through some hoops to try to restore that state, and evidently
> not getting it entirely right.
>
> Is there a reason you've left all these constraints in NOT VALID state?
> They're kinda useless that way.


Not at all.  I consider the ability to add constraints in not validated
form one of the 10 best things that happened in postgres in recent years.
They helped us a lot when slowly improving our schemas.

Often just preventing any new or modified rows to validate the constraint
is really all we need or most that is needed.  Which is the only thing I
really care about in this case. And given the size of these tables and
their importance validating the constraints during production hours is
tricky.  Which means to validate them one of us has to sacrifice part of
their Saturday to do these and the marginal utility of having the
constraint validated was just never worth it.  But if that is what's
required to do the upgrade we will do so (the upgrade itself we will have
to do on a Saturday anyway).


Probably if you updated them to be valid
> (see ALTER TABLE ... VALIDATE CONSTRAINT), the upgrade would go through
> without difficulty.
>
> I'm running all the upgrade attempts on our testing instance (which is
nightly restored from the latest backup), it's not a problem to run the
validate command there so I'll do that now and find out if you are right.

I'll look into fixing this, but depending on how messy it turns out to be,
> it might be something we choose to fix only in HEAD.
>
> regards, tom lane
>

Re: [GENERAL] Problems with pg_upgrade after change of unix user running db.

[Tom Lane]

Benedikt Grundmann  writes:
> proddb_testing=# SELECT
> conname,convalidated,conislocal,coninhcount,connoinherit
> proddb_testing-# FROM pg_constraint WHERE conrelid =
> 'js_activity_20110101'::regclass;
>conname   | convalidated | conislocal |
> coninhcount | connoinherit
> -+--++-+--
>  seqno_not_null  | f| t  |
>   1 | f

After some tracing through the code, I think it's the combination of all
three of coninhcount>0, conislocal, and !convalidated that is producing
the problem, and even then possibly only in binary-upgrade mode.  pg_dump
is jumping through some hoops to try to restore that state, and evidently
not getting it entirely right.

Is there a reason you've left all these constraints in NOT VALID state?
They're kinda useless that way.  Probably if you updated them to be valid
(see ALTER TABLE ... VALIDATE CONSTRAINT), the upgrade would go through
without difficulty.

I'll look into fixing this, but depending on how messy it turns out to be,
it might be something we choose to fix only in HEAD.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Problems with pg_upgrade after change of unix user running db.

[Benedikt Grundmann]

On 3 October 2016 at 15:54, Tom Lane  wrote:

> Benedikt Grundmann  writes:
> > And it looks like now I'm back to the error that stopped me last time:
> > pg_restore: [archiver (db)] Error from TOC entry 8425; 2606 416548282
> CHECK
> > CONSTRAINT seqno_not_null postgres_prod
> > pg_restore: [archiver (db)] could not execute query: ERROR:  constraint
> > "seqno_not_null" for relation "js_activity_20110101" already exists
> >Command was: ALTER TABLE "js_activity_20110101"
> >ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT
> VALID;
>
> Hm.  I'm guessing that table is a child table, and this has something to
> do with improper constraint inheritance.  Could we see psql \d+ output
> for both this table and its parent?  And for good measure, maybe the
> output of
>
> SELECT conname,convalidated,conislocal,coninhcount,connoinherit
> FROM pg_constraint WHERE conrelid = 'js_activity_20110101'::regclass;
>
> and likewise for the parent table.
>
> regards, tom lane
>

Sure this is not going to be very pretty but here you are:

proddb_testing=# \d+ js_activity
  Table
"public.js_activity"
  Column   |Type |
 Modifiers
| Storage  | Stats target | Description
---+-+-
+--+--+-
 id| text| not null default
nextval('pnl_fr_imported_trades_id_seq'::re
gclass) | extended |  |
 tid   | text| not null default
currval('pnl_fr_imported_trades_id_seq'::re
gclass) | extended |  |
 entry_time| timestamp without time zone | not null default
now()
| plain|  |
 pnl_date  | date| not null

| plain|  |
 activity_date | date| not null

| plain|  |
 activity_time | time without time zone  |

| plain|  |
 projected_settlement_date | date| not null

| plain|  |
 clearing_firm | text| not null

| extended |  |
 currency  | text| not null

| extended |  |
 account   | text| not null

| extended |  |
 security_code | text| not null

| extended |  |
 trade_type| text| not null

| extended |  |
 quantity  | numeric(28,8)   | not null

| main |  |
 gross_price   | numeric(28,8)   | not null default
0
| main |  |
 net_price | numeric(28,8)   | not null default
0
| main |  |
 net_cash  | numeric(28,8)   | not null default
0
| main |  |
 accrual   | numeric(28,8)   | not null default
0
| main |  |
 commission_amount | numeric(28,8)   | not null default
0
| main |  |
 commission_rate   | numeric(28,8)   | not null default
0
| main |  |
 narrative | text| not null default
''::text
| extended |  |
 counterparty  | text| not null

| extended |  |
 executing_exchange| text| not null default
''::text
| extended |  |
 route | text| not null default
''::text
| extended |  |
 source| text| not null default
''::text
| extended |  |
 tags  | hstore  | not null default
''::hstore
| extended |  |
 booking_type  | character(1)| not null default
'B'::bpchar
| extended |  |
 is_canceled   | text|

| extended |  |
 executing_broker  | text| not null default
''::text
| extended |  |
 version_tags  | hstore  | not null default
''::hstore
| extended |  |
 discretionary_agent   | text| not null default
''::text
| extended |

Re: [GENERAL] Problems with pg_upgrade after change of unix user running db.

[Tom Lane]

Benedikt Grundmann  writes:
> And it looks like now I'm back to the error that stopped me last time:
> pg_restore: [archiver (db)] Error from TOC entry 8425; 2606 416548282 CHECK
> CONSTRAINT seqno_not_null postgres_prod
> pg_restore: [archiver (db)] could not execute query: ERROR:  constraint
> "seqno_not_null" for relation "js_activity_20110101" already exists
>Command was: ALTER TABLE "js_activity_20110101"
>ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT VALID;

Hm.  I'm guessing that table is a child table, and this has something to
do with improper constraint inheritance.  Could we see psql \d+ output
for both this table and its parent?  And for good measure, maybe the
output of

SELECT conname,convalidated,conislocal,coninhcount,connoinherit
FROM pg_constraint WHERE conrelid = 'js_activity_20110101'::regclass;

and likewise for the parent table.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Problems with pg_upgrade after change of unix user running db.

[Benedikt Grundmann]

On 3 October 2016 at 15:30, Tom Lane  wrote:

> Benedikt Grundmann  writes:
> > On 3 October 2016 at 14:12, Tom Lane  wrote:
> >> You're going to need to manually drop that operator from the source
> >> database, as "=>" isn't a legal operator name anymore.  This appears
> >> to be left over from a pre-9.0 version of hstore.
>
> > Thanks for the quick reply.  How do I do that however?  Without dropping
> > the extension itself that is:
> >
> > proddb_testing=# drop operator => (text, text);
> > ERROR:  cannot drop operator =>(text,text) because extension hstore
> requires it
> > HINT:  You can drop extension hstore instead.
> >
> > Is it possible that I need to do some form of hstore extension upgrade
> > dance?
>
> Ah, I'd been guessing that the operator was "loose", but if you still
> have hstore 1.0 installed then yes that's the behavior I'd expect.
> You need to do "alter extension hstore update".  In a 9.2 database
> that should bring it to 1.1 which will get rid of the operator.
>
> regards, tom lane
>

And it looks like now I'm back to the error that stopped me last time:

[root@igm-dbc-001 ~]# cd /usr/local/home/as-proddb/upgrade-logs/
[root@igm-dbc-001 upgrade-logs]# tail pg_upgrade_dump_16416.log
pg_restore: creating CHECK CONSTRAINT "public.seqno_not_null"
pg_restore: creating CHECK CONSTRAINT "public.seqno_not_null"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 8425; 2606 416548282 CHECK
CONSTRAINT seqno_not_null postgres_prod
pg_restore: [archiver (db)] could not execute query: ERROR:  constraint
"seqno_not_null" for relation "js_activity_20110101" already exists
Command was: ALTER TABLE "js_activity_20110101"
ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT VALID;

I figured it might be because I'm on 9.2.*17* so I upgraded to 9.2.*18 *and
tried again but no luck:

[root@igm-dbc-001 upgrade-logs]# tail pg_upgrade_dump_16416.log
pg_restore: creating CHECK CONSTRAINT "public.seqno_not_null"
pg_restore: creating CHECK CONSTRAINT "public.seqno_not_null"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 8425; 2606 416548282 CHECK
CONSTRAINT seqno_not_null postgres_prod
pg_restore: [archiver (db)] could not execute query: ERROR:  constraint
"seqno_not_null" for relation "js_activity_20110101" already exists
Command was: ALTER TABLE "js_activity_20110101"
ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT VALID;

What do you need from me to debug this?

Re: [GENERAL] Problems with pg_upgrade after change of unix user running db.

[Tom Lane]

Benedikt Grundmann  writes:
> On 3 October 2016 at 14:12, Tom Lane  wrote:
>> You're going to need to manually drop that operator from the source
>> database, as "=>" isn't a legal operator name anymore.  This appears
>> to be left over from a pre-9.0 version of hstore.

> Thanks for the quick reply.  How do I do that however?  Without dropping
> the extension itself that is:
> 
> proddb_testing=# drop operator => (text, text);
> ERROR:  cannot drop operator =>(text,text) because extension hstore requires 
> it
> HINT:  You can drop extension hstore instead.
> 
> Is it possible that I need to do some form of hstore extension upgrade
> dance?

Ah, I'd been guessing that the operator was "loose", but if you still
have hstore 1.0 installed then yes that's the behavior I'd expect.
You need to do "alter extension hstore update".  In a 9.2 database
that should bring it to 1.1 which will get rid of the operator.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Problems with pg_upgrade after change of unix user running db.

[Benedikt Grundmann]

On 3 October 2016 at 14:12, Tom Lane  wrote:

> Benedikt Grundmann  writes:
> > I just tried this again.  This time from 9.2.17 to 9.5.4 and pg_upgrade
> > chokes with this:
> >
> > [root@igm-dbc-001 upgrade-logs]# tail pg_upgrade_dump_16416.log
> > pg_restore: [archiver (db)] could not execute query: ERROR:  syntax error
> > at or near "=>"
> > LINE 1: CREATE OPERATOR => (
> > ^
> > Command was: CREATE OPERATOR => (
> > PROCEDURE = "tconvert",
> > LEFTARG = "text",
> > RIGHTARG = "text"
> > );
>
> You're going to need to manually drop that operator from the source
> database, as "=>" isn't a legal operator name anymore.  This appears
> to be left over from a pre-9.0 version of hstore.
>
> regards, tom lane
>

Thanks for the quick reply.  How do I do that however?  Without dropping
the extension itself that is:

postgres=# select * from pg_operator where oprname = '=>';
(0 rows)
postgres=# \c proddb_testing
psql (9.5.4, server 9.2.17)
You are now connected to database "proddb_testing" as user "as-proddb".
proddb_testing=# select * from pg_operator where oprname = '=>';
-[ RECORD 1 ]+-
oprname  | =>
oprnamespace | 2200
oprowner | 10
oprkind  | b
oprcanmerge  | f
oprcanhash   | f
oprleft  | 25
oprright | 25
oprresult| 144920
oprcom   | 0
oprnegate| 0
oprcode  | tconvert
oprrest  | -
oprjoin  | -

proddb_testing=# drop operator => (text, text);
ERROR:  cannot drop operator =>(text,text) because extension hstore
requires it
HINT:  You can drop extension hstore instead.

Is it possible that I need to do some form of hstore extension upgrade
dance?

Re: [GENERAL] Problems with pg_upgrade after change of unix user running db.

[Tom Lane]

Benedikt Grundmann  writes:
> I just tried this again.  This time from 9.2.17 to 9.5.4 and pg_upgrade
> chokes with this:
> 
> [root@igm-dbc-001 upgrade-logs]# tail pg_upgrade_dump_16416.log
> pg_restore: [archiver (db)] could not execute query: ERROR:  syntax error
> at or near "=>"
> LINE 1: CREATE OPERATOR => (
> ^
> Command was: CREATE OPERATOR => (
> PROCEDURE = "tconvert",
> LEFTARG = "text",
> RIGHTARG = "text"
> );

You're going to need to manually drop that operator from the source
database, as "=>" isn't a legal operator name anymore.  This appears
to be left over from a pre-9.0 version of hstore.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Problems with pg_upgrade after change of unix user running db.

[Benedikt Grundmann]

On 30 November 2015 at 17:01, Bruce Momjian  wrote:

> On Mon, Nov 30, 2015 at 04:51:15PM +, Benedikt Grundmann wrote:
> > Are you able to compile from 9.4 git head and test that?  It seems
> > dumping inheriting constraints from parents has not worked properly
> for
> > some time.
> >
> >
> > Do I need to get the latest/head 9.2 or the latest/head 9.4 or both?
> For what
> > it is worth I just tried after upgrading to the latest released 9.2 (and
> same
> > 9.45) and that didn't work :-(
>
> You actually need non-released 9.4.X code that is in pg_dump, and we use
> 9.4 pg_dump to dump the 9.2 database.
>
> > I should certainly be able to compile from source.  But the upgrade to
> 9.4 is
> > by far not high on my priority stack (other than maybe some speed wins
> there is
> > nothing in 9.4 that we are eager for, there are some niceties but I can
> happily
> > live without all of them for years) and has already consumed way more
> time than
> > I had scheduled for it.  So I'll return to focus on other work for at
> least
> > this week and maybe more depending on how that work goes.
> >
> > Thanks to everyone I'll certainly update this thread if / when I have
> more time
> > to devote to this.
>
> The simplest solution is to wait for 9.4.6 to be released and test that.
>
>
I just tried this again.  This time from 9.2.17 to 9.5.4 and pg_upgrade
chokes with this:

[root@igm-dbc-001 upgrade-logs]# tail pg_upgrade_dump_16416.log
pg_restore: [archiver (db)] could not execute query: ERROR:  syntax error
at or near "=>"
LINE 1: CREATE OPERATOR => (
^
Command was: CREATE OPERATOR => (
PROCEDURE = "tconvert",
LEFTARG = "text",
RIGHTARG = "text"
);

-- For binary upgrade, hand...


Any pointers are appreciated.

Thanks,

Bene




> --
>   Bruce Momjian  http://momjian.us
>   EnterpriseDB http://enterprisedb.com
>
> + As you are, so once was I. As I am, so you will be. +
> + Roman grave inscription +
>

Re: [GENERAL] Problems with pg_upgrade after change of unix user running db.

[Bruce Momjian]

On Mon, Nov 30, 2015 at 04:51:15PM +, Benedikt Grundmann wrote:
> Are you able to compile from 9.4 git head and test that?  It seems
> dumping inheriting constraints from parents has not worked properly for
> some time.
> 
> 
> Do I need to get the latest/head 9.2 or the latest/head 9.4 or both?  For what
> it is worth I just tried after upgrading to the latest released 9.2 (and same
> 9.45) and that didn't work :-(

You actually need non-released 9.4.X code that is in pg_dump, and we use
9.4 pg_dump to dump the 9.2 database.

> I should certainly be able to compile from source.  But the upgrade to 9.4 is
> by far not high on my priority stack (other than maybe some speed wins there 
> is
> nothing in 9.4 that we are eager for, there are some niceties but I can 
> happily
> live without all of them for years) and has already consumed way more time 
> than
> I had scheduled for it.  So I'll return to focus on other work for at least
> this week and maybe more depending on how that work goes.
> 
> Thanks to everyone I'll certainly update this thread if / when I have more 
> time
> to devote to this.

The simplest solution is to wait for 9.4.6 to be released and test that.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Roman grave inscription +


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Problems with pg_upgrade after change of unix user running db.

[Bruce Momjian]

On Mon, Nov 30, 2015 at 08:08:50AM +, Benedikt Grundmann wrote:
> 
> 
> On Sat, Nov 28, 2015 at 2:39 AM, Adrian Klaver 
> wrote:
> 
> On 11/27/2015 06:07 PM, Tom Lane wrote:
> > Adrian Klaver  writes:
> >> On 11/27/2015 08:15 AM, Bruce Momjian wrote:
> >>> My guess is you are sharing the constraint name "seqno_not_null" with
> >>> multiple tables.  I think you are going to have to dig into the system
> >>> tables to see where that is referenced and fix it.
> >
> >> In the post below the OP shows the tables involved(they where
> inherited):
> >> http://www.postgresql.org/message-id/
> cadbmknm_y9ewdawdq_8dj1muc0z_fgwtyad2rwchgexj2jv...@mail.gmail.com
> >
> > Inherited eh?  Maybe related to 074c5cfbf.
> 
> 
> I forgot to mention this earlier. This cluster is running 9.2.6 and I'm
> attempting to upgrade to the latest 9.4.5

Well, 9.4.5 we released on October 8, 2015, and the commit mentioned happened
on November 20, 2015, so that fix is not in 9.4.5:

commit 074c5cfbfb4923158be9ccdb77420d6522d77538
Author: Tom Lane 
Date:   Fri Nov 20 14:55:28 2015 -0500

Fix handling of inherited check constraints in ALTER COLUMN TYPE (again).

The previous way of reconstructing check constraints was to do a separate
"ALTER TABLE ONLY tab ADD CONSTRAINT" for each table in an inheritance
hierarchy.  However, that way has no hope of reconstructing the check
constraints' own inheritance properties correctly, as pointed out in
bug #13779 from Jan Dirk Zijlstra.  What we should do instead is to do
a regular "ALTER TABLE", allowing recursion, at the topmost table that
has a particular constraint, and then suppress the work queue entries
for inherited instances of the constraint.

Annoyingly, we'd tried to fix this behavior before, in commit 5ed6546cf,
but we failed to notice that it wasn't reconstructing the pg_constraint
field values correctly.

As long as I'm touching pg_get_constraintdef_worker anyway, tweak it to
always schema-qualify the target table name; this seems like useful backup
to the protections installed by commit 5f173040.

In HEAD/9.5, get rid of get_constraint_relation_oids, which is now unused.
(I could alternatively have modified it to also return conislocal, but that
seemed like a pretty single-purpose API, so let's not pretend it has some
other use.)  It's unused in the back branches as well, but I left it in
place just in case some third-party code has decided to use it.

In HEAD/9.5, also rename pg_get_constraintdef_string to
pg_get_constraintdef_command, as the previous name did nothing to explain
what that entry point did differently from others (and its comment was
equally useless).  Again, that change doesn't seem like material for
back-patching.

I did a bit of re-pgindenting in tablecmds.c in HEAD/9.5, as well.

Otherwise, back-patch to all supported branches.

Are you able to compile from 9.4 git head and test that?  It seems
dumping inheriting contraints from parents has not worked properly for
some time.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Roman grave inscription +


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Problems with pg_upgrade after change of unix user running db.

[Benedikt Grundmann]

On Mon, Nov 30, 2015 at 4:29 PM, Bruce Momjian  wrote:

> On Mon, Nov 30, 2015 at 08:08:50AM +, Benedikt Grundmann wrote:
> >
> >
> > On Sat, Nov 28, 2015 at 2:39 AM, Adrian Klaver <
> adrian.kla...@aklaver.com>
> > wrote:
> >
> > On 11/27/2015 06:07 PM, Tom Lane wrote:
> > > Adrian Klaver  writes:
> > >> On 11/27/2015 08:15 AM, Bruce Momjian wrote:
> > >>> My guess is you are sharing the constraint name "seqno_not_null"
> with
> > >>> multiple tables.  I think you are going to have to dig into the
> system
> > >>> tables to see where that is referenced and fix it.
> > >
> > >> In the post below the OP shows the tables involved(they where
> > inherited):
> > >> http://www.postgresql.org/message-id/
> > cadbmknm_y9ewdawdq_8dj1muc0z_fgwtyad2rwchgexj2jv...@mail.gmail.com
> > >
> > > Inherited eh?  Maybe related to 074c5cfbf.
> >
> >
> > I forgot to mention this earlier. This cluster is running 9.2.6 and I'm
> > attempting to upgrade to the latest 9.4.5
>
> Well, 9.4.5 we released on October 8, 2015, and the commit mentioned
> happened
> on November 20, 2015, so that fix is not in 9.4.5:
>
> commit 074c5cfbfb4923158be9ccdb77420d6522d77538
> Author: Tom Lane 
> Date:   Fri Nov 20 14:55:28 2015 -0500
>
> Fix handling of inherited check constraints in ALTER COLUMN TYPE
> (again).
>
> The previous way of reconstructing check constraints was to do a
> separate
> "ALTER TABLE ONLY tab ADD CONSTRAINT" for each table in an inheritance
> hierarchy.  However, that way has no hope of reconstructing the check
> constraints' own inheritance properties correctly, as pointed out in
> bug #13779 from Jan Dirk Zijlstra.  What we should do instead is to do
> a regular "ALTER TABLE", allowing recursion, at the topmost table that
> has a particular constraint, and then suppress the work queue entries
> for inherited instances of the constraint.
>
> Annoyingly, we'd tried to fix this behavior before, in commit
> 5ed6546cf,
> but we failed to notice that it wasn't reconstructing the pg_constraint
> field values correctly.
>
> As long as I'm touching pg_get_constraintdef_worker anyway, tweak it to
> always schema-qualify the target table name; this seems like useful
> backup
> to the protections installed by commit 5f173040.
>
> In HEAD/9.5, get rid of get_constraint_relation_oids, which is now
> unused.
> (I could alternatively have modified it to also return conislocal, but
> that
> seemed like a pretty single-purpose API, so let's not pretend it has
> some
> other use.)  It's unused in the back branches as well, but I left it in
> place just in case some third-party code has decided to use it.
>
> In HEAD/9.5, also rename pg_get_constraintdef_string to
> pg_get_constraintdef_command, as the previous name did nothing to
> explain
> what that entry point did differently from others (and its comment was
> equally useless).  Again, that change doesn't seem like material for
> back-patching.
>
> I did a bit of re-pgindenting in tablecmds.c in HEAD/9.5, as well.
>
> Otherwise, back-patch to all supported branches.
>
> Are you able to compile from 9.4 git head and test that?  It seems
> dumping inheriting contraints from parents has not worked properly for
> some time.
>

Do I need to get the latest/head 9.2 or the latest/head 9.4 or both?  For
what it is worth I just tried after upgrading to the latest *released* 9.2
(and same 9.45) and that didn't work :-(

I should certainly be able to compile from source.  But the upgrade to 9.4
is by far not high on my priority stack (other than maybe some speed wins
there is nothing in 9.4 that we are eager for, there are some niceties but
I can happily live without all of them for years) and has already consumed
way more time than I had scheduled for it.  So I'll return to focus on
other work for at least this week and maybe more depending on how that work
goes.

Thanks to everyone I'll certainly update this thread if / when I have more
time to devote to this.

cheers,

Bene

Re: [GENERAL] Problems with pg_upgrade after change of unix user running db.

[Benedikt Grundmann]

On Sat, Nov 28, 2015 at 2:39 AM, Adrian Klaver 
wrote:

> On 11/27/2015 06:07 PM, Tom Lane wrote:
> > Adrian Klaver  writes:
> >> On 11/27/2015 08:15 AM, Bruce Momjian wrote:
> >>> My guess is you are sharing the constraint name "seqno_not_null" with
> >>> multiple tables.  I think you are going to have to dig into the system
> >>> tables to see where that is referenced and fix it.
> >
> >> In the post below the OP shows the tables involved(they where
> inherited):
> >>
> http://www.postgresql.org/message-id/cadbmknm_y9ewdawdq_8dj1muc0z_fgwtyad2rwchgexj2jv...@mail.gmail.com
> >
> > Inherited eh?  Maybe related to 074c5cfbf.
>
> I forgot to mention this earlier. This cluster is running 9.2.6 and I'm
attempting to upgrade to the latest 9.4.5


> From the OP's post:
>
>
> The error:
>
> pg_restore: creating CHECK CONSTRAINT seqno_not_null
> pg_restore: creating CHECK CONSTRAINT seqno_not_null
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> pg_restore: [archiver (db)] Error from TOC entry 8359; 2606 416548282 CHECK
> CONSTRAINT seqno_not_null postgres_prod
> pg_restore: [archiver (db)] could not execute query: ERROR:  constraint
> "seqno_not_null" for relation "js_activity_2011" already exists
> Command was: ALTER TABLE "js_activity_2011"
> ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT
> VALID;
>
> The setup:
>
> postgres_prod(at)proddb_testing=# select c.conname, c.conislocal,
> c.coninhcount, c.convalidated as valid, (select relname from pg_class where
> oid = c.conrelid) from pg_constraint c where conname = 'seqno_not_null'
> order by relname;
> conname │ conislocal │ coninhcount │ valid │ relname
> ┼┼─┼───┼──
>  seqno_not_null │ t  │   0 │ f │ js_activity
>  seqno_not_null │ t  │   1 │ f │ js_activity_2009
>  seqno_not_null │ t  │   1 │ f │ js_activity_2010
>  seqno_not_null │ t  │   1 │ f │ js_activity_2011
>  seqno_not_null │ f  │   1 │ f │ js_activity_2012
>  seqno_not_null │ f  │   1 │ t │ js_activity_2013
>  seqno_not_null │ f  │   1 │ t │ js_activity_2014
>  seqno_not_null │ f  │   1 │ f │ js_activity_tip
>
>
>
> [as-proddb(at)nyc-dbc-001 upgrade-logs]$ pg_restore
> pg_upgrade_dump_16416.custom | fgrep seqno_not_null -B 1
> -- For binary upgrade, set up inherited constraint.
> ALTER TABLE ONLY "js_activity_2013"  ADD CONSTRAINT "seqno_not_null" CHECK
> (("seqno" IS NOT NULL));
> --
> SET conislocal = false
> WHERE contype = 'c' AND conname = 'seqno_not_null'
> --
> -- For binary upgrade, set up inherited constraint.
> ALTER TABLE ONLY "js_activity_2014"  ADD CONSTRAINT "seqno_not_null" CHECK
> (("seqno" IS NOT NULL));
> --
> SET conislocal = false
> WHERE contype = 'c' AND conname = 'seqno_not_null'
> --
> --
> -- Name: seqno_not_null; Type: CHECK CONSTRAINT; Schema: public; Owner:
> postgres_prod
> --
> ALTER TABLE "js_activity"
> ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT
> VALID;
> --
> --
> -- Name: seqno_not_null; Type: CHECK CONSTRAINT; Schema: public; Owner:
> postgres_prod
> --
> ALTER TABLE "js_activity_2011"
> ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT
> VALID;
> --
> --
> -- Name: seqno_not_null; Type: CHECK CONSTRAINT; Schema: public; Owner:
> postgres_prod
> --
> ALTER TABLE "js_activity_2010"
> ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT
> VALID;
> --
> --
> -- Name: seqno_not_null; Type: CHECK CONSTRAINT; Schema: public; Owner:
> postgres_prod
> --
> ALTER TABLE "js_activity_2009"
> ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT
> VALID;
>
>
>
>
>
> >
> >   regards, tom lane
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] Problems with pg_upgrade after change of unix user running db.

[Adrian Klaver]

On 11/27/2015 08:05 AM, Benedikt Grundmann wrote:



On Fri, Nov 27, 2015 at 4:04 PM, Bruce Momjian > wrote:

On Fri, Nov 27, 2015 at 09:38:54AM +, Benedikt Grundmann wrote:
> That worked (I also swapped the password columns so that I don't have to 
change
> pgpass entries).  But I then ran into a different problem a little later 
on.  I
> thought I quickly mention it here in case somebody can point me into the 
right
> direction:
>
...
> Restoring database schemas in the new cluster
>
> *failure*
> Consult the last few lines of "pg_upgrade_dump_16416.log" for
> the probable cause of the failure.
> child worker exited abnormally: Invalid argument
>
> *failure*
> Consult the last few lines of "pg_upgrade_server.log" for
> the probable cause of the failure.
>
> [as-proddb@nyc-dbc-001 upgrade-logs]$ tail pg_upgrade_dump_16416.log
> pg_restore: creating CHECK CONSTRAINT seqno_not_null
> pg_restore: creating CHECK CONSTRAINT seqno_not_null
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> pg_restore: [archiver (db)] Error from TOC entry 8359; 2606 416548282 
CHECK
> CONSTRAINT seqno_not_null postgres_prod
> pg_restore: [archiver (db)] could not execute query: ERROR:  constraint
> "seqno_not_null" for relation "js_activity_2011" already exists
> Command was: ALTER TABLE "js_activity_2011"
> ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT 
VALID;

I have no idea, but this is a pg_dump bug or inconsistent system tables,
rather than a pg_upgrade-specific bug.


Any recommendation on how to proceed?



Not sure that it matters, from one of your previous posts:

"
*failure*
Consult the last few lines of "pg_upgrade_server.log" for
the probable cause of the failure."

What do the last lines in pg_upgrade_server.log show?

--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Problems with pg_upgrade after change of unix user running db.

[Benedikt Grundmann]

On Fri, Nov 27, 2015 at 4:04 PM, Bruce Momjian  wrote:

> On Fri, Nov 27, 2015 at 09:38:54AM +, Benedikt Grundmann wrote:
> > That worked (I also swapped the password columns so that I don't have to
> change
> > pgpass entries).  But I then ran into a different problem a little later
> on.  I
> > thought I quickly mention it here in case somebody can point me into the
> right
> > direction:
> >
> ...
> > Restoring database schemas in the new cluster
> >
> > *failure*
> > Consult the last few lines of "pg_upgrade_dump_16416.log" for
> > the probable cause of the failure.
> > child worker exited abnormally: Invalid argument
> >
> > *failure*
> > Consult the last few lines of "pg_upgrade_server.log" for
> > the probable cause of the failure.
> >
> > [as-proddb@nyc-dbc-001 upgrade-logs]$ tail pg_upgrade_dump_16416.log
> > pg_restore: creating CHECK CONSTRAINT seqno_not_null
> > pg_restore: creating CHECK CONSTRAINT seqno_not_null
> > pg_restore: [archiver (db)] Error while PROCESSING TOC:
> > pg_restore: [archiver (db)] Error from TOC entry 8359; 2606 416548282
> CHECK
> > CONSTRAINT seqno_not_null postgres_prod
> > pg_restore: [archiver (db)] could not execute query: ERROR:  constraint
> > "seqno_not_null" for relation "js_activity_2011" already exists
> > Command was: ALTER TABLE "js_activity_2011"
> > ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT
> VALID;
>
> I have no idea, but this is a pg_dump bug or inconsistent system tables,
> rather than a pg_upgrade-specific bug.
>

Any recommendation on how to proceed?

Re: [GENERAL] Problems with pg_upgrade after change of unix user running db.

[Bruce Momjian]

On Fri, Nov 27, 2015 at 04:05:46PM +, Benedikt Grundmann wrote:
> > [as-proddb@nyc-dbc-001 upgrade-logs]$ tail pg_upgrade_dump_16416.log
> > pg_restore: creating CHECK CONSTRAINT seqno_not_null
> > pg_restore: creating CHECK CONSTRAINT seqno_not_null
> > pg_restore: [archiver (db)] Error while PROCESSING TOC:
> > pg_restore: [archiver (db)] Error from TOC entry 8359; 2606 416548282
> CHECK
> > CONSTRAINT seqno_not_null postgres_prod
> > pg_restore: [archiver (db)] could not execute query: ERROR:  constraint
> > "seqno_not_null" for relation "js_activity_2011" already exists
> >     Command was: ALTER TABLE "js_activity_2011"
> >     ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT
> VALID;
> 
> I have no idea, but this is a pg_dump bug or inconsistent system tables,
> rather than a pg_upgrade-specific bug.
> 
> 
> Any recommendation on how to proceed?

My guess is you are sharing the constraint name "seqno_not_null" with
multiple tables.  I think you are going to have to dig into the system
tables to see where that is referenced and fix it.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Roman grave inscription +


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Problems with pg_upgrade after change of unix user running db.

[Bruce Momjian]

On Fri, Nov 27, 2015 at 09:38:54AM +, Benedikt Grundmann wrote:
> That worked (I also swapped the password columns so that I don't have to 
> change
> pgpass entries).  But I then ran into a different problem a little later on.  
> I
> thought I quickly mention it here in case somebody can point me into the right
> direction:
>
...
> Restoring database schemas in the new cluster
> 
> *failure*
> Consult the last few lines of "pg_upgrade_dump_16416.log" for
> the probable cause of the failure.
> child worker exited abnormally: Invalid argument
> 
> *failure*
> Consult the last few lines of "pg_upgrade_server.log" for
> the probable cause of the failure.
> 
> [as-proddb@nyc-dbc-001 upgrade-logs]$ tail pg_upgrade_dump_16416.log
> pg_restore: creating CHECK CONSTRAINT seqno_not_null
> pg_restore: creating CHECK CONSTRAINT seqno_not_null
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> pg_restore: [archiver (db)] Error from TOC entry 8359; 2606 416548282 CHECK
> CONSTRAINT seqno_not_null postgres_prod
> pg_restore: [archiver (db)] could not execute query: ERROR:  constraint
> "seqno_not_null" for relation "js_activity_2011" already exists
>     Command was: ALTER TABLE "js_activity_2011"
>     ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT VALID;

I have no idea, but this is a pg_dump bug or inconsistent system tables,
rather than a pg_upgrade-specific bug.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Roman grave inscription +


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Problems with pg_upgrade after change of unix user running db.

[Tom Lane]

Adrian Klaver  writes:
> On 11/27/2015 08:15 AM, Bruce Momjian wrote:
>> My guess is you are sharing the constraint name "seqno_not_null" with
>> multiple tables.  I think you are going to have to dig into the system
>> tables to see where that is referenced and fix it.

> In the post below the OP shows the tables involved(they where inherited):
> http://www.postgresql.org/message-id/cadbmknm_y9ewdawdq_8dj1muc0z_fgwtyad2rwchgexj2jv...@mail.gmail.com

Inherited eh?  Maybe related to 074c5cfbf.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Problems with pg_upgrade after change of unix user running db.

[Adrian Klaver]

On 11/27/2015 06:07 PM, Tom Lane wrote:
> Adrian Klaver  writes:
>> On 11/27/2015 08:15 AM, Bruce Momjian wrote:
>>> My guess is you are sharing the constraint name "seqno_not_null" with
>>> multiple tables.  I think you are going to have to dig into the system
>>> tables to see where that is referenced and fix it.
> 
>> In the post below the OP shows the tables involved(they where inherited):
>> http://www.postgresql.org/message-id/cadbmknm_y9ewdawdq_8dj1muc0z_fgwtyad2rwchgexj2jv...@mail.gmail.com
> 
> Inherited eh?  Maybe related to 074c5cfbf.

>From the OP's post:


The error:

pg_restore: creating CHECK CONSTRAINT seqno_not_null
pg_restore: creating CHECK CONSTRAINT seqno_not_null
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 8359; 2606 416548282 CHECK
CONSTRAINT seqno_not_null postgres_prod
pg_restore: [archiver (db)] could not execute query: ERROR:  constraint
"seqno_not_null" for relation "js_activity_2011" already exists
Command was: ALTER TABLE "js_activity_2011"
ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT VALID;

The setup:

postgres_prod(at)proddb_testing=# select c.conname, c.conislocal,
c.coninhcount, c.convalidated as valid, (select relname from pg_class where
oid = c.conrelid) from pg_constraint c where conname = 'seqno_not_null'
order by relname;
conname │ conislocal │ coninhcount │ valid │ relname
┼┼─┼───┼──
 seqno_not_null │ t  │   0 │ f │ js_activity
 seqno_not_null │ t  │   1 │ f │ js_activity_2009
 seqno_not_null │ t  │   1 │ f │ js_activity_2010
 seqno_not_null │ t  │   1 │ f │ js_activity_2011
 seqno_not_null │ f  │   1 │ f │ js_activity_2012
 seqno_not_null │ f  │   1 │ t │ js_activity_2013
 seqno_not_null │ f  │   1 │ t │ js_activity_2014
 seqno_not_null │ f  │   1 │ f │ js_activity_tip



[as-proddb(at)nyc-dbc-001 upgrade-logs]$ pg_restore
pg_upgrade_dump_16416.custom | fgrep seqno_not_null -B 1
-- For binary upgrade, set up inherited constraint.
ALTER TABLE ONLY "js_activity_2013"  ADD CONSTRAINT "seqno_not_null" CHECK
(("seqno" IS NOT NULL));
--
SET conislocal = false
WHERE contype = 'c' AND conname = 'seqno_not_null'
--
-- For binary upgrade, set up inherited constraint.
ALTER TABLE ONLY "js_activity_2014"  ADD CONSTRAINT "seqno_not_null" CHECK
(("seqno" IS NOT NULL));
--
SET conislocal = false
WHERE contype = 'c' AND conname = 'seqno_not_null'
--
--
-- Name: seqno_not_null; Type: CHECK CONSTRAINT; Schema: public; Owner:
postgres_prod
--
ALTER TABLE "js_activity"
ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT VALID;
--
--
-- Name: seqno_not_null; Type: CHECK CONSTRAINT; Schema: public; Owner:
postgres_prod
--
ALTER TABLE "js_activity_2011"
ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT VALID;
--
--
-- Name: seqno_not_null; Type: CHECK CONSTRAINT; Schema: public; Owner:
postgres_prod
--
ALTER TABLE "js_activity_2010"
ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT VALID;
--
--
-- Name: seqno_not_null; Type: CHECK CONSTRAINT; Schema: public; Owner:
postgres_prod
--
ALTER TABLE "js_activity_2009"
ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT VALID;





> 
>   regards, tom lane
> 


-- 
Adrian Klaver
adrian.kla...@aklaver.com


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Problems with pg_upgrade after change of unix user running db.

[Adrian Klaver]

On 11/27/2015 08:15 AM, Bruce Momjian wrote:
> On Fri, Nov 27, 2015 at 04:05:46PM +, Benedikt Grundmann wrote:
>>  > [as-proddb@nyc-dbc-001 upgrade-logs]$ tail pg_upgrade_dump_16416.log
>>  > pg_restore: creating CHECK CONSTRAINT seqno_not_null
>>  > pg_restore: creating CHECK CONSTRAINT seqno_not_null
>>  > pg_restore: [archiver (db)] Error while PROCESSING TOC:
>>  > pg_restore: [archiver (db)] Error from TOC entry 8359; 2606 416548282
>>  CHECK
>>  > CONSTRAINT seqno_not_null postgres_prod
>>  > pg_restore: [archiver (db)] could not execute query: ERROR:  
>> constraint
>>  > "seqno_not_null" for relation "js_activity_2011" already exists
>>  > Command was: ALTER TABLE "js_activity_2011"
>>  > ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT
>>  VALID;
>>
>>  I have no idea, but this is a pg_dump bug or inconsistent system tables,
>>  rather than a pg_upgrade-specific bug.
>>
>>
>> Any recommendation on how to proceed?
> 
> My guess is you are sharing the constraint name "seqno_not_null" with
> multiple tables.  I think you are going to have to dig into the system
> tables to see where that is referenced and fix it.
> 

In the post below the OP shows the tables involved(they where inherited):

http://www.postgresql.org/message-id/cadbmknm_y9ewdawdq_8dj1muc0z_fgwtyad2rwchgexj2jv...@mail.gmail.com

Still I did not think there was a restriction that constraint names be unique
across a database or cluster:

test=> create table tbl_a (fld_1 int CONSTRAINT fld_1_chk CHECK(fld_1 > 0) );
CREATE TABLE

test=> create table tbl_b (fld_1 int CONSTRAINT fld_1_chk CHECK(fld_1 > 0) );
CREATE TABLE

test=> \d tbl_* 


 Table "public.tbl_a"   


 Column |  Type   | Modifiers   


+-+---  


 fld_1  | integer | 


Check constraints:  


"fld_1_chk" CHECK (fld_1 > 0)   





 Table "public.tbl_b"   


 Column |  Type   | Modifiers   


+-+---  


 fld_1  | integer | 


Check constraints:  


"fld_1_chk" CHECK (fld_1 > 0) 

-- 
Adrian Klaver
adrian.kla...@aklaver.com


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Problems with pg_upgrade after change of unix user running db.

[Benedikt Grundmann]

On Wed, Nov 25, 2015 at 2:43 PM, Bruce Momjian  wrote:

> On Wed, Nov 25, 2015 at 08:04:49AM +, Benedikt Grundmann wrote:
> > You can see the 9.5 requirements in the pg_upgrade function
> > check_is_install_user().  You might as well just honor what that
> > requires as you will eventually be moving to 9.5.
> >
> >
> > Thanks I'll try this in one of the next days.  Sorry for the radio
> silence in
> > the last 2 days.  We have been quite busy at work.  I don't think I
> understand
>
> Sure, no problem.  I would have liked to reply to this sooner too, but
> had to do some research.
>

That worked (I also swapped the password columns so that I don't have to
change pgpass entries).  But I then ran into a different problem a little
later on.  I thought I quickly mention it here in case somebody can point
me into the right direction:

...
Performing Upgrade
--
Analyzing all rows in the new cluster   ok
Freezing all rows on the new clusterok
Deleting files from new pg_clog ok
Copying old pg_clog to new server   ok
Setting next transaction ID and epoch for new cluster   ok
Deleting files from new pg_multixact/offsetsok
Setting oldest multixact ID on new cluster  ok
Resetting WAL archives  ok
Setting frozenxid and minmxid counters in new cluster   ok
Restoring global objects in the new cluster ok
Adding support functions to new cluster ok
Restoring database schemas in the new cluster

*failure*
Consult the last few lines of "pg_upgrade_dump_16416.log" for
the probable cause of the failure.
child worker exited abnormally: Invalid argument

*failure*
Consult the last few lines of "pg_upgrade_server.log" for
the probable cause of the failure.

[as-proddb@nyc-dbc-001 upgrade-logs]$ tail pg_upgrade_dump_16416.log
pg_restore: creating CHECK CONSTRAINT seqno_not_null
pg_restore: creating CHECK CONSTRAINT seqno_not_null
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 8359; 2606 416548282 CHECK
CONSTRAINT seqno_not_null postgres_prod
pg_restore: [archiver (db)] could not execute query: ERROR:  constraint
"seqno_not_null" for relation "js_activity_2011" already exists
Command was: ALTER TABLE "js_activity_2011"
ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT VALID;


Now js_activity is the parent table and js_activity_* are all child tables
(for partitioning):

postgres_prod@proddb_testing=# select c.conname, c.conislocal,
c.coninhcount, c.convalidated as valid, (select relname from pg_class where
oid = c.conrelid) from pg_constraint c where conname = 'seqno_not_null'
order by relname;
conname │ conislocal │ coninhcount │ valid │ relname
┼┼─┼───┼──
 seqno_not_null │ t  │   0 │ f │ js_activity
 seqno_not_null │ t  │   1 │ f │ js_activity_2009
 seqno_not_null │ t  │   1 │ f │ js_activity_2010
 seqno_not_null │ t  │   1 │ f │ js_activity_2011
 seqno_not_null │ f  │   1 │ f │ js_activity_2012
 seqno_not_null │ f  │   1 │ t │ js_activity_2013
 seqno_not_null │ f  │   1 │ t │ js_activity_2014
 seqno_not_null │ f  │   1 │ f │ js_activity_tip



[as-proddb@nyc-dbc-001 upgrade-logs]$ pg_restore
pg_upgrade_dump_16416.custom | fgrep seqno_not_null -B 1
-- For binary upgrade, set up inherited constraint.
ALTER TABLE ONLY "js_activity_2013"  ADD CONSTRAINT "seqno_not_null" CHECK
(("seqno" IS NOT NULL));
--
SET conislocal = false
WHERE contype = 'c' AND conname = 'seqno_not_null'
--
-- For binary upgrade, set up inherited constraint.
ALTER TABLE ONLY "js_activity_2014"  ADD CONSTRAINT "seqno_not_null" CHECK
(("seqno" IS NOT NULL));
--
SET conislocal = false
WHERE contype = 'c' AND conname = 'seqno_not_null'
--
--
-- Name: seqno_not_null; Type: CHECK CONSTRAINT; Schema: public; Owner:
postgres_prod
--
ALTER TABLE "js_activity"
ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT VALID;
--
--
-- Name: seqno_not_null; Type: CHECK CONSTRAINT; Schema: public; Owner:
postgres_prod
--
ALTER TABLE "js_activity_2011"
ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT VALID;
--
--
-- Name: seqno_not_null; Type: CHECK CONSTRAINT; Schema: public; Owner:
postgres_prod
--
ALTER TABLE "js_activity_2010"
ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT VALID;
--
--
-- Name: seqno_not_null; Type: CHECK CONSTRAINT; Schema: public; Owner:
postgres_prod
--
ALTER TABLE "js_activity_2009"
ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT VALID;

Again thanks in advance,

Bene

Re: [GENERAL] Problems with pg_upgrade after change of unix user running db.

[Benedikt Grundmann]

On Tue, Nov 24, 2015 at 8:04 PM, Bruce Momjian  wrote:

> On Mon, Nov 23, 2015 at 11:12:25AM +, Benedikt Grundmann wrote:
> > I got this error trying to upgrade one of our database clusters (happily
> in
> > testing) from 9.2 to 9.4:
> >
> > Old and new cluster install users have different values for pg_authid.oid
> >
> > Important background here is that we used to run the database as the
> postgres
> > unix user, but recently we had changed it to run as a different user
> (because
> > we have several different databases all running as the postgres user on
> > different machines and we wanted each logically separate database to run
> as a
> > different extra for that purpose unix user -- this simplified internal
> > administration management).
> >
> > We had done this by adding a new superuser to the database (with the
> name of
> > the unix user it will run as in the future). turning off the database,
> chown -R
> >  databasedir, starting the database
>
> Your description is very clear.  In 9.4 and earlier, Postgres checks
> that the user running upgrade has the same pg_authid.oid in the old and
> new clusters.  In 9.5 we check that the user is the
> BOOTSTRAP_SUPERUSERID (10) on both the old and new cluster.
>
> Therefore, what I suggest you do, before running pg_upgrade, is to
> rename the pg_authid.oid = 10 row to be your new install user instead of
> 'postgres', and make your new user row equal 'postgres', e.g. something
> like:
>
> --  You already did this first one
> --> test=> create user my_new_install_user;
> --> CREATE ROLE
>
> select oid from pg_authid where rolname = 'my_new_install_user';
>   oid
> ---
>  16385
> (1 row)
>
> select oid from pg_authid where rolname = 'postgres';
>  oid
> -
>   10
> (1 row)
>
> -- 'XXX' prevents duplicate names
> update pg_authid set rolname = 'XXX' where oid = 10;
> UPDATE 1
> update pg_authid set rolname = 'postgres' where oid = 16385;
> UPDATE 1
> update pg_authid set rolname = 'my_new_install_user' where oid =
> 10;
> UPDATE 1
>
> What this does it to make your new install user the bootstrap user,
> which is a requirement for 9.5 pg_upgrade.  You would do this _before_
> running pg_upgrade as my_new_install_user.  However, keep in mind that
> once you do this, everthing owned by my_new_install_user and postgres
> are now swapped.  This is basically what you need to do after changing
> the ownership of the Postgres file system files.
>
> You can see the 9.5 requirements in the pg_upgrade function
> check_is_install_user().  You might as well just honor what that
> requires as you will eventually be moving to 9.5.
>

Thanks I'll try this in one of the next days.  Sorry for the radio silence
in the last 2 days.  We have been quite busy at work.  I don't think I
understand yet why this restriction exists (Neither the old nor the new).
Is there some doc somewhere that explains what's going on?  I tried to find
something in the otherwise excellent postgres docs but failed.




>
> --
>   Bruce Momjian  http://momjian.us
>   EnterpriseDB http://enterprisedb.com
>
> + As you are, so once was I. As I am, so you will be. +
> + Roman grave inscription +
>

Re: [GENERAL] Problems with pg_upgrade after change of unix user running db.

[Bruce Momjian]

On Wed, Nov 25, 2015 at 08:04:49AM +, Benedikt Grundmann wrote:
> You can see the 9.5 requirements in the pg_upgrade function
> check_is_install_user().  You might as well just honor what that
> requires as you will eventually be moving to 9.5.
> 
> 
> Thanks I'll try this in one of the next days.  Sorry for the radio silence in
> the last 2 days.  We have been quite busy at work.  I don't think I understand

Sure, no problem.  I would have liked to reply to this sooner too, but
had to do some research.

> yet why this restriction exists (Neither the old nor the new).  Is there some
> doc somewhere that explains what's going on?  I tried to find something in the
> otherwise excellent postgres docs but failed.

The comments at the top of pg_upgrade.c do explain this:

 *  To simplify the upgrade process, we force certain system values to be
 *  identical between old and new clusters:
 *
 *  We control all assignments of pg_class.oid (and relfilenode) so toast
 *  oids are the same between old and new clusters.  This is important
 *  because toast oids are stored as toast pointers in user tables.
 *
 *  While pg_class.oid and pg_class.relfilenode are initially the same
 *  in a cluster, they can diverge due to CLUSTER, REINDEX, or VACUUM
 *  FULL.  In the new cluster, pg_class.oid and pg_class.relfilenode will
 *  be the same and will match the old pg_class.oid value.  Because of
 *  this, old/new pg_class.relfilenode values will not match if CLUSTER,
 *  REINDEX, or VACUUM FULL have been performed in the old cluster.
 *
 *  We control all assignments of pg_type.oid because these oids are stored
 *  in user composite type values.
 *
 *  We control all assignments of pg_enum.oid because these oids are stored
 *  in user tables as enum values.
 *
 *  We control all assignments of pg_authid.oid because these oids are stored  
<---
 *  in pg_largeobject_metadata.
<---

I never expected users to care, but based on what you did, you obviously
did need to care.  The good news is that the system generated an error
message that helped diagnose the problem, and the 9.5 error message is
much clearer.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Roman grave inscription +


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Problems with pg_upgrade after change of unix user running db.

[Bruce Momjian]

On Mon, Nov 23, 2015 at 11:12:25AM +, Benedikt Grundmann wrote:
> I got this error trying to upgrade one of our database clusters (happily in
> testing) from 9.2 to 9.4:
> 
> Old and new cluster install users have different values for pg_authid.oid
> 
> Important background here is that we used to run the database as the postgres
> unix user, but recently we had changed it to run as a different user (because
> we have several different databases all running as the postgres user on
> different machines and we wanted each logically separate database to run as a
> different extra for that purpose unix user -- this simplified internal
> administration management).
> 
> We had done this by adding a new superuser to the database (with the name of
> the unix user it will run as in the future). turning off the database, chown 
> -R
>  databasedir, starting the database

Your description is very clear.  In 9.4 and earlier, Postgres checks
that the user running upgrade has the same pg_authid.oid in the old and
new clusters.  In 9.5 we check that the user is the
BOOTSTRAP_SUPERUSERID (10) on both the old and new cluster.

Therefore, what I suggest you do, before running pg_upgrade, is to
rename the pg_authid.oid = 10 row to be your new install user instead of
'postgres', and make your new user row equal 'postgres', e.g. something
like:

--  You already did this first one
--> test=> create user my_new_install_user;
--> CREATE ROLE

select oid from pg_authid where rolname = 'my_new_install_user';
  oid
---
 16385
(1 row)

select oid from pg_authid where rolname = 'postgres';
 oid
-
  10
(1 row)

-- 'XXX' prevents duplicate names
update pg_authid set rolname = 'XXX' where oid = 10;
UPDATE 1
update pg_authid set rolname = 'postgres' where oid = 16385;
UPDATE 1
update pg_authid set rolname = 'my_new_install_user' where oid = 10;
UPDATE 1

What this does it to make your new install user the bootstrap user,
which is a requirement for 9.5 pg_upgrade.  You would do this _before_
running pg_upgrade as my_new_install_user.  However, keep in mind that
once you do this, everthing owned by my_new_install_user and postgres
are now swapped.  This is basically what you need to do after changing
the ownership of the Postgres file system files.

You can see the 9.5 requirements in the pg_upgrade function
check_is_install_user().  You might as well just honor what that
requires as you will eventually be moving to 9.5.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Roman grave inscription +


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Problems with pg_upgrade after change of unix user running db.

[Benedikt Grundmann]

I got this error trying to upgrade one of our database clusters (happily in
testing) from 9.2 to 9.4:

Old and new cluster install users have different values for pg_authid.oid

Important background here is that we used to run the database as the
postgres unix user, but recently we had changed it to run as a different
user (because we have several different databases all running as the
postgres user on different machines and we wanted each logically separate
database to run as a different extra for that purpose unix user -- this
simplified internal administration management).

We had done this by adding a new superuser to the database (with the name
of the unix user it will run as in the future). turning off the database,
chown -R  databasedir, starting the database

(and adapting the startup scripts accordingly).

What I wasn't aware of is that postgres knows which user was used to run
pg_init.

So my first attempt at upgrading by running the below as the new user

old_loc=/mnt/dbc03-d1/proddb/postgres
new_loc=/mnt/dbc03-d1/proddb-94/postgres

rm -rf $new_loc/*
/usr/pgsql-9.4/bin/initdb $new_loc
self-service stop postgresql-9.2

/usr/pgsql-9.4/bin/pg_upgrade \
  -k \
  -j 8 \
  --old-datadir $old_loc \
  --new-datadir $new_loc \
  --old-bindir /usr/pgsql-9.2/bin \
  --new-bindir /usr/pgsql-9.4/bin

Failed with the above "Old and new cluster ..."

In my next attempt I tried adding the bold to the initdb command

/usr/pgsql-9.4/bin/initdb $new_loc *-U postgres*

But that eventually fails during pg_upgrade with:

connection to database failed: FATAL role "" does not exist

could not connect to new postmaster started with the command:
/usr/pgsql-9.4/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D
"/mnt/dbc03-d1/proddb-94/...

last thing it prints before that is

Creating dump of database schemas
  
ok

So I would love to know what the recommended way to go forward is.  Ideally
it avoids using the old postgres unix
and database user (we want to completely get rid of it eventually, but if I
have to do some additional one off work this
time to get past this hurdle using the user that is of course an acceptable
trade off).

Thanks in advance,

Bene

Re: [GENERAL] Problems with pg_upgrade after change of unix user running db.

[Jim Nasby]

On 11/23/15 5:12 AM, Benedikt Grundmann wrote:

So I would love to know what the recommended way to go forward is.
Ideally it avoids using the old postgres unix
and database user (we want to completely get rid of it eventually, but
if I have to do some additional one off work this
time to get past this hurdle using the user that is of course an
acceptable trade off).


Can you provide a test script that demonstrates the problem?

The unix user that runs the database processes is pretty inconsequential 
to Postgres (as long as it's not root). There's generally no need for 
any correlation between unix accounts and database accounts. initdb uses 
the unix account running initdb for the initial database account, but as 
you've seen you can over-ride that.


pg_upgrade does care about unix accounts though (since it's dealing 
directly with the file system). It also needs to be able to connect to 
both databases, so it needs valid database login credentials as well.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general