Re: [GENERAL] Migration Query

[Venkata B Nagothi]

On Tue, May 9, 2017 at 1:13 AM, PAWAN SHARMA 
wrote:

> Hi All,
>
> Does one any having list of bottlenecks and workarounds while migrating
> data
>  from Oracle to Postgresql. like what are thing which we can migrate from
> Oracle database to Postgresql and what we can't?
>

In general you must be able to migrate pretty much any data type to
PostgreSQL. It would be good to understand the data-types specifically used
in the to-be-migrated Oracle database and how that would effect Application
functionality/performance after migrating to PostgreSQL.

If you can provide details on the specific data-types used by Oracle, then,
the required information can be provided. As per my experience as far as
data types are concerned, issues generally crop-up around when migrating
large object types. Having said that, PostgreSQL is rich in supporting side
range of data-types.

Which is the best tool for migration from Oracle to Postgresql?
>

As already suggested, ora2pg is the best open-source tool.

Regards,

Venkata B N
Database Consultant

Re: [GENERAL] Python versus Other Languages using PostgreSQL

[Neil Anderson]

I'm also interested to know which frameworks you're referring to?

Regarding Node and Mongo I imagine that they are well suited because
Mongo stores JSON documents and Node, being Javascript, has first
class support for JSON. Python and PostgreSQL's relationship might be
more of a principled one? Pythonic being a characteristic not
dissimilar to stable, reliable and of high quality.

On 8 May 2017 at 21:22, Adrian Klaver  wrote:
> On 05/08/2017 05:45 PM, Paul Hughes wrote:
>>
>> Thank you for the links. I'm glad there are other languages that are
>> working with PostgreSQL. My question still remains though - why is it that
>> all the largest web platforms that have used PostgreSQL *specifically*
>> choose Python as their back-end language? Why are Postgres and Python so
>> married, in the same way that Node.js is largely married to MondogDB?
>
>
> I think you are going to have name frameworks, because AFAIK Drupal uses
> PHP, Ruby on Rails uses Rails and so on:
>
> https://en.wikipedia.org/wiki/Comparison_of_web_frameworks
>
> As to why Postgres and Python seem to be attached I would say that is
> because about the time people started looking for an alternative to
> MySQL/PHP, Python reached the state and breadth of distribution to became
> the language to pair with Postgres.
>
>>
>>
>> On Mon, May 8, 2017 at 3:50 PM, Adrian Klaver > > wrote:
>>
>> On 05/08/2017 02:26 PM, Paul Hughes wrote:
>>
>> Hello,
>>
>> I noticed that most of the largest web platforms that use
>> PostgreSQL as
>> their primary database, also use Python as their primary back-end
>> language. Yet, according to every benchmark I could find over
>> the last
>> couple of years, back-end languages like PHP, HHVM, and Node.JS
>> outperform Python by 2x to 8x!
>>
>>
>> Postgres does not really care what you use to pull data from it.
>> There are many libraries across many languages that you can use.
>>
>>
>>
>>
>>
>> 
>> Virus-free. www.avast.com
>> 
>>
>> <#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
>
>
>
> --
> 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



-- 
Neil Anderson
n...@postgrescompare.com
https://www.postgrescompare.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] data transformation and replication

[Armand Pirvu (home)]

My bad


db1 I have two tables t1 and t2 (or more)
db2 has one table t3 for example which can get data aggregated from one or more 
multiple tables from the above set . I can updates/inserts/deletes in db1.t1 
and/or db1.t2 which combined may mean related data in db.t3 would need to be 
inserted/deleted/updated. Think of it like ETL processing if you will. This is 
what I mean by data massaging/transformation


db1 and db2 are two different servers.


So I was initially thinking that I can have on db2 the same set of tables from 
db1, replication being done using pglogical. Once data gets to db2 t1 and t2, I 
can have on db2 a set of functions/triggers which can transform the data and as 
such do the relevant inserts/updates/delete from db2.t3

Apparently though that is not possible unless I am missing something

