I recently updated my systems from pg 9.1.8 to 9.5.3. A pg_dumpall was used to migrate the data. Now I'm trying to re-establish replication between master and slave. I'm getting stuck.
When I run pg_basebackup (via a script which worked flawlessly on 9.1.8, AND via command line, ala "manual mode"), it creates the PGDATA directory structure, some files, but soon gets stuck. It writes fewer than 40 MB of a 20GB database. Further, using tcpdump to watch packets on the wire, I confirm no data is going across.
However, it's clear a connection has been made and the process was started. On the master, I see the process table:
postgres: wal sender process pgsync A.B.C.D(42821) sending backup
Meanwhile, on the client, I see no output updates. Here's the command:
pg_basebackup -x -P -v -D $PGDATA -w
PGUSER, PGPASSWORD and PGHOST are set accordingly. The User is a user with the REPLICATION attribute. Just in case, I dropped and re-created the user.
So that's question A.
Question B is related. In attempting to verify that the permissions and HBA were set correctly, I attempted to do a pg_dump using the same PGUSER and PGHOST. What I got surprised me:
$ pg_dump -s onyxeditor
pg_dump: [archiver (db)] query failed: ERROR: permission denied for relation licence
pg_dump: [archiver (db)] query was: LOCK TABLE public.licence IN ACCESS SHARE MODE
This is the replication user. Other schemas worked just fine, but not this one. Is this expected behavior?
Kind regards,
Otheus
--