Re: Can postgresql ignore DST ?

[Laurenz Albe]

Venkata B Nagothi wrote:
> On Thu, 14 Dec 2017 at 4:04 pm, Tom Lane  wrote:
> > Venkata B Nagothi  writes:
> > > To rule out any application issues, is it possible to get postgresql to
> > > ignore DST and render all the timestamps with timezone offsets of +11 ?
> > 
> > set timezone = 11
> 
> We have Timezone configured to Australia/Sydney, we can change that to 11 and 
> do we need to foresee any issues ?

That configuration parameter defines how the client will format
timestamps to strings and vice versa.

It should not have any other effects.

Yours,
Laurenz Albe

Re: Logical replication blocking alter/drop

[Alvaro Herrera]

Mark Fletcher wrote:
> Hi All,
> 
> Postgres 9.6.5. We run several logical replication processes off our main
> postgres server. What we've noticed is that schema changes seem to block
> until we halt the logical replication processes. For example, I just did a
> 'DROP INDEX CONCURRENTLY' command, and it just sat there until I stopped
> the logical replication processes (I did not have to drop the logical
> replication slots).

What sort of logical replication are you running?

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Size of pg_multixact/members increases 11355

[Alvaro Herrera]

Yogesh Sharma wrote:
> Dear Thomas ,
> 
> Thanks for sharing information.
> 
> Is it possible to remove mentioned folder files in some time intervals by
> some DB command?
> Currently i can not upgrade to 3.6.20.
> So please share if any solution is available.

If you do not upgrade, you risk getting your data corrupted.  The
solution is to upgrade, and if the data has any value, the suggestion is
to upgrade urgently.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: pgstattuple free_percent to high

[Nicola Contu]

Hi Rene,
thanks for you reply.

I think tuning the autovacuum settings may increase performances and remove
dead_tuples but as far as I know, the autovacuum runs a vacuum analyze.
The vacuum analyze won't touch the free_percent of the table.

So I'm trying to find a way to adjust the free percent for some tables
without doing a manually full vacuum.
We are now monitoring the free percent, so we may find the part of the code
that can increase that value, but was wondering if there is anything on the
postgres side to resolve this problem.

Thanks,
Nicola


2017-12-14 0:16 GMT+01:00 Rene Romero Benavides :

> Check for long running transactions modifying (update, insert) on those
> tables ,using pg_stat_activity.
>
> Tweak these storage parameters for such tables:
> autovacuum_vacuum_cost_delay : decrease it (the autovacuum daemon goes to
> sleep less often )
> autovacuum_vacuum_threshold : decrease it (to trigger more frequent
> autovacuum activations )
> autovacuum_vacuum_cost_limit : increase it (to allow the autovacuum daemon
> to work for longer periods)
> autovacuum_vacuum_scale_factor : decrease it (to trigger more autovacuum
> activations when this percentage of a table has been modified)
>
> For example I've set these parameters for one table experiencing long
> running transactions, and for its access patterns have worked:
>
> autovacuum_vacuum_cost_delay=5, autovacuum_vacuum_threshold=
> 50,autovacuum_vacuum_cost_limit=3000, autovacuum_vacuum_scale_factor=0.01
> but these settings are very particular for each usage pattern.
>
> Take into account that more activity from autovacuum means more IO, more
> CPU usage, you might also benefit from setting autovacuum_work_mem to a
> higher setting if the available RAM allows it, to give more RAM to the
> autovacuum daemon.
>
>
>
> 2017-12-13 9:49 GMT-06:00 Nicola Contu :
>
>> Hello,
>> We are running postgres 9.6.6 on centos 7.
>>
>> We have a large DB (180GB) with about 1200 tables.
>>
>> We have autovacuum set with default values and we are seeing that for
>> some tables the free percent goes really high (51%) and we need to daily
>> full vacuum those tables.
>>
>> dbanme=# SELECT * FROM pgstattuple('tablename');
>>  table_len  | tuple_count | tuple_len  | tuple_percent | dead_tuple_count
>> | dead_tuple_len | dead_tuple_percent | free_space | free_percent
>> +-++---+
>> --+++---
>> -+--
>>  2119548928 |  526658 | 1023569149 | 48.29 |0
>> |  0 |  0 | 1083485292 |51.12
>> (1 row)
>>
>> I guess this is because of long queries but I'm not really sure.
>> Do you know how to avoid this problem and what can cause it?
>>
>> Do you think that increasing the autovacuum settings for those tables
>> would alleviate the issue?
>>
>> Thanks,
>> Nicola
>>
>
>
>
> --
> El genio es 1% inspiración y 99% transpiración.
> Thomas Alva Edison
> http://pglearn.blogspot.mx/
>
>

