Re: [GENERAL] Postgres 9.1 issues running data directory from VMware shared folder

[Arze, Cesar]

My mistake, the host OS is RHEL 5.9

On August 26, 2014 at 11:09:35 PM, John R Pierce 
(pie...@hogranch.com) wrote:
On 8/26/2014 5:51 PM, Arze, Cesar wrote:
> Thanks for the info, will look into what version of Workstation I am
> running (think I have 9.0) and will see if I can’t get an upgraded
> copy and see if it alleviates the issue.

also, there's several years of patches since RHEL 5.4 was released, I
think its up to 5.9.



--
john r pierce 37N 122W
somewhere on the middle of the left coast



--
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] Postgres 9.1 issues running data directory from VMware shared folder

[John R Pierce]

On 8/26/2014 5:51 PM, Arze, Cesar wrote:
Thanks for the info, will look into what version of Workstation I am 
running (think I have 9.0) and will see if I can’t get an upgraded 
copy and see if it alleviates the issue.


also, there's several years of patches since RHEL 5.4 was released, I 
think its up to 5.9.




--
john r pierce  37N 122W
somewhere on the middle of the left coast



--
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] how to query against nested hstore data type

[Adrian Klaver]

On 08/26/2014 06:50 PM, Huang, Suya wrote:



-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
Sent: Tuesday, August 26, 2014 11:58 PM
To: Huang, Suya; pgsql-general@postgresql.org







I've figured out, have to apply a separate patch to query out of nested 
hstore. http://www.sigaev.ru/git/gitweb.cgi?p=hstore.git;a=summary



Before you go to far with this I would read this blog:

http://obartunov.livejournal.com/177670.html

Basically the patch is a dead end.

--
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] how to query against nested hstore data type

[Huang, Suya]

-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] 
Sent: Tuesday, August 26, 2014 11:58 PM
To: Huang, Suya; pgsql-general@postgresql.org
Subject: Re: [GENERAL] how to query against nested hstore data type

On 08/25/2014 06:05 PM, Huang, Suya wrote:
> -Original Message-
> From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
> Sent: Monday, August 25, 2014 11:42 PM
> To: Huang, Suya; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] how to query against nested hstore data type
>
> On 08/24/2014 07:05 PM, Huang, Suya wrote:
>> Hi,
>>
>> It's my test DB, the version is PostgreSQL 9.4beta2. I'm testing with 
>> nested hstore feature.
>>
>> testdb=# \d+ test
>>
>>Table "public.test"
>>
>> Column |  Type  | Modifiers | Storage  | Stats target | Description
>>
>> ++---+--+--+-
>>
>> id | text   |   | extended |  |
>>
>> stats  | hstore |   | extended |  |
>>
>> testdb=# select * from test;
>>
>> id   |
>>   
>> stats
>>
>> ---+-
>> ---+-
>> ---+-
>> ---+-
>> ---+-
>> ---+-
>> ---+--
>>
>> 2b8ea99d60b30 |
>> "www.ask.com"=>"{visits=>2.11221003268,pages=>4.22442006537,duration=
>> >
>> 197980.836904}",
>> "www.strawberrynet.com"=>"{visits=>2.11221003268,pages=>4.22442006537,duration=>197980.836904}"
>>
>> (1 row)
>
> So how did you get the above values in?
>

The only way I could replicate what you show is:

test=> \d hstore_test
 Table "public.hstore_test"
Column   |  Type   | Modifiers
+-+---
  id | integer |
  hstore_fld | hstore  |



test=> insert into hstore_test values (1, hstore('www.ask.com', 
'{visits=>2.11221003268,pages=>4.22442006537,duration=>197980.836904}'));
INSERT 0 1


test=> select * from hstore_test ;
-[ RECORD 1 
]-
id | 1
hstore_fld | 
"www.ask.com"=>"{visits=>2.11221003268,pages=>4.22442006537,duration=>197980.836904}"


test=> select (each(hstore_fld)).key,(each(hstore_fld)).value from 
hstore_test ;
-[ RECORD 1 ]---
key   | www.ask.com
value | {visits=>2.11221003268,pages=>4.22442006537,duration=>197980.836904}

If that is indeed what you did then you as far as I can tell you do not 
have a nested hstore. Instead you have a key "www.ask.com" and a string 
value "{visits=>2.11221003268,pages=>4.22442006537,duration=>197980.836904}"

Where the => in the string value is decoration not functional. If you 
want nesting then you will need to use the array or JSON types.


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

==
Thanks Adrian,

I've figured out, have to apply a separate patch to query out of nested hstore. 
http://www.sigaev.ru/git/gitweb.cgi?p=hstore.git;a=summary 


-- 
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] Postgres 9.1 issues running data directory from VMware shared folder

[Arze, Cesar]

