[GENERAL] Slaves show different results for query

[Musall Maik]

Hi,

I have a strange case where a SELECT for a primary key returns 0 rows on one 
slave, while it returns the correct 1 row on another slave and on the master. 
It does however return that row on all slaves when queried with LIKE and 
trailing or leading wildcard.

psql version is 9.3.5, because that's what comes with Ubuntu 14.04 LTS
Master runs Ubuntu 14.04 LTS
Slave 1 runs also Ubuntu 14.04 LTS
Slave 2 runs Mac OS X 10.7, pgsql installed via homebrew

Both slaves are configured with streaming replication, and I've been using that 
setup for years, starting with psql 9.1, with no problems so far. Suspecting 
some weird problem, I already re-initialized slave 2 with a fresh backup and 
started replication from the beginning, so the database is fresh from a master 
copy, and is verified to be current.

2015-05-19 20:53:43.937 CEST LOG:  entering standby mode
2015-05-19 20:53:43.974 CEST LOG:  redo starts at 31/3F28
2015-05-19 20:53:45.522 CEST LOG:  consistent recovery state reached at 
31/40CCE6E8
2015-05-19 20:53:45.523 CEST LOG:  database system is ready to accept read only 
connections
2015-05-19 20:53:45.604 CEST LOG:  started streaming WAL from primary at 
31/4100 on timeline 1


So here's the query.

SELECT * FROM MyTable WHERE email = 'f...@example.com';

This returns 1 row on master and slave 1, but 0 on slave 2, while this query:

SELECT * FROM MyTable WHERE email LIKE 'f...@example.com%';

or this one

SELECT * FROM MyTable WHERE email LIKE '%f...@example.com';

returns the correct 1 row on all three systems. Note that this works with the 
wildcard on either end, or also somewhere in the middle, doesn't matter. Note: 
"email" is the primary key on this table.

This behaviour is the same with any address to be queried, and is also the same 
on a similar second table. This does NOT occur on any other table, which all 
have integer primary keys. There is also no problem when I select for other 
attributes on these tables.

Does anyone have a hint?

Thanks
Maik



-- 
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 Concern

[Sameer Thakur]

Hello,
>First: When I have given checkpoint segments=200 (As per my knowledge WAL
file should be generated >200*2+3=403 only) but why it’s generating too much
file. MY each WAL file is 16 MB.

The formula is
(2 + checkpoint_completion_target) * checkpoint_segments + 1 
or 
checkpoint_segments + wal_keep_segments + 1 files

So what is your checkpoint_completion_target and wal_keep_segments?
regards
Sameer



--
View this message in context: 
http://postgresql.nabble.com/pg-xlog-Concern-tp5849713p5850100.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

[GENERAL] Memory Utilization Issue

[Sachin Srivastava]

Dear Concern,


Always my server memory utilization is remain >99%. I have 4 DB server and
RAM of the server is (32 GB, 64 GB, 64 GB and 132 GB*). In every server
always we are getting the memory utilization > 99%. *Kindly suggest why
this problem is and which parameter will resolve this problem.

I am using PostgreSQL 9.1.2 and PostGIS 1.5 version on every Red hat Linux
server. Kindly find the "TOP" result, "ulimit -a"  result,
("kernel.shmax", "kernel.shmall", "kernel.sem" value) and pg_setting result
of one server as below. If you require any other information then inform to
me.





[root@CPPMOMA_DB01 ~]# top

top - 12:08:08 up 15 days, 15:45,  5 users,  load average: 1.05, 1.19, 1.35

Tasks: 605 total,   1 running, 604 sleeping,   0 stopped,   0 zombie

Cpu(s):  6.9%us,  2.1%sy,  0.0%ni, 78.3%id, 12.7%wa,  0.0%hi,  0.1%si,
0.0%st
Mem:  32832364k total, 32621168k used,   211196k free,77572k buffers


[root@CPPMOMA_DB01 ~]# ulimit -a
core file size  (blocks, -c) 0
data seg size   (kbytes, -d) unlimited
scheduling priority (-e) 0
file size   (blocks, -f) unlimited
pending signals (-i) 256323
max locked memory   (kbytes, -l) 64
max memory size (kbytes, -m) unlimited
open files  (-n) 4096
pipe size(512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
real-time priority  (-r) 0
stack size  (kbytes, -s) 10240
cpu time   (seconds, -t) unlimited
max user processes  (-u) 256323
virtual memory  (kbytes, -v) unlimited
file locks  (-x) unlimited
[root@CPPMOMA_DB01 ~]#


kernel.shmmax = 32212254720

kernel.shmall = 1073741824

kernel.sem = 250 32000 100 384


postgres=# SELECT name, source, setting FROM pg_settings WHERE source !=
'default' AND source != 'override' ORDER by 2, 1;

name|source| setting

+--+--

 application_name   | client   | psql.bin

 client_encoding| client   | UTF8

 archive_command| configuration file   | cp %p
/dbkup/momacpp_213_live/%f

 archive_mode   | configuration file   | on

 autovacuum | configuration file   | on

 autovacuum_max_workers | configuration file   | 3

 checkpoint_segments| configuration file   | 200

 checkpoint_timeout | configuration file   | 300

 checkpoint_warning | configuration file   | 30

 DateStyle  | configuration file   | ISO, MDY

 default_text_search_config | configuration file   | pg_catalog.english

 effective_cache_size   | configuration file   | 524288

 lc_messages| configuration file   | en_US.UTF-8

 lc_monetary| configuration file   | en_US.UTF-8

 lc_numeric | configuration file   | en_US.UTF-8

 lc_time| configuration file   | en_US.UTF-8

 listen_addresses   | configuration file   | *

 log_destination| configuration file   | stderr

 log_directory  | configuration file   | pg_log

 logging_collector  | configuration file   | on

 log_line_prefix| configuration file   | %t

 log_rotation_age   | configuration file   | 1440

 maintenance_work_mem   | configuration file   | 1638400

 max_connections| configuration file   | 2000

 max_files_per_process  | configuration file   | 2000

 max_wal_senders| configuration file   | 5

 port   | configuration file   | 5432

 shared_buffers | configuration file   | 1572864

 temp_buffers   | configuration file   | 4096

 wal_level  | configuration file   | archive

 work_mem   | configuration file   | 32768

 log_timezone   | environment variable | Asia/Kolkata

 max_stack_depth| environment variable | 2048

 TimeZone   | environment variable | Asia/Kolkata

(34 rows)



postgres=#






Regards,

*Sachin Srivastava*
Assistant Technical Lead(Oracle/PostgreSQL)| TSG
*Cyient* | www.cyient.com

[GENERAL] Testing Views

[Andy Chambers]

Hello All,

I have a problem for which a view seems like a nice solution. Basically we
want to see all records in some table that are older than 5 days and
haven't yet gone through further processing.

This particular view is probably simple enough that it doesn't require unit
tests but I wonder how people test complex views that depend on the current
date or time. I found a thread on the hackers list [1] that talked about
stubbing pg_catalog.now() but was wondering if any TDD minded developers
had invented anything better since then.

One option I thought of was to just not use views that depend on the
current date or time and instead create a set returning function that takes
the time as a parameter. Would such a function have similar performance
characteristics to an equivalent view?

Cheers,
Andy

[1]:
http://postgresql.nabble.com/overriding-current-timestamp-td5507701.html

Re: [GENERAL] Optimizing a read-only database

[Sameer Thakur]

Hello,
You could disable fsync as write reliability is not relevant
regards
Sameer



--
View this message in context: 
http://postgresql.nabble.com/Optimizing-a-read-only-database-tp5849746p5850103.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
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] Memory Utilization Issue