I reached that conclusion by using a trigger and a function like the auditing 
one to track insers/updates/deletes in an audit table

Having these said I was thinking

(a) - 
On db1 I will have the t3 table as is on dsb2. All data transformation goes 
into db1.t3 which on it's turn will replicate to db2.t3 using pglogical

(b) -
On db2 I will have the t1 t2 as they are on db1. Those are replicated using 
Slony/Bucardo. Once data lands on db2.t1 and db2.t2 another set of 
triggers/functions responsible for data transformation will do the 
inserts/deletes/updates in db2.t3

I wold much prefer pglogical approach as stated in the what I see as a failed 
case 


If the only options is Slony/Bucardo , so be it. but that begs the following 
questions
- which one has the smallest overhead ?
- which one is the easiest to manage ?
- which one is the most reliable ?
- I recall data transformation can be used in Bucardo but did not see any 
examples on that. 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 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 
>> some transformation in between in real time
>> 
>> I was looking at something like
>> 
>> db1 -> db2 replicates the same set of tables and with the same structures 
>> using pglogical for example
>> db2.tbl1 -> db2.tbl2 data gets massages/transformed based on what replicates 
>> from db1.tbl1 using triggers and functions
>> 
>> 
>> Other than that I reckon db1 -> db2 would be trigger based using something 
>> like slonik maybe (?) and data massage/transformation gets moved from db2 to 
>> db1 machine and then db1.tbl2 -> db2.tbl2 using pglogical
> 
> I was following you until the last part, "... moved from db2 to db1 machine 
> and then db1.tbl2 -> db2.tbl2 ..."
> 
> Is this correct?
> 
> If so why db1 --> db2 --> db1 --> db2?
> 
> A complete answer is going to depend on at least an outline of what you mean 
> by massage/transform?
> 
>> 
>> 
>> Is this doable ? If so any pointers as to where to look about it ?
>> 
>> 
>> Many thanks
>> Armand
>> 
>> 
>> 
>> 
> 
> 
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com

Re: [GENERAL] Python versus Other Languages using PostgreSQL

[Paul Hughes]

Thank you for the links. I'm glad there are other languages that are
working with PostgreSQL. My question still remains though - why is it that
all the largest web platforms that have used PostgreSQL *specifically*
choose Python as their back-end language? Why are Postgres and Python so
married, in the same way that Node.js is largely married to MondogDB?


On Mon, May 8, 2017 at 3:50 PM, Adrian Klaver 
wrote:

> On 05/08/2017 02:26 PM, Paul Hughes wrote:
>
>> Hello,
>>
>> I noticed that most of the largest web platforms that use PostgreSQL as
>> their primary database, also use Python as their primary back-end
>> language. Yet, according to every benchmark I could find over the last
>> couple of years, back-end languages like PHP, HHVM, and Node.JS
>> outperform Python by 2x to 8x!
>>
>
> Postgres does not really care what you use to pull data from it. There are
> many libraries across many languages that you can use.





Virus-free.
www.avast.com

<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

[GENERAL] slow query on multiple table join

[tao tony]

hi guys，

I met a query performance issue in postgresql 9.6.2 with multiple tables 
joined.

there were 2 slow queries,and the reasons were the same:the optimizer 
generate a bad explain which using nest loop.

attached is the query and its explain.all tables  are small and the 
indexes were only created on primary keys .

in query 1 I noticed  the explain forecast the nest loop anti join 
return 1 row as below,it was the result of  (f join p) join pt:

  ->  Nested Loop  (cost=1.95..14838.66 rows=1 width=163)
Join Filter: ((f.shop)::text = (s.uuid)::text)
->  Nested Loop Anti Join  (cost=1.95..14743.60 rows=1 
width=111)
  ->  Hash Join  (cost=1.53..12067.46 rows=4751 
width=115)
Hash Cond: ((p.shop)::text = (f.shop)::text)
->  Seq Scan on shopsku p 
(cost=0.00..11483.96 rows=106892 width=106)
  Filter: ((state)::text = 'normal'::text)
