Re: [GENERAL] no pg_hba.conf entry for replication connection

Yep relaoded But darn typo Finger going too fast I guess Thanks bunch AP > On Aug 16, 2017, at 8:03 PM, Ian Barwick wrote: > > On 08/17/2017 05:26 AM, armand pirvu wrote: >> Hi >> master (172.16.26.7) and slave (172.16.26.4) >> master runs on

[GENERAL] no pg_hba.conf entry for replication connection

Hi master (172.16.26.7) and slave (172.16.26.4) master runs on port 5433 though SELECT pglogical.create_subscription( subscription_name := 'shw_sub', replication_sets := '{shw_set}', provider_dsn := 'host=172.16.26.7 port=5433 dbname=levregdb user=repuser'); ERROR: could not connect to the p

Re: [GENERAL] Where is pg_hba.conf

Normally should reside in the data dir. My case below armandps-MacBook-Air:~ armandp$ ps -fu postgres |grep data 502 29591 1 0 Thu09PM ?? 0:01.63 /Library/PostgreSQL/9.6/bin/postgres -D /Library/PostgreSQL/9.6/data armandps-MacBook-Air:~ armandp$ ls -l /Library/PostgreSQL/9.6/dat

Re: [GENERAL] pglogical sync

I see says the blind man Looked in the code and correct the syntax and ran on subscriber all good > On Aug 10, 2017, at 12:29 PM, armand pirvu wrote: > > Hi folks > > A question about pglogical sync > > I have a setup with a schema named myschema. > All works

[GENERAL] Re: could not find function "pglogical_table_data_filtered" in file "/usr/pgsql-9.5/lib/pglogical.so"

Looks like upgrading from 9.5.2 to 9.5.8 did it I had another box on 9.5.5 and it was broken there too > On Aug 10, 2017, at 2:13 PM, armand pirvu wrote: > > So I ventured in uninstalling the > https://www.2ndquadrant.com/en/resources/pglogical/pglogical-installation-

[GENERAL] could not find function "pglogical_table_data_filtered" in file "/usr/pgsql-9.5/lib/pglogical.so"

So I ventured in uninstalling the https://www.2ndquadrant.com/en/resources/pglogical/pglogical-installation-instructions/ yum install http://packages.2ndquadrant.com/pglogical/yum-repo-rpms/pglogical-rh

Re: [GENERAL] pglogical repo

Updated since last time I installed it Okay Will go move from 1.0.2 to 1.0.3 to stay up to date Thks > On Aug 10, 2017, at 1:21 PM, John R Pierce wrote: > > On 8/10/2017 10:30 AM, armand pirvu wrote: >> Looking at the installatoion steps and the yum repositories

[GENERAL] pglogical repo

Hi folks Looking at the installatoion steps and the yum repositories sudo yum install http://packages.2ndquadrant.com/pglogical/yum-repo-rpms/pglogical-rhel-1.0-2.noarch.rpm [ ] pglogical-rhel-1.0-1.noarch.rpm [ ] pglogical-rhel-1.0-2.noarch.rpm [ ] pglogical-rhel-1.0-3.noarch.rpm

[GENERAL] pglogical sync

Hi folks A question about pglogical sync I have a setup with a schema named myschema. All works well but looking at sync pglogical.alter_subscription_resynchronize_table(subscription_name name, relation regclass) Resynchronize one existing table. WARNING: This function will truncate the table

Re: [GENERAL] hot standby questions

On Wed, Aug 2, 2017 at 8:19 PM, armand pirvu <mailto:armand.pi...@gmail.com>> wrote: > > Hi > > Just trying to put together the hot_standby setup > All docs I read are pointing to use as prefered method to use pg_basebackup > to set the base > So far so good &g

Re: [GENERAL] unexpected pageaddr

Well this reproduces with pg_basebackup Disturbing. I wonder what else if lurking around > On Aug 2, 2017, at 10:34 PM, armand pirvu wrote: > > Hi > > Setting up the hot_standby the only way I could it get the base remote > cluster running was including pg_xlog > Now u

[GENERAL] unexpected pageaddr

Hi Setting up the hot_standby the only way I could it get the base remote cluster running was including pg_xlog Now using rsync as opposed to pg_basebackup seems ok aka psql postgres -c "select pg_start_backup('backup')" rsync -a /var/lib/pgsql/9.5/data postgres@192.168.5.252:/var/lib/pgsql/9.

[GENERAL] hot standby questions

Hi Just trying to put together the hot_standby setup All docs I read are pointing to use as prefered method to use pg_basebackup to set the base So far so good But psql postgres -c "select pg_start_backup('backup')" pg_basebackup -D /var/lib/pgsql/sample -Ft -z -P psql postgres -c "select pg

Re: [GENERAL] upsert and update filtering

