Re: Restore from dumps

[Nicola Contu]

That worked.
Thanks guys.

2018-07-25 16:33 GMT+02:00 Nicola Contu :

> yeah, we updated that function in production to says public.all_days.
> I will let you know at the next restore.
>
> Thanks guys, appreciated.
>
> 2018-07-25 16:28 GMT+02:00 Tom Lane :
>
>> Laurenz Albe  writes:
>> > Nicola Contu wrote:
>> >> pg_restore: [archiver (db)] could not execute query: ERROR:  relation
>> "all_days" does not exist
>> >> LINE 3: from all_days
>> >> ^
>> >> QUERY:
>> >> select count(*)::numeric
>> >> from all_days
>> >> where (("date" between $2::date and $1::date) or ("date" between
>> $1::date and $2::date))
>> >> and dow not in (0,6)
>> >>
>> >> CONTEXT:  SQL function "bdays" during inlining
>>
>> > Try to ALTER the materialized view so that it refers to
>> "public.all_days"
>> > rather than "all_days".
>>
>> Per the error message, what needs fixing is the SQL function "bdays",
>> not the matview as such.
>>
>> regards, tom lane
>>
>
>

Re: Permission denied on schema for all users on insert to table with fk

[Adrian Klaver]

On 07/26/2018 03:52 AM, Leland Weathers wrote:









And a more complete example of what we are seeing with multiple
accounts. This particular set is from an account that has their role
set to that of the database & schema owner which is different than
the table owner role.

__

__ __

system=# insert into results.batch (hostname, assemblyversion)
VALUES ('mycomp','0.0..0');

INSERT 0 1

system=# select lastval();

lastval

-

    6

(1 row)

__ __

__ __

system=# INSERT INTO results.historyitem
(batchid,datasourceid,sequence_order)

system-# VALUES

system-# (6,20,1);

ERROR:  permission denied for schema results

LINE 1: SELECT 1 FROM ONLY "results"."batch"...

    ^

QUERY:  SELECT 1 FROM ONLY "results"."batch" x WHERE "id"
OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x

system=# SELECT FROM results.batch WHERE id=6;

--

(1 row)


What does \dn+ results show?

Before you mentioned a trigger. I am not seeing that in the schema you 
sent. Is there one and if so what is it's definition and that of its 
associated function?


What does show?:

select session_user, current_user;

INSERT INTO results.historyitem (batchid,datasourceid,sequence_order) 
VALUES (6,20,1);



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

Re: Permission denied on schema for all users on insert to table with fk

[Adrian Klaver]

On 07/26/2018 06:57 AM, Leland Weathers wrote:




Before you mentioned a trigger. I am not seeing that in the schema
you sent. Is there one and if so what is it's definition and that of
its associated function?


I was referring to the "built-in" PostgreSQL system trigger for 
validating fk constraints are met. The trigger that uses the "SELECT 1 
FROM ONLY..." query. That particular query which the logs say I don't 
have permissions to execute is not part of my schema/code.



What does show?:

select session_user, current_user;


For this particular example, the session_user is: lw, current_user is 
dba (database and schema owner role)


So if I am following neither of these roles have permissions on the 
tables. Is that correct?


If you try the INSERT as system_admin, jb or gb does it work?




INSERT INTO results.historyitem
(batchid,datasourceid,sequence_order) VALUES (6,20,1);


-- 
Adrian Klaver

adrian.kla...@aklaver.com 





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

Re: Permission denied on schema for all users on insert to table with fk

[Leland Weathers]

On Wed, Jul 25, 2018 at 11:32 AM, Adrian Klaver 
wrote:

> On 07/25/2018 06:40 AM, Leland Weathers wrote:
>
>> I just ran into an issue on 9.5.13 after creating a new schema with a set
>> of tables in them, that no accounts (including schema / table owners) can
>> insert into a table with a fk relation. A snippet of the error output in
>> log files is as follows and looks like it is permissions related to the
>> parent table:
>>
>
> Is the FK to a table in another schema?
>

The tables are in the same schema.

