Restore from dumps

[Nicola Contu]

Hello,
we recently moved from postgres 9.6.6 to 10.4

We perform a pg_dump in production to restore daily in a preprod env.
This process used to work perfectly, but now we have a tiny problem.

We first restore data, we perform a vacuum and then we restore matviews.
Restoring matviews now we have :

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 23090; 0 1912379424
MATERIALIZED VIEW DATA matview_vrs_request_sla postgres
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
Command was: REFRESH MATERIALIZED VIEW public.matview_vrs_request_sla;

The relation is there, in fact if I go there when I get in to the office,
the same command works.

I'm not sure why it does not work here, this seems really strange to me.

Can anyone help?

Thank you,
Nicola

Re: Restore from dumps

[Alban Hertroys]

> On 25 Jul 2018, at 9:43, Nicola Contu  wrote:
> 
> Hello,
> we recently moved from postgres 9.6.6 to 10.4
> 
> We perform a pg_dump in production to restore daily in a preprod env.
> This process used to work perfectly, but now we have a tiny problem.
> 
> We first restore data, we perform a vacuum and then we restore matviews.

What are the commands you used? You don't seem to mention restoring the schema?

> Restoring matviews now we have :
> 
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> pg_restore: [archiver (db)] Error from TOC entry 23090; 0 1912379424 
> MATERIALIZED VIEW DATA matview_vrs_request_sla postgres
> 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
> Command was: REFRESH MATERIALIZED VIEW public.matview_vrs_request_sla;

Is all_days a table? Or is it perhaps another view, possibly materialized even?

> The relation is there, in fact if I go there when I get in to the office, the 
> same command works.

This sounds to me like you may be using a different version of pg_restore in 
the office. Are both versions 10.4 or newer?
It can't hurt to check that you used version 10.4 of pg_dump as well.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

Re: Restore from dumps

[Nicola Contu]

Hello
these are the commands we use pretty much:

tar -xf tarname.tar  -C /backupdatadir --strip-components=4
pg_restore -l /backupdatadir | sed '/MATERIALIZED VIEW DATA/d' >
/restore.lst
pg_restore -U postgres  -L /restore.lst -d DBNAME  -j 32 /backupdatadir
vacuumdb --analyze-in-stages  -U postgres --jobs 32 -d  DBNAME
pg_restore -l /backupdatadir  | grep 'MATERIALIZED VIEW DATA' > /refresh.lst
pg_restore -U postgres  -L /refresh.lst -d DBNAME  -j 32 /backupdatadir


all_days is a table yes. bdays instead is a function and it include in the
first file.

Both servers have 10.4 for psql commands, we take the backup with 10.4 and
we restore with 10.4

We used to have postgres9.6.6 in production and pì10.4 in preprod, and the
restore went always fine. After switching to 10.4 in prod we started having
the problem.


2018-07-25 11:28 GMT+02:00 Alban Hertroys :

>
> > On 25 Jul 2018, at 9:43, Nicola Contu  wrote:
> >
> > Hello,
> > we recently moved from postgres 9.6.6 to 10.4
> >
> > We perform a pg_dump in production to restore daily in a preprod env.
> > This process used to work perfectly, but now we have a tiny problem.
> >
> > We first restore data, we perform a vacuum and then we restore matviews.
>
> What are the commands you used? You don't seem to mention restoring the
> schema?
>
> > Restoring matviews now we have :
> >
> > pg_restore: [archiver (db)] Error while PROCESSING TOC:
> > pg_restore: [archiver (db)] Error from TOC entry 23090; 0 1912379424
> MATERIALIZED VIEW DATA matview_vrs_request_sla postgres
> > 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
> > Command was: REFRESH MATERIALIZED VIEW public.matview_vrs_request_
> sla;
>
> Is all_days a table? Or is it perhaps another view, possibly materialized
> even?
>
> > The relation is there, in fact if I go there when I get in to the
> office, the same command works.
>
> This sounds to me like you may be using a different version of pg_restore
> in the office. Are both versions 10.4 or newer?
> It can't hurt to check that you used version 10.4 of pg_dump as well.
>
> Alban Hertroys
> --
> If you can't see the forest for the trees,
> cut the trees and you'll find there is no forest.
>
>

Re: Restore from dumps

[Laurenz Albe]

Nicola Contu wrote:
> we recently moved from postgres 9.6.6 to 10.4
> 
> We perform a pg_dump in production to restore daily in a preprod env.
> This process used to work perfectly, but now we have a tiny problem.
> 
> We first restore data, we perform a vacuum and then we restore matviews.
> Restoring matviews now we have :
> 
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> pg_restore: [archiver (db)] Error from TOC entry 23090; 0 1912379424 
> MATERIALIZED VIEW DATA matview_vrs_request_sla postgres
> 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
> Command was: REFRESH MATERIALIZED VIEW public.matview_vrs_request_sla;
> 
> The relation is there, in fact if I go there when I get in to the office, the 
> same command works.
> 
> I'm not sure why it does not work here, this seems really strange to me.

I suspect that it has to do with the recent security fixes around the "public" 
schema.

Try to ALTER the materialized view so that it refers to "public.all_days"
rather than "all_days".

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: Restore from dumps

[Nicola Contu]

Thanks Laurenz
I will try that.
Btw, just wondering why it works if I refresh it later, even if the
definition is still without public

2018-07-25 12:06 GMT+02:00 Laurenz Albe :

> Nicola Contu wrote:
> > we recently moved from postgres 9.6.6 to 10.4
> >
> > We perform a pg_dump in production to restore daily in a preprod env.
> > This process used to work perfectly, but now we have a tiny problem.
> >
> > We first restore data, we perform a vacuum and then we restore matviews.
> > Restoring matviews now we have :
> >
> > pg_restore: [archiver (db)] Error while PROCESSING TOC:
> > pg_restore: [archiver (db)] Error from TOC entry 23090; 0 1912379424
> MATERIALIZED VIEW DATA matview_vrs_request_sla postgres
> > 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
> > Command was: REFRESH MATERIALIZED VIEW public.matview_vrs_request_
> sla;
> >
> > The relation is there, in fact if I go there when I get in to the
> office, the same command works.
> >
> > I'm not sure why it does not work here, this seems really strange to me.
>
> I suspect that it has to do with the recent security fixes around the
> "public" schema.
>
> Try to ALTER the materialized view so that it refers to "public.all_days"
> rather than "all_days".
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>