That's because the access on this case is done to the existing row using the table's name / alias , and to the rows that we attempt to insert using the excluded Thank you Peter Armand > On Jul 31, 2017, at 4:31 PM, Peter Geoghegan wrote: > > armand pirvu wrote

[GENERAL] upsert and update filtering

Hi create table dimc1 ( col1 integer not null, col2 char(10), primary key (col1) ); create table dimc2 ( col1 integer not null, col2 char(10), primary key (col1) ); testdb=# select * from dimc1 order by 1; col1 |col2 --+ 111 | foo111 112 | foo112 (2 row

Re: [GENERAL] hash join performance question

> On Jul 18, 2017, at 10:30 PM, Tom Lane wrote: > > armand pirvu writes: >> testdb3=# explain analyze SELECT a.company_id FROM csischema.dim_company a, >> woc.dim_company b >> testdb3-# WHERE a.company_id = b.company_id; >>

[GENERAL] hash join performance question

Hi testdb3=# \d csischema.dim_company; Table "csischema.dim_company" Column |Type | Modifiers -+-+--- company_id | integer | not null company_name| character varying

Re: [GENERAL] insert on conflict

> On Jun 27, 2017, at 3:30 PM, Peter Geoghegan wrote: > > On Tue, Jun 27, 2017 at 12:40 PM, armand pirvu wrote: >> so how is it working in fact ? Isn't it working like looping in the >> IVEE.dim_company and for each company_id if the record does

[GENERAL] insert on conflict

Hi Got question birstdb=# \d csischema.dim_company Table "csischema.dim_company" Column |Type | Modifiers -+-+--- company_id | integer | not null company_name| ch

Re: [GENERAL] Strange port in pg_upgrade output

Got it Thank you > On Jun 12, 2017, at 4:16 PM, Adrian Klaver wrote: > > On 06/12/2017 02:07 PM, armand pirvu wrote: >> Hi >> I was doing a test upgrade from 9.5 to 9.6 and the following lines caught my >> eye >> postgres 10967 10911 0 15:59 pts/0

[GENERAL] Strange port in pg_upgrade output

Hi I was doing a test upgrade from 9.5 to 9.6 and the following lines caught my eye postgres 10967 10911 0 15:59 pts/000:00:00 /usr/pgsql-9.6/bin/pg_upgrade -d /var/lib/pgsql/9.5/data -D /var/lib/pgsql/9.6/data -b /usr/pgsql-9.5/bin -B /usr/pgsql-9.6/bin -k -v postgres 11141 1 0 16:00

Re: [GENERAL] Vacuum and state_change

> On Jun 9, 2017, at 5:42 PM, Adrian Klaver wrote: > > On 06/09/2017 02:26 PM, armand pirvu wrote: >>> On Jun 9, 2017, at 4:20 PM, Adrian Klaver wrote: >>> >>> On 06/09/2017 02:01 PM, armand pirvu wrote: >>>>> On Jun 9, 2017, at 3:52 PM, Adri

Re: [GENERAL] Vacuum and state_change

> On Jun 9, 2017, at 4:20 PM, Adrian Klaver wrote: > > On 06/09/2017 02:01 PM, armand pirvu wrote: >>> On Jun 9, 2017, at 3:52 PM, Adrian Klaver wrote: >>> >>> On 06/09/2017 01:31 PM, armand pirvu wrote: >>> >>>>> > > >

Re: [GENERAL] Vacuum and state_change

> On Jun 9, 2017, at 3:52 PM, Adrian Klaver wrote: > > On 06/09/2017 01:31 PM, armand pirvu wrote: > >>> >>> Are these large tables? > > >> I would say yes >> select count(*) from csischema.tf_purchased_badge; >> 9380749 >> sele

Re: [GENERAL] Vacuum and state_change

> On Jun 9, 2017, at 11:23 AM, Adrian Klaver wrote: > > On 06/09/2017 09:13 AM, armand pirvu wrote: >>> On Jun 9, 2017, at 11:01 AM, Adrian Klaver >> <mailto:adrian.kla...@aklaver.com>> wrote: >>> >>> On 06/09/2017 08:45 AM, armand pirvu wro

Re: [GENERAL] Vacuum and state_change

> On Jun 9, 2017, at 11:01 AM, Adrian Klaver wrote: > > On 06/09/2017 08:45 AM, armand pirvu wrote: >> Hi >> Had a couple of processes blocking the vacuum so I terminated them using >> select pg_terminate_backend(pid); >> Running the following >> select d

[GENERAL] Vacuum and state_change

Hi Had a couple of processes blocking the vacuum so I terminated them using select pg_terminate_backend(pid); Running the following select distinct pid, backend_start, query_start, state_change, state, query from pg_catalog.pg_stat_activity order by 1; pid | backend_start |

Re: [GENERAL] jsonb case insensitive search