>
> Can we see the schema definitions for the affected tables?
>>
>>
>>
> Here are the two tables schema as well as their permissions:
>
> system=# \d results.historyitem
>
> Table
> "results.historyitem"
>
>  Column |Type
> | Modifiers
>
> +-+-
> ---
>
> id | integer | not null default
> nextval(('results."historyitem_id_seq"'::text)::regclass)
>
> batchid| integer | not null
>
> datasourceid   | integer |
>
> sequence_order | integer | not null
>
> description| text|
>
> causedfailure  | boolean |
>
> timestamp  | timestamp without time zone |
>
> modulename | text|
>
> modulebasename | text|
>
> Indexes:
>
> "pk_historyitem_id" PRIMARY KEY, btree (id)
>
> "ixfk_historyitem_batch" btree (batchid)
>
> Foreign-key constraints:
>
> "fk_historyitem_batch" FOREIGN KEY (batchid) REFERENCES
> results.batch(id) ON DELETE CASCADE
>
>
>
>
>
> system=# \d results.batch
>
>   Table "results.batch"
>
> Column|Type
> |  Modifiers
>
> --+-+---
> ---
>
> id   | integer | not null default
> nextval(('results."batch_id_seq"'::text)::regclass)
>
> hostname | character varying(255)  |
>
> assemblyversion  | character varying(255)  |
>
> commandlinearguments | text|
>
> starttime| timestamp without time zone |
>
> endtime  | timestamp without time zone |
>
> exitcode | integer |
>
> windowsidentity  | character varying(255)  |
>
> threadcount  | integer |
>
> Indexes:
>
> "pk_batch_id" PRIMARY KEY, btree (id)
>
> Referenced by:
>
> TABLE "results.historyitem" CONSTRAINT "fk_historyitem_batch" FOREIGN
> KEY (batchid) REFERENCES results.batch(id) ON DELETE CASCADE
>
> TABLE "results.result" CONSTRAINT "fk_result_batch" FOREIGN KEY
> (batchid) REFERENCES results.batch(id) ON DELETE CASCADE
>
>
>
>
>
> system=# \dp results.batch
>
>Access
> privileges
>
>  Schema  | Name  | Type  |  Access privileges   | Column
> privileges | Policies
>
> -+---+---+--
> +---+--
>
> results | batch | table | system_admin=arwdDxt/system_admin
> +|   |
>
>  |   |   | system_reader=r/system_admin
> +|   |
>
>  |   |   | system_batch_writer=arw/system_admin+|
> |
>
>  |   |   | gb=arwdDxt/system_admin
> +|   |
>
>  |   |   | jb=arwdDxt/system_admin
> |   |
>
> (1 row)
>
>
>
>
>
> system=# \dp results.historyitem
>
>   Access
> privileges
>
>  Schema  |Name | Type  |   Access privileges  |
> Column privileges | Policies
>
> -+-+---+
> --+---+--
>
> results | historyitem | table | system_admin=arwdDxt/system_admin
> +|   |
>
>  | |   | system_reader=r/system_admin
> +|   |
>
>  | |   | system_batch_writer=arw/system_admin+|
> |
>
>  | |   | gb=arwdDxt/system_admin
> +|   |
>
>  | |   | jb=arwdDxt/system_admin
> |   |
>
> (1 row)
>
>
>
>
>
> system=#
>
>
>
>
>
>
> And a more complete example of what we are seeing with multiple accounts.
> This particular set is from an account that has their role set to that of
> the database & schema owner which is different than the table owner role.
>
>
>
> system=# insert into results.batch (hostname, assemblyversion) VALUES
> ('mycomp','0.0..0');
>
> INSERT 0 1
>
> system=# select lastval();
>
> lastval
>
> -

Re: Permission denied on schema for all users on insert to table with fk

[Leland Weathers]

On Thu, Jul 26, 2018 at 8:31 AM, Adrian Klaver 
wrote:

> On 07/26/2018 03:52 AM, Leland Weathers wrote:
>
>>
>>
>>
>
>
>
>> And a more complete example of what we are seeing with multiple
>> accounts. This particular set is from an account that has their role
>> set to that of the database & schema owner which is different than
>> the table owner role.
>>
>> __
>>
>> __ __
>>
>> system=# insert into results.batch (hostname, assemblyversion)
>> VALUES ('mycomp','0.0..0');
>>
>> INSERT 0 1
>>
>> system=# select lastval();
>>
>> lastval
>>
>> -
>>
>> 6
>>
>> (1 row)
>>
>> __ __
>>
>> __ __
>>
>> system=# INSERT INTO results.historyitem
>> (batchid,datasourceid,sequence_order)
>>
>> system-# VALUES
>>
>> system-# (6,20,1);
>>
>> ERROR:  permission denied for schema results
>>
>> LINE 1: SELECT 1 FROM ONLY "results"."batch"...
>>
>> ^
>>
>> QUERY:  SELECT 1 FROM ONLY "results"."batch" x WHERE "id"
>> OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x
>>
>> system=# SELECT FROM results.batch WHERE id=6;
>>
>> --
>>
>> (1 row)
>>
>
> What does \dn+ results show?
>