->  Hash  (cost=1.29..1.29 rows=19 width=9)
  ->  Seq Scan on shopfranchise f  
(cost=0.00..1.29 rows=19 width=9)
Filter: (enabled = 1)
  ->  Index Only Scan using platformsku_pkey on 
platformsku pt  (cost=0.42..0.55 rows=1 width=36)
Index Cond: (uuid = (p.platformsku)::text)
->  Seq Scan on shop s  (cost=0.00..75.58 rows=1558 
width=61)
  ->  Hash  (cost=2823.76..2823.76 rows=43376 width=46)
->  Seq Scan on merchantsku m (cost=0.00..2823.76 
rows=43376 width=46)

while in analyze explain,it actually returns 57458 row.so higher level 
nest loop would get 57458*1558 rows,this cause this query runs for more 
than 40 seconds.

  ->  Nested Loop  (cost=1.95..14838.66 rows=1 width=163) 
(actual time=0.817..43150.583 rows=57458 loops=1)
Join Filter: ((f.shop)::text = (s.uuid)::text)
Rows Removed by Join Filter: 89462106
->  Nested Loop Anti Join  (cost=1.95..14743.60 rows=1 
width=111) (actual time=0.060..408.092 rows=57458 loops=1)
  ->  Hash Join  (cost=1.53..12067.46 rows=4751 
width=115) (actual time=0.046..174.523 rows=57485 loops=1)
Hash Cond: ((p.shop)::text = (f.shop)::text)
->  Seq Scan on shopsku p 
(cost=0.00..11483.96 rows=106892 width=106) (actual time=0.008..107.416 
rows=106580 loops=1)
  Filter: ((state)::text = 'normal'::text)
  Rows Removed by Filter: 429
->  Hash  (cost=1.29..1.29 rows=19 width=9) 
(actual time=0.026..0.026 rows=20 loops=1)
  Buckets: 1024  Batches: 1  Memory 
Usage: 9kB
  ->  Seq Scan on shopfranchise f  
(cost=0.00..1.29 rows=19 width=9) (actual time=0.006..0.017 rows=20 loops=1)
Filter: (enabled = 1)
Rows Removed by Filter: 4
  ->  Index Only Scan using platformsku_pkey on 
platformsku pt  (cost=0.42..0.55 rows=1 width=36) (actual 
time=0.003..0.003 rows=0 loops=57485)
Index Cond: (uuid = (p.platformsku)::text)
Heap Fetches: 0
->  Seq Scan on shop s  (cost=0.00..75.58 rows=1558 
width=61) (actual time=0.001..0.332 rows=1558 loops=57458)

If I disabled nest loop,ti only use 519 ms.

in query 2 ,the nest loop join also process more than 200m rows,it runs 
almost 2 minutes.After disable nest loop,it use hash join,finished in 
1.5 sec.

 purchase join (shopfranchise f_4 join inventory k) 
join gdname
  ->  Nested Loop Left Join (cost=3972.43..4192.40 
rows=1 width=1074) (actual time=268.989..106066.932 rows=45615 loops=1)
Join Filter: (((k.shop)::text = 
(purchase.shop)::text) AND ((k.shopsku)::text = (purchase.shopsku)::text))
Rows Removed by Join Filter: 208410367

 (shopfranchise f_4 join inventory k) join 
gdname
->  Hash Join  (cost=3972.43..4165.52 rows=1 
width=1112) (actual time=247.088..1754.448 rows=45615 loops=1)
  Hash Cond: (((gdname.shop)::text = 
(k.shop)::text) AND ((gdname.shopsku)::text = (k.shopsku)::text))
  ->  CTE Scan on gdname (cost=0.00..140.42 
rows=7021 width=1246) (actual time=156.543..1563.121 rows=51153 loops=1)
  ->  Hash  (cost=3925.81..3925.81 rows=3108 
width=63) (actual time=90.520..90.520 rows=45622 loops=1)
Buckets: 65536 (originally 4096)  
Batches: 1 (originally 1)  Memory Usage: 4745kB
 shopfranchise f_4 join inventory k
 

