Re: logical replication snapshots

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(dot)klaver(at)aklaver(dot)com