[Albe Laurenz]

Sachin Srivastava wrote:
> Always my server memory utilization is remain >99%. I have 4 DB server and 
> RAM of the server is (32
> GB, 64 GB, 64 GB and 132 GB). In every server always we are getting the 
> memory utilization > 99%.
> Kindly suggest why this problem is and which parameter will resolve this 
> problem.
> 
> I am using PostgreSQL 9.1.2 and PostGIS 1.5 version on every Red hat Linux 
> server. Kindly find the
> "TOP" result, "ulimit -a"  result, ("kernel.shmax", "kernel.shmall", 
> "kernel.sem" value) and
> pg_setting result of one server as below. If you require any other 
> information then inform to me.

That sounds just fine.
Linux uses memory for the file system cache.  That memory is shown as "in use", 
but
it is available for processes if they need it.

It doesn't look like your machine is swapping.

Do you experience problems?

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

Re: [GENERAL] Optimizing a read-only database

[Albe Laurenz]

Sameer Thakur wrote:
> You could disable fsync as write reliability is not relevant

That is bad advice.
If there are no writes, fsync won't hurt anyway.
Never disable fsync for anything but test systems.

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

Re: [GENERAL] Optimizing a read-only database

[Sameer Thakur]

Hello,
>I was more dreaming of something like "disable read write locks or 
>mutexes" when accessing the database in read-only mode, but sadly this 
>case seems unhandled. 

You could use transactions in read only mode. They do not generate
XID's,which reduces the 
need to do VACUUM to protect against XID wraparound. 

Ref: http://postgresql.nabble.com/read-only-transactions-td3209290.html





--
View this message in context: 
http://postgresql.nabble.com/Optimizing-a-read-only-database-tp5849746p5850107.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
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] Optimizing a read-only database

[Sameer Thakur]

Hello
>That is bad advice. 
>If there are no writes, fsync won't hurt anyway. 
>Never disable fsync for anything but test systems. 

Yep. Its a bad way to speed up writes. Not relevant to this context and bad
anyway
regards
Sameer



--
View this message in context: 
http://postgresql.nabble.com/Optimizing-a-read-only-database-tp5849746p5850108.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
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] Optimizing a read-only database

[Sameer Thakur]

Hello,

In addition to what has already been suggested

1. Use  

VACUUM FREEZE ANALYZE; 

Otherwise you will still have some trickle of write-activity going on, 
not always efficiently, despite being in read-only mode.  It's because 
of what's referred to as Hint Bits:   
http://wiki.postgresql.org/wiki/Hint_Bits

2. Low value for maintenance_work_mem

Ref:http://postgresql.nabble.com/How-to-configure-a-read-only-database-server-td4311924.html

regards
Sameer





--
View this message in context: 
http://postgresql.nabble.com/Optimizing-a-read-only-database-tp5849746p5850109.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
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] Optimizing a read-only database

[François Battail]

Dear List,

Thank you for all for your advices, even if there's not a direct and 
magical solution, I've now some paths to try.


I really enjoy the PostgreSQL community.

Wish you a nice day/night, best regards.


--
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] Testing Views

[Gavin Flower]

On 20/05/15 19:47, Andy Chambers wrote:

Hello All,

I have a problem for which a view seems like a nice solution. 
Basically we want to see all records in some table that are older than 
5 days and haven't yet gone through further processing.


This particular view is probably simple enough that it doesn't require 
unit tests but I wonder how people test complex views that depend on 
the current date or time. I found a thread on the hackers list [1] 
that talked about stubbing pg_catalog.now() but was wondering if any 
TDD minded developers had invented anything better since then.


One option I thought of was to just not use views that depend on the 
current date or time and instead create a set returning function that 
takes the time as a parameter. Would such a function have similar 
performance characteristics to an equivalent view?


Cheers,
Andy

[1]: 
http://postgresql.nabble.com/overriding-current-timestamp-td5507701.html

How about using a 'Time Machine'?

Run the database in a VM and change the date/time.  You can take a clone 
of the VM, and recreate it multiple times from a known state.


A technique we used to test for Y2K problems, except in those days we 
did have Virtual Machines like we have now!



Cheers,
Gavin


--
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] Memory Utilization Issue

[Mathew Moon]

What is the output of 'free -m' ? Look at the third column second row (+/- 
cache). This is the most relevant number. Even if you are swapping that can 
happen with plenty of RAM available if 'swappiness' is set too low. 

Sent from my iPhone