Re: [GENERAL] Python versus Other Languages using PostgreSQL

[Adrian Klaver]

On 05/08/2017 05:45 PM, Paul Hughes wrote:
Thank you for the links. I'm glad there are other languages that are 
working with PostgreSQL. My question still remains though - why is it 
that all the largest web platforms that have used PostgreSQL 
*specifically* choose Python as their back-end language? Why are 
Postgres and Python so married, in the same way that Node.js is largely 
married to MondogDB?


I think you are going to have name frameworks, because AFAIK Drupal uses 
PHP, Ruby on Rails uses Rails and so on:


https://en.wikipedia.org/wiki/Comparison_of_web_frameworks

As to why Postgres and Python seem to be attached I would say that is 
because about the time people started looking for an alternative to 
MySQL/PHP, Python reached the state and breadth of distribution to 
became the language to pair with Postgres.





On Mon, May 8, 2017 at 3:50 PM, Adrian Klaver > wrote:


On 05/08/2017 02:26 PM, Paul Hughes wrote:

Hello,

I noticed that most of the largest web platforms that use
PostgreSQL as
their primary database, also use Python as their primary back-end
language. Yet, according to every benchmark I could find over
the last
couple of years, back-end languages like PHP, HHVM, and Node.JS
outperform Python by 2x to 8x!


Postgres does not really care what you use to pull data from it.
There are many libraries across many languages that you can use.




 
	Virus-free. www.avast.com 
 



<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>



--
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] [plnc...@gmail.com: Can I safe my Data?]

[Adrian Klaver]

On 05/08/2017 03:34 PM, Alvaro Herrera wrote:



Hello.. My name is Rian.

I have a problem, my Database attacked by ransomware virus. Some file 
has encrypt. for pg_hba and postgresql.conf i have a backup, but for 
some file like pg_filenode.map i dont have.


this a log when i start the postgres:

2017-05-08 10:08:17 ICT FATAL:  relation mapping file 
"global/pg_filenode.map" contains invalid data


Looks like the PGDATA directory was encrypted. Unless you have a backed 
up dump file that you created using pg_dump or have been archiving WAL 
files you are not going to be able to reclaim the database without 
getting the files unencrypted.



thanks for kindness.

postgresql-2017-05-08_100800.log

2017-05-08 10:08:07 ICT LOG:  database system was interrupted; last 
known up at 2017-05-08 10:05:29 ICT
2017-05-08 10:08:07 ICT LOG:  database system was not properly shut 
down; automatic recovery in progress

2017-05-08 10:08:08 ICT LOG:  record with zero length at 1B1/7F306250
2017-05-08 10:08:08 ICT LOG:  redo is not required
2017-05-08 10:08:13 ICT LOG:  database system is ready to accept connections
2017-05-08 10:08:17 ICT LOG:  autovacuum launcher started
2017-05-08 10:08:17 ICT FATAL:  relation mapping file 
"global/pg_filenode.map" contains invalid data
2017-05-08 10:08:17 ICT LOG:  autovacuum launcher process (PID 816) 
exited with exit code 1

2017-05-08 10:08:17 ICT LOG:  terminating any other active server processes
2017-05-08 10:08:18 ICT LOG:  all server processes terminated; 
reinitializing
2017-05-08 10:08:29 ICT FATAL:  pre-existing shared memory block is 
still in use
2017-05-08 10:08:29 ICT HINT:  Check if there are any old server 
processes still running, and terminate them.





--
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] Python versus Other Languages using PostgreSQL

[Adrian Klaver]

On 05/08/2017 02:26 PM, Paul Hughes wrote:

Hello,

I noticed that most of the largest web platforms that use PostgreSQL as
their primary database, also use Python as their primary back-end
language. Yet, according to every benchmark I could find over the last
couple of years, back-end languages like PHP, HHVM, and Node.JS
outperform Python by 2x to 8x!


