Re: V9.5

[Michael Paquier]

On Tue, Mar 10, 2020 at 12:23:49PM +0530, Sonam Sharma wrote:
> We have pg_read_all_stats role from v9.6. do we have a similar role for
> v9.5 and lower versions ?

No, and pg_read_all_stats has been introduced in Postgres 10, not 9.6:
https://www.postgresql.org/docs/10/release-10.html
--
Michael


signature.asc
Description: PGP signature

Streaming replication - 11.5

[Nicola Contu]

Hello,
I have two servers connected to the same switch running postgres 11.5

I am trying to replicate one of those servers after a planned work on the
master, so the replica has been lost. It has always worked but now I get
this :

pg_basebackup: could not receive data from WAL stream: server closed the
connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.

I don't really understand what the issue is.
I had this issue last week as well in another DC and I had to reboot the
slave to make it working (not sure why it helped)

Do you know what can cause this?

Thank you,
Nicola

Re: Is it safe to rename an index through pg_class update?

[Kouber Saparev]

На пн, 9.03.2020 г. в 20:34 Andres Freund  написа:

> You'd not have to take an ACCESS EXCLUSIVE. A lower level would
> suffice, e.g. SHARE UPDATE EXCLUSIVE, which still allows data changes.
>
> > There is nobody else doing DDLs except me - Mr. DBA, so I guess I am
> > safe on this side. ;)
>
> If autovacuum triggered a vacuum/analyze it'd would e.g. also try to
> update pg_class.
>

I can so to say then use the strategy behind Peter Eisentraut's patch
(reduce index rename locks) applied in version 12 in my case (9.3) manually.
As far as I can see (and understand the source code), only the table
holding the index is locked (and not pg_class).

db=# begin;
BEGIN
db=*# lock table x in share update exclusive mode;
LOCK TABLE
db=*# update pg_class set relname = 'y_idx' where oid = 'x_idx'::regclass;
UPDATE 1
db=*# commit;
COMMIT
It looks good. The only exceptional case I am able to discover is when the
index is used within a constraint, in which case I should also update
pg_constraint.
Thank's again for the accurate responses.

Regards,
--
Kouber Saparev

Re: How to discover what table is

[PegoraroF10]

Now I have the same problem with a different message.
I´ve added a table on all schemas and did a refresh publication. when
postgres sees a new table on publisher he goes to replicamand trucates that
table to start copying. ok but I have 300 schemas, how can I know what
schema that table belongs to ?

On log of replica server ...

Message
cannot truncate a table referenced in a foreign key constraint  
Detail
Table "rel_indicacao" references "cad_digitacao".   
Hint
Truncate table "rel_indicacao" at the same time, or use TRUNCATE ...
CASCADE.




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

Re: select * from test where name like 'co_%'

[sivapostg...@yahoo.com]

Hello,
What returns when I run a query like this;
Select * from test where name like 'co_%';
I expect anything that starts with 'co_' and NOT 'co' only.  Am I right?  But I 
get every names that starts with 'co'. Why ?
Happiness Always
BKR Sivaprakash

Re: select * from test where name like 'co_%'

[Kenneth Marshall]

On Tue, Mar 10, 2020 at 12:49:01PM +, sivapostg...@yahoo.com wrote:
> Hello,
> What returns when I run a query like this;
> Select * from test where name like 'co_%';
> I expect anything that starts with 'co_' and NOT 'co' only.  Am I right?  But 
> I get every names that starts with 'co'. Why ?
> Happiness Always
> BKR Sivaprakash

Hi,

Check out the documentation:

https://www.postgresql.org/docs/12/functions-matching.html#FUNCTIONS-LIKE

You have 2 meta characters there. The '_' matches any character also.

Regards,
Ken

Re: select * from test where name like 'co_%'

[Paul Foerster]

Hi,

an underscore matches a single character, any character. You'd have to
escape it and tell the query what the escape character is if you want
it to be treated as a standard character:

db=# create table t(t text);
CREATE TABLE
db=# insert into t(t) values ('fox'), ('fo_'), ('fo_x');
INSERT 0 3
db=# select * from t;
  t
--
 fox
 fo_
 fo_x
(3 rows)

