from:"Achilleas Mantzios"


Hello Dear List,

we have a table holding email attachments as bytea, and we would like to 
filter out images of small dimensions, which are not of any value to our 
logic.


I took a look at pg_image extension, tested it, and it proved 
problematic, it killed my 200+ days uptime FreeBSD box :( . I dropped 
the extension and uninstalled this as soon as fsck finally finished.


So I would like to ask you, basically we have PNGs and JPEGs, is there 
an easy way of parsing their headers and getting info about their 
dimensions?


I could write a C function for that. For PNG it is quite easy but for 
JPEG it gets a little bit complicated, albeit doable, just asking for 
something out of the box. Currently we load images (in our java 
enterprise system) and filter them in Java, but this brings wildfly down 
to its knees pretty easy and quickly.



Thank you and happy Easter.

Re: easy way to acquire height / width from images (PNG, JPEG) stored as bytea?


On 17/4/20 4:09 μ.μ., Adam Brusselback wrote:

Why not extract and store that metadata with the image rather than 
trying to extract it to filter on at query time? That way you can 
index your height and width columns to speed up that filtering if 
necessary.


Yes I thought of that, but those are coming automatically from our mail 
server (via synonym), we have written an alias : a program that parses 
and stores emails. This is generic, I wouldn't like to add specific code 
(or specific columns)  just for image attachments. However I dig the 
idea of the indexes.
You may be able to write a wrapper for a command line tool like 
imagemagic or something so you can call that from a function to 
determine the size if you did want to stick with extracting that at 
query time.
As I describe above, those attachments are nowhere as files. They are 
email attachments. Also we got about half TB of them.

Re: easy way to acquire height / width from images (PNG, JPEG) stored as bytea?


On 17/4/20 5:47 μ.μ., Steve Atkins wrote:



If running an extension crashed your server you should look at how / 
why, especially if it corrupted your filesystem.
That shouldn't happen on a correctly configured system, so the 
underlying issue might cause you other problems. Crashing postgresql, 
sure, but not anything that impacts the rest of the server.


Hello, This machine runs several extensions with no issues (even pljava 
for Christ's sake, our heavy modified version of DBMirror, and lots of 
our own C functions included among others), two bhyve VMs running 
ubuntu, and one jail. + it functions as my workstation as well (wildfly, 
eclipse, etc). And it can run for years, without reboot.


Apparently lousy memory management (consumed all 32GB of RAM + 8GB swap) 
by pg_image didn't crash postgresql but brought the system to its knees. 
Plus this extension was lastly touched in 2013, go figure.



Cheers,
  Steve

Re: easy way to acquire height / width from images (PNG, JPEG) stored as bytea?

On 17/4/20 6:14 μ.μ., Imre Samu wrote:

> it killed my 200+ days uptime FreeBSD box :( .
> As I describe above, those attachments are nowhere as files.
> They are email attachments. Also we got about half TB of them.

it is possible - that some image is a "decompression bomb" ?

/"Because of the efficient compression method used in Portable Network
Graphics (PNG) files, a small PNG file can expand tremendously, acting
as a "decompression bomb". *Malformed PNG chunks can consume a large
amount of CPU and wall-clock time and large amounts of memory, up to
all memory available on a system, causing a Denial of Service (DoS).*
Libpng-1.4.1 has been revised to use less CPU time and memory, and
provides functions that applications can use to further defend against
such files."/

https://libpng.sourceforge.io/decompression_bombs.html
https://stackoverflow.com/questions/33602308/how-to-check-png-file-if-its-a-decompression-bomb

Regards,
Imre

Thank you a lot Imre. Great info.

Achilleas Mantzios <mailto:ach...@matrix.gatewaynet.com>> ezt írta (időpont: 2020. ápr.
17., P, 16:39):

On 17/4/20 4:09 μ.μ., Adam Brusselback wrote:

Why not extract and store that metadata with the image rather
than trying to extract it to filter on at query time? That way
you can index your height and width columns to speed up that
filtering if necessary.

Yes I thought of that, but those are coming automatically from our
mail server (via synonym), we have written an alias : a program
that parses and stores emails. This is generic, I wouldn't like to
add specific code (or specific columns) just for image
attachments. However I dig the idea of the indexes.

You may be able to write a wrapper for a command line tool like
imagemagic or something so you can call that from a function to
determine the size if you did want to stick with extracting that
at query time.

As I describe above, those attachments are nowhere as files. They
are email attachments. Also we got about half TB of them.

Re: serie of serie

2020-04-18 Thread Achilleas Mantzios



On 18/4/20 10:18 π.μ., Olivier Leprêtre wrote:


Hi,

I would like to generate a serie of serie and didn’t find how.  With 
generate_series. I can generate a serie


1

1

1

2

2

2

3

3

3

But how can I repeat this serie several times ?

1

1

1

2

2

2

3

3

3

1



with foo as (select floor(a) as a from 
generate_series(1.,4,1.0/3.0) as s(a))

select foo.a from foo, generate_series(1,10) as itera(b) order by b,a;


1

1

2

2

2

3

3

3

1

1

1

2

2

2

3

3

3

Thanks for any help,


 
	Garanti sans virus. www.avast.com 
 



<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

Re: Oracle vs. PostgreSQL - a comment

2020-06-05 Thread Achilleas Mantzios


On 2/6/20 10:45 μ.μ., Ravi Krishna wrote:

Generally speaking, I discourage having lots of databases under one PG
cluster for exactly these kinds of reasons.  PG's individual clusters
are relatively lightweight, after all.


Plus PG does not directly support cross database queries using 3 part name, 
something
sqlserver excels at.

Maybe because SQL server does not have real databases but schemas instead ?
This sucks security wise.








--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Re: Oracle vs. PostgreSQL - a comment

2020-06-05 Thread Achilleas Mantzios

On 5/6/20 3:33 μ.μ., Ravi Krishna wrote:

Plus PG does not directly support cross database queries using 3 part name,
something
sqlserver excels at.

Maybe because SQL server does not have real databases but schemas instead ?
This sucks security wise.

SQLServer has real databases with its own transaction log files. You can
restore individual databases in a cluster.
They also have schemas which are not same as users (Oracle treats users and
schemas same).

Ok, I never meant SQL Server does not have real databases, I meant it handles
databases as top level schemas.

For security, there is grant connect to the DB and further filtering based on
schema.

PostgreSQL has stronger protection at connection level, via pg_hba.conf .
PostgreSQL also supports db-level GRANTs .
In MS SQL server if you need an additional DB for maintenance tasks or to act as an intermediate bridge (like in our case) with write access on it, then automatically you write access to the main
schema (ok DB in MS SQL terms). (and need further REVOKES to fix security). This (security-wise) is just poor.

So the cross db joins come with a price.

I find SQLServer implementation pretty strong in this.

The only time this can be a problem is when few databases failover in a
mirrored environment (streaming replication in PG speak).
Then suddenly 3 part names would fail if the remote DB is no longer primary. My
experience with SQLServer is badly dated. Last
time I worked was SS2008. I believe in later versions they solved this problem
by the failover group concept which failovers all
inter-related databases at one go.

BTW Mysql treats databases and schemas as same (that's what it was few years
ago)

--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Re: Oracle vs. PostgreSQL - a comment

2020-06-05 Thread Achilleas Mantzios


On 5/6/20 5:19 μ.μ., Thomas Kellerer wrote:

Achilleas Mantzios schrieb am 05.06.2020 um 14:05:

Plus PG does not directly support cross database queries using 3 part name, 
something
sqlserver excels at.

Maybe because SQL server does not have real databases but schemas instead ?
This sucks security wise.

That is wrong.

SQL Server has both: databases and schemas and it allows for standard compliant 
catalog.schema.table references.

I think you are confusing that with MySQL where a schema and a database are the 
same thing
I think that you are confusing what you think I am confusing. I am talking about our MS SQL installation here at work. Not done by me anyways I am the pgsql guy, but still do most of the query stuff 
in ms sql as well.

I wrote already that my comment was far fetched in a subsequent email.






--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Re: Multitenent architecture

2020-06-10 Thread Achilleas Mantzios


On 4/6/20 6:52 μ.μ., Vasu Madhineni wrote:

Hi All,

We are planning a POC on multitenant architecture in Postgres, Could you please 
help us with steps for multitenant using schema for each application model.

Hello,
you may benefit from this blog post :
https://severalnines.com/database-blog/multitenancy-options-postgresql


Thank you so much all.

Regards,
Vasu



--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Re: Creating many tables gets logical replication stuck

2020-08-21 Thread Achilleas Mantzios


Dear Laurenz thank you for your analysis and report.

On 21/8/20 4:00 μ.μ., Laurenz Albe wrote:

Reproducer on 12.4:

This is identical problem with this report here : 
https://www.postgresql.org/message-id/6fa054d8-ad14-42a2-8926-5d79c97ecd65%40matrix.gatewaynet.com

Yours,
Laurenz Albe



--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Re: pgbouncer bug?

2020-08-21 Thread Achilleas Mantzios




On 21/8/20 7:56 μ.μ., greigwise wrote:

Not sure if this is the right place to post this, but if not someone please
point me in the right direction.

My issue is with pgbouncer 1.14.   This does not seem to happen on 1.13.

If I do a service pgbouncer restart, then anytime I try to connect to my
databases via pgbouncer, I get ERROR: no such user regardless of what user
I'm using.  It's almost like it's not recognizing the auth_query I have
configured.  But then if I issue a reload, then it seems to work fine and I
no longer get the user not found.  The problem is easy enough to work around
as I don't restart pgbouncer all that much, but it doesn't seem like this is
probably the intended behavior.


You may go here : 
https://github.com/pgbouncer/pgbouncer/commits/pgbouncer_1_14_0


and review all commits between 1.13 and 1.14



Thanks,
Greig Wise

[Full Disclosure: I accidentally posted this message at first to the "base"
PostgreSQL group and am now moving to "PostgreSQL - general".]



--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Creating many tables gets logical replication stuck

2020-08-25 Thread Achilleas Mantzios


Hello Keisuke
On 25/8/20 9:50 π.μ., Keisuke Kuroda wrote:

Hi All,

There was a similar problem in this discussion:
   Logical decoding CPU-bound w/ large number of tables
   
https://www.postgresql.org/message-id/flat/CAHoiPjzea6N0zuCi%3D%2Bf9v_j94nfsy6y8SU7-%3Dbp4%3D7qw6_i%3DRg%40mail.gmail.com


RelfilenodeMapHash from 1024 entries to 64.

The above changes reduced the performance impact.

However, I think the problem that there are too
many invalidations of RelfilenodeMapHash still remains.
As you report, when many tables are created/dropped/truncated,

Right! IIRC also a massive drop created the very same problem during the latest 
attempts for reproduction but I did not focus on this scenario.

The walsender process can get stuck.




--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Re: Doubt in pgbouncer

2020-10-02 Thread Achilleas Mantzios


On 2/10/20 9:50 π.μ., Fabio Pardi wrote:

Hi Rama,

On 02/10/2020 01:42, Rama Krishnan wrote:

Hi Friends,

By using pg bouncer can we split read and queries



pgbouncer is just a connection pooler.

The logic where to send the reads and where the writes, should be in our 
application.
yeah reading back some comments over the github on various issues, the pgbouncer hackers were of the philosophy that pgbouncer should remain as query agnostic as possible, in the sense that it should 
not investigate the content of the queries. Those had to do with classic problems like search_path in transaction mode (some PR's were rejected because of that).

You might find this interesting : https://github.com/awslabs/pgbouncer-rr-patch


regards,

fabio pardi



--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Re: Can I get some PostgreSQL developer feedback on these five general issues I have with PostgreSQL and its ecosystem?

2020-10-04 Thread Achilleas Mantzios



On 2/10/20 2:08 π.μ., tutilu...@tutanota.com wrote:


On Thu, Sep 24, 2020 at 10:40 PM mailto:tutilu...@tutanota.com>> wrote:


Well not partial as in incremental. Instead dump only some
portion of the schema with or without its associated data.

It's funny that you should bring that up, considering how it
was one of my points... See the point about pg_dump's bug on
Windows.


And you seem to have ignored the fact that one of the core
developers pointed out that it likely isn't a pg_dump bug - if
your terminal is using the same locale as the database, it should
have no difficulty dealing with the characters you are having
trouble with.  It seems likely that you simply need to learn how
to get your terminal set up correctly for it to work.

Yeah, this isn't rude or insulting at all...

Funny how my "incorrectly set up terminal" works perfectly for all 
other programs and my own test scripts, but not for pg_dump 
specifically. And only when using "special" characters. As already 
pointed out multiple times, in great detail, to deaf ears. Very 
interesting how you can manage to twist and bend that into it still 
somehow being "my fault". Because of course it cannot be pg_dump's 
fault. Absolutely not. It is unthinkable. It's the "rude user"'s fault 
who had the audacity to point out yet another PG bug which more than 
likely won't *ever* be fixed, as it's not even recognized, much less 
cared about. Probably because they *want* PostgreSQL to be crippled on 
Windows, judging by the responses in the past and how incredibly 
broken the joke of an installer is.


You should call it "Linux software with minimal pre-alpha Windows 
support" instead of pretending that it's cross-platform, and that goes 
for many FOSS projects as well which think exactly the same as you. 
The fact that I still use this garbage OS (Windows) speaks volumes of 
how incredibly crappy Linux is, which is utterly *unusable*.


But of course I should be grateful no matter what because it doesn't 
cost money. Because my time and energy is worthless. And the 
competition is "even worse", so that means I cannot point out any 
fault, ever, no matter how serious or how easily it could be fixed. I 
should just shut up and thank everyone for insulting me through 
carelessness and words. Or "fix it myself", because that's obviously 
an option as I haven't done it so far...


I did read the rest of your e-mail, but it would be pointless to reply 
to it as you clearly have the mentality that everyone should dedicate 
their lives to configuring a database and buying books instead of 
using it, because everyone should be core developers and everything 
must always be cryptic and difficult and blablabla. I'm sick of this 
attitude, and especially of being called "rude" by such 
rude-beyond-words people.


It would be refreshing to hear your be honest for once and just admit 
that you *want* it to be difficult. You *like* that there's a high 
threshold and it makes you feel superior to exclude "dumb" people who 
can't figure out all these cryptic (and downright broken) things. I 
truly believe that this is the reason for a lot of "weird" things 
which seem to make no sense on the surface.



I'd say take your time, take some deep breaths and decide that's good 
for you. Back in 2004 and after 3 yrs of full production software with 
postgresql someone from the mailing list (he's also in this thread!!) 
called me a "newbie", and I immediately started looking for 
alternatives, only to find out simply that there was no better DB 
software in the market/world back then (and still as we speak). So I 
stayed with PGSQL and wrote what I believe the best non-tcp-ip DB 
replication solution for marine and shipping business (over satellite), 
which still thrives today as far as easiness, automation, completeness, 
correctness and cost are concerned.


+ I discover every day that I am still a newbie, after 20 yrs with 
postgresql. This is not personal, this is about being successful in the 
long run, one should weigh his options and act accordingly. It took 
me/us a long time before we spent a single penny on someone to write or 
fix code that would work for us, but this moment eventually came, there 
is a roof when going with community software. That roof came for us much 
much later since we begun using PostgreSQL. Value for money is so hard 
to beat. I have seen the code by our MS SQL partners , some interesting 
and serious things happening there but when they listen what stock free 
pgsql can do they just freak out (the ones who understand).


So my advice, tell your CEO's the true potential of this technology and 
maybe show them some stats, some results of others, some numbers. Put 
them side by side with the rest of serious solutions and then decide.

What do you guys use for issue tracking, CI/CD and team management? any nice open source options?

2022-04-14 Thread Achilleas Mantzios


Hi All,
not pgsql related, since I could not find an more general list I am writing on 
-general, I hope this is ok.

We are at a phase of restructuring our software infrastructure, and I'd like to 
ask you guys, what do you use for :
a) Issue/bug tracking, we used to have Bugzilla then youtrack, the first one 
seems dead the second one has restriction over the number of users
b) Continuous Integration / Continuous Delivery (currently on TeamCity, not a 
big fan of this one either)
c) Project Management/team mgmt (nothing so far)

We use Git to manage our code base. Ideally all the above should be integrated 
with Git.

What issue/bug tracking is PostgreSQL itself using?
What continuous build system (CI/CD) is PostgreSQL itself using?
Any tool that you ppl or the PostgreSQL infrastructure use that links 
people/committers with bugs/issues, automatically closes issues upon 
commit/push, manages issues/projects/people ?

I know I am asking a lot, any help welcome, thanks!

--
Achilleas Mantzios
DBA, Analyst, IT Lead
IT DEPT
Dynacom Tankers Mgmt

Re: What do you guys use for issue tracking, CI/CD and team management? any nice open source options?

2022-04-18 Thread Achilleas Mantzios


On 16/4/22 6:39 π.μ., Michael Paquier wrote:

On Thu, Apr 14, 2022 at 06:19:44PM +0300, Achilleas Mantzios wrote:

What issue/bug tracking is PostgreSQL itself using?
What continuous build system (CI/CD) is PostgreSQL itself using?
Any tool that you ppl or the PostgreSQL infrastructure use that
links people/committers with bugs/issues, automatically closes
issues upon commit/push, manages issues/p rojects/people ?

The community does not have a bug tracker, everything is mail-based as
of today.  There is what we call the commit fest app:
https://commitfest.postgresql.org/

This is more a patch tracker for the development of new features
though, still there is a category for published patches that aim at
fixing bugs (see "Bug Fixes").  This does not act as a bug tracker to
be able to see all the live issues reported.

Thank you Michael!

--
Michael



--
Achilleas Mantzios
DBA, Analyst, IT Lead
IT DEPT
Dynacom Tankers Mgmt

pgsql 10.19 : "ERROR: cannot convert infinity to numeric" except there is no infinity


dynacom=# select it.id ,cept.value::numeric as val, 
numrange(ceptl.min_alarm::numeric,ceptl.max_alarm::numeric
,'()') as range from items it, cept_report cept , dynacom.vessels vsl, machdefs 
md, cept_reportlimits ceptl wh
ere it.id=cept.id AND md.defid=ceptl.defid AND it.defid=md.defid AND 
it.vslwhid=vsl.id AND vsl.vslstatus='Acti
ve' and md.application = 'Critical Equipment Performance Test' AND 
cept.systemdate>= (now()-'1 year'::interval
) AND  cept.value < -100 ORDER BY 1;
id | val | range
+-+---
(0 rows)


select it.id ,cept.value::numeric as val, 
numrange(ceptl.min_alarm::numeric,ceptl.max_alarm::numeric
,'()') as range from items it, cept_report cept , dynacom.vessels vsl, machdefs 
md, cept_reportlimits ceptl wh
ere it.id=cept.id AND md.defid=ceptl.defid AND it.defid=md.defid AND 
it.vslwhid=vsl.id AND vsl.vslstatus='Acti
ve' and md.application = 'Critical Equipment Performance Test' AND 
cept.systemdate>= (now()-'1 year'::interval
) AND  cept.value::numeric>'-1' ORDER BY 1;
ERROR:  cannot convert infinity to numeric

-- has no problem testing against infinity

select it.id ,cept.value::numeric as val, 
numrange(ceptl.min_alarm::numeric,ceptl.max_alarm::numeric
,'()') as range from items it, cept_report cept , dynacom.vessels vsl, machdefs 
md, cept_reportlimits ceptl wh
ere it.id=cept.id AND md.defid=ceptl.defid AND it.defid=md.defid AND 
it.vslwhid=vsl.id AND vsl.vslstatus='Acti
ve' and md.application = 'Critical Equipment Performance Test' AND 
cept.systemdate>= (now()-'1 year'::interval
) AND  cept.value='inf' ORDER BY 1;
id | val | range
+-+---
(0 rows)

-- still has problem testing the range

select it.id ,cept.value::numeric as val, 
numrange(ceptl.min_alarm::numeric,ceptl.max_alarm::numeric
,'()') as range from items it, cept_report cept , dynacom.vessels vsl, machdefs 
md, cept_reportlimits ceptl wh
ere it.id=cept.id AND md.defid=ceptl.defid AND it.defid=md.defid AND 
it.vslwhid=vsl.id AND vsl.vslstatus='Acti
ve' and md.application = 'Critical Equipment Performance Test' AND 
cept.systemdate>= (now()-'1 year'::interval
) AND  numrange(ceptl.min_alarm::numeric,ceptl.max_alarm::numeric,'()') @> 
cept.value::numeric ORDER BY 1;
ERROR:  cannot convert infinity to numeric


-- no problem if the query goes into its barrier and the test done outside
with bar as (select it.id ,cept.value::numeric as val, 
numrange(ceptl.min_alarm::numeric,ceptl.max_a
larm::numeric,'()') as range from items it, cept_report cept , dynacom.vessels 
vsl, machdefs md, cept_reportli
mits ceptl where it.id=cept.id AND md.defid=ceptl.defid AND it.defid=md.defid 
AND it.vslwhid=vsl.id AND vsl.vs
lstatus='Active' and md.application = 'Critical Equipment Performance Test' AND 
cept.systemdate>= (now()-'1 ye
ar'::interval)  ORDER BY 1)
select * from bar where NOT range @> val;
-- good data here

--
Achilleas Mantzios
DBA, Analyst, IT Lead
IT DEPT
Dynacom Tankers Mgmt

Re: pgsql 10.19 : "ERROR: cannot convert infinity to numeric" except there is no infinity


Thanks David

Στις 19/7/22 17:19, ο/η David G. Johnston έγραψε:
On Tuesday, July 19, 2022, Achilleas Mantzios 
 wrote:



ERROR:  cannot convert infinity to numeric

-- has no problem testing against infinity

select it.id <http://it.id> ,cept.value::numeric as val,
numrange(ceptl.min_alarm::numeric,ceptl.max_alarm::numeric
,'()') as range from items it, cept_report cept , dynacom.vessels
vsl, machdefs md, cept_reportlimits ceptl wh
ere it.id <http://it.id>=cept.id <http://cept.id> AND
md.defid=ceptl.defid AND it.defid=md.defid AND it.vslwhid=vsl.id
<http://vsl.id> AND vsl.vslstatus='Acti
ve' and md.application = 'Critical Equipment Performance Test' AND
cept.systemdate>= (now()-'1 year'::interval
) AND  cept.value='inf' ORDER BY 1;
id | val | range
+-+---
(0 rows)


The column cept.value contains an infinity.  I see nothing unusual in 
any of these queries given that fact.  If you try to cast the infinity 
to numeric it will fail.  If that doesn’t happen the query won’t fail.


Sorry I must have been dizzy today with so much support.

Yep, there are some infinity in there, but not in this result set.

I think when the casting is in the WHERE filter for some reason some 
subplan uses this filter (and fails). But when this check is applied to 
the result, no infinity is found and works correctly.




David J.

Re: pgsql 10.19 : "ERROR: cannot convert infinity to numeric" except there is no infinity




Στις 19/7/22 17:23, ο/η Tom Lane έγραψε:

"David G. Johnston"  writes:

On Tuesday, July 19, 2022, Achilleas Mantzios 
wrote:

ERROR:  cannot convert infinity to numeric

The column cept.value contains an infinity.  I see nothing unusual in any
of these queries given that fact.  If you try to cast the infinity to
numeric it will fail.  If that doesn’t happen the query won’t fail.

FWIW, PG 14 and later do support infinity in the numeric type.
Yes I noticed that, thank you Tom, I hope we'll be able to upgrade in 
the near future.


regards, tom lane

Re: pgsql 10.19 : "ERROR: cannot convert infinity to numeric" except there is no infinity


Thank you Adrian!

Στις 19/7/22 18:36, ο/η Adrian Klaver έγραψε:

On 7/19/22 03:38, Achilleas Mantzios wrote:

I reformatted queries to see thing better.



-- still has problem testing the range

select it.id ,cept.value::numeric as val, 
numrange(ceptl.min_alarm::numeric,ceptl.max_alarm::numeric
,'()') as range from items it, cept_report cept , dynacom.vessels 
vsl, machdefs md, cept_reportlimits ceptl wh
ere it.id=cept.id AND md.defid=ceptl.defid AND it.defid=md.defid AND 
it.vslwhid=vsl.id AND vsl.vslstatus='Acti
ve' and md.application = 'Critical Equipment Performance Test' AND 
cept.systemdate>= (now()-'1 year'::interval
) AND 
 numrange(ceptl.min_alarm::numeric,ceptl.max_alarm::numeric,'()') @> 
cept.value::numeric ORDER BY 1;

ERROR:  cannot convert infinity to numeric


SELECT
    it.id,
    cept.value::numeric AS val,
    numrange(ceptl.min_alarm::numeric, ceptl.max_alarm::numeric, '()') 
AS RANGE

FROM
    items it,
    cept_report cept,
    dynacom.vessels vsl,
    machdefs md,
    cept_reportlimits ceptl wh ere it.id = cept.id
    AND md.defid = ceptl.defid
    AND it.defid = md.defid
    AND it.vslwhid = vsl.id
    AND vsl.vslstatus = 'Acti
ve' and md.application = 'Critical Equipment Performance Test' AND
cept.systemdate>= (now()-'1 year'::interval)
AND numrange(ceptl.min_alarm::numeric, ceptl.max_alarm::numeric, '()') 
@> cept.value::numeric

ORDER BY
    1;

So the above fails. In your title when you say there is no infinity 
that means the cept.value, ceptl.min_alarm or ceptl.max_alarm  fields 
do not have any '-infinity' or 'infinity' values, correct?

There is infinity in cept.value , just not in this result set.
I got confused and wrongly assumed that since the result set (without 
the filter in the WHERE clause including cept.value::numeric) did not 
contain any infinity it should also work with the filter in the WHERE 
clause. Apparently a subplan executes this conversion in the WHERE 
before the other filters. I did not do any analyze to prove this.





-- no problem if the query goes into its barrier and the test done 
outside
with bar as (select it.id ,cept.value::numeric as val, 
numrange(ceptl.min_alarm::numeric,ceptl.max_a
larm::numeric,'()') as range from items it, cept_report cept , 
dynacom.vessels vsl, machdefs md, cept_reportli
mits ceptl where it.id=cept.id AND md.defid=ceptl.defid AND 
it.defid=md.defid AND it.vslwhid=vsl.id AND vsl.vs
lstatus='Active' and md.application = 'Critical Equipment Performance 
Test' AND cept.systemdate>= (now()-'1 ye

ar'::interval)  ORDER BY 1)
select * from bar where NOT range @> val;
-- good data here


WITH bar AS (
    SELECT
    it.id,
    cept.value::numeric AS val,
    numrange(ceptl.min_alarm::numeric, ceptl.max_alarm::numeric, 
'()') AS

RANGE
    FROM
    items it,
    cept_report cept,
    dynacom.vessels vsl,
    machdefs md,
    cept_reportli mits ceptl
    WHERE
    it.id = cept.id
    AND md.defid = ceptl.defid
    AND it.defid = md.defid
    AND it.vslwhid = vsl.id
    AND vsl.vs lstatus = 'Active'
    AND md.application = 'Critical Equipment Performance Test'
    AND cept.systemdate >= (now() - '1 ye
ar'::interval)
    ORDER BY
    1
)
SELECT
    *
FROM
    bar
WHERE
    NOT RANGE @> val;

This version succeeds, correct? So breaking the range construction 
into one step and the @> test into another works. Though I am not sure 
why  @> changed to NOT  @>?
Yes this succeeds. The correct is with the NOT, it does not change the 
behavior of the initial query.


--
Achilleas Mantzios
DBA, Analyst, IT Lead
IT DEPT
Dynacom Tankers Mgmt

Re: pgsql 10.19 : "ERROR: cannot convert infinity to numeric" except there is no infinity

2022-07-20 Thread Achilleas Mantzios


On 19/7/22 20:31, David G. Johnston wrote:

On Tuesday, July 19, 2022, Achilleas Mantzios  
wrote:

Thanks David

Στις 19/7/22 17:19, ο/η David G. Johnston έγραψε:

On Tuesday, July 19, 2022, Achilleas Mantzios 
 wrote:


ERROR:  cannot convert infinity to numeric

-- has no problem testing against infinity

select it.id <http://it.id> ,cept.value::numeric as val, 
numrange(ceptl.min_alarm::numeric,ceptl.max_alarm::numeric
,'()') as range from items it, cept_report cept , dynacom.vessels vsl, 
machdefs md, cept_reportlimits ceptl wh
ere it.id <http://it.id>=cept.id <http://cept.id> AND md.defid=ceptl.defid 
AND it.defid=md.defid AND it.vslwhid=vsl.id <http://vsl.id> AND vsl.vslstatus='Acti
ve' and md.application = 'Critical Equipment Performance Test' AND 
cept.systemdate>= (now()-'1 year'::interval
) AND  cept.value='inf' ORDER BY 1;
id | val | range
+-+---
(0 rows)


The column cept.value contains an infinity.  I see nothing unusual in any 
of these queries given that fact. If you try to cast the infinity to numeric it 
will fail. If that doesn’t happen the
query won’t fail.


Sorry I must have been dizzy today with so much support.

Yep, there are some infinity in there, but not in this result set.

I think when the casting is in the WHERE filter for some reason some 
subplan uses this filter (and fails). But when this check is applied to the 
result, no infinity is found and works correctly.


That is what it means for SQL to be a declarative language, the order of execution/evaluation is determined to be efficient and not what is explicitly written.  You do have some control though, but 
using it also means you might make things worse.


I think you have issues anyway if you are doing equality checks on what seems 
to be a floating point column, regardless of which way you do the cast.

Thank you, will look into it further when I get the time.


David J.




--
Achilleas Mantzios
DBA, Analyst, IT Lead
IT DEPT
Dynacom Tankers Mgmt

Re: pgsql 10.19 : "ERROR: cannot convert infinity to numeric" except there is no infinity

2022-07-20 Thread Achilleas Mantzios


On 19/7/22 20:32, Adrian Klaver wrote:

On 7/19/22 10:26 AM, Achilleas Mantzios wrote:

Thank you Adrian!


Actually thank:

https://sqlformat.darold.net/



Στις 19/7/22 18:36, ο/η Adrian Klaver έγραψε:

On 7/19/22 03:38, Achilleas Mantzios wrote:

I reformatted queries to see thing better.






AND numrange(ceptl.min_alarm::numeric, ceptl.max_alarm::numeric, '()') @> 
cept.value::numeric
ORDER BY
    1;

So the above fails. In your title when you say there is no infinity that means 
the cept.value, ceptl.min_alarm or ceptl.max_alarm  fields do not have any 
'-infinity' or 'infinity' values, correct?

There is infinity in cept.value , just not in this result set.
I got confused and wrongly assumed that since the result set (without the filter in the WHERE clause including cept.value::numeric) did not contain any infinity it should also work with the filter 
in the WHERE clause. Apparently a subplan executes this conversion in the WHERE before the other filters. I did not do any analyze to prove this.




Have you tried:

NULLIF(cept.value, 'inf')::numeric

no, cause the CTE version worked. Will keep in mind for similar future problems.



--
Achilleas Mantzios
DBA, Analyst, IT Lead
IT DEPT
Dynacom Tankers Mgmt













--
Achilleas Mantzios
DBA, Analyst, IT Lead
IT DEPT
Dynacom Tankers Mgmt

Re: PostgreSQL vs MariaDB

2023-03-25 Thread Achilleas Mantzios


Στις 24/3/23 13:07, ο/η Inzamam Shafiq έγραψε:

Hi Team,

Hope you are doing well.

Can someone please list pros and cons of MariaDB vs PostgreSQL that 
actually needs serious consideration while choosing the right database 
for large OLTP DBs (Terabytes)?




I would say that for small shops and simpler apps MySQL / MariaDB may be 
fine. If you go to enterprise class apps and infrastructure you'll start 
to need more, which means PostgreSQL.



For instance : unnest , transactional DDL, the extensions system, custom 
types, the MVCC etc those are very important features that some 
commercial DBs lack.





Thanks.

Regards,

/Inzamam Shafiq/
/Sr. DBA/


--
Achilleas Mantzios
 IT DEV - HEAD
 IT DEPT
 Dynacom Tankers Mgmt

Re: doc sql-grant.html Synopsis error?

2023-04-08 Thread Achilleas Mantzios


Στις 8/4/23 15:58, ο/η jian he έγραψε:


Hi.
--work as intended.
grant ALL PRIVILEGES on FUNCTION pg_catalog.pg_reload_conf() to test;
grant ALL PRIVILEGES on FUNCTION pg_reload_conf() to test;

-errors. it should be work, or I interpret the doc the 
wrong way?
GRANT ALL PRIVILEGES ON FUNCTION pg_reload_conf() IN SCHEMA pg_catalog 
TO test;
GRANT ALL PRIVILEGES ON FUNCTION pg_catalog.pg_reload_conf() IN SCHEMA 
pg_catalog TO test;


doc: https://www.postgresql.org/docs/current/sql-grant.html
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
 ON { { FUNCTION | PROCEDURE | ROUTINE }/|routine_name|/  [ ( [ 
[/|argmode|/  ] [/|arg_name|/  ]/|arg_type|/  [, ...] ] ) ] [, ...]
  | ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA/|schema_name|/  
[, ...] }
 TO/|role_specification|/  [, ...] [ WITH GRANT OPTION ]
 [ GRANTED BY/|role_specification|/  ]
I am using postgres 16, but the grant function part does not change.
What did I miss?


Hello, You missed the docs. The "IN SCHEMA" version always goes with ALL 
FUNCTIONS in the beginning. What would be the point specifying 
pg_catalog.pg_reload_conf() IN SCHEMA pg_catalog, i.e. giving the SCHEMA 
twice ?


--
Achilleas Mantzios
 IT DEV - HEAD
 IT DEPT
 Dynacom Tankers Mgmt

Re: Guidance on INSERT RETURNING order

2023-04-11 Thread Achilleas Mantzios


Στις 11/4/23 23:06, ο/η Adrian Klaver έγραψε:

On 4/11/23 12:47, Federico wrote:

Hello list,


https://www.sqlite.org/lang_returning.html#limitations_and_caveats


Searching the archive seems that a using the INSERT SELECT ORDER BY
form should be a better solution,
so the above insert should be rewritten as

 INSERT INTO t(data)
 SELECT data FROM (VALUES ('a', 1), ('b', 2), ('c', 3)) as vv(data,
num) ORDER BY num
 RETURNING id


Or

with i as (INSERT INTO t(data) VALUES ('a', 1), ('b', 2), ('c', 3) 
returning id)

select i.id from i order by id;

+1 for this version!



Sorry for the long email,
Thanks

 Federico





--
Achilleas Mantzios
 IT DEV - HEAD
 IT DEPT
 Dynacom Tankers Mgmt

Re: pg_basebackup / recovery

2023-04-12 Thread Achilleas Mantzios


Στις 13/4/23 01:31, ο/η Michael Paquier έγραψε:

On Wed, Apr 12, 2023 at 01:45:56PM +0300, Achilleas Mantzios - cloud wrote:

On 4/12/23 12:32, Fabrice Chapuis wrote:

During recovery process of a self contained backup, how postgres know to
stop reading wal when consistency is reached?

Because it knows the full packup info. It will observe the

STOP WAL LOCATION: 3BC7/4B000130 (file 00023BC7004B)

inside the backup file

There is a bit more to that in the recovery logic, depending mostly
on the presence of backup_ label file in the data folder when recovery
begins.  Once the backup_label is found at the beginning of recovery,
its information is stored in the control file and the file is renamed
to backup_label.old hence stopping the server when recovery has not
reached its expected point would rely on the control file contents
later on.  Then, the startup process and its WAL redo makes sure that
WAL replays until it finds the WAL record marking the end of the
backup.  Grepping for XLOG_BACKUP_END (WAL record type in this case)
shows all the areas that rely on that, and xlogrecovery.c covers the
most relevant bits.


Thank you for the info!

Sorry about my stupid typo "packup" :(


--
Michael


--
Achilleas Mantzios
 IT DEV - HEAD
 IT DEPT
 Dynacom Tankers Mgmt

Re: Copy data from DB2 (Linux) to PG

2018-11-02 Thread Achilleas Mantzios


On 1/11/18 7:27 μ.μ., Ravi Krishna wrote:

I have a project to develop a script/tool to copy data from DB2 to PG.  The 
approach I am thinking is

1. Export data from db2 in a text file, with, say pipe as delimiter.
2. Load the data from the text file to PG using COPY command.

In order to make it faster I can parallelize export and load with upto X number 
of tables concurrently.

Is there a tool in which I can avoid first exporting and then loading.  I think 
one way I can do it is by
using pipes by which I can export from db2 to a pipe and simultaneously load it 
to PG using COPY.

https://pgloader.io/


Any other tool for this job?

thanks.




--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Re: Logical replication hangs up.

2018-11-05 Thread Achilleas Mantzios

  len (rec/tot): 24/    24, tx:  0, lsn:
426/AAE55BB8, prev 426/AAE55B80, desc: SWITCH


I've also did the same for sent_lsn:

select pg_walfile_name('429/69E9CC60');

+--+
| pg_walfile_name  |
+--+
| 000104290069 |
+--+

/usr/lib/postgresql/10/bin/pg_waldump -s 429/69E9CC60
000104290069 | less
rmgr: Heap    len (rec/tot): 76/    76, tx:    5536495, lsn:
429/69E9CC60, prev 429/69E9CC10, desc: DELETE off 81 KEYS_UPDATED ,
blkref #0: rel 1663/16421/38572 blk 40
rmgr: Heap    len (rec/tot): 76/    76, tx:    5536495, lsn:
429/69E9CCB0, prev 429/69E9CC60, desc: DELETE off 82 KEYS_UPDATED ,
blkref #0: rel 1663/16421/38572 blk 40
rmgr: Heap    len (rec/tot): 76/    76, tx:    5536495, lsn:
429/69E9CD00, prev 429/69E9CCB0, desc: DELETE off 83 KEYS_UPDATED ,
blkref #0: rel 1663/16421/38572 blk 40

The relation 38572 from list above is an ordinary non replicated
table.

Any help or advice how to diagnose/get it working is highly
appreciated.

Kind regards Ales Zeleny





--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Re: LOG: incomplete startup packet

2018-11-08 Thread Achilleas Mantzios


On 8/11/18 11:19 π.μ., Pavel Demidov wrote:

Hello,

Time to time found in postgres.log the following message

01:35:22.608  LOG: incomplete startup packet
01:35:27.147  LOG: incomplete startup packet
01:35:52.593  LOG: incomplete startup packet
01:35:57.146  LOG: incomplete startup packet

01:36:22.596  LOG: incomplete startup packet
01:36:27.146  LOG: incomplete startup packet
01:36:52.593  LOG: incomplete startup packet
01:36:57.145  LOG: incomplete startup packet

Are any way exists how to identify the source. What need to collect and what 
need to see.

Just try enable log_connections and the previous line should tell you the IP.


Best Regards
Paul



--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

pgconf eu 2018 slides entry missing from https://wiki.postgresql.org/wiki/PostgreSQL_Related_Slides_and_Presentations

2018-11-18 Thread Achilleas Mantzios


Hello,

The pgconf eu 2018 entry is missing from 
https://wiki.postgresql.org/wiki/PostgreSQL_Related_Slides_and_Presentations 
.


I am afraid the more time passes since the event the harder to get 
people post their slides there, so pls update this ASAP.

Re: PostgreSQL MVCC and alternative REDO implementation - my thoughts

2018-11-18 Thread Achilleas Mantzios




On 18/11/18 2:04 μ.μ., Gerhard Wiesinger wrote:

On 18.11.2018 12:14, Martín Marqués wrote:

El 18/11/18 a las 04:49, Gerhard Wiesinger escribió:

Hello,

I just read that blog article and I think switching to REDO logic is 
not

a good idea.

I think you got it the other way around. Postgres implementes REDO, that
is, move forward by REDOing the changes which are stored in WAL files

BTW, could you point us to the blog you read this?

Maybe you got REDO and UNDO mixed up.



Yes, I ment UNDO ...

Link is here:

https://severalnines.com/blog/five-cool-things-i-learned-postgresql-conference-europe-2018 



sunday morning, too early :-)



In this blog I had the role of the reporter for the event :)

When and if this makes it to the core, both heaps will be supported. 
Nevertheless this sounds exciting feature to have, however lots of work 
must be done before PostgreSQL is able to adopt this in an elegant manner.





Ciao,

Gerhard

Re: pgconf eu 2018 slides entry missing from https://wiki.postgresql.org/wiki/PostgreSQL_Related_Slides_and_Presentations

2018-11-18 Thread Achilleas Mantzios

On 19/11/18 8:48 π.μ., Charles Clavadetscher wrote:

Hello Achilleas

-Original Message-
From: Achilleas Mantzios 
Sent: Sonntag, 18. November 2018 12:23
To: pgsql-gene...@postgresql.org
Subject: pgconf eu 2018 slides entry missing from
https://wiki.postgresql.org/wiki/PostgreSQL_Related_Slides_and_Presentatio
ns

Hello,

The pgconf eu 2018 entry is missing from
https://wiki.postgresql.org/wiki/PostgreSQL_Related_Slides_and_Presentatio
ns
.

I am afraid the more time passes since the event the harder to get
people post their slides there, so pls update this ASAP.

[>] I made an entry in the wiki:

https://wiki.postgresql.org/wiki/PostgreSQL_Conference_Europe_Talks_2018

As mentioned there, the slides are linked, as long as they have been delivered 
by the speakers, in the talk descriptions in the schedule.

Thank you! Lets hope the list fills up!

Bye
Charles

--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Re: pgconf eu 2018 slides entry missing from https://wiki.postgresql.org/wiki/PostgreSQL_Related_Slides_and_Presentations

2018-11-19 Thread Achilleas Mantzios


On 19/11/18 3:27 μ.μ., Stephen Frost wrote:

Greetings,

* Charles Clavadetscher (clavadetsc...@swisspug.org) wrote:

https://wiki.postgresql.org/wiki/PostgreSQL_Conference_Europe_Talks_2018

As mentioned there, the slides are linked, as long as they have been delivered 
by the speakers, in the talk descriptions in the schedule.

I'm not sure what the point of duplicating that information on to the
wiki is..?



It is nice to have a single source to all slides from all conferences, so at 
least a link to the conference page should be there on the wiki.




Seems like what we should do here is add a feature to the conference
system where a particular URL will produce a list of talks with links
to slides, if uploaded.  That shouldn't be hard to do if someone is
interested and knows a little python/django- the code is here:

https://git.postgresql.org/gitweb/?p=pgeu-website.git;a=summary

Patches very much accepted. :)

Thanks!

Stephen



--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Re: Reg: Query for DB growth size

2018-11-29 Thread Achilleas Mantzios

Hello,

On 29/11/18 10:15 π.μ., R.R. PRAVEEN RAJA wrote:

Hi Andreas,

I hope you did not understand my question. I am not asking query for current database size. I am asking for database growth, i mean predicting the increase in database size over a period like a
month just like its available in oracle. For oracle you can see the below link.

http://www.dba-oracle.com/t_database_growth_reports.htm

A huge list of monitoring solutions for Unix/Linux/PostgreSQL. The hot new
trend is Prometheus, also you could checkout nagios, icinga, munin, zabbix lots
and lots of options there.
You might also try a commercial offering (like e.g. Several Nines), they have
some pretty graphs, alarms, alerts of all kinds.
And last but not least, you could have your own monitoring table where you keep
periodic snapshots of the live metrics.
You may read here for an overview :
https://severalnines.com/blog/key-things-monitor-postgresql-analyzing-your-workload

Thanks in advance.

Regards,
PraveenRaja

*From:* Andreas Joseph Krogh
*Sent:* Thursday, November 29, 2018 1:10 PM
*To:* pgsql-general@lists.postgresql.org
*Subject:* Sv: Reg: Query for DB growth size
På torsdag 29. november 2018 kl. 07:41:24, skrev R.R. PRAVEEN RAJA
mailto:rrpraveenr...@live.com>>:

Hi All,
Can i get the query or steps for how to check the Database growth in
postgres. Thanks in advance.

select pg_size_pretty(pg_database_size(current_database()));
--
*Andreas Joseph Krogh*

--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Re: Transition Tables doesn´t have OID

2018-12-01 Thread Achilleas Mantzios




On 1/12/18 6:51 μ.μ., PegoraroF10 wrote:

ok
then, help me to find alternatives to it.
As I sad, sometimes whe change our PK, so using OID would be a smart way to
have a join between old and new transition tables and we would like to use
transition tables because each statement is a lot faster than each row for
auditing purposes.


Unique key with a sequence, which you'll have to leave alone.



So, whats the alternative ? One trigger for each row just for changing PK
values (will occur few times) and another trigger for each statement to do
the logging ?



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Database corruption in cascaded replica, "pg_xact/003A" doesn't exist, reading as zeroes"

2018-12-05 Thread Achilleas Mantzios


On 5/12/18 4:10 μ.μ., Dejan Petrovic wrote:

I believe this is a result of my "broken" procedure for setting up a cascaded 
replica. I would love to know where the issue is.

This is a report of a database corruption which was detected after promoting a replica server to a master server. We suspect the actual corruption occurred during replication of a two-level cascaded 
replica, however it was not detected until after it was promoted to master server when pg_dump and pg_restore failed. After re-checking postgresql logs on the corrupted node, we found this log line, 
which was the only indicator of a possible issue, but was overlooked: "2018-07-25 13:14:42 UTCLOG: file "pg_xact/003A" doesn't exist, reading as zeroes".


After extensive investigation, we detected three different cases of data 
corruption:
1.)Issue with pg_toast - happens during pg_dump or when row with corrupted data 
is selected
"pg_dump: Dumping the contents of table failed: PQgetResult() failed.
pg_dump: Error message from server: ERROR: missing chunk number 2 for toast value 
86123528 in pg_toast_41291242"

2.) Issue with uncommitted transactions, occurs when running VACUUM or 
pg_dump/pg_restore:
"ERROR: uncommitted xmin 60817551 from before xid cutoff 61487222 needs to be 
frozen
SQL state: XX001"

3.) 2 duplicated rows in different tables, violated PK unique constraint, 
occurs during pg_restore