system=# \dn+ results

  List of schemas

  Name   | Owner | Access privileges | Description

-+---+---+-

results | dba   | dba=UC/dba   +|

 |   | system_reader=U/dba  +|

 |   | system_batch_writer=U/dba+|

 |   | gb=U/dba  |

(1 row)





system=#


>
> Before you mentioned a trigger. I am not seeing that in the schema you
> sent. Is there one and if so what is it's definition and that of its
> associated function?
>

I was referring to the "built-in" PostgreSQL system trigger for validating
fk constraints are met. The trigger that uses the "SELECT 1 FROM ONLY..."
query. That particular query which the logs say I don't have permissions to
execute is not part of my schema/code.

>
> What does show?:
>
> select session_user, current_user;
>

For this particular example, the session_user is: lw, current_user is dba
(database and schema owner role)

>
> INSERT INTO results.historyitem (batchid,datasourceid,sequence_order)
> VALUES (6,20,1);
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Permission denied on schema for all users on insert to table with fk

[Leland Weathers]

On Thu, Jul 26, 2018 at 9:19 AM, Adrian Klaver 
wrote:

> On 07/26/2018 06:57 AM, Leland Weathers wrote:
>
>
>>
>> Before you mentioned a trigger. I am not seeing that in the schema
>> you sent. Is there one and if so what is it's definition and that of
>> its associated function?
>>
>>
>> I was referring to the "built-in" PostgreSQL system trigger for
>> validating fk constraints are met. The trigger that uses the "SELECT 1 FROM
>> ONLY..." query. That particular query which the logs say I don't have
>> permissions to execute is not part of my schema/code.
>>
>>
>> What does show?:
>>
>> select session_user, current_user;
>>
>>
>> For this particular example, the session_user is: lw, current_user is dba
>> (database and schema owner role)
>>
>
> So if I am following neither of these roles have permissions on the
> tables. Is that correct?
>
> If you try the INSERT as system_admin, jb or gb does it work?
>


Thanks that was the right direction and I feel stupid now and the issue is
resolved. The system_admin account (the table owner) did not have usage
permission on the schema - re-reading some SO articles, it was there in the
comments and I had missed it. All the users had permissions but even
superuser can't insert without the table owner having schema permissions.

>
>>
>> INSERT INTO results.historyitem
>> (batchid,datasourceid,sequence_order) VALUES (6,20,1);
>>
>>
>> -- Adrian Klaver
>> adrian.kla...@aklaver.com 
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: logical replication snapshots

[Dimitri Maziuk]

On 07/25/2018 07:57 PM, Andres Freund wrote:
> On 2018-07-25 12:31:01 -0700, Adrian Klaver wrote:

>>> Would "permission denied" be relevant?
>>
>> Logical decoding is something I am still learning. The "permission denied"
>> would to me be relevant only to the extent that it seems to be provoking:
> 
> Yes, it looks related. Looks like logical rep was trying to perform the
> intiial sync of those tables, and couldn't due to permission errors.

I'm not sure what happened, I remember the initial sync of that
particular schema failing on one table only, but looking at it now, all
tables are empty on the subscriber.

>> "LOG:  logical decoding found consistent point at 19/E6942440"
>> DETAIL:  There are no running transactions."
>>
>> Others with more experience in this area would need to fill whether that
>> might account for the 13 million files in the ~snapshot/ directory.
> 
> That indicates there's some error handling issues to be resolved. Petr?

It logs "COPY TO STDOUT", does that actually cache to disk (in ~snapshot/)?

Would it be safe to delete every file in ~snapshot/ older than some
X-number of minutes? (And what a good X might be?)

-- 
Dimitri Maziuk
Programmer/sysadmin
BioMagResBank, UW-Madison -- http://www.bmrb.wisc.edu



signature.asc
Description: OpenPGP digital signature

Re: logical replication snapshots

[Adrian Klaver]

On 07/26/2018 10:54 AM, Dimitri Maziuk wrote:

On 07/25/2018 07:57 PM, Andres Freund wrote:

On 2018-07-25 12:31:01 -0700, Adrian Klaver wrote:



Would "permission denied" be relevant?