db=# select * from t where t like 'fo_%';
  t
--
 fox
 fo_
 fo_x
(3 rows)

db=# select * from t where t like 'fo\_%' escape '\';
  t
--
 fo_
 fo_x
(2 rows)

Cheers,
Paul

On Tue, Mar 10, 2020 at 1:49 PM sivapostg...@yahoo.com
 wrote:
>
> Hello,
>
> What returns when I run a query like this;
>
> Select * from test where name like 'co_%';
>
> I expect anything that starts with 'co_' and NOT 'co' only.  Am I right?  But 
> I get every names that starts with 'co'. Why ?
>
> Happiness Always
> BKR Sivaprakash
>

Re: select * from test where name like 'co_%'

[sivapostg...@yahoo.com]

 Thanks.  Got it.
On Tuesday, 10 March, 2020, 06:30:27 pm IST, Paul Foerster 
 wrote:  
 
 Hi,

an underscore matches a single character, any character. You'd have to
escape it and tell the query what the escape character is if you want
it to be treated as a standard character:

db=# create table t(t text);
CREATE TABLE
db=# insert into t(t) values ('fox'), ('fo_'), ('fo_x');
INSERT 0 3
db=# select * from t;
  t
--
 fox
 fo_
 fo_x
(3 rows)

db=# select * from t where t like 'fo_%';
  t
--
 fox
 fo_
 fo_x
(3 rows)

db=# select * from t where t like 'fo\_%' escape '\';
  t
--
 fo_
 fo_x
(2 rows)

Cheers,
Paul

On Tue, Mar 10, 2020 at 1:49 PM sivapostg...@yahoo.com
 wrote:
>
> Hello,
>
> What returns when I run a query like this;
>
> Select * from test where name like 'co_%';
>
> I expect anything that starts with 'co_' and NOT 'co' only.  Am I right?  But 
> I get every names that starts with 'co'. Why ?
>
> Happiness Always
> BKR Sivaprakash
>


  

Re: How to discover what table is

[Adrian Klaver]

On 3/10/20 5:16 AM, PegoraroF10 wrote:

Now I have the same problem with a different message.
I´ve added a table on all schemas and did a refresh publication. when
postgres sees a new table on publisher he goes to replicamand trucates that
table to start copying. ok but I have 300 schemas, how can I know what
schema that table belongs to ?


Postgres version(s)?

Are you using the built in logical replication or the pglogical plugin?




On log of replica server ...

Message
cannot truncate a table referenced in a foreign key constraint  
Detail
Table "rel_indicacao" references "cad_digitacao".   
Hint
Truncate table "rel_indicacao" at the same time, or use TRUNCATE ...
CASCADE.




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





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

Re: Streaming replication - 11.5

[Adrian Klaver]

On 3/10/20 2:26 AM, Nicola Contu wrote:

Hello,
I have two servers connected to the same switch running postgres 11.5

I am trying to replicate one of those servers after a planned work on 
the master, so the replica has been lost. It has always worked but now I 
get this :


pg_basebackup: could not receive data from WAL stream: server closed the 
connection unexpectedly

         This probably means the server terminated abnormally
         before or while processing the request.

I don't really understand what the issue is.


I would start with the logs from the Postgres server you are taking the 
backup from.


I had this issue last week as well in another DC and I had to reboot the 
slave to make it working (not sure why it helped)


Do you know what can cause this?

Thank you,
Nicola



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

Re: How to discover what table is

[PegoraroF10]

built in logical replication



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

Patterns to look for in the PostgreSQL server log

[Mageshwaran Janarthanam]

Hi Team...I am trying to setup some monitoring over the PostgreSQL server
log. I am not clear which error I should be most concerned about. Could you
please share your thoughts on what pattern I should search in the log file?

Re: Real application clustering in postgres.

[Peter J. Holzer]

On 2020-03-09 09:57:37 +0100, Laurenz Albe wrote:
> On Sun, 2020-03-08 at 21:13 +0100, Peter J. Holzer wrote:
> > But to be fair, a master/slave setup a la patroni isn't immune against
> > "writing junk" either: Not on the hardware level (either of the nodes
> > may have faulty hardware, and you may not notice it until too late), and
> > more importantly, not on the software level. An erroneus DML statement
> > (because of a bug in the application, or because the user/admin made a
> > mistake) will cause the same wrong data to be distributed to all nodes
> > (of course this also applies to RAC).
> 
> Of course, nobody debates that.
> 
> A high-availability solution only protects you from certain, well-defined
> kinds of problems, usually related to hardware.