This is how we fixed the three issues:
1.) Using bisection and SELECT, we found the row/column with corrupted data. We 
nulled the corrupt column and deleted the row (it was not critical, had no FKs)
2.) We ran VACUUM on all tables to check where this issue occured and updated all rows between reported xmin and xid cutoff with same data - this generated a new xmin on the broken rows, which fixed 
the issue.

3.) We removed the duplicated rows in a transaction, disabled all triggers 
before and enabled them right after
4.) Our final step is to do a full dump and restore on master DB so that the DB 
is rebuilt

This is the procedure we used to replicate the cascaded replica (master -> replica 
-> replica)

Notes:
Machines are running on Centos 7, Postgresql 10.2
DB-1 = master
DB-2 = replica of DB-1
DB-3 = replica of DB-2

--> DB-3 (new cascaded replica)
systemctl disable postgresql-10
systemctl stop postgresql-10
--> DB-1 (master)
psql -U postgres
select pg_start_backup('clone',true);
<- DB-3 (cascaded replica)
rsync -azv -e --delete DB-2:/isl/pgsql10/data /isl/pgsql10/ --exclude pg_wal 
--exclude postgresql.pid
assert rsync finished
vi recovery.conf
    standby_mode = 'on'
    primary_conninfo = 'host=DB-2'
    recovery_target_timeline = 'latest'