Re: Read only to schema

[Łukasz Jarych]

Hi !

I have user :

[image: image.png]

and this user can not login to Database AccessLog.

I tried to use:

GRANT CONNECT
ON DATABASE " AccessLog"
TO "Luke";

GRANT CREATE ON SCHEMA PUBLIC TO "Luke";

GRANT USAGE
ON SCHEMA public
TO "Luke"

So expecting result : can modyfy DDL and DML in whole database but user it
is not SUPERUSER.

i tried to use:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO  "Luke";

I have error:

[image: image.png]


Please help,

How to create USER with all privilages (but no superuser) at once?

And how to delete User - drop role "Luke" is saying that there are
dependiences for this user...

Please help,
Jacek





sob., 14 lip 2018 o 12:31 Charles Clavadetscher 
napisał(a):

> Hello Jacek
>
>
>
> *From:* Łukasz Jarych [mailto:jarys...@gmail.com]
> *Sent:* Samstag, 14. Juli 2018 11:55
> *To:* clavadetsc...@swisspug.org; pgsql-gene...@postgresql.org >>
> PG-General Mailing List 
> *Subject:* Re: Read only to schema
>
>
>
> Thank you very much Charles!
>
>
>
> Awesome knowledge, thank you!
>
>
>
> I will test it and let you know if it is working like a charm (i am on
> vacation now and without access to postgresql).
>
>
>
> I am wondering with one thing:
>
>
>
> GRANT CREATE ON SCHEMA PUBLIC TO jaryszek;
>
>
>
> This will allow to create, drop, isnert and delete? All ddl and dml
> commands?
>
> Or should i use GRANT ALL ON SCHEMA PUBLIC TO jaryszek ?
>
>
>
> No. There are 2 privileges that you can grant on a schema:
>
> USAGE: Allows to use objects in that schema.
>
> CREATE: Allows to create and destroy objects in that schema.
>
>
>
> None of those says anything about which privileges users have within the
> schema. You need to define additionally privileges (e.g. select, insert,
> update, delete) on the objects within the schema.
>
>
>
> So in order to get access to a table public.test a user must have:
>
>
>
> USAGE on schema public AND SELECT (or whatever) on the table itself. If
> any of those is missing the user will not be able to access the table.
>
>
>
> GRANT ALL is generally a bad idea, althought on schemas there is not much
> you can do wrong.
>
> It doesn’t hurt if you add USAGE (which would be included in ALL along
> CREATE) for user jaryszek, but it is not necessary, because public (= any
> user) was only revoked CREATE (s. example in last mail). That means public
> still has USAGE on schema public and obviously you are on one of “any user”.
>
>
>
> I hope I could explain that somehow.
>
> If you still have questions just get back on the list.
>
>
>
> Bye
>
> Charles
>
>
>
> Best,
>
> Jacek
>
>
>
> sob., 14 lip 2018 o 08:23 Charles Clavadetscher <
> clavadetsc...@swisspug.org> napisał(a):
>
> Hello
>
>
>
> *From:* Łukasz Jarych [mailto:jarys...@gmail.com]
> *Sent:* Freitag, 13. Juli 2018 16:39
> *To:* pgsql-gene...@postgresql.org >> PG-General Mailing List <
> pgsql-gene...@postgresql.org>
> *Subject:* Re: Read only to schema
>
>
>
> I found something like this:
>
>
>
> CREATE ROLE readonly_user
>
>WITH LOGIN
>
>ENCRYPTED PASSWORD '1234'
>
>
>
> ALTER ROLE readonly_user
>
> SET search_path to
>
> public
>
>
>
>  GRANT CONNECT
>
> ON DATABASE "TestDb"
>
> TO readonly_user;
>
>
>
>  GRANT USAGE
>
> ON SCHEMA public
>
> TO readonly_user;
>
>
>
> GRANT USAGE
>
> ON ALL SEQUENCES  -- Alternatively: ON SEQUENCE seq1, seq2, seq3 ...
>
> IN SCHEMA public
>
> TO readonly_user;
>
>
>
>  GRANT SELECT
>
> ON ALL TABLES  -- Alternatively: ON TABLE table1, view1, table2 ...
>
> IN SCHEMA public
>
> TO readonly_user;
>
>
>
> Question is how to give this user opposite access? I mean give him access
> to all functionalities like inserting, deleting, creating tables and staff
> like this.
>
>
>
> I mean i want to assign user "jaryszek" to this read_only role and after
> changing schema i want to give user "jaryszek" all credentials.
>
>
>
> Best,
>
> Jacek
>
>
>
>
>
> You can change your readonly_user to NOINHERIT and GRANT the role to
> jaryszek.
>
> When you then want to act as readonly_user you set the role explicitly.
>
>
>
> Here basically:
>
>
>
> Revoke create from public, so that only granted users will be able to
> create or drop objects.
>
> REVOKE CREATE ON SCHEMA PUBLIC FROM public;
>
>
>
> Create the role as group (nologin) and without implicit inheritance of
> privileges.
>
> CREATE ROLE readonly_user NOINHERIT NOLOGIN;
>
>
>
> Your normal user should be able to create tables.
>
> GRANT CREATE ON SCHEMA PUBLIC TO jaryszek;
>
>
>
> Add your user to the readonly_user group.
>
> GRANT readonly_user TO jaryszek;
>
>
>
> Now when you log in as jaryszek you can create table add data, etc.
>
>
>
> jaryszek@db.localhost=> SELECT SESSION_USER, CURRENT_USER;
>
> session_user | current_user
>
> --+--
>
> jaryszek | jaryszek
>
>
>
> jaryszek@db.localhost=> CREATE TABLE public.test (a INTEGER);
>
> CREATE TABLE
>
> jaryszek@db.localhost=> INSERT INTO public.test VALUES (1);
>
> INSERT

Re: Order of execution for permissive RLS policies