Right. And enterprise class SAN storage does this: It protects you from
failure of a single disk, a single cable, a single controller. Very
often you can physically spread out the components so that loss of a
whole rack (or server room) wouldn't affect availability. There are of
course limits: When a message sent over a single cable is corrupted in a
way that the checksum doesn't catch, corrupted data may be stored. But
then if data in RAM is corrupted that ECC doesn't catch, the same will
happen. A Patroni-based cluster isn't free of single points of failure
either.

So I don't buy the argument "X isn't a high availability solution
because it uses shared storage". While I have seen expensive SAN boxes
fail, I've also managed to wreck Patroni clusters. I'm not at all
convinced that the availability of a Patroni cluster is higher than that
of a failover cluster using shared storage. 

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: Patterns to look for in the PostgreSQL server log

[Adrian Klaver]

On 3/10/20 9:57 AM, Mageshwaran Janarthanam wrote:
Hi Team...I am trying to setup some monitoring over the PostgreSQL 
server log. I am not clear which error I should be most concerned about. 
Could you please share your thoughts on what pattern I should search in 
the log file?


That really depends on you. To get an idea of what the message levels 
are being triggered for take a look at:


https://www.postgresql.org/docs/12/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT

Table 19.1. Message Severity Levels


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

Re: How to discover what table is

[Adrian Klaver]

On 3/10/20 8:42 AM, PegoraroF10 wrote:

built in logical replication


Well it does not do DDL replication so I am not sure how the new table 
is getting to the replica?


It might help if you provide an start to end example of what you are doing.





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





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

Re: Streaming replication - 11.5

[Adrian Klaver]

On 3/10/20 8:17 AM, Nicola Contu wrote:
Please post to list also.
Ccing list.

What came immediately before the temporary file error?

   2020-03-10 15:10:17 GMT [[local]] [28171]: [1-1] 
db=postgres,user=postgres LOG:  temporary file: path 
"base/pgsql_tmp/pgsql_tmp28171.0", size 382474936
2020-03-10 15:10:17 GMT [[local]] [28171]: [4-1] 
db=postgres,user=postgres LOG:  could not send data to client: Broken pipe
2020-03-10 15:10:17 GMT [[local]] [28171]: [5-1] 
db=postgres,user=postgres FATAL:  connection to client lost
2020-03-10 15:10:26 GMT [] [12598]: [3544-1] db=,user= LOG: 
  restartpoint complete: wrote 37315 buffers (0.4%); 0 WAL file(s) 
added, 0 removed, 16 recycled; write=269.943 s, sync=0.039 s, 
total=269.999 s; sync files=1010, longest=0.001 s, average=0.000 s; 
distance=175940 kB, estimate=416149 kB
2020-03-10 15:10:26 GMT [] [12598]: [3545-1] db=,user= LOG:  recovery 
restart point at 6424/1D7DEDE8


It is a cascade replication

Il giorno mar 10 mar 2020 alle ore 15:58 Adrian Klaver 
mailto:adrian.kla...@aklaver.com>> ha scritto:


On 3/10/20 2:26 AM, Nicola Contu wrote:
 > Hello,
 > I have two servers connected to the same switch running postgres 11.5
 >
 > I am trying to replicate one of those servers after a planned
work on
 > the master, so the replica has been lost. It has always worked
but now I
 > get this :
 >
 > pg_basebackup: could not receive data from WAL stream: server
closed the
 > connection unexpectedly
 >          This probably means the server terminated abnormally
 >          before or while processing the request.
 >
 > I don't really understand what the issue is.

I would start with the logs from the Postgres server you are taking the
backup from.

 > I had this issue last week as well in another DC and I had to
reboot the
 > slave to make it working (not sure why it helped)
 >
 > Do you know what can cause this?
 >
 > Thank you,
 > Nicola


-- 
Adrian Klaver

adrian.kla...@aklaver.com 




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