> On May 20, 2015, at 2:25 AM, Sachin Srivastava  
> wrote:
> 
> Dear Concern,
>  
> Always my server memory utilization is remain >99%. I have 4 DB server and 
> RAM of the server is (32 GB, 64 GB, 64 GB and 132 GB). In every server always 
> we are getting the memory utilization > 99%. Kindly suggest why this problem 
> is and which parameter will resolve this problem.
> 
> I am using PostgreSQL 9.1.2 and PostGIS 1.5 version on every Red hat Linux 
> server. Kindly find the "TOP" result, "ulimit -a"  result, ("kernel.shmax", 
> "kernel.shmall", "kernel.sem" value) and pg_setting result of one server as 
> below. If you require any other information then inform to me.
>  
>  
> [root@CPPMOMA_DB01 ~]# top
> top - 12:08:08 up 15 days, 15:45,  5 users,  load average: 1.05, 1.19, 1.35
> Tasks: 605 total,   1 running, 604 sleeping,   0 stopped,   0 zombie
> Cpu(s):  6.9%us,  2.1%sy,  0.0%ni, 78.3%id, 12.7%wa,  0.0%hi,  0.1%si,  0.0%st
> Mem:  32832364k total, 32621168k used,   211196k free,77572k buffers
> 
> 
> [root@CPPMOMA_DB01 ~]# ulimit -a
> core file size  (blocks, -c) 0
> data seg size   (kbytes, -d) unlimited
> scheduling priority (-e) 0
> file size   (blocks, -f) unlimited
> pending signals (-i) 256323
> max locked memory   (kbytes, -l) 64
> max memory size (kbytes, -m) unlimited
> open files  (-n) 4096
> pipe size(512 bytes, -p) 8
> POSIX message queues (bytes, -q) 819200
> real-time priority  (-r) 0
> stack size  (kbytes, -s) 10240
> cpu time   (seconds, -t) unlimited
> max user processes  (-u) 256323
> virtual memory  (kbytes, -v) unlimited
> file locks  (-x) unlimited
> [root@CPPMOMA_DB01 ~]#
> 
> 
> kernel.shmmax = 32212254720
> 
> kernel.shmall = 1073741824
> 
> kernel.sem = 250 32000 100 384
> 
>  
> postgres=# SELECT name, source, setting FROM pg_settings WHERE source != 
> 'default' AND source != 'override' ORDER by 2, 1;
> 
> name|source| setting
> +--+--
>  application_name   | client   | psql.bin
>  client_encoding| client   | UTF8
>  archive_command| configuration file   | cp %p 
> /dbkup/momacpp_213_live/%f
>  archive_mode   | configuration file   | on
>  autovacuum | configuration file   | on
>  autovacuum_max_workers | configuration file   | 3
>  checkpoint_segments| configuration file   | 200
>  checkpoint_timeout | configuration file   | 300
>  checkpoint_warning | configuration file   | 30
>  DateStyle  | configuration file   | ISO, MDY
>  default_text_search_config | configuration file   | pg_catalog.english
>  effective_cache_size   | configuration file   | 524288
>  lc_messages| configuration file   | en_US.UTF-8
>  lc_monetary| configuration file   | en_US.UTF-8
>  lc_numeric | configuration file   | en_US.UTF-8
>  lc_time| configuration file   | en_US.UTF-8
>  listen_addresses   | configuration file   | *
>  log_destination| configuration file   | stderr
>  log_directory  | configuration file   | pg_log
>  logging_collector  | configuration file   | on
>  log_line_prefix| configuration file   | %t
>  log_rotation_age   | configuration file   | 1440
>  maintenance_work_mem   | configuration file   | 1638400
>  max_connections| configuration file   | 2000
>  max_files_per_process  | configuration file   | 2000
>  max_wal_senders| configuration file   | 5
>  port   | configuration file   | 5432
>  shared_buffers | configuration file   | 1572864
>  temp_buffers   | configuration file   | 4096
>  wal_level  | configuration file   | archive
>  work_mem   | configuration file   | 32768
>  log_timezone   | environment variable | Asia/Kolkata
>  max_stack_depth| environment variable | 2048
>  TimeZone   | environment variable | Asia/Kolkata
> (34 rows)
>  
> postgres=#
>  
> 
>  
>  
> Regards,
> Sachin Srivastava
> Assistant Technical Lead(Oracle/PostgreSQL)| TSG
> Cyient | www.cyient.com

Re: [GENERAL] pg_xlog Concern

[Mathew Moon]

--
> "MY each WAL file is 16 MB."
---
Are you saying that you want WAL segments to be smaller? WAL file segments are 
always the same size, which can be set with --with-WAL-segsize at build time 
but not at runtime.

Regards
Mathew 

Sent from my iPhone

> On May 20, 2015, at 2:16 AM, Sameer Thakur  wrote:
> 
> Hello,
>> First: When I have given checkpoint segments=200 (As per my knowledge WAL
> file should be generated >200*2+3=403 only) but why it’s generating too much
> file. MY each WAL file is 16 MB.
> 
> The formula is
> (2 + checkpoint_completion_target) * checkpoint_segments + 1 
> or 
> checkpoint_segments + wal_keep_segments + 1 files
> 
> So what is your checkpoint_completion_target and wal_keep_segments?
> regards
> Sameer
> 
> 
> 
> --
> View this message in context: 
> http://postgresql.nabble.com/pg-xlog-Concern-tp5849713p5850100.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
> 
> 
> -- 
> 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] Memory Utilization Issue

[Naveed Shaikh]

Could you also please check the Transparent huge page(THP) are enabled on
the server or not, they can also result in intermittent poor performance
along with high system cpu time counted against the database processes.

This can be confirmed by below command:

cat /sys/kernel/mm/redhat_transparent_hugepage/enabled

Here "always" is selected, showing THP are enabled, this needs to be
disable with following command:

echo never > /sys/kernel/mm/redhat_transparent_hugepage/enabled

Thanks & Regards,
Naveed Shaikh





On Wed, May 20, 2015 at 12:55 PM, Sachin Srivastava  wrote:

> Dear Concern,
>
>
> Always my server memory utilization is remain >99%. I have 4 DB server and
> RAM of the server is (32 GB, 64 GB, 64 GB and 132 GB*). In every server
> always we are getting the memory utilization > 99%. *Kindly suggest why
> this problem is and which parameter will resolve this problem.
>
> I am using PostgreSQL 9.1.2 and PostGIS 1.5 version on every Red hat Linux
> server. Kindly find the "TOP" result, "ulimit -a"  result,
> ("kernel.shmax", "kernel.shmall", "kernel.sem" value) and pg_setting result
> of one server as below. If you require any other information then inform to
> me.
>
>
>
>
>
> [root@CPPMOMA_DB01 ~]# top
>
> top - 12:08:08 up 15 days, 15:45,  5 users,  load average: 1.05, 1.19,
> 1.35
>
> Tasks: 605 total,   1 running, 604 sleeping,   0 stopped,   0 zombie
>
> Cpu(s):  6.9%us,  2.1%sy,  0.0%ni, 78.3%id, 12.7%wa,  0.0%hi,  0.1%si,
> 0.0%st
> Mem:  32832364k total, 32621168k used,   211196k free,77572k buffers
>
>
> [root@CPPMOMA_DB01 ~]# ulimit -a
> core file size  (blocks, -c) 0
> data seg size   (kbytes, -d) unlimited
> scheduling priority (-e) 0
> file size   (blocks, -f) unlimited
> pending signals (-i) 256323
> max locked memory   (kbytes, -l) 64
> max memory size (kbytes, -m) unlimited
> open files  (-n) 4096
> pipe size(512 bytes, -p) 8
> POSIX message queues (bytes, -q) 819200
> real-time priority  (-r) 0
> stack size  (kbytes, -s) 10240
> cpu time   (seconds, -t) unlimited
> max user processes  (-u) 256323
> virtual memory  (kbytes, -v) unlimited
> file locks  (-x) unlimited
> [root@CPPMOMA_DB01 ~]#
>
>
> kernel.shmmax = 32212254720
>
> kernel.shmall = 1073741824
>
> kernel.sem = 250 32000 100 384
>
>
> postgres=# SELECT name, source, setting FROM pg_settings WHERE source !=
> 'default' AND source != 'override' ORDER by 2, 1;
>
> name|source| setting
>
>
> +--+--
>
>  application_name   | client   | psql.bin
>
>  client_encoding| client   | UTF8
>
>  archive_command| configuration file   | cp %p
> /dbkup/momacpp_213_live/%f
>
>  archive_mode   | configuration file   | on
>
>  autovacuum | configuration file   | on
>
>  autovacuum_max_workers | configuration file   | 3
>
>  checkpoint_segments| configuration file   | 200
>
>  checkpoint_timeout | configuration file   | 300
>
>  checkpoint_warning | configuration file   | 30
>
>  DateStyle  | configuration file   | ISO, MDY
>
>  default_text_search_config | configuration file   | pg_catalog.english
>
>  effective_cache_size   | configuration file   | 524288
>
>  lc_messages| configuration file   | en_US.UTF-8
>
>  lc_monetary| configuration file   | en_US.UTF-8
>
>  lc_numeric | configuration file   | en_US.UTF-8
>
>  lc_time| configuration file   | en_US.UTF-8
>
>  listen_addresses   | configuration file   | *
>
>  log_destination| configuration file   | stderr
>
>  log_directory  | configuration file   | pg_log
>
>  logging_collector  | configuration file   | on
>
>  log_line_prefix| configuration file   | %t
>
>  log_rotation_age   | configuration file   | 1440
>
>  maintenance_work_mem   | configuration file   | 1638400
>
>  max_connections| configuration file   | 2000
>
>  max_files_per_process  | configuration file   | 2000
>
>  max_wal_senders| configuration file   | 5
>
>  port   | configuration file   | 5432
>
>  shared_buffers | configuration file   | 1572864
>
>  temp_buffers   | configuration file   | 4096
>
>  wal_level  | configuration file   | archive
>
>  work_mem   | configuration file   | 32768
>
>  log_timezone   | environment variable | Asia/Kolkata
>
>  max_stack_depth| environment variable | 2048
>
>  TimeZone   | environment variable | Asia/Kolkata
>
> (34 rows)
>
>
>
> postgres=#
>
>
>
>
>
>
> Regards,
>
> *

Re: [GENERAL] pg_xlog Concern

[PT]

(I'm not going to try to fix the top-posting)

I've seen what I think you're describing on several occasions.

What we basically discovered is very simple: disks have a top speed and
you can't exceed it.

If I understand you correctly, you see the # of wal segments grow very
large during and shortly after bulk data changes, then they shrink back
down to what you'd expect, but grow again during the next bulk data
change.

Essentially, what we discovered was happening was that we were doing
bulk data manipulations at about 100% the speed of the disk subsystem.
As a result, there was no additional capacity for the wal archiving to
copy files with. Archiving still runs, but it's much slowed down, just
like any other disk activity when the disks are very busy. Since
PostgreSQL thinks your data is important, it won't discard wal files
that have not yet been archived, so they stick around until it can
catch up.

If I'm diagnosing your situation correclty, you have a few options:

1) Just accept it, that's what we decided since the bulk operations
   only happened about once a month and the problem cleared up in a
   few hours.
2) Faster disks: move to SSDs or a better RAID controller or whatever
   it takes to make the disks fast enough not to have the problem.
3) Move the wal and/or the wal archive directories onto a different disk
   subsystem, which essentially increases the speed of the disks through
   "divide and conquer". You many not even need new hardware to accomplish
   this -- if you have enough disks you might benefit from rearranging
   how they're organized in the RAID controller. Of course, you'll have
   to back up and restore the system to do so.
4) Change the application that does the bulk loading to throttle itself
   so it doesn't overload the disks, which will then allow wal archiving
   to keep up through the process.

Of course, each of these solutions has its benefits and drawbacks, so
you'll have to decide which is right for you.

On Mon, 18 May 2015 17:34:21 +0300
Koray Eyidoğan  wrote:

> Hi,
> 
> Any kind of slowness on your archive directory may cause the archiving
> process fall behind thus accumulating segment files in your cluster's
> pg_xlog directory.
> 
> I assume that you are on PostgreSQL 9.4. Could you please check your
> archiver status by typing "select * from pg_catalog.pg_stat_archiver;" in
> psql ? If the last_archived_wal column's value is not so close to your
> current xlog location, then it probably means a slow write speed on your
> archive path compared to your pg_xlog path.
> 
> You can check your current xlog file by typing "select
> pg_xlogfile_name(pg_current_xlog_location());" in psql.
> 
> If you are not on PostgreSQL 9.4, you can check your archiver status by
> typing "ps -ef | grep archiver" in your shell.
> 
> I've also assumed that you are not using replication slots.
> 
> As far as I know, long running transactions (just sitting in idle) won't
> affect pg_xlog directory size. Correct me if I'm wrong.
> 
> Hope that helps.
> 
> Have a nice day.
> 
> Koray
> 
> 
> 
> 
> On Mon, May 18, 2015 at 5:00 PM, Torsten Förtsch 
> wrote:
> 
> > On 18/05/15 13:44, Sachin Srivastava wrote:
> > > But currently my pg_xlog size is 60 GB and there are 3740 WAL file in
> > > this folder and in Last week this was 400 GB(pg_xlog folder) and WAL
> > > file were approx. 3. Due to archiving pg_xlog folder size is
> > > decreasing now but it’s taking one week to come in normal size.
> >
> > Any chance you have unfinished transactions running for a week?
> >
> > pg_stat_activity should be able to tell you that.
> >
> > Torsten
> >
> >
> > --
> > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
> >