Logical decoding is something I am still learning. The "permission denied"
would to me be relevant only to the extent that it seems to be provoking:


Yes, it looks related. Looks like logical rep was trying to perform the
intiial sync of those tables, and couldn't due to permission errors.


I'm not sure what happened, I remember the initial sync of that
particular schema failing on one table only, but looking at it now, all
tables are empty on the subscriber.


To me that indicates all the syncs failed.




"LOG:  logical decoding found consistent point at 19/E6942440"
DETAIL:  There are no running transactions."

Others with more experience in this area would need to fill whether that
might account for the 13 million files in the ~snapshot/ directory.


That indicates there's some error handling issues to be resolved. Petr?


It logs "COPY TO STDOUT", does that actually cache to disk (in ~snapshot/)?


That is getting deeper into the code then I have been, so I don't know. 
FYI the code is below and the part that triggered the error is around 
line 762 (copy_table(Relation rel)).


https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/replication/logical/tablesync.c;h=acc6498567d07c3e93d0adf5039a10c693e38f64;hb=HEAD



Would it be safe to delete every file in ~snapshot/ older than some
X-number of minutes? (And what a good X might be?)



Again I don't know the answer to this. Are you trying this on a test 
setup or production one?


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

Re: logical replication snapshots

[Andres Freund]

On 2018-07-26 12:54:19 -0500, Dimitri Maziuk wrote:
> >> "LOG:  logical decoding found consistent point at 19/E6942440"
> >> DETAIL:  There are no running transactions."
> >>
> >> Others with more experience in this area would need to fill whether that
> >> might account for the 13 million files in the ~snapshot/ directory.
> > 
> > That indicates there's some error handling issues to be resolved. Petr?
> 
> It logs "COPY TO STDOUT", does that actually cache to disk (in ~snapshot/)?

No, it doesn't. The snapshots are internal to logical replication.


> Would it be safe to delete every file in ~snapshot/ older than some
> X-number of minutes? (And what a good X might be?)

You shouldn't manually delete them. But an explicit 'checkpoint;' (and
all automatically triggered ones), should do so for all that are
older. Is there a chance that you have some old leftover replication
slots around? Because that'd prevent cleanup?

What does SELECT * FROM pg_replication_slots ; return?

Greetings,

Andres Freund

Re: logical replication snapshots

[Dimitri Maziuk]

On 07/26/2018 02:54 PM, Adrian Klaver wrote:
> On 07/26/2018 10:54 AM, Dimitri Maziuk wrote:

>> I'm not sure what happened, I remember the initial sync of that
>> particular schema failing on one table only, but looking at it now, all
>> tables are empty on the subscriber.
> 
> To me that indicates all the syncs failed.