Re: pgstattuple free_percent to high

[Stephen Frost]

Greetings Nicola,

* Nicola Contu (nicola.co...@gmail.com) wrote:
> I think tuning the autovacuum settings may increase performances and remove
> dead_tuples but as far as I know, the autovacuum runs a vacuum analyze.
> The vacuum analyze won't touch the free_percent of the table.

That's not entirely accurate.  If all of the free space is at the *end*
of the relation then autovacuum will attempt to lock the relation and
truncate the table to give that free space back to the OS.

On a table where all of the rows are regularly updated, eventually the
"live" data should end up towards the front of the relation and the end
of the relation will be all dead tuples, allowing the truncate to
happen.  If you have tuples at the end of the relation that aren't ever
updated but they're "live" then we won't be able to truncate.

The pg_freespacemap extension can be useful to see where the free space
is in the relation.

There are a few tools out there that aren't part of core PostgreSQL that
you could consider using such as pg_repack and pg_squeeze.

> So I'm trying to find a way to adjust the free percent for some tables
> without doing a manually full vacuum.
> We are now monitoring the free percent, so we may find the part of the code
> that can increase that value, but was wondering if there is anything on the
> postgres side to resolve this problem.

Having some free space in the relation isn't a 'problem' and is a good
thing because it means that new rows (from either INSERTs or UPDATEs)
have a place to go that doesn't require extending the relation (which
requires an additional lock as well as some additional work).  As for
how much free space is good to have and how much is too much depends on
the specific workload.

Thanks!

Stephen


signature.asc
Description: Digital signature

User-defined print format for extension-defined types in psql output

[Peter Devoy]

Hi list

To make inspecting PostGIS tables in psql easier I have written a
function which outputs PostGIS Geometry objects as a string of ASCII
art.

Please does anyone know if there is there some way I can have my
function called automatically by psql instead of me writing it into
the query each time?  E.g. a user defined \pset option or some sort of
extension API which gives me access to the print logic or
'presentation layer' of psql.

By 'automatically' I mean through association with the PostGIS geometry types.

Example of how it looks at the moment: https://hastebin.com/nihujociga.sql

Kind regards


Peter

Does the delete+insert is fater and less cpu consumer than update?

[hmidi slim]

Hi,
I have a table named 'establishment' which has a join with a table
'category' and a table 'product'. I added indexes to foreign keys to
enhance the execution time of the update or delete of data.
I have to update the table establishment when I get a new data received
from an external api. The amount of updated data is about 3000-5000 rows.
I used an update query to update the modified rows, however when I search
on the net I found that some people said that update is cpu intensive
consumer and delete+insert is faster.
I didn't make any test for the performance between them and I want first of
all to know if the update consumes more cpu in case of a large amount of
data and with table with join.

Re: Does the delete+insert is fater and less cpu consumer than update?

[Tom Lane]

hmidi slim  writes:
> I used an update query to update the modified rows, however when I search
> on the net I found that some people said that update is cpu intensive
> consumer and delete+insert is faster.

It's possible that there are RDBMSes somewhere for which that is true.
But as far as Postgres is concerned, it's nonsense.

regards, tom lane

Re: Does the delete+insert is fater and less cpu consumer than update?

[David G. Johnston]

On Thu, Dec 14, 2017 at 3:08 PM, hmidi slim  wrote:

> I didn't make any test for the performance between them and I want first
> of all to know if the update consumes more cpu in case of a large amount of
> data and with table with join.
>