-- 
PT 


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

[GENERAL] partitioned tables

[George Neuner]

Hi all,

I've been using Postgresql (9.3) for a while, but I'm new to
partitioned tables.


1.  I have noticed that when the partition trigger function returns
NULL (to stop operation on the parent table), the operation always
reports no (zero) rows affected - even when rows have been affected.
That's a problem for error checking in my client applications.  Is
there a way to get the number of rows affected regardless of the
trigger?


2.  I need to do upserts as opposed to just inserts.  Does it make
sense to try to redirect updates to the current active partition in a
trigger (as with insert) or is it better to choose the update
target(s) using a where clause?


3.  Do the child tables all need separate indexing.  I've seen
conflicting information on the web - particularly in regards to
defining a primary key on the parent table.


4.  I need to keep 15 months of data in circular fashion.  Is there a
clever way of selecting partition by date when there are 15 of them?
Or is it waterfall time (add/delete tables every month)?  


Thanks,
George



-- 
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] Slaves show different results for query

[Melvin Davidson]

First, are your postgresql.conf options the same on master and slave2 with
regard to memory allocation and all Planner Method Configuration options?

Next, is it possible you have a corrupted index on slave2.?

I would suggest verifying there is no difference in the query plan between
master and slave2.
IE: EXPLAIN SELECT * FROM MyTable WHERE email = 'f...@example.com';

Check your indexes with:

SELECT n.nspname as schema,
   i.relname as table,
   i.indexrelname as index,
   i.idx_scan,
   i.idx_tup_read,
   i.idx_tup_fetch,
   CASE WHEN idx.indisprimary
THEN 'pkey'
WHEN idx.indisunique
THEN 'uidx'
ELSE 'idx'
END AS type,
   pg_get_indexdef(idx.indexrelid),
   CASE WHEN idx.indisvalid
THEN 'valid'
ELSE 'INVALID'
END as istatus,
   pg_relation_size(quote_ident(n.nspname)|| '.' ||
quote_ident(i.relname)) as size_in_bytes,
   pg_size_pretty(pg_relation_size(quote_ident(n.nspname)|| '.' ||
quote_ident(i.relname))) as size
  FROM pg_stat_all_indexes i
  JOIN pg_class c ON (c.oid = i.relid)
  JOIN pg_namespace n ON (n.oid = c.relnamespace)
  JOIN pg_index idx ON (idx.indexrelid =  i.indexrelid )
 WHERE i.relname = 'MyTable'
ORDER BY 1, 2, 3;

On Wed, May 20, 2015 at 2:52 AM, Musall Maik  wrote:

> Hi,
>
> I have a strange case where a SELECT for a primary key returns 0 rows on
> one slave, while it returns the correct 1 row on another slave and on the
> master. It does however return that row on all slaves when queried with
> LIKE and trailing or leading wildcard.
>
> psql version is 9.3.5, because that's what comes with Ubuntu 14.04 LTS
> Master runs Ubuntu 14.04 LTS
> Slave 1 runs also Ubuntu 14.04 LTS
> Slave 2 runs Mac OS X 10.7, pgsql installed via homebrew
>
> Both slaves are configured with streaming replication, and I've been using
> that setup for years, starting with psql 9.1, with no problems so far.
> Suspecting some weird problem, I already re-initialized slave 2 with a
> fresh backup and started replication from the beginning, so the database is
> fresh from a master copy, and is verified to be current.
>
> 2015-05-19 20:53:43.937 CEST LOG:  entering standby mode
> 2015-05-19 20:53:43.974 CEST LOG:  redo starts at 31/3F28
> 2015-05-19 20:53:45.522 CEST LOG:  consistent recovery state reached at
> 31/40CCE6E8
> 2015-05-19 20:53:45.523 CEST LOG:  database system is ready to accept read
> only connections
> 2015-05-19 20:53:45.604 CEST LOG:  started streaming WAL from primary at
> 31/4100 on timeline 1
>
>
> So here's the query.
>
> SELECT * FROM MyTable WHERE email = 'f...@example.com';
>
> This returns 1 row on master and slave 1, but 0 on slave 2, while this
> query:
>
> SELECT * FROM MyTable WHERE email LIKE 'f...@example.com%';
>
> or this one
>
> SELECT * FROM MyTable WHERE email LIKE '%f...@example.com';
>
> returns the correct 1 row on all three systems. Note that this works with
> the wildcard on either end, or also somewhere in the middle, doesn't
> matter. Note: "email" is the primary key on this table.
>
> This behaviour is the same with any address to be queried, and is also the
> same on a similar second table. This does NOT occur on any other table,
> which all have integer primary keys. There is also no problem when I select
> for other attributes on these tables.
>
> Does anyone have a hint?
>
> Thanks
> Maik
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Slaves show different results for query

[Musall Maik]

Hi Melvin,

thanks for the response.

> Am 20.05.2015 um 14:26 schrieb Melvin Davidson :
> 
> First, are your postgresql.conf options the same on master and slave2 with 
> regard to memory allocation and all Planner Method Configuration options?

slave2 has less shared_buffers (256m vs 2048m), temp_buffers (16m vs 128m), 
work_mem (8m vs 16m) and maintenance_work_mem (8m vs 16m) due to hardware 
constraints. All other settings are the same and mostly default.

> Next, is it possible you have a corrupted index on slave2.?

Unlikely, as I replaced the slave2 db with a fresh backup from master yesterday 
to rule this out.

> I would suggest verifying there is no difference in the query plan between 
> master and slave2.
> IE: EXPLAIN SELECT * FROM MyTable WHERE email = 'f...@example.com 
> ';

All three (master, slave1, slave2) use the same plan:

 Index Scan using mytable_pk on mytable  (cost=0.42..8.44 rows=1 width=205)
   Index Cond: ((email)::text = 'f...@example.com'::text)

