Re: pg_basebackup + incremental base backups

[Christopher Pereira]

On 24-May-20 15:48, Stephen Frost wrote:

That really shouldn't be possible.  I'm very curious as to exactly what
happened that resulted in your primary/replica being 'out of sync', as
you say.


Hi Stephen,

Actually this was more a hypothetical question to find a solution in 
case some day one of our standby clusters goes out of sync and we have 
to rebuild it having a very big database.
With proper WAL archiving this shouldn't happen but we wanted to be 
prepared for this scenario just in case.


We did some tests measuring IO and traffic and are very happy with the 
results. We will definitely be adding pgBackRest to our toolchain.


Regarding my initial question, I still believe that the world deserves a 
simple direct pg_basebackup replacement even when putting an additional 
"repo host" in the middle is a better idea in the long term.


As you said, all the pieces are there and it would be quite easy to 
write a new "pg_basebackup_delta" script that could be executed on the 
standby host to:


1) setup a pgBackRest repo on the primary host (via SSH)

2) create a backup on the primary host (via SSH)

3) do a delta restore on the standby

Even when the repository on the primary host is only created temporarily 
(and require double storage, resources, etc), it may still be worth 
considering the traffic that can be saved by doing a delta restore on a 
standby host in a different region, right?


Thanks and congratulations for the good work.

Re: FDW and RLS

[Charles Clavadetscher]

Hello

On 2020-05-25 15:50, Laurenz Albe wrote:

On Fri, 2020-05-22 at 08:02 -0500, Ted Toth wrote:

Will RLS be applied to data being retrieved via a FDW?


ALTER FOREIGN TABLE rp_2019 ENABLE ROW LEVEL SECURITY;
ERROR:  "rp_2019" is not a table

Doesn't look good.

Yours,
Laurenz Albe


Actually it does work if you set the policy on the source table and 
access it using the user defined in the user mappings on the foreign 
table on the remote server.


Server 1:

charles@kofdb.archivedb.5432=# \d public.test_fdw_rls
 Table "public.test_fdw_rls"
  Column  |  Type   | Collation | Nullable | Default
--+-+---+--+-
 id   | integer |   |  |
 content  | text|   |  |
 username | text|   |  |
Policies:
POLICY "kofadmin_select" FOR SELECT
  TO kofadmin
  USING ((username = ("current_user"())::text))

kofadmin@kofdb.archivedb.5432=> \dp public.test_fdw_rls
 Access privileges
 Schema | Name | Type  |Access privileges| Column 
privileges |   Policies

+--+---+-+---+--
 public | test_fdw_rls | table | charles=arwdDxt/charles+|   
| kofadmin_select (r):+
|  |   | kofadmin=arwd/charles   |   
|   (u): (username = ("current_user"())::text)+
|  |   | |   
|   to: kofadmin


charles@kofdb.archivedb.5432=# SELECT CURRENT_USER; SELECT * FROM 
public.test_fdw_rls;


charles@kofdb.archivedb.5432=# SELECT CURRENT_USER; SELECT * FROM 
public.test_fdw_rls;

 current_user
--
 charles
(1 row)

 id | content  | username
+--+--
  1 | Text for charles | charles
  1 | Access from fdw via user fdwsync | fdwsync
(2 rows)

charles@kofdb.archivedb.5432=# set role fdwsync ;
SET
charles@kofdb.archivedb.5432=> SELECT CURRENT_USER; SELECT * FROM 
public.test_fdw_rls;

 current_user
--
 fdwsync
(1 row)

 id | content  | username
+--+--
  1 | Access from fdw via user fdwsync | fdwsync
(1 row)

On the server accessing the table via FDW:

kofadmin@kofdb.t-archivedb.5432=> \deu+
List of user mappings
   Server   | User name | FDW options
+---+-
 kofdb_prod | kofadmin  | (password 'mysecret', "user" 'fdwsync')

kofadmin@kofdb.t-archivedb.5432=> SELECT CURRENT_USER; SELECT * FROM 
public.test_fdw_rls ;

 current_user
--
 kofadmin
(1 row)

 id | content  | username
+--+--
  1 | Access from fdw via user fdwsync | fdwsync
(1 row)

Regards
Charles

Re: pg_dump crashes

[Adrian Klaver]

On 5/24/20 10:30 PM, Nico De Ranter wrote:
Unfortunately not. I discovered the issue rather late. The last working 
backup is about 2 months old.


Well first it is entirely possible this is not the only corruption in 
the database.


Second you are probably going to have to reach out to the Bacula folks:

https://www.bacula.org/support/
https://sourceforge.net/projects/bacula/lists/bacula-users

I would say the questions to ask them are:

1) Is the md5 required for a file?

2) If so how and over what is calculated?