Clearing the entire table, via truncate (not delete), and inserting all new
records is going to be the best solution you can get.  The problem is most
use cases involve joins to other tables and dropping an entire table is
simply not possible.  If you can go this route it is also worth considering
whether you make said table UNLOGGED.  The vast majority of my ETL purposed
tables are routinely truncated and defined unlogged.  But they are also
usually not involved in views or production queries.

The best thing to do is only update those records that have changed, delete
those that no longer exist and insert new ones.  For records that have
changed there are advantages to only updating non-index-used columns (I
believe the system detects actual changes, so the index-used can still
appear in the SET clause of the update).  If an indexed column changes then
there will be no material difference between delete+insert and update *since
an update is, in its general implementation, a delete+insert anyway.*

You speak about CPU consumption but database admins/users are usually more
concerned with IO/Disk than CPU - especially when performing writes.

David J.

Re: Does the delete+insert is fater and less cpu consumer than update?

[legrand legrand]

If you use partitioned tables,
and just want to reload data from some partitions

you can then use the truncate partition syntax
or develop a procedure like described here

https://www.postgresql.org/message-id/am4pr03mb171327323dcd2069a532756190...@am4pr03mb1713.eurprd03.prod.outlook.com

Regards
PAscal



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

Re: Can postgresql ignore DST ?

[Venkata B Nagothi]

On Thu, 14 Dec 2017 at 7:28 pm, Laurenz Albe 
wrote:

> Venkata B Nagothi wrote:
> > On Thu, 14 Dec 2017 at 4:04 pm, Tom Lane  wrote:
> > > Venkata B Nagothi  writes:
> > > > To rule out any application issues, is it possible to get postgresql
> to
> > > > ignore DST and render all the timestamps with timezone offsets of
> +11 ?
> > >
> > > set timezone = 11
> >
> > We have Timezone configured to Australia/Sydney, we can change that to
> 11 and do we need to foresee any issues ?
>
> That configuration parameter defines how the client will format
> timestamps to strings and vice versa.
>
> It should not have any other effects.


I think what I see is some dates might have changed - is that a possibility
?

If yes, is there any way I can revert back ?

Regards,
Ven


> --

Regards,

Venkata B N
Database Consultant

Hot standby failing with PANIC: WAL contains references to invalid pages

[TAKATSUKA Haruka]

Hi folks,

I got the same failure with the following report on Amazon Linux 2017.09
and postgresql95-9.5.8-1.73.amzn1.x86_64.

The WAL's RMGR ID and its type (Heap2/VISIBLE) are also same.
The servers had worked for over 2 weeks and the standby crashed.

Firstly we suspected something low layer curruption, but any exact
reason haven't been found yet.
I think possibility of a bug now because of the other similar report.

Does anyone has any idea or similar experience?

[Hot standby failing with page # of relation # is uninitialized]
https://www.postgresql.org/message-id/a46709a5-3c41-7acb-1db0-60a317283...@tracktrans.com

(log output @stanby)
2017-12-11 18:14:16 JST [5094]: [13-1] db=,user=,state=01000 WARNING: page 
1646627 of relation pg_tblspc/16407/PG_9.5_201510051/16406/31164 is 
uninitialized
2017-12-11 18:14:16 JST [5094]: [14-1] db=,user=,state=01000 CONTEXT: xlog redo 
Heap2/VISIBLE: cutoff xid 210320
2017-12-11 18:14:16 JST [5094]: [15-1] db=,user=,state=XX000 PANIC: WAL 
contains references to invalid pages
2017-12-11 18:14:16 JST [5094]: [16-1] db=,user=,state=XX000 CONTEXT: xlog redo 
Heap2/VISIBLE: cutoff xid 210320
2017-12-11 18:14:16 JST [5088]: [5-1] db=,user=,state=0 LOG: startup 
process (PID 5094) was terminated by signal 6: Aborted
2017-12-11 18:14:16 JST [5088]: [6-1] db=,user=,state=0 LOG: terminating 
any other active server processes

 with best regards,
 Haruka Takatsuka

Re: Can postgresql ignore DST ?

[Ben Madin]

I'd be a little worried that if you set timezone = 11 for Australia/Sydney
you are embedding the daylight savings value, not the standard time value
(UTC+10)

If it helps (it may not) we always set our servers to Australia/Brisbane
(UTC+10), as Queensland doesn't have daylight savings, and is fairly
unlikely to, thus flicking all responsibility to the interface
developers... :)