I apologize before hand replying again on my own reply . I know it is frowned upon . My inline comments. > On Jun 1, 2017, at 2:05 PM, armand pirvu wrote: > > Thank you Karl and David > > Ideally as far as I can tell the index would need to be show_id, file_id, > lo

Re: [GENERAL] jsonb case insensitive search

Thank you Karl and David Ideally as far as I can tell the index would need to be show_id, file_id, lower(…) The question is if this is possible ? Thanks Armand > On Jun 1, 2017, at 12:24 PM, Karl Czajkowski wrote: > > On May 31, armand pirvu modulated: > >> The id

Re: [GENERAL] jsonb case insensitive search

> On May 31, 2017, at 2:32 PM, David G. Johnston > wrote: > > On Wed, May 31, 2017 at 12:18 PM, armand pirvu <mailto:armand.pi...@gmail.com>> wrote: > > For the example mentioned > > SELECT * > FROM cfg_files_data > WHERE cfg_files_data.show_id =

[GENERAL] jsonb case insensitive search

Hi The goal would be to be able to search case insensitive by any key:value combined with some other columns like outlined below, but initially would be Company:CompuTestSystems A sample would be SELECT * FROM cfg_files_data WHERE cfg_files_data.show_id = 32 AND cfg_files_data.file_id =

Re: [GENERAL] column names and dollar sign