Yeah, well... the error message said one table failed and I went off to
find out why (a co-worker added a column behind everyone's back) and
never checked 'count(*)' on the other tables.

... deleting files in ~snapshots
> Again I don't know the answer to this. Are you trying this on a test
> setup or production one?

I could fire up another instance on a different port off the now-broken
$PGDATA easily enough and test. However if whatever uses those files
needs to start from the very first file and "replay" them in sequence,
that won't work.

The files are named like 19_E6942440.snap which presumably corresponds
to "LOG: logical decoding found consistent point at 19/E6942440" and
they seem to get progressively larger. That suggest that maybe just one
(the newest one) could be good enough...

-- 
Dimitri Maziuk
Programmer/sysadmin
BioMagResBank, UW-Madison -- http://www.bmrb.wisc.edu



signature.asc
Description: OpenPGP digital signature

Re: logical replication snapshots

[Adrian Klaver]

On 07/26/2018 02:06 PM, Dimitri Maziuk wrote:

On 07/26/2018 02:54 PM, Adrian Klaver wrote:

On 07/26/2018 10:54 AM, Dimitri Maziuk wrote:



I'm not sure what happened, I remember the initial sync of that
particular schema failing on one table only, but looking at it now, all
tables are empty on the subscriber.


To me that indicates all the syncs failed.


Yeah, well... the error message said one table failed and I went off to
find out why (a co-worker added a column behind everyone's back) and
never checked 'count(*)' on the other tables.


The thing that has me somewhat confused is:

ERROR: permission denied for schema macromolecules

I would thought the replication user could access that.

Is there something special about that schema?

Did the user role you set up for replication have the REPLICATION 
attribute or was it a superuser?




... deleting files in ~snapshots

Again I don't know the answer to this. Are you trying this on a test
setup or production one?


I could fire up another instance on a different port off the now-broken
$PGDATA easily enough and test. However if whatever uses those files
needs to start from the very first file and "replay" them in sequence,
that won't work.


If I remember back correctly this is because the server chokes on the 13 
million files in the ~/pg_logical/snapshot directly.


I wonder what happens if you change(on test setup)?:

wal_level = logical

to

wal_level = replica





The files are named like 19_E6942440.snap which presumably corresponds
to "LOG: logical decoding found consistent point at 19/E6942440" and
they seem to get progressively larger. That suggest that maybe just one
(the newest one) could be good enough...




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

Re: logical replication snapshots

[Dimitri Maziuk]

On 07/26/2018 04:39 PM, Adrian Klaver wrote:

> The thing that has me somewhat confused is:
> 
> ERROR: permission denied for schema macromolecules
> 
> I would thought the replication user could access that.

The more I look at the errors, the less sense it all makes.

Let me ask a different question: if I drop and re-create a published
table on the publisher without doing anything to the publication and
subscription, what happens?

Do I need to re-create the publication when I re-create the table?

-- 
Dimitri Maziuk
Programmer/sysadmin
BioMagResBank, UW-Madison -- http://www.bmrb.wisc.edu



signature.asc
Description: OpenPGP digital signature

Re: logical replication snapshots

[Adrian Klaver]

On 07/26/2018 03:01 PM, Dimitri Maziuk wrote:

On 07/26/2018 04:39 PM, Adrian Klaver wrote:


The thing that has me somewhat confused is:

ERROR: permission denied for schema macromolecules

I would thought the replication user could access that.


The more I look at the errors, the less sense it all makes.


Sounds like my problems:)



Let me ask a different question: if I drop and re-create a published
table on the publisher without doing anything to the publication and
subscription, what happens?



Take a look at:

https://www.postgresql.org/docs/10/static/sql-alterpublication.html



Do I need to re-create the publication when I re-create the table?




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

Re: logical replication snapshots

[Dimitri Maziuk]

On 07/26/2018 05:34 PM, Adrian Klaver wrote:
> On 07/26/2018 03:01 PM, Dimitri Maziuk wrote:

>> Let me ask a different question: if I drop and re-create a published
>> table on the publisher without doing anything to the publication and
>> subscription, what happens?
> 
> Take a look at:
> 
> https://www.postgresql.org/docs/10/static/sql-alterpublication.html

Thanks but what I ask (and what I suspect happened here while I wasn't
looking) is

- create schema foo with tables bar and baz and some rows in 'em
- create publication foopub with foo.bar and foo.baz
- create subscription to foopub on the other node, initial sync and all
- drop schema foo on the publisher
- create schema foo with tables bar and baz and some rows in 'em

The publication foopub is at this point fubar I take it? And needs to be
re-created on the publisher and reconnected on the subscriber? Complete
with with inital resync?

-- 
Dimitri Maziuk
Programmer/sysadmin
BioMagResBank, UW-Madison -- http://www.bmrb.wisc.edu



signature.asc
Description: OpenPGP digital signature

Re: logical replication snapshots

[Adrian Klaver]

On 07/26/2018 04:48 PM, Dimitri Maziuk wrote:

On 07/26/2018 05:34 PM, Adrian Klaver wrote:

On 07/26/2018 03:01 PM, Dimitri Maziuk wrote:



Let me ask a different question: if I drop and re-create a published
table on the publisher without doing anything to the publication and
subscription, what happens?


Take a look at:

https://www.postgresql.org/docs/10/static/sql-alterpublication.html


Thanks but what I ask (and what I suspect happened here while I wasn't
looking) is

- create schema foo with tables bar and baz and some rows in 'em
- create publication foopub with foo.bar and foo.baz
- create subscription to foopub on the other node, initial sync and all
- drop schema foo on the publisher
- create schema foo with tables bar and baz and some rows in 'em

The publication foopub is at this point fubar I take it? And needs to be
re-created on the publisher and reconnected on the subscriber? Complete
with with inital resync?


Not sure. Personally I would try:

1) ALTER PUBLICATION DROP TABLE foo|bar;

2) ALTER PUBLICATION ADD TABLE foo|bar;

3) ALTER SUBSCRIPTION sub_name REFRESH PUBLICATION

If you get to 3) it will re-sync the data unless you tell it otherwise.

The above is probably dependent on the size of the publication. If you 
did a publication for ALL it would make more sense to do the above then 
if you did a publication for just foo or bar.




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