Re: [GENERAL] pg_xlog on a hot_stanby slave

[Sameer Kumar]

 On Wed, 17 Jun 2015 15:24 Xavier 12  wrote:

On 17/06/2015 03:17, Sameer Kumar wrote:


On Tue, 16 Jun 2015 16:55 Xavier 12  wrote:

 Hi everyone,

Questions about pg_xlogs again...
I have two Postgresql 9.1 servers in a master/slave stream replication
(hot_standby).

Psql01 (master) is backuped with Barman and pg_xlogs is correctly
purged (archive_command is used).

Hower, Psql02 (slave) has a huge pg_xlog (951 files, 15G for 7 days
only, it keeps growing up until disk space is full). I have found
documentation and tutorials, mailing list, but I don't know what is
suitable for a Slave. Leads I've found :

- checkpoints
- archive_command
- archive_cleanup

Master postgresq.conf :

[...]
wal_level = 'hot_standby'
archive_mode = on
archive_command = 'rsync -az /var/lib/postgresql/9.1/main/pg_xlog/%f
bar...@nas.lan:/data/pgbarman/psql01/incoming/%f'
max_wal_senders = 5
wal_keep_segments = 64

  What's this parameter's value on Slave?

 Hm... You have a point.
That autovacuum parameter seems to be useless on a slave.
I'll try to remove it and check pg_xlog.

 That was not my point. I was actually asking about wal_keep_segment.
Nevermind I found that I had misses the info (found it below. Please see my
response).
Besides I try to keep my master and standby config as same as possible(so
my advise ia to not switchoff autovacuum). The parameters which are
imeffective on slave anyways won't have an effect. Same goes for parameters
on master.
This helps me when I swap roles or do a failover. I have less parameters to
be worried about.

 Can you check the pg_log for log files. They may have se info? I am sorry
if you have already provided that info (after I finish I will try to look
at your previous emails on this thread)

Also can you share the vacuum cost parameters in your environment?


autovacuum = on

Slave postgresql.conf :

[...]
wal_level = minimal
wal_keep_segments = 32

  Sorry I missed this somehow earlier. Any reason why you think you need to
retain 32 wal files on slave?


hot_standby = on

Slave recovery.conf :

standby_mode = 'on'
primary_conninfo = 'host=10.0.0.1 port=5400 user=postgres'
trigger_file = '/var/lib/postgresql/9.1/triggersql'
restore_command='cp /var/lib/postgresql/9.1/wal_archive/%f "%p"'
archive_cleanup_command =
'/usr/lib/postgresql/9.1/bin/pg_archivecleanup
/var/lib/postgresql/9.1/wal_archive/ %r'

 Also consider setting hot_standby_feesback to on.

How can I reduce the number of WAL files on the hot_stanby slave ?

Thanks

Regards.

Xavier C.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Momentary Delay

[Bill Moran]

On Wed, 17 Jun 2015 10:33:37 -0300
Anderson Valadares  wrote:

> 2015-06-15 18:19 GMT-03:00 Bill Moran :
> 
> > On Mon, 15 Jun 2015 17:48:54 -0300
> > Anderson Valadares  wrote:
> >
> > > 2015-06-12 19:56 GMT-03:00 Bill Moran :
> > >
> > > > Please do not remove the mailing list from replies. See below.
> > > >
> > > > On Fri, 12 Jun 2015 09:21:19 -0300
> > > > Anderson Valadares  wrote:
> > > >
> > > > > 2015-06-08 20:33 GMT-03:00 Bill Moran :
> > > > >
> > > > > > On Mon, 8 Jun 2015 11:59:31 -0300
> > > > > > Anderson Valadares  wrote:
> > > > > >
> > > > > > > Hi
> > > > > > >  We are experiencing an intermittent problem in a GIS database
> > from a
> > > > > > > client. Some times of the day is as if the PostgreSQL executed
> > the
> > > > > > slowest
> > > > > > > operations see below an example. The query was performed three
> > times,
> > > > > > twice
> > > > > > > I canceled and the third time I left it to end. The consultation
> > > > took 10
> > > > > > > seconds to finish, but the measured execution time is 20 ms. As
> > we
> > > > have
> > > > > > > several queries that run every 2 seconds when this momentary
> > delay
> > > > occurs
> > > > > > > queries accumulate and burst the limit of 203 connections
> > allowed.
> > > > The
> > > > > > > interval of "momentary stop" are approximately 2 seconds
> > occurring at
> > > > > > > random times and during these stoppages occur no locks and no
> > > > increased
> > > > > > > load on the server is identified. There is a job 2/2 seconds
> > locks
> > > > > > > collection of information, running queries, etc., nmon also
> > collects
> > > > > > > information every 2 seconds. My client asks what causes these
> > > > momentary
> > > > > > > stops? because it affects all operations of the database? etc.
> > How
> > > > do I
> > > > > > > identify what is causing these delays in executions of
> > operations in
> > > > the
> > > > > > > database?
> > > > > >
> > > > > > Number of points to consider:
> > > > > > * Check the contents of pg_locks and see if something is waiting
> > on a
> > > > > >   lock when the query is slow.
> > > > >
> > > > >  There is nothing waiting when the query is slow, see:
> > > > >
> > > > >  snap_datetime | waiting | count
> > > > > ---+-+---
> > > > >  2015-06-05 09:25:00.954731-03 | f   |74
> > > > >  2015-06-05 09:26:00.249187-03 | f   |   205
> > > > >  2015-06-05 09:27:00.826874-03 | f   |   207
> > > >
> > > > I don't know what that means. Since you don't show the query that
> > > > generated that output, I have no idea if your statement is valid, or
> > > > if you're running a query that will inherently produce incorrect
> > > > results.
> > >
> > > Sorry the information was incomplete. Below is the query that was
> > > performed to extract information from tables that used to monitor the
> > > database.
> > > The snap_datetime column indicates the time that occurred monitoring,
> > > the waiting column tells you whether any connection was on hold and the
> > > column
> > > count tells how many connections existed at the time.
> > >
> > > select
> > >  s.snap_datetime
> > >  ,a.waiting
> > >  , count(*)
> > > from stat_snapshot s, stat_activity a
> > > where s.snap_id = a.snap_id
> > >   and s.snap_datetime >= '2015-06-05 09:25:00'
> > >   and s.snap_datetime <= '2015-06-05 09:28:00'
> > > group by
> > >   s.snap_datetime
> > >   ,a.waiting
> > > order by s.snap_datetime
> > > ;
> > >  snap_datetime | waiting | count
> > > ---+-+---
> > >  2015-06-05 09:25:00.954731-03 | f   |74
> > >  2015-06-05 09:26:00.249187-03 | f   |   205
> > >  2015-06-05 09:27:00.826874-03 | f   |   207
> > > (3 rows)
> >
> > Given what I'm seeing above, there's still a lot of information
> > missing. Those tables are not standard PostgreSQL tables, and I'm
> > not aware of any tool that creates them, thus I have to assume
> > it's a cron job or something similar created in-house. Given the
> > query you ran and the resultant data, my first guess is that the
> > data in stat_snapshot and stat_activity is worthless: i.e. it's
> > captured once per minute, and therefore there are 59+ seconds
> > worth of detail that aren't captured, thus the actual liklihood
> > that the those tables will contain any indication of the problem
> > is very low.
> 
> Yes it is a cron, created at home, running every 1 hour gathering
> information from PostgreSQL tables (pg_stat_activity, pg_locks, etc).
> How to improve the collection of information PostgreSQL?