Postgres does not really care what you use to pull data from it. There 
are many libraries across many languages that you can use.




So here are my questions:

1) Why do the largest web applications that use PostgreSQL also use
Python, even though Python is significantly slower than it's biggest
competitors?


Because the Python code that does the SELECT * from some_table is not 
going to be the part that really determines performance. Performance is 
going to be dependent on well provisioned the database is and what you 
are using to display the data.




2) Can PostgreSQL just as easily be used with PHP or Node.js? If not,
why not?


See above.



3) Can PostgreSQL be made to work seamlessly to take advantage of the
superior performance of HHVM or Node.js?


A quick search on node.js Postgres found:

https://github.com/brianc/node-postgres

http://mherman.org/blog/2015/02/12/postgresql-and-nodejs/#.WRD1XK3TD4Y

php Postgres:

http://php.net/manual/en/book.pgsql.php

https://www.linkedin.com/pulse/how-make-postgresql-db-connections-php-j-c-thomas-rogers-iii




Thank you in advance!

~Paul


Virus-free. www.avast.com



<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>



--
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] [p...@vivation.com: Python versus Other Languages using PostgreSQL]

[Alvaro Herrera]

--- Begin Message ---
Hello,

I noticed that most of the largest web platforms that use PostgreSQL as
their primary database, also use Python as their primary back-end language.
Yet, according to every benchmark I could find over the last couple of
years, back-end languages like PHP, HHVM, and Node.JS outperform Python by
2x to 8x!

So here are my questions:

1) Why do the largest web applications that use PostgreSQL also use Python,
even though Python is significantly slower than it's biggest competitors?

2) Can PostgreSQL just as easily be used with PHP or Node.js? If not, why
not?

3) Can PostgreSQL be made to work seamlessly to take advantage of the
superior performance of HHVM or Node.js?


Thank you in advance!

~Paul


Virus-free.
www.avast.com

<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
--- End Message ---

[GENERAL] Python versus Other Languages using PostgreSQL

[Paul Hughes]

Hello,

I noticed that most of the largest web platforms that use PostgreSQL as
their primary database, also use Python as their primary back-end language.
Yet, according to every benchmark I could find over the last couple of
years, back-end languages like PHP, HHVM, and Node.JS outperform Python by
2x to 8x!

So here are my questions:

1) Why do the largest web applications that use PostgreSQL also use Python,
even though Python is significantly slower than it's biggest competitors?

2) Can PostgreSQL just as easily be used with PHP or Node.js? If not, why
not?

3) Can PostgreSQL be made to work seamlessly to take advantage of the
superior performance of HHVM or Node.js?


Thank you in advance!

~Paul


Virus-free.
www.avast.com

<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

[GENERAL] [plnc...@gmail.com: Can I safe my Data?]

[Alvaro Herrera]

-- 
Álvaro Herrera Developer, https://www.PostgreSQL.org/
"The problem with the facetime model is not just that it's demoralizing, but
that the people pretending to work interrupt the ones actually working."
   (Paul Graham)
--- Begin Message ---
Hello.. My name is Rian.

I have a problem, my Database attacked by ransomware virus. Some file has
encrypt. for pg_hba and postgresql.conf i have a backup, but for some file
like pg_filenode.map i dont have.

this a log when i start the postgres:

2017-05-08 10:08:17 ICT FATAL:  relation mapping file
"global/pg_filenode.map" contains invalid data

thanks for kindness.


postgresql-2017-05-08_100800.log
Description: Binary data


pg_filenode.map.id_2343700345_fgb45ft3pqamyji7.onion
Description: Binary data


pg_internal.init.id_2343700345_fgb45ft3pqamyji7.onion
Description: Binary data


PG_VERSION
Description: Binary data
--- End Message ---

-- 
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] Select from tableA - if not exists then tableB

[Brian Dunavant]

>From what you're saying about migrating, I'm assuming the new table
has additional columns or something.  If you can map the difference,
then you could use CTE's to select from the first table, and if
nothing is there, then pull from the second table and pad it with
nulls so they "match".  This should work fine in 9.1.