Thanks for the info, will look into what version of Workstation I am running 
(think I have 9.0) and will see if I can’t get an upgraded copy and see if it 
alleviates the issue.


On August 26, 2014 at 8:36:23 PM, Steve Atkins 
(st...@blighty.com) wrote:

On Aug 26, 2014, at 3:08 PM, Arze, Cesar  wrote:
>
> I probably should be posting to the VMware mailing list with this question 
> but I wanted to see if anyone had any insight or suggestions here. I’ve seen 
> many similar issues but none of the solutions proposed there worked for me.

This might not be what you're seeing, but there was a hideous bug in the shared 
folder (hgfs) driver for linux guest OSes that'll silently corrupt your 
filesystem if it's accessed via more than one filehandle (e.g. multiple opens, 
multiple processes, ...).

If you're using vmware tools bundled with workstation 10.0.1 or fusion 6.0.2, 
you have that bug and cannot safely use hgfs mounts for any files, let alone 
postgresql. (There was a different bug, with similar results, for earlier 
versions too, including at least fusion 5.0.1). VMWare claim it's fixed in the 
tools bundled with 10.0.2 / 6.0.3 (I've not tested it). If you're not running 
the very latest vmware, upgrade to it and install the latest tools (or avoid 
using hgfs).

Cheers,
Steve



--
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] Postgres 9.1 issues running data directory from VMware shared folder

[Arze, Cesar]

So what is the host OS?

Where is the shared directory located, which OS?

What file system is the directory located on?

Host OS is Redhat 5.4

The shared directory is located on the host OS of Redhat 5.4 and is located on 
a local drive of the desktop.

The directory is on an ext3 filesystem.

What is in /mnt/pg_data after the error?

drwx-- 1 postgres postgres  4096 2014-08-26 21:58 base

drwx-- 1 postgres postgres  4096 2014-08-26 21:58 global

drwx-- 1 postgres postgres  4096 2014-08-26 21:58 pg_clog

-rw--- 1 postgres postgres  4476 2014-08-26 21:58 pg_hba.conf

-rw--- 1 postgres postgres  1636 2014-08-26 21:58 pg_ident.conf

drwx-- 1 postgres postgres  4096 2014-08-26 21:58 pg_multixact

drwx-- 1 postgres postgres  4096 2014-08-26 21:58 pg_notify

drwx-- 1 postgres postgres  4096 2014-08-26 21:58 pg_serial

drwx-- 1 postgres postgres  4096 2014-08-26 21:58 pg_stat_tmp

drwx-- 1 postgres postgres  4096 2014-08-26 21:58 pg_subtrans

drwx-- 1 postgres postgres  4096 2014-08-26 21:58 pg_tblspc

drwx-- 1 postgres postgres  4096 2014-08-26 21:58 pg_twophase

-rw--- 1 postgres postgres 4 2014-08-26 21:58 PG_VERSION

drwx-- 1 postgres postgres  4096 2014-08-26 21:58 pg_xlog

-rw--- 1 postgres postgres 19169 2014-08-26 21:58 postgresql.conf


On August 26, 2014 at 7:30:41 PM, Adrian Klaver 
(adrian.kla...@aklaver.com) wrote:

On 08/26/2014 03:08 PM, Arze, Cesar wrote:
> Hi,
>
> I’ve recently encountered an issue running Postgres (both 8.4 and 9.1)
> on a VMware VM running Ubuntu 10.04 LTS as the guest OS with the data
> directory running out of a VMware shared folder. Previously on 8.4 this
> had worked out for me but after upgrading VMware and re-building my VM
> I’ve started to encounter this issue. It seems like the problem occurs
> when I run initdb, I get the following error:

So what is the host OS?

Where is the shared directory located, which OS?

What file system is the directory located on?

>
> # sudo -u postgres /usr/lib/postgresql/9.1/bin/initdb --noclean -D
> /mnt/pg_data/

> creating template1 database in /mnt/pg_data/base/1 ... FATAL: could not
> open file "pg_xlog/00010001" (log file 0, segment 1): No
> such file or directory
>
> child process exited with exit code 1
> initdb: data directory "/mnt/pg_data" not removed at user's request
>

What is in /mnt/pg_data after the error?


> Thanks for any help,
>
> Cesar
>
>
>
>


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

Re: [GENERAL] Postgres 9.1 issues running data directory from VMware shared folder

[Steve Atkins]

On Aug 26, 2014, at 3:08 PM, Arze, Cesar  wrote:
> 
> I probably should be posting to the VMware mailing list with this question 
> but I wanted to see if anyone had any insight or suggestions here. I’ve seen 
> many similar issues but none of the solutions proposed there worked for me.