> Check your indexes with:
> 
> SELECT n.nspname as schema,
>i.relname as table,
>i.indexrelname as index,
>i.idx_scan,
>i.idx_tup_read,
>i.idx_tup_fetch,
>CASE WHEN idx.indisprimary
> THEN 'pkey'
> WHEN idx.indisunique
> THEN 'uidx'
> ELSE 'idx'
> END AS type,
>pg_get_indexdef(idx.indexrelid),
>CASE WHEN idx.indisvalid
> THEN 'valid'
> ELSE 'INVALID'
> END as istatus,
>pg_relation_size(quote_ident(n.nspname)|| '.' || 
> quote_ident(i.relname)) as size_in_bytes,
>pg_size_pretty(pg_relation_size(quote_ident(n.nspname)|| '.' || 
> quote_ident(i.relname))) as size
>   FROM pg_stat_all_indexes i
>   JOIN pg_class c ON (c.oid = i.relid)
>   JOIN pg_namespace n ON (n.oid = c.relnamespace)
>   JOIN pg_index idx ON (idx.indexrelid =  i.indexrelid )
>  WHERE i.relname = 'MyTable'
> ORDER BY 1, 2, 3;

master:
 schema |  table  |index| idx_scan | idx_tup_read | 
idx_tup_fetch | type |   pg_get_indexdef
 | istatus | size_in_bytes |  size
+-+-+--+--+---+--+---+-+---+
 public | mytable | mytable_emailaddressref_idx |6 |   11 | 
4 | idx  | CREATE INDEX mytable_emailaddressref_idx ON mytable USING 
btree (emailaddressref) | valid   | 123609088 | 118 MB
 public | mytable | mytable_pk  |  1291541 |  1305655 | 
  1291371 | pkey | CREATE UNIQUE INDEX mytable_pk ON mytable USING btree 
(email) | valid   | 123609088 | 118 MB
 public | mytable | mytable_syncstatus_idx  | 3710 |  2250428 | 
0 | idx  | CREATE INDEX mytable_syncstatus_idx ON mytable USING btree 
(syncstatus)   | valid   | 123609088 | 118 MB
 public | mytable | mytable_userref_idx |0 |0 | 
0 | idx  | CREATE INDEX mytable_userref_idx ON mytable USING btree 
(userref) | valid   | 123609088 | 118 MB

slave1:
 schema |  table  |   index | idx_scan | idx_tup_read | 
idx_tup_fetch | type |   pg_get_indexdef
 | istatus | size_in_bytes |  size
+-+-+--+--+---+--+---+-+---+
 public | mytable | mytable_emailaddressref_idx |0 |0 | 
0 | idx  | CREATE INDEX mytable_emailaddressref_idx ON mytable USING 
btree (emailaddressref) | valid   | 123609088 | 118 MB
 public | mytable | mytable_pk  |3 |   13 | 
3 | pkey | CREATE UNIQUE INDEX mytable_pk ON mytable USING btree 
(email) | valid   | 123609088 | 118 MB
 public | mytable | mytable_syncstatus_idx  |0 |0 | 
0 | idx  | CREATE INDEX mytable_syncstatus_idx ON mytable USING btree 
(syncstatus)   | valid   | 123609088 | 118 MB
 public | mytable | mytable_userref_idx |0 |0 | 
0 | idx  | CREATE INDEX mytable_userref_idx ON mytable USING btree 
(userref) | valid   | 123609088 | 118 MB

slave2:
 schema |  table  |index| idx_scan | idx_tup_read | 
idx_tup_fetch | type |   pg_get_indexdef
 | istatus | size_in_bytes |  size
+-+-+--+--+---+--+--

Re: [GENERAL] Slaves show different results for query

[Adrian Klaver]

On 05/19/2015 11:52 PM, Musall Maik wrote:

Hi,

I have a strange case where a SELECT for a primary key returns 0 rows on one 
slave, while it returns the correct 1 row on another slave and on the master. 
It does however return that row on all slaves when queried with LIKE and 
trailing or leading wildcard.

psql version is 9.3.5, because that's what comes with Ubuntu 14.04 LTS
Master runs Ubuntu 14.04 LTS
Slave 1 runs also Ubuntu 14.04 LTS
Slave 2 runs Mac OS X 10.7, pgsql installed via homebrew

Both slaves are configured with streaming replication, and I've been using that 
setup for years, starting with psql 9.1, with no problems so far. Suspecting 
some weird problem, I already re-initialized slave 2 with a fresh backup and 
started replication from the beginning, so the database is fresh from a master 
copy, and is verified to be current.

2015-05-19 20:53:43.937 CEST LOG:  entering standby mode
2015-05-19 20:53:43.974 CEST LOG:  redo starts at 31/3F28
2015-05-19 20:53:45.522 CEST LOG:  consistent recovery state reached at 
31/40CCE6E8
2015-05-19 20:53:45.523 CEST LOG:  database system is ready to accept read only 
connections
2015-05-19 20:53:45.604 CEST LOG:  started streaming WAL from primary at 
31/4100 on timeline 1


So here's the query.

SELECT * FROM MyTable WHERE email = 'f...@example.com';

This returns 1 row on master and slave 1, but 0 on slave 2, while this query:

SELECT * FROM MyTable WHERE email LIKE 'f...@example.com%';

or this one

SELECT * FROM MyTable WHERE email LIKE '%f...@example.com';

returns the correct 1 row on all three systems. Note that this works with the wildcard on 
either end, or also somewhere in the middle, doesn't matter. Note: "email" is 
the primary key on this table.

This behaviour is the same with any address to be queried, and is also the same 
on a similar second table. This does NOT occur on any other table, which all 
have integer primary keys. There is also no problem when I select for other 
attributes on these tables.

Does anyone have a hint?


What are the encodings on the various machines and in the databases?



Thanks
Maik






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


--
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] Slaves show different results for query

[Musall Maik]

> Am 20.05.2015 um 15:17 schrieb Adrian Klaver :
> 
> On 05/19/2015 11:52 PM, Musall Maik wrote:
>> Hi,
>> 
>> I have a strange case where a SELECT for a primary key returns 0 rows on one 
>> slave, while it returns the correct 1 row on another slave and on the 
>> master. It does however return that row on all slaves when queried with LIKE 
>> and trailing or leading wildcard.
>> 
>> […]

>> Does anyone have a hint?
> 
> What are the encodings on the various machines and in the databases?

All encodings UTF8, all collate and ctype en_US.UTF-8. What do you mean by 
"machines" exactly? This is not dependent on shell environment or something, I 
get this also via JDBC.

Maik



-- 
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] Slaves show different results for query

[Adrian Klaver]

On 05/20/2015 06:34 AM, Musall Maik wrote:



Am 20.05.2015 um 15:17 schrieb Adrian Klaver :