For example:

db=# create table old ( id integer );
CREATE TABLE
db=# create table new ( id integer, newcol text );
CREATE TABLE
db=# insert into old (id) values (1), (2);
INSERT 0 2
db=# insert into new (id, newcol) values (1, 'a');
INSERT 0 1

New table:

db=# with new_check as (
db(#   select id, newcol from new where id = 1
db(# )
db-# select id, null::text as newcol from old where id = 1
db-# and not exists ( select 1 from new_check )
db-# union all
db-# select * from new_check;
 id | newcol
+
  1 | a
(1 row)

Old table:

db=# with new_check as (
db(#   select id, newcol from new where id = 2
db(# )
db-# select id, null::text as newcol from old where id = 2
db-# and not exists ( select 1 from new_check )
db-# union all
db-# select * from new_check;
 id | newcol
+
  2 |
(1 row)

Neither:

db=# with new_check as (
db(#   select id, newcol from new where id = 3
db(# )
db-# select id, null::text as newcol from old where id = 3
db-# and not exists ( select 1 from new_check )
db-# union all
db-# select * from new_check;
 id | newcol
+
(0 rows)





On Mon, May 8, 2017 at 5:56 PM, Patrick B  wrote:
> Hi guys,
>
> I have two tables that supports the same data, but different table DDL (We
> are migrating all the data from one to another).
>
> What I need is basically:
>
> 1. Query looks for the data on table A,
> 2. if it doesn't find it on table A, go look for it on table B
>
> Now, how could I do that in a Select? Can you please provide some examples?
>
>
> I'm using PostgreSQL 9.1.
>
> Thanks
> Patrick


-- 
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] Select from tableA - if not exists then tableB

[David Rowley]

On 9 May 2017 at 09:56, Patrick B  wrote:
> Hi guys,
>
> I have two tables that supports the same data, but different table DDL (We
> are migrating all the data from one to another).
>
> What I need is basically:
>
> 1. Query looks for the data on table A,
> 2. if it doesn't find it on table A, go look for it on table B
>
> Now, how could I do that in a Select? Can you please provide some examples?
>
>
> I'm using PostgreSQL 9.1.

You could exploit DISTINCT ON for this.

Something like:

select distinct on (id) id,value from (select *,'a' tablename from a
where id=1 union all select *,'b' tablename from b where id=1) ab
order by id,tablename;

Assuming that id is what you want to be unique.


-- 
 David Rowley   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
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] Select from tableA - if not exists then tableB

[Adrian Klaver]

On 05/08/2017 02:56 PM, Patrick B wrote:

Hi guys,

I have two tables that supports the same data, but different table DDL
(We are migrating all the data from one to another).

What I need is basically:

1. Query looks for the data on table A,
2. if it doesn't find it on table A, go look for it on table B

Now, how could I do that in a Select? Can you please provide some examples?


If you can provide some information:

1) The table schema

2) How the data is related between the two tables.




I'm using PostgreSQL 9.1.

Thanks
Patrick



--
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] Select from tableA - if not exists then tableB

[Patrick B]

Hi guys,

I have two tables that supports the same data, but different table DDL (We
are migrating all the data from one to another).

What I need is basically:

1. Query looks for the data on table A,
2. if it doesn't find it on table A, go look for it on table B

Now, how could I do that in a Select? Can you please provide some examples?


I'm using PostgreSQL 9.1.

Thanks
Patrick

Re: [GENERAL] data transformation and replication

[Adrian Klaver]

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 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 some 
transformation in between in real time

I was looking at something like

db1 -> db2 replicates the same set of tables and with the same structures using 
pglogical for example
db2.tbl1 -> db2.tbl2 data gets massages/transformed based on what replicates 
from db1.tbl1 using triggers and functions


Other than that I reckon db1 -> db2 would be trigger based using something like 
slonik maybe (?) and data massage/transformation gets moved from db2 to db1 machine 
and then db1.tbl2 -> db2.tbl2 using pglogical


I was following you until the last part, "... moved from db2 to db1 
machine and then db1.tbl2 -> db2.tbl2 ..."


Is this correct?

If so why db1 --> db2 --> db1 --> db2?

A complete answer is going to depend on at least an outline of what you 
mean by massage/transform?





Is this doable ? If so any pointers as to where to look about it ?


Many thanks
Armand







--
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] data transformation and replication