This might not be what you're seeing, but there was a hideous bug in the shared 
folder (hgfs) driver for linux guest OSes that'll silently corrupt your 
filesystem if it's accessed via more than one filehandle (e.g. multiple opens, 
multiple processes, ...).

If you're using vmware tools bundled with workstation 10.0.1 or fusion 6.0.2, 
you have that bug and cannot safely use hgfs mounts for any files, let alone 
postgresql. (There was a different bug, with similar results, for earlier 
versions too, including at least fusion 5.0.1). VMWare claim it's fixed in the 
tools bundled with 10.0.2 / 6.0.3 (I've not tested it). If you're not running 
the very latest vmware, upgrade to it and install the latest tools (or avoid 
using hgfs).

Cheers,
  Steve



-- 
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] Postgres 9.1 issues running data directory from VMware shared folder

[Adrian Klaver]

On 08/26/2014 03:08 PM, Arze, Cesar wrote:

Hi,

I’ve recently encountered an issue running Postgres (both 8.4 and 9.1)
on a VMware VM running Ubuntu 10.04 LTS as the guest OS with the data
directory running out of a VMware shared folder. Previously on 8.4 this
had worked out for me but after upgrading VMware and re-building my VM
I’ve started to encounter this issue. It seems like the problem occurs
when I run initdb, I get the following error:


So what is the host OS?

Where is the shared directory located, which OS?

What file system is the directory located on?



# sudo -u postgres /usr/lib/postgresql/9.1/bin/initdb --noclean -D
/mnt/pg_data/



creating template1 database in /mnt/pg_data/base/1 ... FATAL:  could not
open file "pg_xlog/00010001" (log file 0, segment 1): No
such file or directory

child process exited with exit code 1
initdb: data directory "/mnt/pg_data" not removed at user's request



What is in /mnt/pg_data after the error?



Thanks for any help,

Cesar







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

[GENERAL] Postgres 9.1 issues running data directory from VMware shared folder

[Arze, Cesar]

Hi,

I’ve recently encountered an issue running Postgres (both 8.4 and 9.1) on a 
VMware VM running Ubuntu 10.04 LTS as the guest OS with the data directory 
running out of a VMware shared folder. Previously on 8.4 this had worked out 
for me but after upgrading VMware and re-building my VM I’ve started to 
encounter this issue. It seems like the problem occurs when I run initdb, I get 
the following error:

# sudo -u postgres /usr/lib/postgresql/9.1/bin/initdb --noclean -D /mnt/pg_data/
Running in noclean mode.  Mistakes will not be cleaned up.
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale en_US.UTF-8.
The default database encoding has accordingly been set to UTF8.
The default text search configuration will be set to "english".

fixing permissions on existing directory /mnt/pg_data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 32MB
creating configuration files ... ok
creating template1 database in /mnt/pg_data/base/1 ... FATAL:  could not open 
file "pg_xlog/00010001" (log file 0, segment 1): No such file 
or directory

child process exited with exit code 1
initdb: data directory "/mnt/pg_data" not removed at user's request

Here is a snippet of an strace around where the error occurs:

write(4, "insert OID = 767 ( lo_import 11 "..., 141) = 141
write(4, "insert OID = 765 ( lo_export 11 "..., 132) = 132
write(4, "insert OID = 766 ( int4inc 11 10"..., 125) = 125
write(4, "insert OID = 768 ( int4larger 11"..., 134) = 134
write(4, "insert OID = 769 ( int4smaller 1"..., 136) = 136
write(4, "insert OID = 770 ( int2larger 11"..., 134) = 134
write(4, "insert OID = 771 ( int2smaller 1"..., 136) = 136
write(4, "insert OID = 774 ( gistgettuple "..., 142) = 142
write(4, "insert OID = 638 ( gistgetbitmap"..., 144) = 144
write(4, "insert OID = 775 ( gistinsert 11"..., 158) = 158
write(4, "insert OID = 777 ( gistbeginscan"..., 151) = 151
write(4, "insert OID = 778 ( gistrescan 11"..., 155) = 155
write(4, "insert OID = 779 ( gistendscan 1"..., 137) = 137
write(4, "insert OID = 780 ( gistmarkpos 1"..., 137) = 137
write(4, "insert OID = 781 ( gistrestrpos "..., 139) = 139
write(4, "insert OID = 782 ( gistbuild 11 "..., 143) = 143
write(4, "insert OID = 326 ( gistbuildempt"..., 143) = 143
write(4, "insert OID = 776 ( gistbulkdelet"..., 158) = 158
write(4, "insert OID = 2561 ( gistvacuumcl"..., 155) = 155
write(4, "insert OID = 772 ( gistcostestim"..., 187) = 187
write(4, "insert OID = 2787 ( gistoptions "..., 139) = 139
write(4, "insert OID = 784 ( tintervaleq 1"..., 138) = 138
write(4, "insert OID = 785 ( tintervalne 1"..., 138) = 138
write(4, "insert OID = 786 ( tintervallt 1"..., 138) = 138
write(4, "insert OID = 787 ( tintervalgt 1"..., 138) = 138
write(4, "insert OID = 788 ( tintervalle 1"..., 138FATAL:  could not open file 
"pg_xlog/00010001" (log file 0, segment 1): No such file or 
directory
) = -1 EPIPE (Broken pipe)