On 05/19/2015 11:52 PM, Musall Maik wrote:

Hi,

I have a strange case where a SELECT for a primary key returns 0 rows on one 
slave, while it returns the correct 1 row on another slave and on the master. 
It does however return that row on all slaves when queried with LIKE and 
trailing or leading wildcard.

[…]



Does anyone have a hint?


What are the encodings on the various machines and in the databases?


All encodings UTF8, all collate and ctype en_US.UTF-8. What do you mean by 
"machines" exactly? This is not dependent on shell environment or something,


Actually it is:

http://www.postgresql.org/docs/9.4/interactive/locale.html

"Locale support refers to an application respecting cultural preferences 
regarding alphabets, sorting, number formatting, etc. PostgreSQL uses 
the standard ISO C and POSIX locale facilities provided by the server 
operating system. For additional information refer to the documentation 
of your system."



The reason I ask is that the machine you are having problems with has OS 
X. Over the years I have seen quite a few reports on this list of OS X 
locale/encoding issues.


I get this also via JDBC.


Maik






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


--
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] Slaves show different results for query

[Tom Lane]

Adrian Klaver  writes:
> The reason I ask is that the machine you are having problems with has OS 
> X. Over the years I have seen quite a few reports on this list of OS X 
> locale/encoding issues.

Yes.  Here's the problem: OS X UTF8 locales (other than C) don't sort the
same as UTF8 locales on Linux.  Because of this, the index created by the
master is effectively corrupt from the standpoint of the OS X slave; it's
not in the correct sort order.  It might sometimes find the right results
anyway, but often not.

You might be able to get away with the described configuration if you
recreate the database using C locale, but personally I wouldn't risk it.
Masters and slaves in a WAL-shipping replication configuration should be
on substantially identical platforms, else you're just asking for trouble.

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] Slaves show different results for query

[Musall Maik]

Hi Tom,

that's very useful information. Very cool to get such results so quickly here.

Thanks
Maik


> Am 20.05.2015 um 16:05 schrieb Tom Lane :
> 
> Adrian Klaver  writes:
>> The reason I ask is that the machine you are having problems with has OS 
>> X. Over the years I have seen quite a few reports on this list of OS X 
>> locale/encoding issues.
> 
> Yes.  Here's the problem: OS X UTF8 locales (other than C) don't sort the
> same as UTF8 locales on Linux.  Because of this, the index created by the
> master is effectively corrupt from the standpoint of the OS X slave; it's
> not in the correct sort order.  It might sometimes find the right results
> anyway, but often not.
> 
> You might be able to get away with the described configuration if you
> recreate the database using C locale, but personally I wouldn't risk it.
> Masters and slaves in a WAL-shipping replication configuration should be
> on substantially identical platforms, else you're just asking for trouble.
> 
>   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



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

[GENERAL] Can we simulate Oracle Flashback with pg_export_snapshot()?

[William Dunn]

Hello,

Just had an idea and could use some feedback. If we start a transaction,
leave it idle, and use pg_export_snapshot() to get its snapshot_id MVCC
will hold all the tuples as of that transaction's start and any other
transaction can see the state of the database as of that time using SET
TRANSACTION SNAPSHOT snapshot_id?

http://www.postgresql.org/docs/devel/static/functions-admin.html#FUNCTIONS-SNAPSHOT-SYNCHRONIZATION

I'm thinking of setting up automation to ~every half hour open a
transaction as such, close any that have been open over an hour, and store
the snapshot_id. However, I don't have a busy system that I can test it on.

Of course this would cause some extra bloat because those tuples cannot get
autovacuumed until the transaction closes but that is also the case in
Oracle. Is there anything else I am missing or a reason that this would not
be possible?

Thanks!

*Will J. Dunn*
*willjdunn.com *

[GENERAL] Consistent state for pg_dump and pg_dumpall

[Michael Nolan]

The documentation for pg_dump says that dump files are created in a
consistent state.

Is that true across multiple tables in the same pg_dump command?
(Obviously it would not be true if I dumped tables using separate pg_dump
commands.  But if I put the database into a backup state using
'pg_start_backup', would separately executed pg_dump commands be in a
consistent state across the set of dump files?)

The documentation for pg_dumpall does not say that its dump file is in a
consistent state (eg, across all tables), but it does say that it uses
pg_dump to dump clusters.  So, how consistent are the tables in pg_dumpall
files?
--
Mike Nolan

Re: [GENERAL] Consistent state for pg_dump and pg_dumpall

[David G. Johnston]

Yes.  The entire dump is performed within a single transaction.

On Wed, May 20, 2015 at 9:24 AM, Michael Nolan  wrote:

> The documentation for pg_dump says that dump files are created in a
> consistent state.
>
> Is that true across multiple tables in the same pg_dump command?
> (Obviously it would not be true if I dumped tables using separate pg_dump
> commands.
>

​
​
​
Yes.  The entire dump is performed within a single transaction.​

But if I put the database into a backup state using 'pg_start_backup',
> would separately executed pg_dump commands be in a consistent state across
> the set of dump files?)
>
>
​pg_start_backup and pg_dump are not designed to work together.​  Namely,
pg_start_backup is mostly concerned with making sure future writes are
accounted for in the final backup while pg_dump says to ignore everything
that happens after the command begins.

The documentation for pg_dumpall does not say that its dump file is in a
> consistent state (eg, across all tables), but it does say that it uses
> pg_dump to dump clusters.  So, how consistent are the tables in pg_dumpall
> files?
>

Each database is internally consistent.  There is no guarantee that
databases and globals are consistent with each other (though those are
typically seldom changed) but different databases will to represent the
same point in time vis-a-vis each other.



You might want to describe what you are trying to do here.

David J.

Re: [GENERAL] Consistent state for pg_dump and pg_dumpall

[Michael Nolan]