Thanks Tom Armand On May 17, 2017, at 4:14 PM, Tom Lane wrote: > "Armand Pirvu (home)" writes: >> Ran into the following statement > >> CREATE TABLE test( >> Date$ date, >> Month_Number$ int, >> Month$ varchar(10), >>

Re: [GENERAL] column names and dollar sign

Thank you Steve — Armand On May 17, 2017, at 4:10 PM, Steve Atkins wrote: > >> On May 17, 2017, at 2:02 PM, Armand Pirvu (home) >> wrote: >> >> Hi >> >> Ran into the following statement >> >> CREATE TABLE test( >> Date$ d

[GENERAL] column names and dollar sign

Hi Ran into the following statement CREATE TABLE test( Date$ date, Month_Number$ int, Month$ varchar(10), Year$ int ); While it does execute, I wonder if the $ has any special meaning ? Can anyone shed some light please ? Thanks Armand -- Sent via pgsql-

[GENERAL] pglogical and slony

Thanks to Adrian I got pointed in the direction of enabling triggers to use with pglogical In other words, whatever comes down the pglogical in terms of inserts/updates/deletes, I can track those in some audit table or do something else That got me thinking why not putting at the end of pglogi

Re: [GENERAL] views and fdw usage and performance

On May 9, 2017, at 7:11 PM, Adrian Klaver wrote: > On 05/09/2017 05:02 PM, armand pirvu wrote: >> Well >> Jt1 is prod and jt2 is dev > > You are talking schemas, not databases, correct? > > Correct >> Before someone pushes to prod it does work in dev. The

Re: [GENERAL] views and fdw usage and performance

users doing something in each schema. So my questions still remain Sent from my iPhone > On May 9, 2017, at 6:52 PM, Adrian Klaver wrote: > >> On 05/09/2017 02:36 PM, Armand Pirvu (home) wrote: >> Hi >> I have two schemas jt1, and jt2 in the same db >> In both I h

[GENERAL] views and fdw usage and performance

Hi I have two schemas jt1, and jt2 in the same db In both I have the same table tbl3 The idea is to keep in sync jt1.tbl3 from jt2.tbl3 each time I have an insert/update/delete on jt2.tbl3 So I was thinking about the following cases to avoid replication 1) in jt2 rather than have the tbl3 tab

Re: [GENERAL] data transformation and replication

9.5 both But the enable always trigger I missed that Once that set it runs Thank you for your help Armand On May 9, 2017, at 8:26 AM, Adrian Klaver wrote: > On 05/08/2017 08:31 PM, Armand Pirvu (home) wrote: >> My bad >> db1 I have two tables t1 and t2 (or more) >>

Re: [GENERAL] data transformation and replication

. Any pointers ? Thanks Armand On May 8, 2017, at 4:49 PM, Adrian Klaver wrote: > On 05/08/2017 12:46 PM, Armand Pirvu (home) wrote: >> Hi >> >> Here it is a scenario which I am faced with and I am hoping to find a >> pointer/tip/help >> >> db1 is th

[GENERAL] data transformation and replication

Hi Here it is a scenario which I am faced with and I am hoping to find a pointer/tip/help db1 is the OLTP system db2 is the Reporting system The data from db1 needs to get to db2, but the database on those two have tables with different layout/structure and hence data will need to suffer som

Re: [GENERAL] getting column names

Thanks David Worked like a charm and results are correct Armand On Apr 4, 2017, at 5:00 PM, David G. Johnston wrote: > On Tue, Apr 4, 2017 at 2:51 PM, Armand Pirvu (home) > wrote: > > But if I join back to foo1 like below I get > > select a.audit_id, a.table_name

[GENERAL] getting column names

Hi Can somebody please tell me if the below is possible ? I may not see the forest from the trees Thanks Armand levregdb=# select * from foo1; audit_id | table_name --+ 6012 | foo2 6013 | foo2 6014 | foo2 select * from foo2; levregdb=# select * from

Re: [GENERAL] store key name pattern search

Thank you — Armand On Apr 4, 2017, at 10:50 AM, Oleg Bartunov wrote: > > On Tue, Apr 4, 2017 at 11:41 AM, Armand Pirvu (home) > wrote: > Hi > > I have the following case > > > select * from foo; >

[GENERAL] store key name pattern search

Hi I have the following case select * from foo; col1 - "show_id"=>"1", "group_id"=>"32", "group_name"=>"slb", "group_add_by"=>"557651" "

Re: [GENERAL] audit function and old.column

by%’ it would be great In this case I could go around like it I will post another question related to hstore search since although it has a common ground it is different regarding the goal Thanks Armand On Mar 23, 2017, at 6:38 PM, Adrian Klaver wrote: > On 03/23/2017 02:00 PM, Armand

[GENERAL] audit function and old.column

Hello I am using in a project the audit trigger from the wiki One particular aspect is that not all tables that I am after have the same columns . And it is one in particular I am stumbling onto , and it ends i n _add_by. It can be group_add_by, car_add_by and so on. The old value of this colu

Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

I presume you point at me. Keep the record straight. I got mad not for the help but for the high horse attitude. We all have good and bad. No one is perfect and no one deserves this crap Sent from my iPhone > On Dec 14, 2016, at 7:19 PM, Patrick B wrote: > > > > 2016-12-15 14:00 GMT+13:00 D

Re: [GENERAL] variable value in array_to_string

(and never will) from my own mom let alone somebody else My apologies for asking help. Will try not to do it again Thanks and laters On Nov 21, 2016, at 7:12 PM, David G. Johnston wrote: > Please don't top-post - it makes following the thread a lot harder. > > On Mon, Nov 21

Re: [GENERAL] variable value in array_to_string

Played with unnest but not much luck NOTICE: {item_id,show_id} NOTICE: item_id It takes only the first array element in consideration Ug On Nov 21, 2016, at 5:02 PM, Armand Pirvu (home) wrote: > My bad on the back tick. No idea why it turned that way > > OK got t

Re: [GENERAL] variable value in array_to_string

here on I was looking to get say a.item_id , a.show_id and all sort of variations Thanks for help On Nov 21, 2016, at 4:26 PM, David G. Johnston wrote: > On Mon, Nov 21, 2016 at 3:09 PM, Armand Pirvu (home) > wrote: > Hi > > Is there anyway I can pass a variable in the

[GENERAL] variable value in array_to_string

Hi Is there anyway I can pass a variable in the array_to_string function ? CREATE OR REPLACE FUNCTION test1 () RETURNS void AS $$ DECLARE foo text; foo1 text; begin execute 'select ARRAY( SELECT d.COLUMN_NAME::text from information_schema.constraint_table_usage c, information_schema.key_co

Re: [GENERAL] functions - triggers cross schemas

Ok that was my oversight in understanding that the path does not change upon entering a function. I assumed that .it does by simply picking it from the schema2.test1 Thanks a bunch Armand On Oct 13, 2016, at 5:31 PM, David G. Johnston wrote: > On Thu, Oct 13, 2016 at 3:18 PM, Armand Pi

[GENERAL] functions - triggers cross schemas

2 schemas , schema1 and schema2, with same tables , a base table and a tracking one. Search path is set to schema1 by default. insert into schema2.test1 (col2 , col3) values ('foo1','foo2') I get an entry in schema1.test1_hist and not in schema2.test1_hist I understand that the trigger inhe

Re: [GENERAL] executing os commands from a function

, David G. Johnston wrote: > On Thu, Sep 29, 2016 at 2:41 PM, Armand Pirvu (home) > wrote: > I used plsh extension but I had to use two functions and a trigger, see code > below > > ​I don't see any way to not use a trigger given your requirement. And as > written

[GENERAL] executing os commands from a function

All I know this may sound like heresy since it involves executing an OS command from a function , but here goes After an insert in a table, I want to touch a file I.e After insert into table test values (100) I want in a dir to have file 100 I used plsh extension but I had to use two functio

[GENERAL] jsonb search

Hi In my quest of JSONB querying and searching without having to actually cast into a text, I found JSQuery I do admit my JSONB knowledge shortcoming and I am not a developer but a DBA. As such some examples would be greatly appreciated since I tend to understand better I compiled and install