I probably should be posting to the VMware mailing list with this question but 
I wanted to see if anyone had any insight or suggestions here. I’ve seen many 
similar issues but none of the solutions proposed there worked for me.

Thanks for any help,

Cesar

Re: [GENERAL] Bloat check query

[Keith Fiske]

On Tue, Aug 26, 2014 at 5:44 PM, Keith Fiske  wrote:

> So I recently wrote this script to help us in monitoring for table/index
> bloat
>
> https://github.com/keithf4/pg_bloat_check
>
> I based my query off of the one contained in check_postgres, since I
> thought it seemed fairly accurate with what I was expecting to get back.
>
>
> https://github.com/bucardo/check_postgres/blob/master/check_postgres.pl#L3512
>
> A client however, showed me that running the bloat check from heroku
> brings back some additional results. The additional results are actually
> tables we were kind of expecting to see that the check_postgres query does
> not return.
>
> I've tried decoding these queries to understand what they're actually
> doing, but they're both quite a bit over my head. I did notice one thing in
> the check_postgres one:
>
> CASE WHEN v ~ 'mingw32' OR v ~ '64-bit' THEN 8 ELSE 4 END AS ma
>
> Is that a typo? It seems to be looking for the 32bit version of mingw but
> the 64-bit version when compiled elsewhere. The heroku version just sets
> this value to 4 outright (since they know what they're running). I tried
> changing this value to see if it made a difference, but it doesn't seem to.
>
> Anyone that's better able to evaluate which one of these two bloat check
> queries is more accurate able to provide some assistance?
>
> --
> Keith Fiske
> Database Administrator
> OmniTI Computer Consulting, Inc.
> http://www.keithf4.com
>

Helps if I include the query from Heroku

https://github.com/heroku/heroku-pg-extras/blob/master/lib/heroku/command/pg.rb#L438-L504

--
Keith Fiske
Database Administrator
OmniTI Computer Consulting, Inc.
http://www.keithf4.com

[GENERAL] Bloat check query

[Keith Fiske]

So I recently wrote this script to help us in monitoring for table/index
bloat

https://github.com/keithf4/pg_bloat_check

I based my query off of the one contained in check_postgres, since I
thought it seemed fairly accurate with what I was expecting to get back.

https://github.com/bucardo/check_postgres/blob/master/check_postgres.pl#L3512

A client however, showed me that running the bloat check from heroku brings
back some additional results. The additional results are actually tables we
were kind of expecting to see that the check_postgres query does not return.

I've tried decoding these queries to understand what they're actually
doing, but they're both quite a bit over my head. I did notice one thing in
the check_postgres one:

CASE WHEN v ~ 'mingw32' OR v ~ '64-bit' THEN 8 ELSE 4 END AS ma

Is that a typo? It seems to be looking for the 32bit version of mingw but
the 64-bit version when compiled elsewhere. The heroku version just sets
this value to 4 outright (since they know what they're running). I tried
changing this value to see if it made a difference, but it doesn't seem to.

Anyone that's better able to evaluate which one of these two bloat check
queries is more accurate able to provide some assistance?

--
Keith Fiske
Database Administrator
OmniTI Computer Consulting, Inc.
http://www.keithf4.com

Re: [GENERAL] POWA tool

[Ramesh T]

i downloaded But where i need to unzip powa-REL_1_1.zip i'm using putty
tool remote server..


-bash-4.1$ unzip powa-REL_1_1.zip
-bash: unzip: command not found



On Tue, Aug 26, 2014 at 12:55 AM, Raghu Ram  wrote:

>
> On Fri, Aug 22, 2014 at 4:40 PM, Ramesh T 
> wrote:
>
>> How to include pg_stat_statements in postgres.conf.powa is need it.
>>
>> any help..
>>
>>
>>  Below are the steps to Install POWA Tool:
>
> *Step 1:* Download POWA tool from below Website
>
>> https://github.com/dalibo/powa/archive/REL_1_1.zip
>
> *Step 2:* Unpack the Downloaded file
>
>> [root@localhost tmp]# cd /tmp/
>> [root@localhost powa-REL_1_1]# unzip powa-REL_1_1.zip
>
> *Step 3:* Install POWA Software
>
>> [root@localhost powa-REL_1_1]# export PATH=/opt/PostgreSQL/9.3/bin:$PATH
>> [root@localhost powa-REL_1_1]# export
>> LD_LIBRARY_PATH=/opt/PostgreSQL/9.3/lib:$LD_LIBRARY_PATH
>> [root@localhost powa-REL_1_1]# make install
>> gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith
>> -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute
>> -Wformat-security -fno-strict-aliasing -fwrapv -fpic -I. -I.
>> -I/opt/PostgreSQL/9.3/include/postgresql/server
>> -I/opt/PostgreSQL/9.3/include/postgresql/internal -D_GNU_SOURCE
>> -I/opt/local/20140108/fc7f8f12-7861-11e3-aaff-000c29d23b02/include/libxml2
>> -I/usr/local/include/libxml2 -I/usr/local/include  -c -o powa.o powa.c
>> gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith
>> -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute
>> -Wformat-security -fno-strict-aliasing -fwrapv -fpic
>> -L/opt/PostgreSQL/9.3/lib
>> -L/opt/local/20140108/fc7f8f12-7861-11e3-aaff-000c29d23b02/lib
>> -L/usr/local/lib -Wl,--as-needed
>> -Wl,-rpath,'/opt/PostgreSQL/9.3/lib',--enable-new-dtags  -shared -o powa.so
>> powa.o
>> /bin/mkdir -p '/opt/PostgreSQL/9.3/share/postgresql/extension'
>> /bin/mkdir -p '/opt/PostgreSQL/9.3/share/postgresql/extension'
>> /bin/mkdir -p '/opt/PostgreSQL/9.3/lib/postgresql'
>> /bin/mkdir -p '/opt/PostgreSQL/9.3/doc/postgresql/extension'
>> /usr/bin/install -c -m 644 ./powa.control
>> '/opt/PostgreSQL/9.3/share/postgresql/extension/'
>> /usr/bin/install -c -m 644 ./powa--1.0.sql ./powa--1.1.sql
>> '/opt/PostgreSQL/9.3/share/postgresql/extension/'
>> /usr/bin/install -c -m 755  powa.so '/opt/PostgreSQL/9.3/lib/postgresql/'
>> /usr/bin/install -c -m 644 ./README.md
>> '/opt/PostgreSQL/9.3/doc/postgresql/extension/'
>>
> *Step 4:* Create a POWA database & Create requires extensions
>
>> -bash-4.1$ /opt/PostgreSQL/9.3/bin/psql -p 5435
>> Password:
>> psql.bin (9.3.5)
>> Type "help" for help.
>> postgres=# create database powa;
>> CREATE DATABASE
>> postgres=# \c powa
>> You are now connected to database "powa" as user "postgres".
>> powa=# create extension pg_stat_statements ;
>> CREATE EXTENSION
>> powa=# create extension btree_gist ;
>> CREATE EXTENSION
>> powa=# create extension powa;
>> CREATE EXTENSION
>> powa=# \dt
>>   List of relations
>>  Schema |  Name   | Type  |  Owner
>> +-+---+--
>>  public | powa_functions  | table | postgres
>>  public | powa_last_aggregation   | table | postgres
>>  public | powa_last_purge | table | postgres
>>  public | powa_statements | table | postgres
>>  public | powa_statements_history | table | postgres
>>  public | powa_statements_history_current | table | postgres
>> (6 rows)
>
> *Step 5:*  add "power" & "pg_stat_statements" in the
> shared_preload_libraries in postgresql.conf file
>
>> -bash-4.1$ more /opt/PostgreSQL/9.3/data/postgresql.conf |grep
>> shared_preload
>> # Note:  Increasing max_prepared_transactions costs ~600 bytes of shared
>> memory
>> shared_preload_libraries = 'powa,pg_stat_statements' # (change requires
>> restart)
>
>
> -bash-4.1$ /opt/PostgreSQL/9.3/bin/pg_ctl -D /opt/PostgreSQL/9.3/data/
>> start
>> server starting
>> -bash-4.1$ 2014-07-25 03:48:20 IST LOG:  registering background worker
>> "powa"
>> 2014-07-25 03:48:20 IST LOG:  loaded library "powa"
>> 2014-07-25 03:48:20 IST LOG:  loaded library "pg_stat_statements"
>> 2014-07-25 03:48:20 IST LOG:  redirecting log output to logging collector
>> process
>> 2014-07-25 03:48:20 IST HINT:  Future log output will appear in directory
>> "pg_log".
>
> *Step 6:* Install Mojolicious Software
>
>> [root@localhost ui]# pwd
>> /tmp/powa-REL_1_1/ui
>> [root@localhost ui]# ls -l
>> total 36
>> drwxr-xr-x 4 root root 4096 Aug 19  2014 lib
>> -rw-r--r-- 1 root root  393 Jul 25 04:05 powa.conf
>> -rw-r--r-- 1 root root  393 Aug 19  2014 powa.conf-dist
>> drwxr-xr-x 6 root root 4096 Aug 19  2014 public
>> -rw-r--r-- 1 root root 2121 Aug 19  2014 README.md
>> drwxr-xr-x 2 root root 4096 Aug 19  2014 script
>> drwxr-xr-x 2 root root 4096 Aug 19  2014 t
>> drwxr-xr-x 6 root root 4096 Aug 19  2014 templates
>> -rw-r--r-- 1 root root4 Aug 19  2014 VERSION
>
>
> [root@localhost ui]# cp pow

Re: [GENERAL] indexed range queries on jsonb?

[Larry White]

Thank you Tom,

I made the necessary changes and Explain now shows that the query will use
the index.

Thanks again for your help.




On Tue, Aug 26, 2014 at 10:33 AM, Tom Lane  wrote:

> Larry White  writes:
> > Logically, what I want is to be able to make queries like this:
> > select * from document where ((payload->'intTest')) > 5;
> > With casting, I came up with:
> > select * from document where (((payload->'intTest'))::text)::integer > 5;
> > But this query does not use the index according to Explain
>
> Nope.  You would have to create an index on the casted expression if you
> want to use integer comparisons with the index.  The raw -> expression is
> of type jsonb, which doesn't sort the same as integer.
>
> BTW, you could save a small amount of notation with the ->> operator, ie
> (payload->>'intTest')::integer
>
> regards, tom lane
>

Re: [GENERAL] indexed range queries on jsonb?

[Tom Lane]

Larry White  writes:
> Logically, what I want is to be able to make queries like this:
> select * from document where ((payload->'intTest')) > 5;
> With casting, I came up with:
> select * from document where (((payload->'intTest'))::text)::integer > 5;
> But this query does not use the index according to Explain

Nope.  You would have to create an index on the casted expression if you
want to use integer comparisons with the index.  The raw -> expression is
of type jsonb, which doesn't sort the same as integer.

BTW, you could save a small amount of notation with the ->> operator, ie
(payload->>'intTest')::integer

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] how to query against nested hstore data type