In addition to what Stephen wrote you, from : 
https://www.postgresql.org/docs/10/continuous-archiving.html
the low level backup method is relying on :
"1. Ensure that WAL archiving is enabled and working."
Have you setup this correctly?

Also there should at least be a restore_command inside your recovery.conf . Otherwise how do you know that DB-2 has the WALs you need? Whereas with your WALs archived it is guaranteed that the backup 
is consistent.


But anyway, its better to either use pg_basebackup or invest in some higher 
level tool like Stephen said.


-> DB-1
select pg_stop_backup();
<- DB-3
rsync -azv -e --delete DB-2:/isl/pgsql10/data/pg_wal /isl/pgsql10/data/
systemctl start postgresql-10
systemctl enable postgresql-10
tail log file

Any comments regarding the cascaded replication procedure or database 
corruption detection or resolution are welcome.

Best regards,

Dejan Petrovic
ISL Online





--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Re: finding out what's generating WALs

2018-12-11 Thread Achilleas Mantzios


On 11/12/18 4:00 μ.μ., Chris Withers wrote:

Hi All,

With a 9.4 cluster, what's the best way to find out what's generating the most 
WAL?

I'm looking after a multi-tenant PG 9.4 cluster, and we've started getting 
alerts for the number of WALs on the server.
It'd be great to understand what's generating all that WAL and what's likely to 
be causing any problems.\