On Wed, May 20, 2015 at 12:40 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> Yes.  The entire dump is performed within a single transaction.
>
> On Wed, May 20, 2015 at 9:24 AM, Michael Nolan  wrote:
>
>> The documentation for pg_dump says that dump files are created in a
>> consistent state.
>>
>> Is that true across multiple tables in the same pg_dump command?
>> (Obviously it would not be true if I dumped tables using separate pg_dump
>> commands.
>>
>
> ​
> ​
> ​
> Yes.  The entire dump is performed within a single transaction.​
>
> But if I put the database into a backup state using 'pg_start_backup',
>> would separately executed pg_dump commands be in a consistent state across
>> the set of dump files?)
>>
>>
> ​pg_start_backup and pg_dump are not designed to work together.​  Namely,
> pg_start_backup is mostly concerned with making sure future writes are
> accounted for in the final backup while pg_dump says to ignore everything
> that happens after the command begins.
>
> The documentation for pg_dumpall does not say that its dump file is in a
>> consistent state (eg, across all tables), but it does say that it uses
>> pg_dump to dump clusters.  So, how consistent are the tables in pg_dumpall
>> files?
>>
>
> Each database is internally consistent.  There is no guarantee that
> databases and globals are consistent with each other (though those are
> typically seldom changed) but different databases will to represent the
> same point in time vis-a-vis each other.
>
>
>
> You might want to describe what you are trying to do here.
>
> David J.
>

I'm getting ready for a security audit and I want to make sure I have the
database backup procedures properly documented, including what the
limitations are on each type of backup .  We us a combination of low level
backups with log shipping, dumping of key individual tables, dumping of
entire databases and dumping the entire system (pg_dumpall.)  Hardware for
setting up a slave server may be in a future budget, though I hope to be
able to test having a slave server in the cloud later this year.  (I'm not
sure we have enough network bandwidth for that, hence the test.)

When I moved to a new release of pg (9.3) last December, I stopped all
transaction processing first so that pg_dumpall had no consistency issues.
--
Mike Nolan

[GENERAL] Enum in foreign table: error and correct way to handle.

[Peter Swartz]

I'm creating a foreign table (foo_table) in database_a. foo_table lives in
database_b.foo_table has an enum (bar_type) as one of its columns. Because
this enum is in database_b, the creation of the foreign table fails in
database_a. database_a doesn't understand the column type. Running the
following in database_a

CREATE FOREIGN TABLE foo_table (id integer NOT NULL, bar bar_type) SERVER
database_b

One gets the error:

ERROR: type "bar_type" does not exist

I could just create a copy of bar_type in database_a, but this feels
duplicative and possibly a future cause of inconsistency / trouble. Would
anyone have thoughts on best practices for handling?
Thank you!
Peter

Re: [GENERAL] Consistent state for pg_dump and pg_dumpall

[John R Pierce]

On 5/20/2015 10:44 AM, Michael Nolan wrote:


When I moved to a new release of pg (9.3) last December, I stopped all 
transaction processing first so that pg_dumpall had no consistency 
issues.


the only possible consistency issue would be if you have applications 
doing 2-phase commits to two different databases on the same server, 
otherwise each database is dumped as a single transaction and all data 
elements within that database are point-in-time consistent.


my preferred backup procedure for a whole server dump is to

A) pg_dumpall --globals-only | gzip > ...
B) for each database, do pg_dump -Fc -f $database.Fc.pgdump $database

I do this via the following crontab entry for hte postgres user...

   $ crontab -l
   30 1 * * * /var/lib/pgsql/cronbackup.sh

and this script...

#!/bin/bash
#
/usr/pgsql-9.3/bin/pg_dumpall --globals-only | gzip > 
/home2/backups/pgsql/pgdumpall.globals.`date +\%a`.sql.gz
for i in $(psql -tc "select datname from pg_database where not 
datistemplate"); do \

pg_dump -Fc -f /home2/backups/pgsql/pgdump.$i.$(date +\%a).dump $i
done


--
john r pierce, recycling bits in santa cruz

[GENERAL] About COPY command (and probably file fdw too)

[Stefan Stefanov]

Hi,

I have been using COPY .. FROM a lot these days for reading in tabular data and 
it does a very good job.  Still there is an inconvenience when a (large) text 
file contains more columns than the target table or the columns’ order differs. 
I can imagine three ways round and none is really nice -
- mount the file as a foreign table with all the text file’s columns then 
insert into the target table a select from the foreign table;
- create an intermediate table with all the text file’s columns, copy into it 
from the file then insert into the target table and finally drop the 
intermediate table when no more files are expected;
- remove the unneeded columns from the file with a text editor prior to 
COPY-ing.
I think that this is happening often in real life and therefore have a 
suggestion to add this option “[SKIP] COLUMNS ”  to the WITH 
clause of COPY .. FROM. It may be very useful in file fdw too.
To be able to re-arrange columns’ order would come as a free bonus for users.

Sincerely,
Stefan Stefanov

Re: [GENERAL] About COPY command (and probably file fdw too)

[Nicolas Paris]

2015-05-20 22:16 GMT+02:00 Stefan Stefanov :

>   Hi,
>
> I have been using COPY .. FROM a lot these days for reading in tabular
> data and it does a very good job.  Still there is an inconvenience when a
> (large) text file contains more columns than the target table or the
> columns’ order differs. I can imagine three ways round and none is really
> nice -
> - mount the file as a foreign table with all the text file’s columns then
> insert into the target table a select from the foreign table;
> - create an intermediate table with all the text file’s columns, copy into
> it from the file then insert into the target table and finally drop the
> intermediate table when no more files are expected;
> - remove the unneeded columns from the file with a text editor prior to
> COPY-ing.
> I think that this is happening often in real life and therefore have a
> suggestion to add this option “[SKIP] COLUMNS ”  to the WITH
> clause of COPY .. FROM. It may be very useful in file fdw too.
> To be able to re-arrange columns’ order would come as a free bonus for
> users.
>
> Sincerely,
> Stefan Stefanov
>
>
>

​Hi,

I guess it already does (from documentation):

COPY table_name [ ( column_name [, ...] ) ]
FROM { 'filename' | STDIN }
[ [ WITH ] ( option [, ...] ) ]

Then you can order the column_name as the source file has.​

Re: [GENERAL] pg_xlog Concern

[Venkata Balaji N]

On Wed, May 20, 2015 at 4:12 PM, Sachin Srivastava 
wrote:

> Dear Venkata,
>
> I have not set this parameter "archive_timeout"
>
> I think that is also the reason.
>
> What value I'll assign to this parameter if my *Checkpoint_segments= 200 *and
> *checkpoint_timeout= 5min, *kindly confirm.
>
>
Before we get on to that, can you please let us know what is archive_status
directory saying. Do you see any files there ? if yes, what is there
extension ?
We need to first investigate why pg_xlog is growing, is it because of long
running transactions or archiving is not working as expected.
It is the checkpoint process which cleans up the WAL files from pg_xlog, if
that is not happening

Regards,
Venkata Balaji N

Fujitsu Australia