[Adrian Klaver]

On 08/25/2014 06:05 PM, Huang, Suya wrote:

-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
Sent: Monday, August 25, 2014 11:42 PM
To: Huang, Suya; pgsql-general@postgresql.org
Subject: Re: [GENERAL] how to query against nested hstore data type

On 08/24/2014 07:05 PM, Huang, Suya wrote:

Hi,

It's my test DB, the version is PostgreSQL 9.4beta2. I'm testing with
nested hstore feature.

testdb=# \d+ test

   Table "public.test"

Column |  Type  | Modifiers | Storage  | Stats target | Description

++---+--+--+-

id | text   |   | extended |  |

stats  | hstore |   | extended |  |

testdb=# select * from test;

id   |
  stats

---+--
---+--
---+--
---+--

2b8ea99d60b30 |
"www.ask.com"=>"{visits=>2.11221003268,pages=>4.22442006537,duration=>
197980.836904}",
"www.strawberrynet.com"=>"{visits=>2.11221003268,pages=>4.22442006537,duration=>197980.836904}"

(1 row)


So how did you get the above values in?



The only way I could replicate what you show is:

test=> \d hstore_test
Table "public.hstore_test"
   Column   |  Type   | Modifiers
+-+---
 id | integer |
 hstore_fld | hstore  |



