Re: Re: Re: Enabling checksums on a streaming replica

[Brad Nicholson]

Michael Paquier  wrote on 06/27/2019 11:17:10 PM:

> From: Michael Paquier 
> To: Brad Nicholson 
> Cc: Grigory Smolkin , pgsql-
> gene...@lists.postgresql.org
> Date: 06/27/2019 11:17 PM
> Subject: [EXTERNAL] Re: Re: Enabling checksums on a streaming replica
>
> On Wed, Jun 26, 2019 at 02:08:19PM -0400, Brad Nicholson wrote:
> > In testing, it doesn't appear to matter.  I've ensured that I've
generated
> > some full page writes (confirmed via pg_waldump), and those apply
> > fine.
>
> Full pages writes are first written from shared buffers to WAL, where
> their checksums does not actually apply.  When the WAL records are
> read an applied, a full page image is recovered in shared buffers.
> The checksum of the page would get updated once the shared buffer page
> used is evicted and written back to disk.
>

So if all the checksums are being recalculated on the replica, this
approach should be relatively safe, should it not?

Assuming pg_checksums is doing the right thing (and it looks to me like it
should be).

Brad.

Re: patch 11.2 to 11.4

[Fabrízio de Royes Mello]

Em qui, 27 de jun de 2019 às 22:01, Prakash Ramakrishnan <
prakash.ramakrishnan...@nielsen.com> escreveu:

> Hi Fabrizo,
>
> After applying the update command getting this error ,
>
> postg...@shacoyuhss001.enterprisenet.org:/home/postgres
> ==> psql
> psql (11.4, server 11.3)
> Type "help" for help.
>
> postgres=#
> postgres=# select version();
>  version
>
> -
>  PostgreSQL 11.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
> 20150623 (Red Hat 4.8.5-36), 64-bit
> (1 row)
>
> Please do the needful.
>
>
During a normal "yum update" (if I'm not wrong) PostgreSQL is restarted...
If not you should restart manually.

Regards,

-- 
   Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Analyze command running for 2063 minutes so far

[tony]

Hello,

I started an Analyze command on a database Wednesday evening at around 
9:00PM. it is now Friday morning at 8:00 and it is still running.


If I execute the query:
select max(last_analyze) from pg_stat_user_tables;

The result I receive is:
6/27/2019 8:27 AM

So it appears that no analyze information has been written to the 
pg_stat_user_tables for 24 hours.


When I execute the "top" command the postgres instance running the 
"analyze" command is using 97 to 100% of one of the cores.


Currently the overall performance of the server is not being impacted. I 
have eleven other cores and it is seldom that more than 9 have 90% or 
more utilization and the SSD appears to not be slowed much by whatever 
is happening.


I did try to execute:
SELECT pg_cancel_backend(4029);
and
SELECT pg_terminate_backend(4029);
but neither had any effect.

Since this is not currently causing any user performance issues I don't 
plan to try to do anything else until this weekend.


Since the pg_cancel_backend and pg_terminate_backend have not had any 
effect, what should I expect from shutting postgresql down and bringing 
it back up?


I am running PostgreSQL version 9.3 on Ubuntu 14.04 with 128GB of 
memory, 800GB PCIe SSD for Database files, 1TB SATA SSD for WAL, 512GB 
SATA SSD for system files.


Thank you for your assistance.

Re: patch 11.2 to 11.4

[Prakash Ramakrishnan]

Thanks lot it's working fine now.

Regards,
Prakash.R

On Fri, Jun 28, 2019, 17:46 Fabrízio de Royes Mello 
wrote:

>
> Em qui, 27 de jun de 2019 às 22:01, Prakash Ramakrishnan <
> prakash.ramakrishnan...@nielsen.com> escreveu:
>
>> Hi Fabrizo,
>>
>> After applying the update command getting this error ,
>>
>> postg...@shacoyuhss001.enterprisenet.org:/home/postgres
>> ==> psql
>> psql (11.4, server 11.3)
>> Type "help" for help.
>>
>> postgres=#
>> postgres=# select version();
>>  version
>>
>> -
>>  PostgreSQL 11.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
>> 20150623 (Red Hat 4.8.5-36), 64-bit
>> (1 row)
>>
>> Please do the needful.
>>
>>
> During a normal "yum update" (if I'm not wrong) PostgreSQL is restarted...
> If not you should restart manually.
>
> Regards,
>
> --
>Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
>PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
>

Re: Analyze command running for 2063 minutes so far

[Tom Lane]

t...@exquisiteimages.com writes:
> I started an Analyze command on a database Wednesday evening at around 
> 9:00PM. it is now Friday morning at 8:00 and it is still running.
> ...
> I did try to execute:
> SELECT pg_cancel_backend(4029);
> and
> SELECT pg_terminate_backend(4029);
> but neither had any effect.

Hm, that's interesting.  Can you get a stack trace from that process?

https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend

> I am running PostgreSQL version 9.3 on Ubuntu 14.04 with 128GB of 
> memory, 800GB PCIe SSD for Database files, 1TB SATA SSD for WAL, 512GB 
> SATA SSD for system files.

9.3.what exactly?

(You do know that 9.3.x is out of support, so even if this investigation
reveals a bug, we're not going to fix it in 9.3.x.  I'm willing to look
anyway on the chance that there's a bug that also affects later versions.)

regards, tom lane

Re: Analyze command running for 2063 minutes so far

[tony]

On 2019-06-28 10:15, Tom Lane wrote:

t...@exquisiteimages.com writes:

I started an Analyze command on a database Wednesday evening at around
9:00PM. it is now Friday morning at 8:00 and it is still running.
...
I did try to execute:
SELECT pg_cancel_backend(4029);
and
SELECT pg_terminate_backend(4029);
but neither had any effect.


Hm, that's interesting.  Can you get a stack trace from that process?

https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend


I am running PostgreSQL version 9.3 on Ubuntu 14.04 with 128GB of
memory, 800GB PCIe SSD for Database files, 1TB SATA SSD for WAL, 512GB
SATA SSD for system files.


9.3.what exactly?

(You do know that 9.3.x is out of support, so even if this 
investigation

reveals a bug, we're not going to fix it in 9.3.x.  I'm willing to look
anyway on the chance that there's a bug that also affects later 
versions.)


regards, tom lane


Thanks for the offer to look at it Tom. Fortunately or unfortunately as 
the case may be, after I installed everything to get the stack trace the 
Analyze process actually finished. The max(last_analyze) did not change 
and is still showing '6/27/2019 8:27 AM', so I am not sure what it was 
doing all this time, but nothing seems the worse for it.


Thanks again.

how to understand checkpoint information in pg_control data

[Pavan Kumar]

Hello Experts,

I have couple of questions on pg_controldata output.

postgres=# \! pg_controldata $PGDATA|grep checkpoint


*Latest checkpoint location:   2/9D029BB0,  Latest checkpoint's
REDO location:2/97002D60Latest checkpoint's REDO WAL file:
000300020097*
Latest checkpoint's TimeLineID:   3
Latest checkpoint's PrevTimeLineID:   3
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:  0:1732072
Latest checkpoint's NextOID:  40985
Latest checkpoint's NextMultiXactId:  1
Latest checkpoint's NextMultiOffset:  0
Latest checkpoint's oldestXID:561
Latest checkpoint's oldestXID's DB:   1
Latest checkpoint's oldestActiveXID:  1731942
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint:Fri 28 Jun 2019 02:59:24 PM GMT

What is "Latest checkpoint location"
what is  "Latest checkpoint's REDO location". to my understanding, it is
records lsn number. correct me if I am wrong?
Latest checkpoint's REDO WAL file:000300020097,  to my
understanding  this wal file has the latest record lsn , from where
recovery will start and rollover. please correct me if I am wrong ?







-- 



*Regards,#!  Pavan Kumar--*-
*Sr. Database Administrator..!*
*NEXT GENERATION PROFESSIONALS, LLC*
*Cell#  267-799-3182 #  pavan.dba27 (Gtalk)  *
*India   # 9000459083*

*Take Risks; if you win, you will be very happy. If you lose you will be
Wise  *

iterate over partitions

[Glenn Schultz]

Hi All,

I have a large table partioned by month.  I would like to run a query -
which adds derived data to the current data and inserts the data into a new
table.  The new table is the target for users.

How can I iterate over the partition tables to insert data and build the
new table?
This is done monthly, am I better off creating a materalized view each
month?

Best,
Glenn

Re: PostgreSQL 11 Auto vacuum

[Michael Lewis]

>
> Actually we have notice that Auto vacuum in PG10  keeps vacuuming the
> master tables and  that takes a lot of time and Don't go the child table to
> remove the dead tuples.
>

What do the logs say actually got done during these long running
autovacuums? Is it feasible to increase the work allowed before autovacuum
stops (autovacuum_vacuum_cost_limit) or perhaps increase the number of
workers? What is the update/deletes workload balance? That is, would it
make sense to decrease the fillfactor on these tables such that you get
more HOT (heap only tuple) updates and indexes are less bloated to get
better performance that way? How often are you manually vacuuming?

create extension points to the wrong directory

[Benedict Holland]

Hello all,

I really can't figure this one out. I am trying to get debug working on a
postgrseql 10 database on ubuntu. I installed the proper package
postgresql-10-pldebugger*. *When I go to create the extention, I get the
error:

create extension pldbgapi
ERROR: could not open extension control file
"/usr/share/postgresql/9.5/extension/pldbgapi.control": No such file or
directory

Which is true because it is in
/usr/share/postgrseql/10/extension/pldbgapi.control. I am trying to run
this command in pgadmin4. I am stuck. There doesn't seem to be a
configuration option. Any ideas?

Thanks,
~Ben

Re: create extension points to the wrong directory

[Rob Sargent]

On 6/28/19 5:41 PM, Benedict Holland wrote:

Hello all,

I really can't figure this one out. I am trying to get debug working 
on a postgrseql 10 database on ubuntu. I installed the proper package 
postgresql-10-pldebugger*. *When I go to create the extention, I get 
the error:


create extension pldbgapi
ERROR: could not open extension control file 
"/usr/share/postgresql/9.5/extension/pldbgapi.control": No such file 
or directory
Which is true because it is in 
/usr/share/postgrseql/10/extension/pldbgapi.control. I am trying to 
run this command in pgadmin4. I am stuck. There doesn't seem to be a 
configuration option. Any ideas?


Thanks,
~Ben



Kinda late in the day so most of the pros might not see this.

maybe uninstall postgresql-9.5

Can you try same create extension from psql directly?

select version(); ? version 10

select * from pg_settings where name ~ 'dir';

Re: create extension points to the wrong directory

[Adrian Klaver]

On 6/28/19 4:41 PM, Benedict Holland wrote:

Hello all,

I really can't figure this one out. I am trying to get debug working on 
a postgrseql 10 database on ubuntu. I installed the proper package 
postgresql-10-pldebugger*. *When I go to create the extention, I get the 
error:


create extension pldbgapi
ERROR: could not open extension control file 
"/usr/share/postgresql/9.5/extension/pldbgapi.control": No such file or 
directory


Are you doing this in the 9.5 instance instead of the 10 instance?

In other words what does(in psql):

select version();

show?




Which is true because it is in 
/usr/share/postgrseql/10/extension/pldbgapi.control. I am trying to run 
this command in pgadmin4. I am stuck. There doesn't seem to be a 
configuration option. Any ideas?


Thanks,
~Ben



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

Re: Re: Re: Enabling checksums on a streaming replica

[Michael Paquier]

On Fri, Jun 28, 2019 at 07:43:30AM -0400, Brad Nicholson wrote:
> So if all the checksums are being recalculated on the replica, this
> approach should be relatively safe, should it not?

Yep.

> Assuming pg_checksums is doing the right thing (and it looks to me like it
> should be).

The constraint that a cluster needs to be cleanly shut down to be able
to enable checksums with pg_checksums is the actual deal here.  After
that of course comes the WAL retention on the primary or in the WAL
archives that a standby would need again to catch up while it was
offline.
--
Michael


signature.asc
Description: PGP signature

Trigger function does not modify the NEW value

[Igal @ Lucee.org]

I have a trigger that is created like so:

    create trigger tr_on_table_modified after insert or delete or update
        on some_table for each row execute procedure on_table_modified();

    CREATE OR REPLACE FUNCTION on_table_modified() RETURNS trigger 
LANGUAGE plpgsql $$
        /* some code that does not modify any values but calls PERFORM 
on another function */


        raise notice 'lowercasing %', new.email;
        new.email = lower(new.email);
        raise notice '    to %', new.email;

        return new;
    $$

I can see in the output the notices with the expected values, but the 
value in the updated record is not lower-cased.


update some_table
set    email = 'i...@lucee.org'
where  id = 1;

> 0: lowercasing i...@lucee.org
> 0:     to i...@lucee.org

select email
from   some_table
where  id = 1;

> email |
> --|
> i...@lucee.org|

Any ideas?

Thanks,

Igal Sapir
Lucee Core Developer
Lucee.org 

Re: Trigger function does not modify the NEW value

[David Rowley]

On Sat, 29 Jun 2019 at 14:54, Igal @ Lucee.org  wrote:
> create trigger tr_on_table_modified after insert or delete or update
> on some_table for each row execute procedure on_table_modified();

> I can see in the output the notices with the expected values, but the value 
> in the updated record is not lower-cased.

You'll need a BEFORE trigger, not an AFTER trigger.
https://www.postgresql.org/docs/current/sql-createtrigger.html

-- 
 David Rowley   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: Trigger function does not modify the NEW value

[Igal @ Lucee.org]

On 6/28/2019 8:07 PM, David Rowley wrote:

On Sat, 29 Jun 2019 at 14:54, Igal @ Lucee.org  wrote:

 create trigger tr_on_table_modified after insert or delete or update
 on some_table for each row execute procedure on_table_modified();
I can see in the output the notices with the expected values, but the value in 
the updated record is not lower-cased.

You'll need a BEFORE trigger, not an AFTER trigger.
https://www.postgresql.org/docs/current/sql-createtrigger.html


*facepalm*

I was trying to "save" on creating a separate trigger so I added that to 
the bottom of an existing one and missed that.


Thanks David!

Igal