One way is to keep snapshots of pg_stat_user_tables and then try to identify 
spikes based on the various _tup fields.
Another way is to take a look in your archive (where you keep your archived wals), try to identify a period where excessive wals were generated and then use 
https://www.postgresql.org/docs/11/pgwaldump.html to see what's in there.


More generally, what's number of WALs is "too much"? check_postgres.pl when used in nagios format only appears to be able to alert on absolute thresholds, does this always make sense? What's a good 
threshold to alert on?




Regarding you wals in pg_wal,  a good threshold could be anything more than a 
e.g. 10% increase from wal_keep_segments with a trend to go up. If this number 
goes up chances are something bad is happening.


cheers,

Chris




--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Re: why would postgres be throttling a streaming replication slot's sending?

2018-12-13 Thread Achilleas Mantzios


On 12/12/18 3:45 μ.μ., Chris Withers wrote:

On 11/12/2018 14:48, Achilleas Mantzios wrote:

On 11/12/18 4:00 μ.μ., Chris Withers wrote:


I'm looking after a multi-tenant PG 9.4 cluster, and we've started getting 
alerts for the number of WALs on the server.
It'd be great to understand what's generating all that WAL and what's likely to 
be causing any problems.\


Regarding you wals in pg_wal,  a good threshold could be anything more than a e.g. 10% increase from wal_keep_segments with a trend to go up. If this number goes up chances are something bad is 
happening.