test=> insert into hstore_test values (1, hstore('www.ask.com', 
'{visits=>2.11221003268,pages=>4.22442006537,duration=>197980.836904}'));

INSERT 0 1


test=> select * from hstore_test ;
-[ RECORD 1 
]-

id | 1
hstore_fld | 
"www.ask.com"=>"{visits=>2.11221003268,pages=>4.22442006537,duration=>197980.836904}"



test=> select (each(hstore_fld)).key,(each(hstore_fld)).value from 
hstore_test ;

-[ RECORD 1 ]---
key   | www.ask.com
value | {visits=>2.11221003268,pages=>4.22442006537,duration=>197980.836904}

If that is indeed what you did then you as far as I can tell you do not 
have a nested hstore. Instead you have a key "www.ask.com" and a string 
value "{visits=>2.11221003268,pages=>4.22442006537,duration=>197980.836904}"


Where the => in the string value is decoration not functional. If you 
want nesting then you will need to use the array or JSON types.



--
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] PostgreSQL DB Replication

[Abdul Sayeed]

Hi Rajesh,

I don't know what error you are getting. Though you can use below
workaround to login and change the password.

Step 1: Open pg_hba.conf file and modify md5 to trust

Step 2: reload the cluster (/opt/PostgreSQL/9.2/bin/pg_ctl -D ../data/
reload)