Then you could experiment with trying to update the md5 field with new 
data.



 >
 >
 >
 > Following up on the max(bigint), I tried
 >
 >     SELECT md5 FROM public.file where fileid >2087994666;
 >
 > and got
 >
 >     ERROR:  compressed data is corrupted
 >
 > So it does look like those entries are killing it.  Now for the
 > million-dollar question: how do I get them out?

Do you have recent previous backup?

 >
 > Nico
 >
 > --
 >
 > Nico De Ranter
 >
 > Operations Engineer
 >



-- 
Adrian Klaver

adrian.kla...@aklaver.com 



--

Nico De Ranter

Operations Engineer





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

Re: FDW and RLS

[Laurenz Albe]

On Fri, 2020-05-22 at 08:02 -0500, Ted Toth wrote:
> Will RLS be applied to data being retrieved via a FDW?

ALTER FOREIGN TABLE rp_2019 ENABLE ROW LEVEL SECURITY;
ERROR:  "rp_2019" is not a table

Doesn't look good.

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

Re: Logical replication troubles

[Peter Eisentraut]

On 2020-05-25 10:19, Anders Bøgh Bruun wrote:
Thank you for that clarification. It helps me understand how things work 
a lot better.
I know this might be a bit off topic, but my challenge here is that we 
are using Patroni (by using Zalando's postgres-operator for Kubernetes), 
and any replication slot not created by Patroni, seems to be removed, 
whenever the master pod restarts. We therefore specify in the Patroni 
config, that a permanent replication slot should be created for our 
usage to do logical replication of some select tables, to our data 
warehouse. That means that the replication slot is created as soon as 
the database is ready to use, which is also before any tables, data or 
publications are created. Can you give me a hint as to what the correct 
way to set this up would be?

Or do I need to try contacting the Patroni devs instead?


That would probably be best.

--
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Logical replication troubles

[Anders Bøgh Bruun]

Thank you for that clarification. It helps me understand how things work a
lot better.
I know this might be a bit off topic, but my challenge here is that we are
using Patroni (by using Zalando's postgres-operator for Kubernetes), and
any replication slot not created by Patroni, seems to be removed, whenever
the master pod restarts. We therefore specify in the Patroni config, that a
permanent replication slot should be created for our usage to do logical
replication of some select tables, to our data warehouse. That means that
the replication slot is created as soon as the database is ready to use,
which is also before any tables, data or publications are created. Can you
give me a hint as to what the correct way to set this up would be?
Or do I need to try contacting the Patroni devs instead?

On Fri, 22 May 2020 at 11:00, Peter Eisentraut <
peter.eisentr...@2ndquadrant.com> wrote:

> On 2020-05-20 17:16, Anders Bøgh Bruun wrote:
> > [67] LOG:  logical replication apply worker for subscription
> > "widgets_sub" has started
> > [67] DEBUG:  connecting to publisher using connection string
> > "dbname=testdb host=master port=5432 user=repuser password=abc123"
> > [67] ERROR:  could not receive data from WAL stream: ERROR:  publication
> > "widgets_pub" does not exist
> >CONTEXT:  slot "my_slot", output plugin "pgoutput", in the change
> > callback, associated LSN 0/1674958
> > [1] DEBUG:  unregistering background worker "logical replication worker
> > for subscription 16396"
> > [1] LOG:  background worker "logical replication worker" (PID 67) exited
> > with exit code 1
> >
> > I can verify that the publication called widgets_pub does exist, and I
> > am not seeing any errors on the sending side.
> >
> > The SQL-file named "works" just has the creation of the replication slot
> > moved down to after I insert some data into the table I want to
> > replicate. And that works as expected.
>
> You need to create the publication before the replication slot.  The
> walsender's view of the world moves along with the WAL it is
> decoding/sending.  So when the subscription worker connects, it
> initially sees a state as of the creation of the replication slot, when
> the publication did not exist yet.
>
> --
> Peter Eisentraut  http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>


-- 
Anders Bøgh Bruun

Infrastructure Architect

CellPoint digital
cellpointdigital.com
*WE MAKE TRAVEL EASIER™*

M: +45 31 14 87 41
E: and...@cellpointdigital.com

Chicago | *Copenhagen* | Dubai | London | Miami | Pune | Singapore

Re: Query returns no rows in pg_basebackup cluster

[Andrus]

Hi!


How to set logical replication for all user databases in cluster so that
when new database is added or new tables are added to database they will
start replicate automatically ?

I think that it would be good if you spend some time reading the
documentation on this stuff, particularly the part about restrictions,
to understand the use cases where that can become useful:
https://www.postgresql.org/docs/devel/logical-replication.html


Thank you.
I read it and havent found any reference to PITR recovery.
For PITR recovery it should probably save sql statements to files and allow to 
specify recovery target time
for applying sql statements to base backup.

Is PITR recovery supported only using binary WAL files ?

Other limits can probably be solved.

Andrus.