Look at the log file, details of what is going on are being recorded
as they happen.

However, given your statement below (claiming that nothing happened
during the observed slowdown between 9:26 and 9:27) you may want to
find someone who is actually capable of interpreting the log output,
as there are dozens of indicators of what's wrong. 

[GENERAL] writable cte triggers reverse order

[Миша Тюрин]

hi there!
for me case listed below looks like something goes wrong (at least very strange)

pg 9.2.10
* schema:
base_table ==> inheritance_with_before_trigger ==> child_table ==> 
audit_after_trigger ==> child_table_log

* init sql:
create schema tmp;
--create extension hstore;
-- inheritance part
drop table if exists tmp.test_trg; -- child_table
drop table if exists tmp.test_trg_0; -- base_table
create table tmp.test_trg_0( id serial primary key, tag text );
create table tmp.test_trg( like tmp.test_trg_0 including all ) inherits ( 
tmp.test_trg_0 );
create or replace function tmp.test_trg_inh_func() returns trigger language 
'plpgsql' as
$$
begin 
insert into tmp.test_trg select NEW.*;
--raise notice 'inh % %', TG_TABLE_NAME, NEW.id;
return null;
end;
$$;
create trigger test_trg_inh before insert on tmp.test_trg_0 for each row 
execute procedure tmp.test_trg_inh_func();
-- audit part
drop table if exists tmp.test_trg_log;
create table tmp.test_trg_log( ev_id serial primary key, txid bigint default 
txid_current(), xdata text );
create or replace function tmp.test_trg_func() returns trigger language 
'plpgsql' as
$$
begin 
insert into tmp.test_trg_log ( xdata ) select TG_OP || ' ' || hstore( case when 
TG_OP = 'DELETE' then OLD else NEW end );
--raise notice 'log % %', TG_TABLE_NAME, TG_OP;
return null;
end;
$$
;
create trigger test_trg after insert or update or delete on tmp.test_trg for 
row execute procedure tmp.test_trg_func(); -- log on child
-- populate
--insert into base_table
insert into tmp.test_trg_0 ( tag ) select oid::text from pg_class;

* action sql:
-- delete then insert from/into base_table
with
del as ( delete from tmp.test_trg_0 t where t.id between 15 and 16 returning 
t.* )
insert into tmp.test_trg_0 
select * from del
;

* and data from audit table
select * from ( select * from tmp.test_trg_log order by ev_id desc limit 10 )_ 
order by ev_id

see on order of events -- INSERTs come first before DELETEs!
is it expectable performance? any explanation?
it is also could be a important issue for trigger-based replication systems.

--misha

Re: [GENERAL] pg_class.reltuples VS pg_stat_all_tables.n_live_tup for estimation of table

[William Dunn]

Thanks so much Tom!

*Will J. Dunn*
*willjdunn.com *

On Wed, Jun 17, 2015 at 3:48 PM, Tom Lane  wrote:

> William Dunn  writes:
> > Does anyone which is a more accurate estimate of a table's live
> > rows: pg_class.reltuples (
> > http://www.postgresql.org/docs/current/static/catalog-pg-class.html)
> > OR pg_stat_all_tables.n_live_tup (
> >
> http://www.postgresql.org/docs/current/static/monitoring-stats.html#PG-STAT-ALL-TABLES-VIEW
> )?
>
> They're both inaccurate with different sources of inaccuracy.  I dunno
> that you should assume that one is necessarily better than the other.
>
> >- Is pg_class.reltuples an estimation of live tuples only, or is it of
> >all tuples (both live and dead)? I would guess it's live only but
> that is a
> >guess
>
> Hm.  I'm pretty sure the planner takes it as counting live tuples only,
> but it looks like VACUUM thinks it includes recently-dead-but-not-yet-
> removable tuples.  We might need to do some adjustment there.
>
> regards, tom lane
>

Re: [GENERAL] pg_class.reltuples VS pg_stat_all_tables.n_live_tup for estimation of table

[Tom Lane]

William Dunn  writes:
> Does anyone which is a more accurate estimate of a table's live
> rows: pg_class.reltuples (
> http://www.postgresql.org/docs/current/static/catalog-pg-class.html)
> OR pg_stat_all_tables.n_live_tup (
> http://www.postgresql.org/docs/current/static/monitoring-stats.html#PG-STAT-ALL-TABLES-VIEW)?

They're both inaccurate with different sources of inaccuracy.  I dunno
that you should assume that one is necessarily better than the other.

>- Is pg_class.reltuples an estimation of live tuples only, or is it of
>all tuples (both live and dead)? I would guess it's live only but that is a
>guess

Hm.  I'm pretty sure the planner takes it as counting live tuples only,
but it looks like VACUUM thinks it includes recently-dead-but-not-yet-
removable tuples.  We might need to do some adjustment there.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pg_class.reltuples VS pg_stat_all_tables.n_live_tup for estimation of table

[William Dunn]

Hello,

Does anyone which is a more accurate estimate of a table's live
rows: pg_class.reltuples (
http://www.postgresql.org/docs/current/static/catalog-pg-class.html)
OR pg_stat_all_tables.n_live_tup (
http://www.postgresql.org/docs/current/static/monitoring-stats.html#PG-STAT-ALL-TABLES-VIEW)?
In my test database their values are not the same.

   - Is pg_class.reltuples an estimation of live tuples only, or is it of
   all tuples (both live and dead)? I would guess it's live only but that is a
   guess
   - In the database I am testing:
   - pg_class.reltuples <> pg_stat_all_tables.n_live_tup
  - pg_class.reltuples <> (pg_stat_all_tables.n_live_tup
  + pg_stat_all_tables.n_dead_tup)


I understand that pg_stat_all_tables is probably the more expensive view to
query but I am already querying both so I only care which is more accurate.


Thanks!
Will

*Will J. Dunn*
*willjdunn.com *

[GENERAL] Index Advice in PG

[Jony Cohen]

Hi list,
I am happy to announce the new release of pg_idx_advisor version 0.1.2.
This is a PostgreSQL extension which allows a user to get index advice from
the DB optimizer.

It's the first "stable" release so please don't go running this on your
production environment :)
But, I'd greatly appreciate any comments/thoughts/issues/pull requests...

It's fairly simple to use:
1. # create extension pg_idx_advisor;
2. # load '$libdir/plugins/pg_idx_advisor.so';
and then run explain on the queries you'd like to get advice on:
3. # explain select * from t where a = 100;INFO:
** Plan with original indexes **

   QUERY PLAN

 Seq Scan on t  (cost=0.00..36.75 rows=11 width=8)
   Filter: (a = 100)

 ** Plan with hypothetical indexes **
 read only, advice, index: create index on t(a)
 Bitmap Heap Scan on t  (cost=4.12..14.79 rows=11 width=8)
   Recheck Cond: (a = 100)
   ->  Bitmap Index Scan on :114699  (cost=0.00..4.11 rows=11
width=0)
 Index Cond: (a = 100)
(9 rows)

You can get it from the postgres extension network:
http://pgxn.org/dist/pg_idx_advisor
Or directly from GitHub:
https://github.com/cohenjo/pg_idx_advisor

Regards,
 - Jony

Re: [GENERAL] pg_dump 8.4.9 failing after upgrade to openssl-1.0.1e-30.el6_6.11.x86_64 on redhat linux

[Tom Lane]

Douglas Stetner  writes:
> Looking for confirmation there is an issue with pg_dump failing after 
> upgrade to openssl-1.0.1e-30.el6_6.11.x86_64 on redhat linux.

Quick thought --- did you restart the Postgres service after upgrading
openssl?  If not, your server is still using the old library version,
while pg_dump would be running the new version on the client side.
I don't know exactly what was done to openssl in the last round of
revisions, but maybe there is some sort of version compatibility issue.

Also, you really ought to be running something newer than PG 8.4.9.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] serialization failure why?

[Filipe Pina]

Indeed Bill, if drop the foreign key constraint on stuff_ext table I 
get 0 failures...


But, since I can't remove the foreign key, how can I tell postgres that 
he can "trust" it then?
Because it's obvious (for the code reader at least), that the other 
transaction will not be responsible for creating the foreign record, 
it's that very same transaction that creates it (the previous insert). 
If the first had failed (because it already existed or due to some 
other failure), the second wouldn't even be executed, so the second 
shouldn't fail for serialization on the FK at least..


Is there something that can be annotated or passed in the insert to 
workaround this? I really need to reduce the retries I'm performing and 
this is one of the common cases I have (in different functions, FK 
serialization failures that shouldn't be a problem)..


On Qua, Jun 17, 2015 at 2:45 , Bill Moran  
wrote:

On Tue, 16 Jun 2015 13:33:12 +0001
Filipe Pina  wrote:


 I have these 2 tables:
 
 CREATE TABLE "stuff" ("number" integer NOT NULL PRIMARY KEY, 
"title" 
 varchar(40) NOT NULL);
 CREATE TABLE "stuff_ext" ("stuff_ptr_id" integer NOT NULL PRIMARY 
KEY, 
 "extra" integer NOT NULL);
 ALTER TABLE "stuff_ext" ADD CONSTRAINT 
 "stuff_ext_stuff_ptr_id_5a4ee8edae53404b" FOREIGN KEY 
("stuff_ptr_id") 
 REFERENCES "stuff" ("number") DEFERRABLE INITIALLY DEFERRED;

 CREATE SEQUENCE stuff_seq;
 
 And then the function:
 
 CREATE OR REPLACE FUNCTION create_stuff(number integer, title text)

 RETURNS integer AS $$
 DECLARE
 a1 stuff;
 a2 stuff_ext;
 BEGIN
 IF number IS NULL THEN
 number := nextval('stuff_seq');
 END IF;
 
 a1.number := number;

 a1.title := title;
 
 a2.stuff_ptr_id := a1.number;
 
 INSERT INTO stuff VALUES (a1.*);

 INSERT INTO stuff_ext VALUES (a2.*);
 
 RETURN number;

 END
 $$
 LANGUAGE plpgsql;
 
 
 The DB is configured for SERIALIZABLE transaction mode.
 
 Now, if I can the function without passing number, such as:
 
 select create_stuff(NULL,'title');
 
 in 10 forked processes in a loop with a few iterations in each, I 
get 
 quite a few SERIALIZATON FAILURE (sqlstate 40001).
 
 If I comment out the "INSERT INTO stuff_ext" line, I don't get any.
 
 How is the second insert causing serialize dependencies...?


I'm not sure this is correct, but I have a theory.

Essentially, PostgreSQL can't be sure that the foreign key will be
valid if the other transaction rolls back. i.e., what if the foreign
key is valid becuase the other transaction created the matching row,
and that other transaction then rolls back?

In other isolation modes, it can just wait for the appropriate lock to
free up, then see what happens. But in serializable mode it hits a
condition where it can't ensure serializability.


 The specific error messages vary between
 
 ERROR: could not serialize access due to read/write dependencies 
among 
 transactions
 DETAIL: Reason code: Canceled on identification as a pivot, during 
 commit attempt.

 HINT: The transaction might succeed if retried.
 
 and
 
 ERROR: could not serialize access due to read/write dependencies 
among 
 transactions
 DETAIL: Reason code: Canceled on commit attempt with conflict in 
from 
 prepared pivot.

 HINT: The transaction might succeed if retried.
 
 Thanks!



--
Bill Moran 

[GENERAL] pg_dump 8.4.9 failing after upgrade to openssl-1.0.1e-30.el6_6.11.x86_64 on redhat linux

[Douglas Stetner]

Hi all,

Looking for confirmation there is an issue with pg_dump failing after upgrade 
to openssl-1.0.1e-30.el6_6.11.x86_64 on redhat linux.

-bash-4.1$ pg_dump -V
pg_dump (PostgreSQL) 8.4.9

-bash-4.1$ pg_dump -h localhost -C Hogwarts -a -t mafs -f zz
pg_dump: Dumping the contents of table "mafs" failed: PQgetCopyData() failed.
pg_dump: Error message from server: SSL error: unexpected message
pg_dump: The command was: COPY public.mafs (hugo_symbol, 
...
...
analysis_id) TO stdout;


This is only happening on 2 tables in this database.  The same database can be 
backed up with pgAdmin3.app remotely from a Mac

As stated I am fairly sure the cause was the upgrade of openssl as it started 
to fail the next day:
Jun 16 05:18:25 qcmg-database1 yum[2965]: Updated: 
openssl-1.0.1e-30.el6_6.11.x86_64 

Re: [GENERAL] serialization failure why?

[Filipe Pina]

Hi Kevin,

I have installed:
PostgreSQL 9.3.7 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 
4.8.2-19ubuntu1) 4.8.2, 64-bit


But as you mentioned it should have been fixed in later versions, I've 
upgraded to 9.4:
PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 
4.8.2-19ubuntu1) 4.8.2, 64-bit


Though I still get the failures on 9.4.4..

On Qua, Jun 17, 2015 at 12:52 , Kevin Grittner  
wrote:

Filipe Pina  wrote:


 if drop the foreign key constraint on stuff_ext table there are
 no failures at all…


It is my recollection that we were excluding the queries used to
enforce referential integrity constraints from the conflict
tracking, so I am surprised you are seeing this.  What is the exact
version you are using (as reported by the version() function)?

I am at a conference this week, away from my normal development
environment; but I will take a look next week.

Kevin Grittner

Re: [GENERAL] Momentary Delay

[Anderson Valadares]

2015-06-15 18:19 GMT-03:00 Bill Moran :

> On Mon, 15 Jun 2015 17:48:54 -0300
> Anderson Valadares  wrote:
>
> > 2015-06-12 19:56 GMT-03:00 Bill Moran :
> >
> > > Please do not remove the mailing list from replies. See below.
> > >
> > > On Fri, 12 Jun 2015 09:21:19 -0300
> > > Anderson Valadares  wrote:
> > >
> > > > 2015-06-08 20:33 GMT-03:00 Bill Moran :
> > > >
> > > > > On Mon, 8 Jun 2015 11:59:31 -0300
> > > > > Anderson Valadares  wrote:
> > > > >
> > > > > > Hi
> > > > > >  We are experiencing an intermittent problem in a GIS database
> from a
> > > > > > client. Some times of the day is as if the PostgreSQL executed
> the
> > > > > slowest
> > > > > > operations see below an example. The query was performed three
> times,
> > > > > twice
> > > > > > I canceled and the third time I left it to end. The consultation
> > > took 10
> > > > > > seconds to finish, but the measured execution time is 20 ms. As
> we
> > > have
> > > > > > several queries that run every 2 seconds when this momentary
> delay
> > > occurs
> > > > > > queries accumulate and burst the limit of 203 connections
> allowed.
> > > The
> > > > > > interval of "momentary stop" are approximately 2 seconds
> occurring at
> > > > > > random times and during these stoppages occur no locks and no
> > > increased
> > > > > > load on the server is identified. There is a job 2/2 seconds
> locks
> > > > > > collection of information, running queries, etc., nmon also
> collects
> > > > > > information every 2 seconds. My client asks what causes these
> > > momentary
> > > > > > stops? because it affects all operations of the database? etc.
> How
> > > do I
> > > > > > identify what is causing these delays in executions of
> operations in
> > > the
> > > > > > database?
> > > > >
> > > > > Number of points to consider:
> > > > > * Check the contents of pg_locks and see if something is waiting
> on a
> > > > >   lock when the query is slow.
> > > >
> > > >  There is nothing waiting when the query is slow, see:
> > > >
> > > >  snap_datetime | waiting | count
> > > > ---+-+---
> > > >  2015-06-05 09:25:00.954731-03 | f   |74
> > > >  2015-06-05 09:26:00.249187-03 | f   |   205
> > > >  2015-06-05 09:27:00.826874-03 | f   |   207
> > >
> > > I don't know what that means. Since you don't show the query that
> > > generated that output, I have no idea if your statement is valid, or
> > > if you're running a query that will inherently produce incorrect
> > > results.
> >
> > Sorry the information was incomplete. Below is the query that was
> > performed to extract information from tables that used to monitor the
> > database.
> > The snap_datetime column indicates the time that occurred monitoring,
> > the waiting column tells you whether any connection was on hold and the
> > column
> > count tells how many connections existed at the time.
> >
> > select
> >  s.snap_datetime
> >  ,a.waiting
> >  , count(*)
> > from stat_snapshot s, stat_activity a
> > where s.snap_id = a.snap_id
> >   and s.snap_datetime >= '2015-06-05 09:25:00'
> >   and s.snap_datetime <= '2015-06-05 09:28:00'
> > group by
> >   s.snap_datetime
> >   ,a.waiting
> > order by s.snap_datetime
> > ;
> >  snap_datetime | waiting | count
> > ---+-+---
> >  2015-06-05 09:25:00.954731-03 | f   |74
> >  2015-06-05 09:26:00.249187-03 | f   |   205
> >  2015-06-05 09:27:00.826874-03 | f   |   207
> > (3 rows)
>
> Given what I'm seeing above, there's still a lot of information
> missing. Those tables are not standard PostgreSQL tables, and I'm
> not aware of any tool that creates them, thus I have to assume
> it's a cron job or something similar created in-house. Given the
> query you ran and the resultant data, my first guess is that the
> data in stat_snapshot and stat_activity is worthless: i.e. it's
> captured once per minute, and therefore there are 59+ seconds
> worth of detail that aren't captured, thus the actual liklihood
> that the those tables will contain any indication of the problem
> is very low.
>

Yes it is a cron, created at home, running every 1 hour gathering
information from PostgreSQL tables (pg_stat_activity, pg_locks, etc).
How to improve the collection of information PostgreSQL?



>
> > > > * Also, ensure log_lock_waits is turned on for the server and check
> > > > >   PostgreSQL's logs for messages about long lock waits.
> > > >
> > > > There is also nothing
> > >
> > > This is even worse than the previous statement. What did you _do_ to
> > > determine this?
> > >
> > > Since you're unable to diagnose and fix the problem on your own, the
> > > possibility exists that the problem is with your diagnostic steps. If
> > > you don't share the details of those steps, I have no way to help you
> > > with them.
> >
> > See the log in
> >
> https://github.com/andervalbh/serv-gis01/blob/master/postgresql-201

Re: [GENERAL] serialization failure why?

[Kevin Grittner]

Filipe Pina  wrote:

> if drop the foreign key constraint on stuff_ext table there are
> no failures at all…

It is my recollection that we were excluding the queries used to
enforce referential integrity constraints from the conflict
tracking, so I am surprised you are seeing this.  What is the exact
version you are using (as reported by the version() function)?

I am at a conference this week, away from my normal development
environment; but I will take a look next week.

Kevin Grittner


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] serialization failure why?

[Filipe Pina]

Indeed Bill, if drop the foreign key constraint on stuff_ext table 
there are no failures at all...


But, since I can't remove the foreign key, how can I tell postgres that 
he can "trust" it then?
Because it's obvious (for the code reader at least), that the other 
transaction will not be responsible for creating the foreign record, 
it's that very same transaction that creates it (the previous insert). 
If the first had failed (because it already existed or due to some 
other failure), the second wouldn't even be executed, so the second 
shouldn't fail for serialization on the FK at least..


Is there something that can be annotated or passed in the insert to 
workaround this? I really need to reduce the retries I'm performing and 
this is one of the common cases I have (in different functions, FK 
serialization failures that shouldn't be a problem)..


On Qua, Jun 17, 2015 at 2:45 , Bill Moran  
wrote:

On Tue, 16 Jun 2015 13:33:12 +0001
Filipe Pina  wrote:


 I have these 2 tables:
 
 CREATE TABLE "stuff" ("number" integer NOT NULL PRIMARY KEY, 
"title" 
 varchar(40) NOT NULL);
 CREATE TABLE "stuff_ext" ("stuff_ptr_id" integer NOT NULL PRIMARY 
KEY, 
 "extra" integer NOT NULL);
 ALTER TABLE "stuff_ext" ADD CONSTRAINT 
 "stuff_ext_stuff_ptr_id_5a4ee8edae53404b" FOREIGN KEY 
("stuff_ptr_id") 
 REFERENCES "stuff" ("number") DEFERRABLE INITIALLY DEFERRED;

 CREATE SEQUENCE stuff_seq;
 
 And then the function:
 
 CREATE OR REPLACE FUNCTION create_stuff(number integer, title text)

 RETURNS integer AS $$
 DECLARE
 a1 stuff;
 a2 stuff_ext;
 BEGIN
 IF number IS NULL THEN
 number := nextval('stuff_seq');
 END IF;
 
 a1.number := number;

 a1.title := title;
 
 a2.stuff_ptr_id := a1.number;
 
 INSERT INTO stuff VALUES (a1.*);

 INSERT INTO stuff_ext VALUES (a2.*);
 
 RETURN number;

 END
 $$
 LANGUAGE plpgsql;
 
 
 The DB is configured for SERIALIZABLE transaction mode.
 
 Now, if I can the function without passing number, such as:
 
 select create_stuff(NULL,'title');
 
 in 10 forked processes in a loop with a few iterations in each, I 
get 
 quite a few SERIALIZATON FAILURE (sqlstate 40001).
 
 If I comment out the "INSERT INTO stuff_ext" line, I don't get any.
 
 How is the second insert causing serialize dependencies...?


I'm not sure this is correct, but I have a theory.

Essentially, PostgreSQL can't be sure that the foreign key will be
valid if the other transaction rolls back. i.e., what if the foreign
key is valid becuase the other transaction created the matching row,
and that other transaction then rolls back?

In other isolation modes, it can just wait for the appropriate lock to
free up, then see what happens. But in serializable mode it hits a
condition where it can't ensure serializability.


 The specific error messages vary between
 
 ERROR: could not serialize access due to read/write dependencies 
among 
 transactions
 DETAIL: Reason code: Canceled on identification as a pivot, during 
 commit attempt.

 HINT: The transaction might succeed if retried.
 
 and
 
 ERROR: could not serialize access due to read/write dependencies 
among 
 transactions
 DETAIL: Reason code: Canceled on commit attempt with conflict in 
from 
 prepared pivot.

 HINT: The transaction might succeed if retried.
 
 Thanks!



--
Bill Moran 

Re: [GENERAL] Test for char errors

[Michael Paquier]

On Wed, Jun 17, 2015 at 5:15 PM, 夏高 wrote:
> I don't know why the files are not the same but tests all passed. Helps are
> appreciated, thanks!

Some tests have multiple expected outputs. In the case of char, there
is not only char.out, but as well char_1.out and char_2.out. In your
case char_1.out seems to match.
-- 
Michael


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Test for char errors

[夏高]

I downloaded psotgresql-9.4.4 source code and build it on Centos 6.5 x64
edition. Then I run 'make test' and it reported that 'All 145 tests
passed'. But the expected output and actual output of test 'char' are not
same.

The expected output of in 'src/test/regress/expected/char.out' is:

SELECT '' AS five, c.*
   FROM CHAR_TBL c
   WHERE c.f1 < 'a';
 five | f1
--+
  | A
  | 1
  | 2
  | 3
  |
(5 rows)

But the actual output in 'src/test/regress/results/char.out' is:

SELECT '' AS five, c.*
   FROM CHAR_TBL c
   WHERE c.f1 < 'a';
 five | f1
--+
  | 1
  | 2
  | 3
  |
(4 rows)

I don't know why the files are not the same but tests all passed. Helps are
appreciated, thanks!

Re: [GENERAL] pg_xlog on a hot_stanby slave

[Xavier 12]

On 17/06/2015 03:17, Sameer Kumar wrote:



On Tue, 16 Jun 2015 16:55 Xavier 12 > wrote:


Hi everyone,

Questions about pg_xlogs again...
I have two Postgresql 9.1 servers in a master/slave stream replication
(hot_standby).

Psql01 (master) is backuped with Barman and pg_xlogs is correctly
purged (archive_command is used).

Hower, Psql02 (slave) has a huge pg_xlog (951 files, 15G for 7 days
only, it keeps growing up until disk space is full). I have found
documentation and tutorials, mailing list, but I don't know what is
suitable for a Slave. Leads I've found :

- checkpoints
- archive_command
- archive_cleanup

Master postgresq.conf :

[...]
wal_level = 'hot_standby'
archive_mode = on
archive_command = 'rsync -az /var/lib/postgresql/9.1/main/pg_xlog/%f
bar...@nas.lan:/data/pgbarman/psql01/incoming/%f'
max_wal_senders = 5
wal_keep_segments = 64


What's this parameter's value on Slave?



Hm... You have a point.
That autovacuum parameter seems to be useless on a slave.
I'll try to remove it and check pg_xlog.

Xavier C.



autovacuum = on

Slave postgresql.conf :

[...]
wal_level = minimal
wal_keep_segments = 32
hot_standby = on

Slave recovery.conf :

standby_mode = 'on'
primary_conninfo = 'host=10.0.0.1 port=5400 user=postgres'
trigger_file = '/var/lib/postgresql/9.1/triggersql'
restore_command='cp /var/lib/postgresql/9.1/wal_archive/%f "%p"'
archive_cleanup_command =
'/usr/lib/postgresql/9.1/bin/pg_archivecleanup
/var/lib/postgresql/9.1/wal_archive/ %r'



How can I reduce the number of WAL files on the hot_stanby slave ?

Thanks

Regards.

Xavier C.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_xlog on a hot_stanby slave

[Xavier 12]

On 17/06/2015 02:44, Venkata Balaji N wrote:
On Tue, Jun 16, 2015 at 6:55 PM, Xavier 12 > wrote:


Hi everyone,

Questions about pg_xlogs again...
I have two Postgresql 9.1 servers in a master/slave stream replication
(hot_standby).

Psql01 (master) is backuped with Barman and pg_xlogs is correctly
purged (archive_command is used).

Hower, Psql02 (slave) has a huge pg_xlog (951 files, 15G for 7 days
only, it keeps growing up until disk space is full). I have found
documentation and tutorials, mailing list, but I don't know what is
suitable for a Slave. Leads I've found :

- checkpoints
- archive_command
- archive_cleanup

Master postgresq.conf :

[...]
wal_level = 'hot_standby'
archive_mode = on
archive_command = 'rsync -az /var/lib/postgresql/9.1/main/pg_xlog/%f
bar...@nas.lan:/data/pgbarman/psql01/incoming/%f'
max_wal_senders = 5
wal_keep_segments = 64
autovacuum = on

Slave postgresql.conf :

[...]
wal_level = minimal
wal_keep_segments = 32
hot_standby = on

Slave recovery.conf :

standby_mode = 'on'
primary_conninfo = 'host=10.0.0.1 port=5400 user=postgres'
trigger_file = '/var/lib/postgresql/9.1/triggersql'
restore_command='cp /var/lib/postgresql/9.1/wal_archive/%f "%p"'
archive_cleanup_command =
'/usr/lib/postgresql/9.1/bin/pg_archivecleanup
/var/lib/postgresql/9.1/wal_archive/ %r'


How can I reduce the number of WAL files on the hot_stanby slave ?


If the number of WAL files in pg_xlog are growing, then you need to 
look at why the files are not getting deleted.


Do you see master and standby in sync ? You can check that by getting 
the current pg_xlog position in standby.


Regards,
Venkata Balaji N

Fujitsu Australia





I have a Zabbix check for pg_xlog in master/slave indeed.

Xavier C.

Re: [GENERAL] pg_xlog on a hot_stanby slave

[Xavier 12]

On 16/06/2015 22:28, Guillaume Lelarge wrote:
2015-06-16 15:55 GMT+02:00 Xavier 12 >:


I don't think so. There is no archive_command and the master doesn't
ship its wal here.
But how can I check that ?


What's the complete path to the directory on the salve that contains 
951 files? what does PostgreSQL say on its log files?


in /var/lib/postgresql/9.1/main/pg_xlog/

1059 files today.
Too much to copy/paste here.
Here are the first ones :

-rw--- 1 postgres postgres 16777216 Jun  9 08:40 
0004040B007E
-rw--- 1 postgres postgres 16777216 Jun  9 08:41 
0004040B007F
-rw--- 1 postgres postgres 16777216 Jun  9 08:42 
0004040B0080
-rw--- 1 postgres postgres 16777216 Jun  9 08:44 
0004040B0081


There are no .done or .ready and archive_status is empty.

Nothing critical in the logs :

Jun 17 08:55:11 psql02 postgres[4231]: [2-1] 2015-06-17 08:55:11 CEST 
LOG:  paquet de d?marrage incomplet
Jun 17 08:55:41 psql02 postgres[4322]: [2-1] 2015-06-17 08:55:41 CEST 
LOG:  paquet de d?marrage incomplet
Jun 17 08:56:11 psql02 postgres[4356]: [2-1] 2015-06-17 08:56:11 CEST 
LOG:  paquet de d?marrage incomplet
Jun 17 08:56:41 psql02postgres[4460]: [2-1] 2015-06-17 08:56:41 CEST 
LOG:  paquet de d?marrage incomplet
Jun 17 08:56:55 psql02postgres[4514]: [2-1] 2015-06-17 08:56:55 CEST 
ERREUR:  restauration en cours
Jun 17 08:56:55 psql02postgres[4514]: [2-2] 2015-06-17 08:56:55 CEST 
ASTUCE :  les fonctions de contr?le des journaux de transactions ne 
peuvent pas
Jun 17 08:56:55 psql02postgres[4514]: [2-3] #011?tre ex?cut?es lors de 
la restauration.
Jun 17 08:56:55 psql02postgres[4514]: [2-4] 2015-06-17 08:56:55 CEST 
INSTRUCTION :  select pg_current_xlog_location()


pg_current_xlog_location() is for a zabbix check, "ERREUR" is because 
that server is readyonly.


Xavier C.




2015-06-16 12:41 GMT+02:00 Guillaume Lelarge
mailto:guilla...@lelarge.info>>:
> Le 16 juin 2015 10:57 AM, "Xavier 12" mailto:mania...@gmail.com>> a écrit :
>>
>> Hi everyone,
>>
>> Questions about pg_xlogs again...
>> I have two Postgresql 9.1 servers in a master/slave stream
replication
>> (hot_standby).
>>
>> Psql01 (master) is backuped with Barman and pg_xlogs is correctly
>> purged (archive_command is used).
>>
>> Hower, Psql02 (slave) has a huge pg_xlog (951 files, 15G for 7 days
>> only, it keeps growing up until disk space is full). I have found
>> documentation and tutorials, mailing list, but I don't know what is
>> suitable for a Slave. Leads I've found :
>>
>> - checkpoints
>> - archive_command
>> - archive_cleanup
>>
>> Master postgresq.conf :
>>
>> [...]
>> wal_level = 'hot_standby'
>> archive_mode = on
>> archive_command = 'rsync -az
/var/lib/postgresql/9.1/main/pg_xlog/%f
>> bar...@nas.lan:/data/pgbarman/psql01/incoming/%f'
>> max_wal_senders = 5
>> wal_keep_segments = 64
>> autovacuum = on
>>
>> Slave postgresql.conf :
>>
>> [...]
>> wal_level = minimal
>> wal_keep_segments = 32
>> hot_standby = on
>>
>> Slave recovery.conf :
>>
>> standby_mode = 'on'
>> primary_conninfo = 'host=10.0.0.1 port=5400 user=postgres'
>> trigger_file = '/var/lib/postgresql/9.1/triggersql'
>> restore_command='cp /var/lib/postgresql/9.1/wal_archive/%f "%p"'
>> archive_cleanup_command =
>> '/usr/lib/postgresql/9.1/bin/pg_archivecleanup
>> /var/lib/postgresql/9.1/wal_archive/ %r'
>>
>>
>>
>>
>>
>> How can I reduce the number of WAL files on the hot_stanby slave ?
>>
>
> Depends on what you're talking about. If they are archived wal,
> pg_archive_cleanup is what you're looking for.




--
Guillaume.
http://blog.guillaume.lelarge.info
http://www.dalibo.com

Re: [GENERAL] pg_dump 8.4.9 failing after upgrade to openssl-1.0.1e-30.el6_6.11.x86_64 on redhat linux

[Albe Laurenz]

Douglas Stetner wrote:
> Looking for confirmation there is an issue with pg_dump failing after upgrade 
> to openssl-1.0.1e-
> 30.el6_6.11.x86_64 on redhat linux.
> 
> -bash-4.1$ pg_dump -V
> pg_dump (PostgreSQL) 8.4.9
> 
> -bash-4.1$ pg_dump -h localhost -C Hogwarts -a -t mafs -f zz
> pg_dump: Dumping the contents of table "mafs" failed: PQgetCopyData() failed.
> pg_dump: Error message from server: SSL error: unexpected message
> pg_dump: The command was: COPY public.mafs (hugo_symbol,
> ...
> ...
> analysis_id) TO stdout;
> 
> 
> This is only happening on 2 tables in this database.  The same database can 
> be backed up with
> pgAdmin3.app remotely from a Mac
> 
> As stated I am fairly sure the cause was the upgrade of openssl as it started 
> to fail the next day:
> Jun 16 05:18:25 qcmg-database1 yum[2965]: Updated: 
> openssl-1.0.1e-30.el6_6.11.x86_64

This looks like a renegotiation problem.

Could you run the following two statements in a psql session:
   SET ssl_renegotiation_limit='3kB';
   SELECT repeat('0123456789', 900)";
and see if it triggers the problem?

If you set "ssl_renegotiation_limit" to 0, either on the server on using the 
PGOPTIONS
environment variable on the client side, does the problem go away?

Since you upgraded OpenSSL on one end (you didn't tell us on which), maybe the
problem is the age of the OpenSSL library on the other side ---
if one side follows RFC 5746 and the other doesn't (because it is pre-2010)
you can end up with problems like that.

If my guess is correct, the solution would be to upgrade OpenSSL on the other 
end too.

And while you are at it, upgrade to PostgreSQL 9.4 if your data are important 
for you.

Yours,
Laurenz Albe

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general