wal_keep_segments is 0 here, so I went hunting in pg_replication_slots and 
found that it's the barman slot that's behind on the WAL.



Then your number WALs depends on the traffic generated after the previous checkpoint, the status of archiving and IMO the status of replication slots. The doc : 
https://www.postgresql.org/docs/11/runtime-config-replication.html doesn't specifically imply this last one, but it is documented here : https://www.postgresql.org/docs/10/wal-configuration.html , and 
it has happened to me. If a replication client with replication_slot dies, WALs accumulate till it wakes up again. (case in pgsql 10)



strace'ing on the barman receiving side shows it waiting on the socket, so no 
problem on that side.

on the sending side, it's a little confusing, since the server is basically idle, no cpu or disk activity, and yet strace'ing the sending process attached to the other end of the socket shows time 
being spent waiting on a poll which, while it includes the socket being sent to, also includes a bunch of pipes. I've attached a chunk of the output below in the hope that someone on this list could 
offer an explanation as to what might cause the WAL to be trickling over this port rather than catching up as fast as it can?


pg_stat_replication is what you should use for monitoring. IIRC you wont see 
some cols in the row of the barman.
Also you could query pg_replication_slots as :

select pg_wal_lsn_diff(pg_current_wal_lsn(),confirmed_flush_lsn),pg_wal_lsn_diff(pg_current_wal_lsn(),restart_lsn) from pg_replication_slots where active_pid=pg_stat_replication>;

 pg_wal_lsn_diff | pg_wal_lsn_diff