On 15 December 2017 at 10:20, Venkata B Nagothi  wrote:

>
> On Thu, 14 Dec 2017 at 7:28 pm, Laurenz Albe 
> wrote:
>
>> Venkata B Nagothi wrote:
>> > On Thu, 14 Dec 2017 at 4:04 pm, Tom Lane  wrote:
>> > > Venkata B Nagothi  writes:
>> > > > To rule out any application issues, is it possible to get
>> postgresql to
>> > > > ignore DST and render all the timestamps with timezone offsets of
>> +11 ?
>> > >
>> > > set timezone = 11
>> >
>> > We have Timezone configured to Australia/Sydney, we can change that to
>> 11 and do we need to foresee any issues ?
>>
>> That configuration parameter defines how the client will format
>> timestamps to strings and vice versa.
>>
>> It should not have any other effects.
>
>
> I think what I see is some dates might have changed - is that a
> possibility ?
>
> If yes, is there any way I can revert back ?
>
> Regards,
> Ven
>
>
>> --
>
> Regards,
>
> Venkata B N
> Database Consultant
>
>



-- 

Dr Ben Madin
Managing Director



m : +61 448 887 220

e : b...@ausvet.com.au

5 Shuffrey Street, Fremantle
Western Australia

on the web: www.ausvet.com.au


This transmission is for the intended addressee only and is confidential
information. If you have received this transmission in error, please delete
it and notify the sender. The contents of this email are the opinion of the
writer only and are not endorsed by Ausvet unless expressly stated
otherwise. Although Ausvet uses virus scanning software we do not accept
liability for viruses or similar in any attachments.

Re: Can postgresql ignore DST ?

[Venkata B Nagothi]

On Fri, Dec 15, 2017 at 3:23 PM, Ben Madin  wrote:

> I'd be a little worried that if you set timezone = 11 for Australia/Sydney
> you are embedding the daylight savings value, not the standard time value
> (UTC+10)
>

Totally agree. We have a weird situation where-in i had to do this and i
would like to learn the impact on the data, i hope it would not fiddle the
existing data. We are currently experimenting this.

Regards,
Ven

Re: Can postgresql ignore DST ?

[John R Pierce]

On 12/14/2017 9:17 PM, Venkata B Nagothi wrote:


On Fri, Dec 15, 2017 at 3:23 PM, Ben Madin > wrote:


I'd be a little worried that if you set timezone = 11 for
Australia/Sydney you are embedding the daylight savings value, not
the standard time value (UTC+10)


Totally agree. We have a weird situation where-in i had to do this and 
i would like to learn the impact on the data, i hope it would not 
fiddle the existing data. We are currently experimenting this.



in PostgreSQL, fields that are type TIMESTAMP WITH TIME ZONE convert all 
input time values to UTC, and store it in an internal representation, 
and on output, they are converted to the client's current TIMEZONE.



--
john r pierce, recycling bits in santa cruz

Re: Can postgresql ignore DST ?

[Laurenz Albe]

Venkata B Nagothi wrote:
> > > We have Timezone configured to Australia/Sydney, we can change that to 11 
> > > and do we need to foresee any issues ?
> > 
> > That configuration parameter defines how the client will format
> > timestamps to strings and vice versa.
> > 
> > It should not have any other effects.
> 
> I think what I see is some dates might have changed - is that a possibility ? 
> 
> If yes, is there any way I can revert back ?

I don't understand - you'd have to explain what you mean.

The setting doesn't change any data, it changes how a timestamp with time zone
is displayed.

It also sets the default time zone to use when a timestamp without timezone
is to be converted to a timestamp with time zone, so maybe that's what you
are observing.

Yours,
Laurenz Albe