[Dean Rasheed]

On 24 July 2018 at 15:25, Simon Brent  wrote:
> I've been using postgres for a while now, and have just started looking in
> to row level security. I have found something that I think is a bit strange,
> and wanted to know if anyone knows how/why it is the case.
>
> I have a table with multiple policies, each with a USING statement. When I
> run EXPLAIN ANALYSE SELECT * FROM [table], I see that the policies are OR'd
> together in reverse alphabetical name order. It doesn't matter which order I
> create the policies in - the order they are checked is always (for example)
> zz OR yy OR xx OR ww.

Hmm, the fact that permissive policies sometimes appear to be checked
in reverse alphabetical order looks like an implementation artefact --
that's the order in which RLS policies are read when loading a table's
metadata (see RelationBuildRowSecurity() in
src/backend/commands/policy.c). I don't believe that was intentional,
but any case, PostgreSQL makes no guarantees about the order of
evaluation of clauses under an OR clause, and the query optimiser is
free to re-order them for efficiency, provided that doing so doesn't
affect the query result.

A trivial example where permissive policies won't be evaluated in
reverse alphabetical order would be a policy that said USING (a=1 AND
b=1), and another one that said USING (a=1 AND b=2). The query
optimiser would merge those together to produce a=1 AND (b=1 OR b=2),
and then consider any indexes on 'a' and/or 'b'. So there is, in
general, no well-defined order of evaluation of the policies.

The only case where order can affect the result of a query is multiple
restrictive policies used to check new data inserted into a table
using INSERT or UPDATE. In that case, the error message for new data
violating one of the policies depends on the order in which they are
checked, and it's useful to be able to predict what the error message
will be. This is why restrictive policies are sorted by name. (This is
a little like multiple CHECK constraints on a table, which are also
checked in name order). Again, that name-ordering of restrictive
policies only applies to the checks run on new data; the clauses added
to the WHERE clause to check permission to access existing data may be
rearranged by the query optimiser and evaluated in any order.

Regards,
Dean

Re: DB Backup from WAL Slave

[basti]

Thanks a lot for this tip. I don't know is this is the right param.
Becouse of: "... , but can cause database bloat on the primary for some
workloads."

This the an other way to backup the database and work around


pg_dump: Error message from server: ERROR:  canceling statement due to
conflict with recovery
# DETAIL:  User was holding a relation lock for too long.

this error?

I have try bg_asepackup but this close with:
pg_basebackup: could not connect to server: FATAL:  number of requested
standby connections exceeds max_wal_senders (currently 0)

Best regards,

On 24.07.2018 15:33, Andreas Kretschmer wrote:
> On 24 July 2018 14:44:45 CEST, basti  wrote:
>> Hello,
>>
>> we have a db master and a slave.
> 
> -
>>
>> How can I do an backup with pg_dumpall from slave?
> 
> Set hot_standby_feedback to on.
> 
> 
> Regards, Andreas
> 
> 

Re: DB Backup from WAL Slave

[Fabio Pardi]

Hi Basti,


When you are dumping a big table using hot_standby_feedback=on,you are holding 
the master from cleaning up old records(as in: it was running on master).

If too much time elapses while the lock is held (30 secs by default), then the 
statementis cancelled.

At the expense of potentially accumulating more WAL segments on the primary, 
you might want to look into 
max_standby_archive_delay/max_standby_streaming_delay parameters to have more 
control over query cancellation

regards,

fabio pardi





On 25/07/18 15:14, basti wrote:
> Thanks a lot for this tip. I don't know is this is the right param.
> Becouse of: "... , but can cause database bloat on the primary for some
> workloads."
>
> This the an other way to backup the database and work around
>
>
> pg_dump: Error message from server: ERROR:  canceling statement due to
> conflict with recovery
> # DETAIL:  User was holding a relation lock for too long.
>
> this error?
>
> I have try bg_asepackup but this close with:
> pg_basebackup: could not connect to server: FATAL:  number of requested
> standby connections exceeds max_wal_senders (currently 0)
>
> Best regards,
>
> On 24.07.2018 15:33, Andreas Kretschmer wrote:
>> On 24 July 2018 14:44:45 CEST, basti  wrote:
>>> Hello,
>>>
>>> we have a db master and a slave.
>> -
>>> How can I do an backup with pg_dumpall from slave?
>> Set hot_standby_feedback to on.
>>
>>
>> Regards, Andreas
>>
>>

Re: Restore from dumps