-+-
 312 | 1917736
(1 row)

In the first column you should see diff values (in bytes) close to zero. In the second you should see the length (window) of your replication slot in bytes, which means that this converted to 16MB 
should indicate an excess of the number of wals due to the replication slot in addition to the other factors (archiving, checkpoints). In my system it never reaches the size of a single wal.




cheers,

Chris

strace output for the streaming_barman slot servicing the barman replication 
slot:


IMO no need to go deep to gdb and stack traces unless you are sure there is a 
problem further down.



--- SIGUSR1 {si_signo=SIGUSR1, si_code=SI_USER, si_pid=3577, si_uid=26} ---
write(4, "\0", 1)   = 1
rt_sigreturn({mask=[]}) = -1 EINTR (Interrupted system call)
read(3, "\0", 16)   = 1
read(6, 0x7fffdcd7, 1)  = -1 EAGAIN (Resource temporarily 
unavailable)
recvfrom(10, 0x7fffdcaf, 1, 0, NULL, NULL) = -1 EAGAIN (Resource 
temporarily unavailable)
read(5, 
"\3\1\0\0\7\326\355\2\343\0\0\0@\n\0\0\0\17\1\230\313\4\0\0)\347\372l\0\0\0\0"...,
 4272) = 4272
sendto(10, "d\0\0\20\315w\0\0\4\313\230\1\17P\0\0\4\313\230\1 
\0\0\2\37\321\343\345\306\20\3\1"..., 4302, 0, NULL, 0) = 4302
read(3, 0x7fffdc90, 16) = -1 EAGAIN (Resource temporarily 
unavailable)
poll([{fd=10, events=POLLIN}, {fd=3, events=POLLIN}, {fd=6, events=POLLIN}], 3, 
26076) = ? ERESTART_RESTARTBLOCK (Interrupted by signal)
--- SIGUSR1 {si_signo=SIGUSR1, si_code=SI_USER, si_pid=3577, si_uid=26} ---
write(4, "\0", 1)   = 1
rt_sigreturn({mask=[]}) = -1 EINTR (Interrupted system call)
read(3, "\0", 16)   = 1
read(6, 0x7fffdcd7, 1)  = -1 EAGAIN (Resource temporarily 
unavailable)
recvfrom(10, 0x7fffdcaf, 1, 0, NULL, NULL) = -1 EAGAIN (Resource 
temporarily unavailable)
read(5, "~\320\5\0\3\0\0\0\0 
\1\230\313\4\0\0\3\0\0\0\0\0\0\0\323\316\314\0\0\0\0\0"..., 8192) = 8192
sendto(10, "d\0\0 \35w\0\0\4\313\230\1 
\0\0\0\4\313\230\1@\0\0\2\37\321\343\361\376\226~\320"..., 8222, 0, NULL, 0) = 8222
read(3, 0x7fffdc90, 16) = -1 EAGAIN (Resource temporarily 
unavailable)
poll([{fd=10, events=POLLIN}, {fd=3, events=POLLIN}, {fd=6, events=POLLIN}], 3, 
25275) = ? ERESTART_RESTARTBLOCK (Interrupted by signal)
--- SIGUSR1 {si_signo=SIGUSR1, si_code=SI_USER, si_pid=3577, si_uid=26} ---

Interesting numbers:
- process 3577 is the wal writer process
- fd 10 is the socket, 3 and 6 are pipes
- time is visib

Re: logical replication resiliency

2018-12-30 Thread Achilleas Mantzios

On 23/12/18 3:39 μ.μ., Istvan Soos wrote:

I have a database with about 1 TB of data, mostly inserts, little
updates and deletes, and I want to setup 2 logical replication for it.
What should be my expectation with the replication?

1. What is the time-window that either of the nodes can be offline?
- Will it survive if the node doing the publishing is offline for a
few hours and then goes back online?

No problem with that.

- Will it survive if the subscriber node is offline for a few hours
and then goes back online?
There is a problem with that. Due to the replication slot, the subscriber will start keeping WALs waiting for the subscriber to wake up. It depends on the amount of WAL produced, peak time or not, and
any other special situations (business) that could influence the amount of writes to the DB. You can easily find out by looking at your archive. Note that once the space dedicated for WALs is filled
up, your publisher node will PANIC.

2. If the replication is aborted for whatever reason, is there a way
to do it incrementally, or will it re-sync the 1TB at every such
event?

It is done incrementally, provided there is WAL continuity.

3. What alternative tools are out there to e.g. periodically sync from
one db to another?
PgSQL replication solutions in various forms, sizes and shapes are around since 2001. So plenty of tools out there + your custom scripts that you can always write. But since logical replication is in
the core (starting from pgsql 10.0) I see no reason not to use it.

You can have a look at an article I wrote here :
https://severalnines.com/blog/using-postgresql-logical-replication-maintain-always-date-readwrite-test-server

Thanks,
Istvan

--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Re: Thoughts on row-level security for webapps?

2019-01-02 Thread Achilleas Mantzios


On 31/12/18 6:57 μ.μ., Siegfried Bilstein wrote:

Hi all,

I'm evaluating using a tool called Postgraphile that generates a GraphSQL server from a postgres setup. The recommended way of handling security is to implement RLS within postgres and simply have 
the webserver take a cookie or similar and define which user is querying data.


I've normally built webapps like this: pull out user id from a session cookie -> 
the API endpoint verifies the user and whether or not it has access to the given data 
-> app code mutates the data.

With Postgraphile the request specifies the mutation and the server processes 
the request and relies on Postgres to determine if the user has correct access 
rights.

It seems like I would need to create a ROLE for every single member that signs 
up for my website which I'm a little concerned about.


Why?


Is this a common usage pattern for SQL security? Any gotchas relying on RLS?

--
Siggy Bilstein
CTO ofAyuda Care <https://www.ayudacare.com>
Book some time <https://calendly.com/siggy-cto> with me!



--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Re: Use bytearray for blobs or not?

2019-01-04 Thread Achilleas Mantzios


On 4/1/19 1:41 μ.μ., Thomas Güttler wrote:

Some months ago I wrote a little application with Python+Django which stores
blob data in bytearrays.

It works.

In the future there will be a lot more traffic, and I am unsure
if this is really a good solution. I know this is more FUD than
concrete PG issue.

What do you think?


Performance (at least for JDBC) is known to be better with blobs.
However, with bytea life is just easier for many reasons (backups, logical 
replication, other types of replication, sys management, etc).



Which alternatives could be useful?

Regards,
  Thomas







--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Re: Use bytearray for blobs or not?

2019-01-06 Thread Achilleas Mantzios


On 4/1/19 6:02 μ.μ., Rob Sargent wrote:


On 1/4/19 4:48 AM, Achilleas Mantzios wrote:

On 4/1/19 1:41 μ.μ., Thomas Güttler wrote:

Some months ago I wrote a little application with Python+Django which stores
blob data in bytearrays.

It works.

In the future there will be a lot more traffic, and I am unsure
if this is really a good solution. I know this is more FUD than
concrete PG issue.

What do you think?


Performance (at least for JDBC) is known to be better with blobs.


Do you have any details on which part is slower with bytea? Original insert, read back to client, general (p)sql access.  I'm moving towards bytea but still have time to change my mind if I cannot 
afford the performance hit.

No since I never used them (blobs) in production. Basically the java driver 
does not support streaming. So for very large data (e.g. videos, movies, etc) 
people use blobs.



However, with bytea life is just easier for many reasons (backups, logical 
replication, other types of replication, sys management, etc).

Yes, and in my case I even get a cheap compression from the original file.




Which alternatives could be useful?

Regards,
  Thomas












--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Re: the installation of pgadmin4 makes me weep in frustration

2019-01-14 Thread Achilleas Mantzios


On 14/1/19 9:58 π.μ., robert wrote:


Hi There


first, thanks a lot for the great an beautiful software we get from PostgreSQL 
and all people around it.

But I wonder how it comes, that installing pgadmin4 is so incredibly hard?

And no documentation.

I would like to install pgadmin4 to my ubuntu 18.1 laptop.

Where do I find doku on how to install pgadmin4. Preferably I would like to 
install it using pip?


it's no longer a standalone app, but rather a web app, follow the README's in 
the source. At least that was what I did, and I agree that it was not straight 
forward.



thanks

robert

--
Robert Rottermann CEO

031 333 10 20
rob...@redo2oo.ch <mailto:rob...@redo2oo.ch>
Sickingerstrasse 3, 3014 Bern <http://maps.apple.com/?q=Sickingerstrasse 3,3014 
Bern>

https://Redo2oo.ch <https://redo2oo.ch>

*Ihr Partner wenn es um ERP Lösungen geht.*




--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Re: Oracke BLOB to Postgres BYTEA using ora2pg

2019-01-15 Thread Achilleas Mantzios


Dobro jutro

On 15/1/19 10:39 π.μ., Mihalidesová Jana wrote:


Hi,

We try to migrate from oracle to postgres using ora2pg but we hit some weird 
behavior of bytea. Or it’s just our ignorance.

Table migration were ok, but we are not able to read bytea data. What we did 
wrong.



Assuming your data are jpeg images,  try smth like :
psql -c "copy (select encode(serializable_value,'hex') from alf_node_properties_zaloha 
where serializable_value is not null LIMIT 1) to stdout" | xxd -p -r > foo.jpg

then use some program (gwenview) to see the image.

If you want to dump all images (assuming again we are dealing with images 
here), then loop over each row and do the same as above.

But it is essential that you know what those values represent. Are they pdf 
docs? images? binaries? octet-streams?


Thank you for your help,




--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Re: Barman versus pgBackRest

2019-01-17 Thread Achilleas Mantzios

Hello,

One strong point of barman IMHO is transparently converting an incremental backup to a full backup for retention purposes, so retention specification is far more liberal than with pgbackrest, and
configuring for incremental backup does not pose any limitations to the schedule of backups.
In our environment our net connection to the remote site (repo) is extremely lousy, (although within Switzerland if that makes any difference), so with pgbackrest a full backup of our 1.3TB db, would
take about 2 days ,lets say set in cron weekly on sunday, (Sunday 1:00->Tuesday), then I would have to take incr backups from Wednesday->Saturday. And we would have to also take a full backup next
Sunday.

With pgbarman we had to set :
reuse_backup = link
retention_policy = RECOVERY WINDOW OF 14 DAYS
and just perform regular backups. So for barman every backup can be used as a
base for the next backup, which achieves fast backups and reduced disk space.
In pgbackrest one has to be explicit about the retention of both full backups
and diff backups.
it would be nice in pgbackrest to only have incremental backups and let the
system do the necessary management (converting incremental to full)
transparently and asynchronously, e.g.via cron.
I have read about the --repo-hardlink option.
"
This gives the appearance that each backup is a full backup at the file-system
level
"
So could we just take a first full backup and then switch permanently to incr
backups?

--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Re: Barman versus pgBackRest

2019-01-17 Thread Achilleas Mantzios


On 17/1/19 4:12 μ.μ., Jehan-Guillaume (ioguix) de Rorthais wrote:

On Thu, 17 Jan 2019 13:09:18 +0200
Achilleas Mantzios  wrote:


Hello,

One strong point of barman IMHO is transparently converting an incremental
backup to a full backup for retention purposes, so retention specification is
far more liberal than with pgbackrest, and configuring for incremental backup
does not pose any limitations to the schedule of backups. In our environment
our net connection to the remote site (repo) is extremely lousy, (although
within Switzerland if that makes any difference), so with pgbackrest a full
backup of our 1.3TB db, would take about 2 days ,lets say set in cron weekly
on sunday, (Sunday 1:00->Tuesday),  then I would have to take incr backups
from Wednesday->Saturday. And we would have to also take a full backup next
Sunday. With pgbarman we had to set : reuse_backup = link retention_policy =
RECOVERY WINDOW OF 14 DAYS and just perform regular backups. So for barman
every backup can be used as a base for the next backup, which achieves fast
backups and reduced disk space. In pgbackrest one has to be explicit about
the retention of both full backups and diff backups. it would be nice in
pgbackrest to only have incremental backups and let the system do the
necessary management (converting incremental to full) transparently and
asynchronously, e.g.via cron. I have read about the --repo-hardlink option. "
This gives the appearance that each backup is a full backup at the
file-system level " So could we just take a first full backup and then switch
permanently to incr backups?

Funnily enough, I opened an issue a month ago about this feature. If you want
to join the brainstorming, discuss, add some opinions and thoughts, see
https://github.com/pgbackrest/pgbackrest/issues/644

Surely I enjoyed your convo there!





--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Re: Upgrading from 9.2.15 to 9.4.20

2019-01-18 Thread Achilleas Mantzios


On 18/1/19 12:30 μ.μ., Michal wrote:

Im trying to upgrade PostgreSQL from 9.2.15 to 9.4.20
with this :
  /usr/pgsql-9.4/bin/pg_upgrade --old-bindir=/usr/bin
--new-bindir=/usr/pgsql-9.4/bin --old-datadir=/var/lib/pgsql/  data
--new-datadir=/var/lib/pgsql/9.4/data

Can anybody help me with this? I also try -p port parameter not helped. Both
databse instances are shut down.

that give me this output :

Performing Consistency Checks
-
Checking cluster versions   ok

*failure*
Consult the last few lines of "pg_upgrade_server.log" for
the probable cause of the failure.

connection to database failed: could not connect to server: No such file or
directory
 Is the server running locally and accepting
 connections on Unix domain socket "/run/postgresql/.s.PGSQL.50432"?


could not connect to old postmaster started with the command:
"/usr/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/pgsql/data" -o
"-p 50432 -b  -c listen_addresses='' -c unix_so  cket_permissions=0700 -c
unix_socket_directory='/run/postgresql'" start
Failure, exiting

tweak your .pgpass a little bit, I remember from 9.3 to 10 I had to define :
/var/lib/pgsql:50432:*:postgres:

or just make local UNIX connections trusted in pg_hba.conf
also try to start the old postmaster by hand using the command shown and look 
to verify that you can locate the socket under /run/postgresql/.s.PGSQL.50432

make sure that the dir is there as well.






--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html




--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Re: PostgreSQL logical replication depends on WAL segments?

On 22/1/19 3:18 μ.μ., Josef Machytka wrote:

Hello, I already tried to ask on stackoverflow but so far without success.
(https://stackoverflow.com/questions/54292816/postgresql-logical-replication-depends-on-wal-segments)

Could someone help me please?

I am successfully using logical replication between 2 PG 11 cloud VMs for latest data. But I tried to publish also some older tables to transfer data between databases and got strange error about
missing WAL segment.

These older partitions contain data 5-6 days old. I successfully published them on master and refreshed subscription on logical replica. But now I am getting these strange error messages on logical
replica:

|2019-01-21 15:03:14.713 UTC [17203] LOG: logical replication table synchronization worker for subscription "mysubscription", table "mytable_20190115" has finished 2019-01-21 15:03:19.768 UTC
[18877] LOG: logical replication apply worker for subscription "mysubscription" has started 2019-01-21 15:03:19.797 UTC [18877] ERROR: could not receive data from WAL stream: ERROR: requested WAL
segment 0001098E00CB has already been removed 2019-01-21 15:03:19.799 UTC [29534] LOG: background worker "logical replication worker" (PID 18877) exited with exit code 1 2019-01-21
15:03:24.806 UTC [18910] LOG: logical replication apply worker for subscription "mysubscription" has started 2019-01-21 15:03:24.824 UTC [18911] LOG: logical replication table synchronization worker
for subscription "mysubscription", table "mytable_20190116" has started 2019-01-21 15:03:24.831 UTC [18910] ERROR: could not receive data from WAL stream: ERROR: requested WAL segment
0001098E00CB has already been removed 2019-01-21 15:03:24.834 UTC [29534] LOG: background worker "logical replication worker" (PID 18910) exited with exit code 1 |

if you have WAL archiving enabled then try to find the missing WALs and copy
them inside your data/pg_wal directory .
Normally the replication slot will retain all logs that have not been applied
to the subscriber yet. So what you describe is not normal. Do you have any cron
task that touches files in this dir?

Which is confusing for me. I tried to find some info but did not find anything
about logical replication depending on WAL segments.

There is no streaming replication running on that particular master and these
error messages I see on both master and replica connected with only logical
replication.

Am I doing something wrong? Is there some special way how to publish older
data? For newer data and latest data all works without problems.

Of course since I published like ~20 tables it took some time for replica to process all tables - currently it processes always 2 at the time. But I still do not understand why it should depend on
WAL segments... Thank you very much.

I tried to unpublished and unsubscribe these older tables and publish and
subscribe them again but getting still the same error message for the exactly
the same WAL segment number.

**I unpublished and unsubscribed those problematic tables and error messages
stopped so they were definitely related to logical replication. Could they be
caused by snapshot?

I even made additional strange experience with WAL segments errors - my logical replica had only quite small disk and during all that fiddling I forgot to check disk usage. So postgresql on logical
replica crashed due to full disk. Since I use GCE I just resized root disk and after restart of the instance got more space. But I also got back missing WAL segments errors in connections with
logical replication. My postgresql log on replica is now full of sequence of these 3 lines:

|2019-01-22 09:47:14.408 UTC [1946] LOG: logical replication apply worker for subscription "mysubscription" has started 2019-01-22 09:47:14.429 UTC [1946] ERROR: could not receive data from WAL
stream: ERROR: requested WAL segment 0001099D007A has already been removed 2019-01-22 09:47:14.431 UTC [737] LOG: background worker "logical replication worker" (PID 1946) exited with
exit code 1 |

Why logical replication depends on some old WAL segments? Today's data seem to work perfectly although there cannot be all WAL segments for today available on the logical master. But I am unable to
publish older data...

Thanks for help.

Josef Machytka

--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Re: PostgreSQL logical replication depends on WAL segments?


On 22/1/19 6:01 μ.μ., Josef Machytka wrote:


https://www.postgresql.org/docs/11/logical-replication-architecture.html


Thanks, I see... So if I understand it correctly - since I have quite big partitions like ~30 GB each in one parent table and from ~1GB to ~5 GB in several others I presume I had to set 
wal_keep_segments to some really high number and stop our security cronjob cleaning old WAL segments (because we already had some problems with almost full disk due to old WAL segments)


I asked you if you had any cron jobs messing around with your WALs. The WALs in pg_wal dir is PostgreSQL's business to manage, not your scripts. Dont do that. Defining wal_keep_segments to a very 
large value or using replication slots (as with logical repl) won't help if your crons are deleting the WALs.

Now what you have to do :
- read up about the basic concepts of pgsql administration : 
https://www.postgresql.org/docs/11/admin.html
- understand how postgresql replication slots work (logical repl is based on 
those)
- understand how WAL archiving works
- understand how streaming replication works
- understand how logical replication works
- understand how the various runtime configuration params act on the number of 
WALs kept

until the whole transfer of snapshot is done. Because only after the whole snapshot is transferred logical replication workers start to transfer WAL logs reflecting changes done from the moment 
snapshot was taken...


jm



--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Re: PostgreSQL logical replication depends on WAL segments?

On 22/1/19 8:59 μ.μ., Jeremy Finzel wrote:

On Tue, Jan 22, 2019 at 12:52 PM Andres Freund > wrote:

Hi,

On 2019-01-22 11:10:27 -0600, Jeremy Finzel wrote:
> P.S. do heed the advice of the others and get more familiar with
the docs
> around WAL archiving.

Logical replication doesn't normally interact with WAL archiving
in any
way, so that seems orthogonal.

But he is manually removing WAL files via cron which should normally 
be held until the replication slot doesn't need it anymore.  I do 
believe that is why he is getting errors.  The suggestion is to rely 
on Postgres' WAL archiving rather than removing WAL files manually...

wal retention in pg_wal and wal archiving are different things. The OP 
got problems cause he deliberately went on to delete files in pg_wal 
which means he used the wrong method to address a wrong situation.

However, if the OP has still those WAL files archived he can use them to 
bring logical replication back in synch. Don't ask me how I know : 
https://www.postgresql.org/message-id/ae8812c3-d138-73b7-537a-a273e15ef6e1%40matrix.gatewaynet.com

Thanks,
Jeremy

Re: PostgreSQL logical replication depends on WAL segments?



On 22/1/19 10:18 μ.μ., Jeremy Finzel wrote:
On Tue, Jan 22, 2019 at 2:16 PM Jeremy Finzel > wrote:


any one of those WAL files will not be archived and
recycled as long as it no longer needed by a replication slot


:(.  s/no longer/still.  I meant to say any one of those WAL files 
will not be archived and recycled as long as it still needed by a 
replication slot.



A WAL segment will be archived as soon as it is completed. 
|https://www.postgresql.org/docs/11/continuous-archiving.html

|

Re: PostgreSQL logical replication depends on WAL segments?