[Armand Pirvu (home)]

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 some 
transformation in between in real time

I was looking at something like 

db1 -> db2 replicates the same set of tables and with the same structures using 
pglogical for example 
db2.tbl1 -> db2.tbl2 data gets massages/transformed based on what replicates 
from db1.tbl1 using triggers and functions


Other than that I reckon db1 -> db2 would be trigger based using something like 
slonik maybe (?) and data massage/transformation gets moved from db2 to db1 
machine and then db1.tbl2 -> db2.tbl2 using pglogical 


Is this doable ? If so any pointers as to where to look about it ?


Many thanks
Armand




-- 
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] Can PG replace redis, amqp, s3 in the future?

[Scott Marlowe]

On Mon, May 1, 2017 at 2:59 PM, Sven R. Kunze  wrote:
> On 30.04.2017 16:25, Steve Atkins wrote:
>
> You can use postgresql for caching, but caches don't require the data
> durability that a database offers, and can be implemented much more
> efficiently.
>
>
> I for one can understand Thomas' need for a single solution.
> Just recently I needed a cache which was supposed to be set up in a
> SERIALIZABLE manner as in
> https://www.postgresql.org/docs/devel/static/transaction-iso.html#xact-serializable
> Available cache mechanisms would have produce erroneous results. So, I went
> for PG.

This brings up another subject, reliability. If PostgreSQL is fast
enough, and on stable hardware, it's often the preferred choice
because of its very good stability. Try running a big production noSQL
cluster and you'll find plenty of sharp corners in most. A lot of
times it's just easier to set up a pair of VMs (on good hardware) and
toss a pg db at the problem, esp if performance is a secondary
consideration, or not likely to tax pgsql's basic architecture.


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

[GENERAL] Partitioning and Table Inheritance

[Paul A Jungwirth]

I'm working on a problem where partitioning seems to be the right
approach, but we would need a lot of partitions (say 10k or 100k).
Everywhere I read that after ~100 child tables you experience
problems. I have a few questions about that:

1. Is it true that the only disadvantage to 10k children is query
planning time when hitting the parent table? Is there anything else to
worry about? (Unlike ext3, ext4 seems to have no problem with lots of
files in one directory (e.g. 1,000,000). It doesn't break and it even
performs fine.) I know a lot of tables increases memory use. There is
clutter too in \dt but I think I can use schemas to cut down on that.
Anything else?

2. Is there any reason I shouldn't bypass the query planning cost by
having the app just SELECT from the correct child table (which is easy
for us 99% of the time)? Similarly I could bypass trigger time by
INSERTing directly into the child, etc.

3. Is it true that query planning time should scale linearly as I add
more child tables? I'm seeing ~16ms planning time from EXPLAIN ANALYZE
with 1000 tables. I haven't tried 10k tables yet, but so far 16ms or
even 160ms seems tolerable if it's only for the 1% of queries that
can't SELECT directly from a known child table.

4. I tried a scheme where instead of one parent table with 1000 direct
children, I have 1 parent with 10 children, and they each have 10
children, and they each have 10 children. I thought by using
increasingly-specific constraints I could maybe make query planning
scale by O(log n) instead of O(n), but instead it went up! Upon
investigating it looks like maybe the planner flattens the inheritance
hierarchy before doing constraint_exclusion work. Is that true? If so,
is there any interest in changing this in future versions? (I might be
willing to take a stab at a patch for it.)

Btw this is on 9.4 but it could be 9.5 or 9.6 if that would help any.

Thanks,
Paul


-- 
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] Migration Query

[Chris Mair]