[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: Restore from dumps

[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
>

Order in which tables are dumped

[Ron]

Hi,

v8.4 if it matters.

It looked like the tables were being backed up in alphanumeric order, but 
now I see that table "docformat" is being dumped *after* "doc_image".


Are there some other rules besides alphabetical sorting?

--
Angular momentum makes the world go 'round.

Re: Order in which tables are dumped

[Tom Lane]

Ron  writes:
> It looked like the tables were being backed up in alphanumeric order, but 
> now I see that table "docformat" is being dumped *after* "doc_image".

Looks like standard C-locale (ASCII) sort order to me ...

> Are there some other rules besides alphabetical sorting?

Dependencies can override the normal sort order, though a dependency
directly between two tables is rare.

regards, tom lane

Re: logical replication snapshots

[Andres Freund]

On 2018-07-24 14:02:26 -0500, Dimitri Maziuk wrote:
> On 07/24/2018 01:43 PM, Andres Freund wrote:
> > On 2018-07-24 12:22:24 -0500, Dimitri Maziuk wrote:
> >> On 07/24/2018 12:21 PM, Alvaro Herrera wrote:
> >>>
> >>> Are you serious about us trying to diagnose a bug with this description?
> >>
> >> What do you want to know, exactly?
> > 
> > A recipe that we can follow and reproduce the issue.
> 
> The nutshell version is as I said: I pretty much followed the manual to
> create a test publication/subscription setup and let it run for a few
> weeks. I had to increase a few limits but otherwise everything's at the
> default settings as shipped in the rpms.

Are you really expecting us to be able to reproduce the problem based on
the above description? Our test suites do setup plain replications
setups, and the problem doesn't occur there.


> If you are asking for something other than those two extremes, please
> let me know what it is and I'll be happy to try and provide it.

A command by command reproducer would be good.


> I can send you postgres config files from both nodes and the entire
> database dump -- it's all public domain. I can `zfs send` you the
> snapshot of the entire /var/lib/pgsql/10 as is, too.

Without the genesis, that's probably not as helpful, unfortunately.


Greetings,

Andres Freund

Re: logical replication snapshots

[Dimitri Maziuk]

On 7/25/2018 10:28 AM, Andres Freund wrote:


Are you really expecting us to be able to reproduce the problem based on
the above description? Our test suites do setup plain replications
setups, and the problem doesn't occur there.


I don't, by definition, have a reproducible case: it only happened once 
so far.


If nobody knows what limits the number of files created in 
$PGDATA/pg_logical/snapshots then we'll all have to wait until this 
happens again.


(To somebody else as I'm obviously not turning logical replication back 
on until I know it won't kill my server again.)


Dima

Re: Order in which tables are dumped

[Vick Khera]

On Wed, Jul 25, 2018 at 11:15 AM, Ron  wrote:

> Hi,
>
> v8.4 if it matters.
>
> It looked like the tables were being backed up in alphanumeric order, but
> now I see that table "docformat" is being dumped *after* "doc_image".
>
> Are there some other rules besides alphabetical sorting?
>

Is there some concern about the order? Lower case f comes after _ in ascii.

The only time it could possibly matter is on restore when there are
references for foreign keys, but on a restore those are all done after the
data is restored.

Re: Order in which tables are dumped

[Ron]

On 07/25/2018 10:28 AM, Tom Lane wrote:

Ron  writes:

It looked like the tables were being backed up in alphanumeric order, but
now I see that table "docformat" is being dumped *after* "doc_image".

Looks like standard C-locale (ASCII) sort order to me ...


I hate spreadsheets.  And gnu sort, and unicode...  :(

--
Angular momentum makes the world go 'round.

Re: logical replication snapshots

[Adrian Klaver]

On 07/25/2018 08:40 AM, Dimitri Maziuk wrote:

On 7/25/2018 10:28 AM, Andres Freund wrote:


Are you really expecting us to be able to reproduce the problem based on
the above description? Our test suites do setup plain replications
setups, and the problem doesn't occur there.


I don't, by definition, have a reproducible case: it only happened once 
so far.


Where you using pg_export_snapshot() by any chance?:

https://www.postgresql.org/docs/10/static/functions-admin.html#FUNCTIONS-SNAPSHOT-SYNCHRONIZATION


Where there any relevant error messages in the log before the database hung?




If nobody knows what limits the number of files created in 
$PGDATA/pg_logical/snapshots then we'll all have to wait until this 
happens again.


(To somebody else as I'm obviously not turning logical replication back 
on until I know it won't kill my server again.)


Given that it took 3 weeks to manifest itself before, I would say give 
it a try and monitor $PGDATA/pg_logical/snapshots. That would help 
provide information for getting at the source of the problem. You can 
always disable the replication if it looks like it running away.




Dima




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

Re: Order in which tables are dumped

[Ron]

On 07/25/2018 10:43 AM, Vick Khera wrote:
On Wed, Jul 25, 2018 at 11:15 AM, Ron > wrote:


Hi,

v8.4 if it matters.

It looked like the tables were being backed up in alphanumeric order,
but now I see that table "docformat" is being dumped *after* "doc_image".

Are there some other rules besides alphabetical sorting?


Is there some concern about the order? Lower case f comes after _ in ascii.


I'm tracking the progress of a very long backup, and the spreadsheet I using 
(and gnu sort, too, and IIRC Postgres' own ORDER BY) sort based on the 
current locale (en_US), whereas pg_dump sorts based on the C locale.  Thus, 
things aren't as I expected.


The only time it could possibly matter is on restore when there are 
references for foreign keys, but on a restore those are all done after the 
data is restored.


--
Angular momentum makes the world go 'round.

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

[Leland Weathers]

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:



“permission denied for schema ",,,"SELECT 1 FROM ONLY
. x WHERE ""id"" OPERATOR(pg_catalog.=) $1 FOR KEY
SHARE OF x",20





The schema and tables are all owned by the same group role, and members of
the owner role are also getting the error. So far, all users both owner and
non-owner have been able to successfully execute the select statement used
by trigger and get either a 1 back, or no rows when the correct id is
entered. When run from the application, writes to this table are
immediately after writes to the parent table so that the id can be returned
for the child table writes. Writes to both parent/child tables are
occurring with the same account. The following short snippet are a couple
of the commands run by an account which is in the group role owning the
database, schema and tables in question:



=# SELECT 1 FROM ONLY ""."" x WHERE "id"
OPERATOR(pg_catalog.=) 3 FOR

KEY SHARE OF x;

?column?

--

1

(1 row)



=# INSERT INTO .sentryhistoryitem

-#   (batchid,datasourceid,sequence_order,description,
causedfailure,"timestamp",modulename,modulebasename)

-# VALUES

-#   (3,20,1,'Found datasource [Id: 20, Name:
ds1].',False,'07/24/2018 03:05:58.668','Datasource','Object')

-# ;

ERROR:  permission denied for schema 

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

   ^

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



I’ve spent a bit of time searching on different sites trying to find
pointers to this particular case and haven’t found any good ideas yet for
next steps on troubleshooting or pointing at root cause. Any pointers to
next steps would be appreciated.

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

[Adrian Klaver]

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?

Can we see the schema definitions for the affected tables?



__ __

“permission denied for schema ",,,"SELECT 1 FROM ONLY 
. x WHERE ""id"" OPERATOR(pg_catalog.=) $1 FOR 
KEY SHARE OF x",20


__ __

__ __

The schema and tables are all owned by the same group role, and members 
of the owner role are also getting the error. So far, all users both 
owner and non-owner have been able to successfully execute the select 
statement used by trigger and get either a 1 back, or no rows when the 
correct id is entered. When run from the application, writes to this 
table are immediately after writes to the parent table so that the id 
can be returned for the child table writes. Writes to both parent/child 
tables are occurring with the same account. The following short snippet 
are a couple of the commands run by an account which is in the group 
role owning the database, schema and tables in question:


__ __

=# SELECT 1 FROM ONLY ""."" x WHERE "id" 
OPERATOR(pg_catalog.=) 3 FOR


KEY SHARE OF x;

?column?

--

     1

(1 row)

__ __

=# INSERT INTO .sentryhistoryitem

-#   
(batchid,datasourceid,sequence_order,description,causedfailure,"timestamp",modulename,modulebasename)


-# VALUES

-#   (3,20,1,'Found datasource [Id: 20, Name: 
ds1].',False,'07/24/2018 03:05:58.668','Datasource','Object')


-# ;

ERROR:  permission denied for schema 

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

    ^

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


__ __

I’ve spent a bit of time searching on different sites trying to find 
pointers to this particular case and haven’t found any good ideas yet 
for next steps on troubleshooting or pointing at root cause. Any 
pointers to next steps would be appreciated.






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

Need text wrapping inside a column to avoid record wrapping (tabular output of 'select')

[David Gauthier]

Hi:

psql (9.6.7, server 9.1.9)  on RHEL6

In order to avoid record wrapping in the tabular output of a "select"
statement, I need to limit the width of certain columns.  For those
columns, I would like to have text wrapping so as not to lose any
information (IOW, I don't want to simply truncatate and lose info).

Example:

name   |age  |life_story
|favorite_color
---+-+--+--
madame marie   | 123 | She was born a long time ago, blah, blah,| yellow
   | | blah, blah, blah, blah, blah, blah, blah,|
   | | blah, more-more-mmore-more-more, |
   | | andmore-andmore-andmore-andmore, blah,   |
   | | blah, blah, blah, blah, blah, blah, blah |
   | | and now she's 123 years old  |
---+-+--+---

... that sort of thing.  Also, line breaks in the "life_story" where they
make sense, like on spaces, as you see in lines 3&4 of that column

Is there an easy way to configure PG to do this ?

Thanks in Advance !

Re: Need text wrapping inside a column to avoid record wrapping (tabular output of 'select')

[Alvaro Herrera]

On 2018-Jul-25, David Gauthier wrote:

> Hi:
> 
> psql (9.6.7, server 9.1.9)  on RHEL6
> 
> In order to avoid record wrapping in the tabular output of a "select"
> statement, I need to limit the width of certain columns.  For those
> columns, I would like to have text wrapping so as not to lose any
> information (IOW, I don't want to simply truncatate and lose info).

\pset format wrapped

It doesn't break on whitespace though.

 $ 999,00 │ Nine hundred ninety nine dollars and z…
  │…ero cents
 $ 1.000,…│ One thousand  dollars and zero cents
…  00 │ 

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Need text wrapping inside a column to avoid record wrapping (tabular output of 'select')

[Adrian Klaver]

On 07/25/2018 09:40 AM, David Gauthier wrote:

Hi:

psql (9.6.7, server 9.1.9)  on RHEL6

In order to avoid record wrapping in the tabular output of a "select" 
statement, I need to limit the width of certain columns.  For those 
columns, I would like to have text wrapping so as not to lose any 
information (IOW, I don't want to simply truncatate and lose info).


Example:

name           |age  |life_story
|favorite_color

---+-+--+--
madame marie   | 123 | She was born a long time ago, blah, blah,| yellow
                |     | blah, blah, blah, blah, blah, blah, blah,|
                |     | blah, more-more-mmore-more-more,         |
                |     | andmore-andmore-andmore-andmore, blah,   |
                |     | blah, blah, blah, blah, blah, blah, blah |
                |     | and now she's 123 years old              |
---+-+--+---

... that sort of thing.  Also, line breaks in the "life_story" where 
they make sense, like on spaces, as you see in lines 3&4 of that column


Is there an easy way to configure PG to do this ?


In psql:

create table wrap_test(name varchar, age integer, life_story varchar, 
favorite_color varchar);


insert into wrap_test values ('madame marie', 123, 'She was born a long 
time ago, blah, blah, yellow eblah, blah, blah, blah, blah, blah, blah, 
blah, more-more-mmore-more-more, andmore-andmore-andmore-andmore, blah, 
blah, blah, blah, blah, blah, blah, blah and now she''s 123 years old', 
'yellow');


\pset format wrapped

select * from wrap_test ;

I am not showing output as email client distorts it.



Thanks in Advance !



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

Re: logical replication snapshots

[Dimitri Maziuk]

On 07/25/2018 11:16 AM, Adrian Klaver wrote:

> Where you using pg_export_snapshot() by any chance?:

If it's not in chapter 31 of TFM then I have not touched it. The only
notable thing I changed from 31.9 quickstart is that replica identity is
full on all tables (the data's a mess).

> Where there any relevant error messages in the log before the database
> hung?

Do you seriously expect me to know what "relevant" is? ;) -- It's 7
days' worth of

> 2018-07-23 09:43:12.851 CDT [7975] STATEMENT:  COPY 
> macromolecules."Entity_common_name" TO STDOUT
> 2018-07-23 09:43:12.925 CDT [7982] LOG:  logical decoding found consistent 
> point at 19/E6942408
> 2018-07-23 09:43:12.925 CDT [7982] DETAIL:  There are no running transactions.
> 2018-07-23 09:43:12.935 CDT [7982] ERROR:  permission denied for schema 
> macromolecules
> 2018-07-23 09:43:12.935 CDT [7982] STATEMENT:  COPY macromolecules."Assembly" 
> TO STDOUT
> 2018-07-23 09:43:13.034 CDT [7987] LOG:  logical decoding found consistent 
> point at 19/E6942440
> 2018-07-23 09:43:13.034 CDT [7987] DETAIL:  There are no running transactions.
> 2018-07-23 09:43:13.044 CDT [7987] ERROR:  permission denied for schema 
> macromolecules
> 2018-07-23 09:43:13.044 CDT [7987] STATEMENT:  COPY 
> macromolecules."Spectral_transition" TO STDOUT
> 2018-07-23 09:43:13.135 CDT [7990] LOG:  logical decoding found consistent 
> point at 19/E6942478
> 2018-07-23 09:43:13.135 CDT [7990] DETAIL:  There are no running transactions.
> 2018-07-23 09:43:13.145 CDT [7990] ERROR:  permission denied for schema 
> macromolecules
> 2018-07-23 09:43:13.145 CDT [7990] STATEMENT:  COPY 
> macromolecules."Assembly_db_link" TO STDOUT

that ends with

> 2018-07-23 09:45:40.280 CDT [6032] LOG:  database system was interrupted; 
> last known up at 2018-07-06 17:04:28 CDT
> 2018-07-23 09:45:40.281 CDT [6033] FATAL:  the database system is starting up
> 2018-07-23 09:45:40.282 CDT [6034] FATAL:  the database system is starting up
... ad infinitum ...

Would "permission denied" be relevant?

(Not sure where that is coming from: I did initial sync as postgres then
altered it to replication user. Can't track it down now since the
database is dead.)

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



signature.asc
Description: OpenPGP digital signature

Re: Need text wrapping inside a column to avoid record wrapping (tabular output of 'select')

[David Gauthier]

That does it.  Good enough, despite the non-white space wrapping thing.
Thanks !

On Wed, Jul 25, 2018 at 12:53 PM, Adrian Klaver 
wrote:

> On 07/25/2018 09:40 AM, David Gauthier wrote:
>
>> Hi:
>>
>> psql (9.6.7, server 9.1.9)  on RHEL6
>>
>> In order to avoid record wrapping in the tabular output of a "select"
>> statement, I need to limit the width of certain columns.  For those
>> columns, I would like to have text wrapping so as not to lose any
>> information (IOW, I don't want to simply truncatate and lose info).
>>
>> Example:
>>
>> name   |age  |life_story
>> |favorite_color
>> ---+-+--
>> +--
>> madame marie   | 123 | She was born a long time ago, blah, blah,| yellow
>> | | blah, blah, blah, blah, blah, blah, blah,|
>> | | blah, more-more-mmore-more-more, |
>> | | andmore-andmore-andmore-andmore, blah,   |
>> | | blah, blah, blah, blah, blah, blah, blah |
>> | | and now she's 123 years old  |
>> ---+-+--
>> +---
>>
>> ... that sort of thing.  Also, line breaks in the "life_story" where they
>> make sense, like on spaces, as you see in lines 3&4 of that column
>>
>> Is there an easy way to configure PG to do this ?
>>
>
> In psql:
>
> create table wrap_test(name varchar, age integer, life_story varchar,
> favorite_color varchar);
>
> insert into wrap_test values ('madame marie', 123, 'She was born a long
> time ago, blah, blah, yellow eblah, blah, blah, blah, blah, blah, blah,
> blah, more-more-mmore-more-more, andmore-andmore-andmore-andmore, blah,
> blah, blah, blah, blah, blah, blah, blah and now she''s 123 years old',
> 'yellow');
>
> \pset format wrapped
>
> select * from wrap_test ;
>
> I am not showing output as email client distorts it.
>
>
>> Thanks in Advance !
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: logical replication snapshots

[Adrian Klaver]

On 07/25/2018 11:10 AM, Dimitri Maziuk wrote:

On 07/25/2018 11:16 AM, Adrian Klaver wrote:


Where you using pg_export_snapshot() by any chance?:


If it's not in chapter 31 of TFM then I have not touched it. The only
notable thing I changed from 31.9 quickstart is that replica identity is
full on all tables (the data's a mess).


So no.




Where there any relevant error messages in the log before the database
hung?


Do you seriously expect me to know what "relevant" is? ;) -- It's 7
days' worth of


Generally speaking relevancy increases the closer you get to the fall 
over event. So we are most interested in entries close to the point 
where the database stopped/failed.





2018-07-23 09:43:12.851 CDT [7975] STATEMENT:  COPY 
macromolecules."Entity_common_name" TO STDOUT
2018-07-23 09:43:12.925 CDT [7982] LOG:  logical decoding found consistent 
point at 19/E6942408
2018-07-23 09:43:12.925 CDT [7982] DETAIL:  There are no running transactions.
2018-07-23 09:43:12.935 CDT [7982] ERROR:  permission denied for schema 
macromolecules
2018-07-23 09:43:12.935 CDT [7982] STATEMENT:  COPY macromolecules."Assembly" 
TO STDOUT
2018-07-23 09:43:13.034 CDT [7987] LOG:  logical decoding found consistent 
point at 19/E6942440
2018-07-23 09:43:13.034 CDT [7987] DETAIL:  There are no running transactions.
2018-07-23 09:43:13.044 CDT [7987] ERROR:  permission denied for schema 
macromolecules
2018-07-23 09:43:13.044 CDT [7987] STATEMENT:  COPY 
macromolecules."Spectral_transition" TO STDOUT
2018-07-23 09:43:13.135 CDT [7990] LOG:  logical decoding found consistent 
point at 19/E6942478
2018-07-23 09:43:13.135 CDT [7990] DETAIL:  There are no running transactions.
2018-07-23 09:43:13.145 CDT [7990] ERROR:  permission denied for schema 
macromolecules
2018-07-23 09:43:13.145 CDT [7990] STATEMENT:  COPY 
macromolecules."Assembly_db_link" TO STDOUT


that ends with


2018-07-23 09:45:40.280 CDT [6032] LOG:  database system was interrupted; last 
known up at 2018-07-06 17:04:28 CDT
2018-07-23 09:45:40.281 CDT [6033] FATAL:  the database system is starting up
2018-07-23 09:45:40.282 CDT [6034] FATAL:  the database system is starting up

... ad infinitum ...

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:


"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.




(Not sure where that is coming from: I did initial sync as postgres then
altered it to replication user. Can't track it down now since the
database is dead.)



In your previous post you said:

"(To somebody else as I'm obviously not turning logical replication back 
on until I know it won't kill my server again.) "


So what are you working off now?



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

Re: logical replication snapshots

[Dimitri Maziuk]

On 07/25/2018 02:31 PM, Adrian Klaver wrote:

> In your previous post you said:
> 
> "(To somebody else as I'm obviously not turning logical replication back
> on until I know it won't kill my server again.) "
> 
> So what are you working off now?

I have that database rebuilt with no publications defined, and I have
the original $PGDATA on a ZFS volume.

I have not looked at the subscriber host yet, I've more urgent fires to
douse first.
-- 
Dimitri Maziuk
Programmer/sysadmin
BioMagResBank, UW-Madison -- http://www.bmrb.wisc.edu



signature.asc
Description: OpenPGP digital signature

Re: could not read block 0 in file : read only 0 of 8192 bytes when doing nasty on immutable index function

[Andres Freund]

Hi,

On 2018-07-09 12:06:21 -0700, Peter Geoghegan wrote:
> > I assume we'll have to backpatch this issue, so I think it'd probably a
> > good idea to put a specific CacheInvalidateHeapTuple() in there
> > explicitly in the back branches, and do the larger fix in 12. ISTM
> > there's some risks that it'd cause issues.  Will you tackle this?
> 
> Okay.

Any progress on that?

Peter, given that your patch made this more likely, and that you're a
committer these days, I'm opening an open items entry, and assign it to
you. Does that sound ok?

Greetings,

Andres Freund

Re: could not read block 0 in file : read only 0 of 8192 bytes when doing nasty on immutable index function

[Andres Freund]

Hi,

On 2018-06-28 08:02:10 -0700, Andres Freund wrote:
> I believe this happens because there's currently no relcache
> invalidation registered for the main relation, until *after* the index
> is built. Normally it'd be the CacheInvalidateRelcacheByTuple(tuple) in
> index_update_stats(), which is called at the bottom of index_build().
> But we never get there, because the earlier error.  That's bad, because
> any relcache entry built *after* the CommandCounterIncrement() in
> CommandCounterIncrement() will now be outdated.
> 
> In the olden days we most of the time didn't build a relcache entry
> until after the index was built - but plan_create_index_workers() now
> does. I'm suspect there's other ways to trigger that earlier, too.
> 
> Putting in a CacheInvalidateRelcache(heapRelation); before the CCI in
> index_create() indeed makes the "borked relcache" problem go away.
> 
> 
> I wonder why we don't just generally trigger invalidations to an
> indexes' "owning" relation in CacheInvalidateHeapTuple()?
>   else if (tupleRelId == IndexRelationId)
>   {
>   Form_pg_index indextup = (Form_pg_index) GETSTRUCT(tuple);
> 
>   /*
>* When a pg_index row is updated, we should send out a 
> relcache inval
>* for the index relation.  As above, we don't know the shared 
> status
>* of the index, but in practice it doesn't matter since 
> indexes of
>* shared catalogs can't have such updates.
>*/
>   relationId = indextup->indexrelid;
>   databaseId = MyDatabaseId;
>   }

Tom, do you have any comments about the above?  The biggest argument
against hardcoding that a pg_index update also invalidates the
corresponding relation, is that there's a lot of other things that we
could handle similarly. But I don't think any of those are as important
to relcache entries...

Greetings,

Andres Freund

Re: could not read block 0 in file : read only 0 of 8192 bytes when doing nasty on immutable index function

[Tom Lane]

Andres Freund  writes:
> On 2018-06-28 08:02:10 -0700, Andres Freund wrote:
>> I wonder why we don't just generally trigger invalidations to an
>> indexes' "owning" relation in CacheInvalidateHeapTuple()?

> Tom, do you have any comments about the above?

It seems like an ugly and fragile hack, offhand.  I can see the point
about needing to recompute the owning relation's index list, but there's
no good reason why an update of a pg_index row ought to force that.
You're using that as a proxy for creation or deletion of an index, but
(at least in principle) pg_index rows might get updated for existing
indexes.

Also, it's not clear to me why the existing places that force relcache
inval on the owning table during index create/delete aren't sufficient
already.  I suppose it's probably a timing problem, but it's not clear
why hacking CacheInvalidateHeapTuple in this fashion fixes that, or why
we could expect it to stay fixed.

regards, tom lane

Re: could not read block 0 in file : read only 0 of 8192 bytes when doing nasty on immutable index function

[Peter Geoghegan]

On Wed, Jul 25, 2018 at 4:03 PM, Andres Freund  wrote:
> Peter, given that your patch made this more likely, and that you're a
> committer these days, I'm opening an open items entry, and assign it to
> you. Does that sound ok?

I intend to follow through on this soon. I have been distracted by
project work. I accept responsibility for the open item, though.

-- 
Peter Geoghegan

Re: could not read block 0 in file : read only 0 of 8192 bytes when doing nasty on immutable index function

[Andres Freund]

On 2018-07-25 19:27:47 -0400, Tom Lane wrote:
> Andres Freund  writes:
> > On 2018-06-28 08:02:10 -0700, Andres Freund wrote:
> >> I wonder why we don't just generally trigger invalidations to an
> >> indexes' "owning" relation in CacheInvalidateHeapTuple()?
> 
> > Tom, do you have any comments about the above?
> 
> It seems like an ugly and fragile hack, offhand.  I can see the point
> about needing to recompute the owning relation's index list, but there's
> no good reason why an update of a pg_index row ought to force that.
> You're using that as a proxy for creation or deletion of an index, but
> (at least in principle) pg_index rows might get updated for existing
> indexes.

Sure, but in at least some of those cases you'd also need to update the
list, as it could invalidate things like the determination of the oid,
pkey or whatnot index.  But yea, I don't think it's a *great* idea, just
an idea worth considering.


> Also, it's not clear to me why the existing places that force relcache
> inval on the owning table during index create/delete aren't sufficient
> already.  I suppose it's probably a timing problem, but it's not clear
> why hacking CacheInvalidateHeapTuple in this fashion fixes that, or why
> we could expect it to stay fixed.

Hm? As I point out in my email, there's simply nothing forcing an
invalidation in the relevant path? We register an invalidation, but only
*after* successfully building the entire index. So if the index build
fails, but we actually accessed the relcache (as e.g. the new
plan_create_index_workers() pretty much always does), the relcache entry
refers to the failed index build.  I think it's basically a failure to
follow proper invalidation procedures - when modifying something
affecting a relcache entry it's not OK to delay the
CacheInvalidateRelcacheByTuple() to after a point where we can fail
nonFATALy.

Greetings,

Andres Freund

Re: logical replication snapshots

[Adrian Klaver]

On 07/25/2018 11:10 AM, Dimitri Maziuk wrote:

On 07/25/2018 11:16 AM, Adrian Klaver wrote:


Where you using pg_export_snapshot() by any chance?:


If it's not in chapter 31 of TFM then I have not touched it. The only
notable thing I changed from 31.9 quickstart is that replica identity is
full on all tables (the data's a mess).


Where there any relevant error messages in the log before the database
hung?


Do you seriously expect me to know what "relevant" is? ;) -- It's 7
days' worth of


2018-07-23 09:43:12.851 CDT [7975] STATEMENT:  COPY 
macromolecules."Entity_common_name" TO STDOUT
2018-07-23 09:43:12.925 CDT [7982] LOG:  logical decoding found consistent 
point at 19/E6942408
2018-07-23 09:43:12.925 CDT [7982] DETAIL:  There are no running transactions.
2018-07-23 09:43:12.935 CDT [7982] ERROR:  permission denied for schema 
macromolecules
2018-07-23 09:43:12.935 CDT [7982] STATEMENT:  COPY macromolecules."Assembly" 
TO STDOUT
2018-07-23 09:43:13.034 CDT [7987] LOG:  logical decoding found consistent 
point at 19/E6942440
2018-07-23 09:43:13.034 CDT [7987] DETAIL:  There are no running transactions.
2018-07-23 09:43:13.044 CDT [7987] ERROR:  permission denied for schema 
macromolecules
2018-07-23 09:43:13.044 CDT [7987] STATEMENT:  COPY 
macromolecules."Spectral_transition" TO STDOUT
2018-07-23 09:43:13.135 CDT [7990] LOG:  logical decoding found consistent 
point at 19/E6942478
2018-07-23 09:43:13.135 CDT [7990] DETAIL:  There are no running transactions.
2018-07-23 09:43:13.145 CDT [7990] ERROR:  permission denied for schema 
macromolecules
2018-07-23 09:43:13.145 CDT [7990] STATEMENT:  COPY 
macromolecules."Assembly_db_link" TO STDOUT


A little slow, but I realized that the above looks like the initial sync 
for a table. Do these errors stretch back all the way back to your 
original sync?






that ends with


2018-07-23 09:45:40.280 CDT [6032] LOG:  database system was interrupted; last 
known up at 2018-07-06 17:04:28 CDT
2018-07-23 09:45:40.281 CDT [6033] FATAL:  the database system is starting up
2018-07-23 09:45:40.282 CDT [6034] FATAL:  the database system is starting up

... ad infinitum ...

Would "permission denied" be relevant?

(Not sure where that is coming from: I did initial sync as postgres then
altered it to replication user. Can't track it down now since the
database is dead.)




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

Re: logical replication snapshots

[Andres Freund]

On 2018-07-25 12:31:01 -0700, Adrian Klaver wrote:
> On 07/25/2018 11:10 AM, Dimitri Maziuk wrote:
> > On 07/25/2018 11:16 AM, Adrian Klaver wrote:
> > 
> > > Where you using pg_export_snapshot() by any chance?:
> > 
> > If it's not in chapter 31 of TFM then I have not touched it. The only
> > notable thing I changed from 31.9 quickstart is that replica identity is
> > full on all tables (the data's a mess).
> 
> So no.
> 
> > 
> > > Where there any relevant error messages in the log before the database
> > > hung?
> > 
> > Do you seriously expect me to know what "relevant" is? ;) -- It's 7
> > days' worth of
> 
> Generally speaking relevancy increases the closer you get to the fall over
> event. So we are most interested in entries close to the point where the
> database stopped/failed.
> 
> > 
> > > 2018-07-23 09:43:12.851 CDT [7975] STATEMENT:  COPY 
> > > macromolecules."Entity_common_name" TO STDOUT
> > > 2018-07-23 09:43:12.925 CDT [7982] LOG:  logical decoding found 
> > > consistent point at 19/E6942408
> > > 2018-07-23 09:43:12.925 CDT [7982] DETAIL:  There are no running 
> > > transactions.
> > > 2018-07-23 09:43:12.935 CDT [7982] ERROR:  permission denied for schema 
> > > macromolecules
> > > 2018-07-23 09:43:12.935 CDT [7982] STATEMENT:  COPY 
> > > macromolecules."Assembly" TO STDOUT
> > > 2018-07-23 09:43:13.034 CDT [7987] LOG:  logical decoding found 
> > > consistent point at 19/E6942440
> > > 2018-07-23 09:43:13.034 CDT [7987] DETAIL:  There are no running 
> > > transactions.
> > > 2018-07-23 09:43:13.044 CDT [7987] ERROR:  permission denied for schema 
> > > macromolecules
> > > 2018-07-23 09:43:13.044 CDT [7987] STATEMENT:  COPY 
> > > macromolecules."Spectral_transition" TO STDOUT
> > > 2018-07-23 09:43:13.135 CDT [7990] LOG:  logical decoding found 
> > > consistent point at 19/E6942478
> > > 2018-07-23 09:43:13.135 CDT [7990] DETAIL:  There are no running 
> > > transactions.
> > > 2018-07-23 09:43:13.145 CDT [7990] ERROR:  permission denied for schema 
> > > macromolecules
> > > 2018-07-23 09:43:13.145 CDT [7990] STATEMENT:  COPY 
> > > macromolecules."Assembly_db_link" TO STDOUT
> > 
> > that ends with
> > 
> > > 2018-07-23 09:45:40.280 CDT [6032] LOG:  database system was interrupted; 
> > > last known up at 2018-07-06 17:04:28 CDT
> > > 2018-07-23 09:45:40.281 CDT [6033] FATAL:  the database system is 
> > > starting up
> > > 2018-07-23 09:45:40.282 CDT [6034] FATAL:  the database system is 
> > > starting up
> > ... ad infinitum ...
> > 
> > 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.


> "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?

Greetings,

Andres Freund