Step 3: Connect to the database, it should not ask for password:

bash-4.1$ ./psql -p 5435 postgres
psql.bin (9.2.7)
Type "help" for help.

postgres=# \password postgres;
Enter new password:
Enter it again:
postgres=# \q
bash-4.1$ ./psql -p 5435 postgres
Password:
psql.bin (9.2.7)
Type "help" for help.

postgres=#

If the above workaround does not work, please share what error you are
getting.

Hope this helps….



Thanks & Regards,
Abdul Sayeed
PostgreSQL DBA
Postgres Professional Certified
EnterpriseDB Corp
Skype: abdul.sayeed24



On Tue, Aug 26, 2014 at 11:29 AM, K P Manoj  wrote:

> Hi Rajesh,
>
> Parameter  hot_standby is on on slave server ?. You are trying to change
> postgres password on master or slave ?.
>
> Regards
> Manoj K P
> 8050972028
>
>
> On Tue, Aug 26, 2014 at 10:15 AM, sivananda reddy 
> wrote:
>
>> Hi Rajesh
>>
>> Please share the below information:
>>
>> 1)postgresql.conf file (Both master and slave)
>> 2) database server logs
>> 3)recovery.conf file
>> 4)pg_hba.conf file
>>
>> With Regards,
>> Sivananda Reddy
>> 07385114008
>>
>>
>> On Tue, Aug 26, 2014 at 9:34 AM, wd  wrote:
>>
>>> What's the output when you try to login? Have your slave configured as a
>>> standby server ?
>>>
>>>
>>> On Mon, Aug 25, 2014 at 12:46 PM, Rajesh K  wrote:
>>>
 Dear Sir,

 I have planned to configure  PostgreSQL 9.2.4 database Master and Slave
 replication on Redhat Linux Server 6.As per the documentation i have done
 the settings on both Master and Slave.I choose 7 simple step binary
 replication method as per the PostgreSQL website.After DB Synchronization
 command I  start the slave and master as per the documentation.After that I
 am  not able to login as postgres user on slave and not able to change the
 password of the user postgres.Kindly help me to done the same .

 Thanks & Regards
 Rajesh.K/Pramod
 Developer
 Cochin
 8129823272





>>>
>>
>

Re: [GENERAL] indexed range queries on jsonb?

[Christian Ramseyer]

On 8/26/14 3:30 PM, Larry White wrote:


> 
> Logically, what I want is to be able to make queries like this:
> 
> select * from document where ((payload->'intTest'))> 5;
> 
> With casting, I came up with:
> 
> select * from document where (((payload->'intTest'))::text)::integer
> > 5;
> 
> But this query does not use the index according to Explain
> 

I have not tested this with a jsonb property but you should be able to
define an index over payload->'intTest'::integer using an expression
index, described here:

http://www.postgresql.org/docs/9.1/static/indexes-expressional.html

Christian


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

[GENERAL] indexed range queries on jsonb?

[Larry White]

Hi,

I'm trying to find a way to do a range query on json such that it will use
an index. This seems possible given that jsonb supports btrees and
expression indices.

For example I have:

create index t1 on document using btree ((payload->'intTest'));


where: payload is a jsonb column and intTest is a json key whose value is
always an int. Based on the documentation examples, I created an index like
this:

create index t1 on document using btree ((payload->'intTest'));


Logically, what I want is to be able to make queries like this:

select * from document where ((payload->'intTest'))> 5;

With casting, I came up with:

select * from document where (((payload->'intTest'))::text)::integer > 5;

But this query does not use the index according to Explain

"Seq Scan on public.document  (cost=0.00..1868.33 rows=5764 width=619)
(actual time=286.228..1706.638 rows=1974 loops=1)"
"  Output: owner, document_type, guid, schema_version, payload,
last_update, payload_class, instance_version, acl_read, deleted, fts_text"
"  Filter: document.payload -> 'intTest'::text))::text)::integer > 5)"
"  Rows Removed by Filter: 15319"
"  Buffers: shared hit=5420 read=29085"
"Planning time: 0.108 ms"
"Execution time: 1706.941 ms"

Any help at all would be appreciated.

Thanks.

Re: [GENERAL] POWA tool

[John McKown]

On Tue, Aug 26, 2014 at 5:43 AM, Raghu Ram  wrote:

> On Tue, Aug 26, 2014 at 3:11 PM, Ramesh T 
> wrote:
>
>> i downloaded But where i need to unzip powa-REL_1_1.zip i'm using putty
>> tool remote server..
>>
>>
>> -bash-4.1$ unzip powa-REL_1_1.zip
>> -bash: unzip: command not found
>>
>>
> You need to Install "unzip" utility. Once you installed "unzip"
> utility,you can extract in "/tmp" directory.
>