Does one any having list of bottlenecks and workarounds while migrating data
  from Oracle to Postgresql. like what are thing which we can migrate from 
Oracle database to Postgresql and what we can't?

Which is the best tool for migration from Oracle to Postgresql?


Hi,

I like this tool and have used it successfully in the past:

http://ora2pg.darold.net/

Bye,
Chris.




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

[GENERAL] Migration Query

[PAWAN SHARMA]

Hi All,

Does one any having list of bottlenecks and workarounds while migrating data
 from Oracle to Postgresql. like what are thing which we can migrate from
Oracle database to Postgresql and what we can't?

Which is the best tool for migration from Oracle to Postgresql?

-Pawan

Re: [GENERAL] Very suspicious plan difference for select and corresponding delete queries PostgreSQL 9.6.2

[Tom Lane]

Maxim Boguk  writes:
> I can not see any possible/logical/realistic reason for the database to
> switch between INDEX ONLY SCAN and SEQ SCAN for EXIST part of query in this
> two cases.

If you'd done an EXPLAIN VERBOSE, you'd have noted that the scan was
returning ctid, which it could not get out of the index.  This is
necessary for possible EPQ rechecks.

regards, tom lane


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

[GENERAL] Very suspicious plan difference for select and corresponding delete queries PostgreSQL 9.6.2

[Maxim Boguk]

Hi,

I found a case with very curious plan difference between:

explain select * from jobs_to_delete WHERE EXISTS(SELECT 1 FROM
job_stats_new_201411  jsm WHERE jsm.job_reference =
jobs_to_delete.job_reference);

QUERY PLAN
-
 Hash Join  (cost=239386.25..376872.49 rows=111372 width=41)
   Hash Cond: ((jobs_to_delete.job_reference)::text =
(jsm.job_reference)::text)
   ->  Seq Scan on jobs_to_delete  (cost=0.00..101547.10 rows=9286780
width=41)
   ->  Hash  (cost=237994.10..237994.10 rows=111372 width=18)
 ->  HashAggregate  (cost=236880.38..237994.10 rows=111372 width=18)
   Group Key: (jsm.job_reference)::text
   ->  Index Only Scan using
job_stats_master_201411_job_reference_idx_ebs on job_stats_new_201411 jsm
(cost=0.56..214784.97 rows=8838161 width=18)

and corresponding delete (which I suspect should have the same plan)

explain delete from jobs_to_delete WHERE EXISTS(SELECT 1 FROM
job_stats_new_201411  jsm WHERE jsm.job_reference =
jobs_to_delete.job_reference);
 QUERY PLAN

 Delete on jobs_to_delete  (cost=266351.88..403838.13 rows=111372 width=12)
   ->  Hash Join  (cost=266351.88..403838.13 rows=111372 width=12)
 Hash Cond: ((jobs_to_delete.job_reference)::text =
(jsm.job_reference)::text)
 ->  Seq Scan on jobs_to_delete  (cost=0.00..101547.10 rows=9286780
width=43)
 ->  Hash  (cost=264959.73..264959.73 rows=111372 width=24)
   ->  HashAggregate  (cost=263846.01..264959.73 rows=111372
width=24)
 Group Key: (jsm.job_reference)::text
 ->  Seq Scan on job_stats_new_201411 jsm
(cost=0.00..241750.61 rows=8838161 width=24)

Manual analyze of the both tables didn't change a result.

I can not see any possible/logical/realistic reason for the database to
switch between INDEX ONLY SCAN and SEQ SCAN for EXIST part of query in this
two cases.

I not sure that it's a but, so I better post in -general first.

-- 
Maxim Boguk
Senior Postgresql DBA
http://dataegret.com/ 

Phone RU: +7 910 405 4718
Phone AU: +61 45 218 5678

LinkedIn: http://www.linkedin.com/pub/maksym-boguk/80/b99/b1b
Skype: maxim.boguk
Jabber: maxim.bo...@gmail.com

"People problems are solved with people.
If people cannot solve the problem, try technology.
People will then wish they'd listened at the first stage."