As an alternative to the "unzip" command, the "jar" command from the Java
SDK can be used. Java "jar" files are simply zip files. And the jar command
can list, create, and extract from jar files.

jar xf powa-REL_1_1.zip # unzip

===

jar cf new.file.zip set.txt of.txt subdir/files.bin # create new

jar lf new.file.zip # list contents




-- 
There is nothing more pleasant than traveling and meeting new people!
Genghis Khan

Maranatha! <><
John McKown

Re: [GENERAL] Using fmgr_hook

[Sameer Thakur]

Hello,
> Try like this:
> - connect with psql
> - call your custom_fmgr_hook
> - attach to the backend with gdb
> - set the breakpoint
> - call custom_fmgr_hook again

I tried to check in a different way, by just using log statements to
see if customs* hook functions ever get executed. They were not. So it
was not a breakpoint or debugger issue.
Then instead of using auto_explain, we created a user defined sysdate
function, implemented in C and added the fmgr hook code in there.
Now we see custom hook functions being called. So looks like it was
not a debugger issue, but fmgr hook work within user defined functions

Thank you,
Sameer


-- 
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] POWA tool

[Raghu Ram]

On Tue, Aug 26, 2014 at 3:11 PM, Ramesh T 
wrote:

> i downloaded But where i need to unzip powa-REL_1_1.zip i'm using putty
> tool remote server..
>
>
> -bash-4.1$ unzip powa-REL_1_1.zip
> -bash: unzip: command not found
>
>
You need to Install "unzip" utility. Once you installed "unzip" utility,you
can extract in "/tmp" directory.

for Debian and Ubuntu:
   1
 apt-get install unzip

for Red Hat Linux/Fedora/CentOS users:
   1
 yum install unzip

Re: [GENERAL] Restart replicated slave procedure

[Soni M]

On Sat, Aug 23, 2014 at 2:18 AM, Joseph Kregloh 
wrote:

>
>
> On Fri, Aug 22, 2014 at 2:21 PM, Jerry Sievers 
> wrote:
>
>> Joseph Kregloh  writes:
>>
>> > Hi,
>> >
>> > Currently I am doing asynchronous replication from master to
>> > slave. Now if I restart the slave it will fall out of sync with the
>> > master. Is there a correct procedure or set of steps to avoid this? I
>> > am looking for best practices or suggestions. Whenever my slave fell
>> > out of sync I would either issue a new pg_base_backup() or set the
>> > master to pg_start_backup() do an rsync and stop using
>> > pg_stop_backup(). If there is a way to avoid any of that, for example
>> > pause replication to hold all the wal files until the replicated slave
>> > comes back and then release them once the replicated slave is up.
>> >
>> > I apologize if this question has already been asked. I did some
>> searching beforehand.
>>
>> See the manual and read up on the 2 GUCs; archive_command and
>> wal_keep_segments.
>>
>>
> Thanks, i'll read into this some more.
>
>
>> wal_keep_segments lets you hold a configurable number of WAL segments
>> back and buy some more time till you have to resync the stand bys.
>>
>> Setting archive_command to '' or something like '/bin/false' lets you
>> delay archiving forever till you change them back again and/or fill
>> whatever file system pg_xlog writes to :-)
>>
>>
> So disabling the archive_command by setting it to and empty string or
> /bin/false will effectively pause log shipping? When I re-enable the
> archive command will it continue where it left of when the archive_command
> was "disabled"?
>
>
AFAIK, disabling archive_command will result on accumulated wal files on
xlog dir on master. And when You re-enable the archive_command, it will
continue where it left of. It has the status of last archived wal files.
Check on PGDATA/pg_xlog/archive_status/


>
>
>> >
>> > Thanks,
>> > -Joseph Kregloh
>> >
>>
>> --
>> Jerry Sievers
>> Postgres DBA/Development Consulting
>> e: postgres.consult...@comcast.net
>> p: 312.241.7800
>>
>
>


-- 
Regards,

Soni Maula Harriz

Re: [GENERAL] Using fmgr_hook

[Albe Laurenz]

Sameer Thakur wrote:
>> My experience is that you cannot set breakpoints before the library
>> is loaded, so you first have to call a function in the library, then
>> you interrupt and set the breakpoint.

> I tried to do the following
> 1. Execute Postgres (now auto_explain is loaded)
> 2. Start a psql session and attach gdb to forked Postmaster process
> 3. Now set break point in custom_fmgr_hook
> 4. Execute select * from now();
> 
> Still the breakpoint gets skipped.

But gdb tells you that it cannot set the breakpoint correctly
when you try to, right?

Try like this:
- connect with psql
- call your custom_fmgr_hook
- attach to the backend with gdb
- set the breakpoint
- call custom_fmgr_hook again

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