Re: Performance comparison between Pgsql 10.5 and Pgsql 11.2

[Nicola Contu]

So the first file is on Postgres11.2 on a test server (and where I compare
10 vs 11)
The second file, is our preprod machine running Postgres 11.2 (different
hardware etc, it is a VM). I know that could be confusing, but I just
wanted to compare that too because if you see the two files there's a lot
of difference between the two machines.
And they are both running CentOS 7.

So at this point I have two problems. One inside the machine between
Postgres 10 and 11 and another problem on the preprod (similar to prod)
with a lot of lseek.

Sorry if this is confusing, hope it is clear now.

Regarding partitions/tables. The first file involves just one table. The
second file (with a huge lseek) was running the test on a single table, but
meanwhile it was accessible by the preprod web application. So it was maybe
hit by some user and some other table.


Question:
1) Is it possible that pgbench could not be really a good tool for testing
the performances? If I use a sql script of thousands of insert records and
compare on the same server between pg10 and pg11 I get pretty much the same
result (maybe better on pg11)
2) regarding preprod, is there any way to reduce those lseek()?  Just to
let you know, comparing the same insert script between the first server,
the first server takes 2m the second one takes 5-7m.

Thanks a lot,





Il giorno gio 7 mar 2019 alle ore 04:47 Thomas Munro 
ha scritto:

> On Thu, Mar 7, 2019 at 12:12 AM Nicola Contu 
> wrote:
> > This is instead the strace of another server running the same version
> compiled  but that is even slower.
>
> Huh.  That's a lot of lseek().  Some of these will be for random
> reads/writes and will go way in v12, and some will be for probing the
> size of relations while planning, and some while executing scans.  I
> bet you could make some of them go away by using prepared statements.
> Does the query in your test involve many partitions/tables?
>
> % time seconds  usecs/call callserrors syscall
> -- --- --- - - 
>  32.50  143.010306   7  21044095   lseek
>  26.21  115.354045  14   8144577   read
>   6.18   27.185578  16   166988910 sendto
>   5.29   23.300584  57407528   fdatasync
>   4.93   21.709522   9   2313529824174 recvfrom
>   3.31   14.547568  19765897   write
>   2.73   12.007486  14867088 14494 epoll_wait
>   2.189.597460  15659871 84097 futex
>   1.858.147759  14567414   close
>   1.777.767832  18437656 11319 open
>
> The other results had 1 usec lseek(), and much fewer of them relative
> to the number of reads and writes.  BTW, are you comparing v10 and v11
> on the same hardware, kernel, filesystem?  Just wondering if there
> could be some change in syscall overhead on different kernel patch
> levels or something like that: we see 7 usec vs 1 usec in those two
> files (though I have no idea how reliable these times are) and if
> we're going to call it 21 million times at some point it might
> matter...
>
> --
> Thomas Munro
> https://enterprisedb.com
>

Re: Question about pg_upgrade from 9.2 to X.X

[Justin Pryzby]

On Wed, Mar 06, 2019 at 09:44:16PM -0800, Perumal Raj wrote:
> Any pointers for pg_repack schema creation ?

With recent postgres, you should use just: "CREATE EXTENSION pg_repack", which
does all that for you.

> Will there be any impact in the future , Since i used --link option ?

You probably have an old DB directory laying around which is (at least
partially) hardlinks.  You should remove it .. but be careful to remove the
correct dir.  My scripts always rename the old dir before running pg_upgrade,
so it's less scary to rm -fr it later.

Justin

Resolved: Question about pg_upgrade from 9.2 to X.X

[Perumal Raj]

Thanks again.

Perumal Raju

On Thu, Mar 7, 2019, 2:32 AM Justin Pryzby  wrote:

> On Wed, Mar 06, 2019 at 09:44:16PM -0800, Perumal Raj wrote:
> > Any pointers for pg_repack schema creation ?
>
> With recent postgres, you should use just: "CREATE EXTENSION pg_repack",
> which
> does all that for you.
>
> > Will there be any impact in the future , Since i used --link option ?
>
> You probably have an old DB directory laying around which is (at least
> partially) hardlinks.  You should remove it .. but be careful to remove the
> correct dir.  My scripts always rename the old dir before running
> pg_upgrade,
> so it's less scary to rm -fr it later.
>
> Justin
>

Connection pooling for differing databases?

[Arjun Ranade]

Hi all,

I'm wondering if there's a tool like pgpool that can provide a single
origin point (host/port) that will proxy/direct connections to the specific
servers that contain the db needing to be accessed.

For example... lets say we had two databases: db1.company.com:5432 and
db2.company.com:5433

Db1 has the database: env1
Db2 has the database: env2

Is there a tool that will accept connections, so that when users connect
they see there are two databases they can go to: env1 and env2.

If they choose to connect to the env1 db, it routes all traffic to
db1.company.com:5432 and if they choose env2 it routes them to
db2.company.com:5433

Of course there would have to be some requirement such as the databases on
any given server cannot have name collisions with database names on another
server, etc.  Is there a way to do something like this?

Thanks,
Arjun

Re: Connection pooling for differing databases?

[Fabrízio de Royes Mello]

Em qui, 7 de mar de 2019 às 16:10, Arjun Ranade 
escreveu:
>
> Hi all,
>
> I'm wondering if there's a tool like pgpool that can provide a single
origin point (host/port) that will proxy/direct connections to the specific
servers that contain the db needing to be accessed.
>
> For example... lets say we had two databases: db1.company.com:5432 and
db2.company.com:5433
>
> Db1 has the database: env1
> Db2 has the database: env2
>
> Is there a tool that will accept connections, so that when users connect
they see there are two databases they can go to: env1 and env2.
>
> If they choose to connect to the env1 db, it routes all traffic to
db1.company.com:5432 and if they choose env2 it routes them to
db2.company.com:5433
>
> Of course there would have to be some requirement such as the databases
on any given server cannot have name collisions with database names on
another server, etc.  Is there a way to do something like this?
>

Yeap, pgbouncer do that. See "databases" configuration section [1].

Regards,

[1] https://pgbouncer.github.io/config.html#section-databases

--
   Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Re: Connection pooling for differing databases?

[Moreno Andreo]

Il 07/03/2019 20:27, Arjun Ranade ha scritto:

Hi all,

I'm wondering if there's a tool like pgpool that can provide a single 
origin point (host/port) that will proxy/direct connections to the 
specific servers that contain the db needing to be accessed.
Yes, I think there are many, but I'm encouraging you to take a look at 
pgbouncer


https://pgbouncer.github.io/

in pgbouncer.ini you enter database configuration values like

database = host=hostname port=xyzk, like
mydb1 = host=cluster1 port=6543 or
mydb2 = host=cluster1 port=9876
mydb3 = host=cluster2 port=6543

but there many other parameters to refine your config (like "proxying" 
database names, so if you share names across clusters you can easily 
avoid conflicts)


Pgbouncer should be installed on the same server as the databases or in 
another and listens on a different port than Postgres' (say 5431 while 
postgres is on 5432)
I'm actively using in my environment with 2 clusters and about 500 
databases, works flawlessly.


One thing you have to consider, if under heavy workload (say 100's of 
connections) is to raise kernel value of maximum open files


Cheers

Moreno.-

Re: Future Non-server Windows support???

[Bill Haught]

On 3/3/2019 7:30 AM, Laurenz Albe wrote:

Bill Haught wrote:

My main concern is that Microsoft has Enterprise versions of Windows and
versions for everything else which makes me wonder if at some point
Windows versions for desktop use may not have features needed by some
database applications or differences between the versions may be enough
to necessitate slight tweaks to code and compiling additional versions.


Speaking as a semi-ignorant, I had the impressions that all Windows versions
are pretty similar under the hood (with occasional annoying behavior changes),
and most of the differences are on the GUI level, while the C API is pretty
much the same.

Yours,
Laurenz Albe



For some reason my previous message went to one member and not the 
group.  I keep getting Wrigley's gum treatment, two of each.


I assume you mean from the perspectives of administrators and 
"end-lusers" (as many in the GPL / Open Source world would say)?


"...most Windows 95 applications still run fine in Windows 10 - that's 
20 years of binary compatibility"  See Major Linux Problems on the 
Desktop, 2018 edition by Artem S. Tashkinov

https://itvision.altervista.org/why.linux.is.not.ready.for.the.desktop.current.html


And yes there are critical differences between Windows Versions.  It use 
to be that you could not put Home on a domain.  Even if you have Pro 
versions you probably still need a Server or Enterprise version to do 
so.  I suspect using the usual peer-to-peer networking to big one of 
many major sources of hassles (with lack of a package manager and a 
package format that requires the information needed to clean uninstall 
or just create a new sets of ini and registry files and boot menu 
entries being numero uno, ¿entiende?).  Home version does not have Group 
Policy.  You cannot set (Enable) "No auto-restart with logged on users 
for scheduled automatic updates installations" under 
%SystemRoot%\System32\mmc.exe %SystemRoot%\System32\gpedit.msc > Local 
Computer Policy\Computer Configuration\Administrative Templates\Windows 
Components\Windows Update  I am betting the Enterprise version is very 
different under the hood and optimized for very heavy multitasking, disk 
access and whatever else is needed in that environment.


Micro$oft has a habit of putting in features and then taking them away, 
hence my concern.


If only Darling got half the support Wine does, they'd probably have 
something functional, unlike the quarter-baked Wine.


I really wish Linux or Linux plus Darling was a real alternative to 
Winblows.

Re: Connection pooling for differing databases?

[Arjun Ranade]

I'm looking at pgbouncer and it does most of what I need.  I'm wondering
about clients connecting via pgadmin, is there a way for users using
pgadmin or another tool to see all the databases that are part of the
configs?
Thanks,
Arjun


On Thu, Mar 7, 2019 at 2:39 PM Moreno Andreo 
wrote:

> Il 07/03/2019 20:27, Arjun Ranade ha scritto:
> > Hi all,
> >
> > I'm wondering if there's a tool like pgpool that can provide a single
> > origin point (host/port) that will proxy/direct connections to the
> > specific servers that contain the db needing to be accessed.
> Yes, I think there are many, but I'm encouraging you to take a look at
> pgbouncer
>
> https://pgbouncer.github.io/
>
> in pgbouncer.ini you enter database configuration values like
>
> database = host=hostname port=xyzk, like
> mydb1 = host=cluster1 port=6543 or
> mydb2 = host=cluster1 port=9876
> mydb3 = host=cluster2 port=6543
>
> but there many other parameters to refine your config (like "proxying"
> database names, so if you share names across clusters you can easily
> avoid conflicts)
>
> Pgbouncer should be installed on the same server as the databases or in
> another and listens on a different port than Postgres' (say 5431 while
> postgres is on 5432)
> I'm actively using in my environment with 2 clusters and about 500
> databases, works flawlessly.
>
> One thing you have to consider, if under heavy workload (say 100's of
> connections) is to raise kernel value of maximum open files
>
> Cheers
>
> Moreno.-
>
>
>
>

Re: Connection pooling for differing databases?

[Jerry Sievers]

Arjun Ranade  writes:

> Hi all,
>
> I'm wondering if there's a tool like pgpool that can provide a single
> origin point (host/port) that will proxy/direct connections to the
> specific servers that contain the db needing to be accessed.
>
> For example... lets say we had two databases: db1.company.com:5432
> and db2.company.com:5433
>
> Db1 has the database: env1
> Db2 has the database: env2
>
> Is there a tool that will accept connections, so that when users
> connect they see there are two databases they can go to: env1 and
> env2. 

No, not at least the "they can see 2 DBs" portion of your req.

PgBouncer can route traffic on behalf of multiple server/DBs by
configuration but AFAIK even administrator access to the special
endpoint 'pgbouncer' does *not* list all possible DBs unless they are in
use and/or recently enough used to be still shown by 'show databases'.

Direct consultation of the INI file would be required... ergo, there is
nothing equivalent to psql -l.

Disclaimer: I do *not* have recent experience with PgPool as-if to
weigh-in there, but likely someone else will.

HTH


>
> If they choose to connect to the env1 db, it routes all traffic to 
> db1.company.com:5432 and if they choose env2 it routes them to 
> db2.company.com:5433
>
> Of course there would have to be some requirement such as the
> databases on any given server cannot have name collisions with
> database names on another server, etc.  Is there a way to do
> something like this?
>
> Thanks,
> Arjun
>
>

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consult...@comcast.net

Re: Future Non-server Windows support???

[Thomas Kellerer]

Bill Haught schrieb am 07.03.2019 um 20:41:

"...most Windows 95 applications still run fine in Windows 10 -
that's 20 years of binary compatibility"  See Major Linux Problems on
the Desktop, 2018 edition by Artem S. Tashkinov 
https://itvision.altervista.org/why.linux.is.not.ready.for.the.desktop.current.html


I really wish Linux or Linux plus Darling was a real alternative to
Winblows.


On the server, Linux definitely *is* a real alternative to Windows


My 0.02€

Thomas

Re: Future Non-server Windows support???

[Bill Haught]

On 3/7/2019 8:29 PM, Thomas Kellerer wrote:

Bill Haught schrieb am 07.03.2019 um 20:41:

"...most Windows 95 applications still run fine in Windows 10 -
that's 20 years of binary compatibility"  See Major Linux Problems on
the Desktop, 2018 edition by Artem S. Tashkinov 
https://itvision.altervista.org/why.linux.is.not.ready.for.the.desktop.current.html 



I really wish Linux or Linux plus Darling was a real alternative to
Winblows.


On the server, Linux definitely *is* a real alternative to Windows


My 0.02€

Thomas






In the case of servers, I thought it was the other way around.  Last I 
heard they were neck-and-neck after Linux being overwhelmingly in the 
lead for at least something like the first decade of the (at least 
"public") Internet.


Like why would anyone choose Winbloz *if* you don't *have* too, I have 
no idea.

Re: python install location

[Peter Eisentraut]

On 2019-03-07 01:12, Alan Nilsson wrote:
> How does postgres determine which install of python to use in conjunction 
> with plpythonu?

It looks for a "python" program and then uses that to find the location
of the required library.

> Is there a way, in postgres, short of rebuilding that we can tell postgres 
> which install of python to use when invoking plpython?

No, that it determined at build time.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Future Non-server Windows support???

[Adrian Klaver]

On 3/7/19 12:58 PM, Bill Haught wrote:

On 3/7/2019 8:29 PM, Thomas Kellerer wrote:

Bill Haught schrieb am 07.03.2019 um 20:41:

"...most Windows 95 applications still run fine in Windows 10 -
that's 20 years of binary compatibility"  See Major Linux Problems on
the Desktop, 2018 edition by Artem S. Tashkinov 
https://itvision.altervista.org/why.linux.is.not.ready.for.the.desktop.current.html 



I really wish Linux or Linux plus Darling was a real alternative to
Winblows.


On the server, Linux definitely *is* a real alternative to Windows


My 0.02€

Thomas






In the case of servers, I thought it was the other way around.  Last I 
heard they were neck-and-neck after Linux being overwhelmingly in the 
lead for at least something like the first decade of the (at least 
"public") Internet.


See:

https://www.zdnet.com/article/linux-now-dominates-azure/



Like why would anyone choose Winbloz *if* you don't *have* too, I have 
no idea.







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

Re: Property Graph Query Language proposed for SQL:2020

[Peter Eisentraut]

On 2019-02-25 01:09, Stefan Keller wrote:
> Anyone aware and following this standardization activities?
> Forthcoming SQL:2020 seems to contain "Property Graph Query Extensions".
> See:
> * GQL: a standard for property graph querying
> https://www.gqlstandards.org/
> * Property Graph Query Language (PGQL), an SQL-like query language for
> graphs, including an Open-sourced parser and static query validator on
> GitHub by Oracle.
> http://pgql-lang.org/

Yes, I was just at this meeting this week:
https://www.w3.org/Data/events/data-ws-2019/

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Tools to migrate data from Json files to PostgreSQL DB.

[github kran]

Hello PostgreSQL Team,

Are there are any tools to migrate data present in the json files ? to the
postgreSQL database.
We have data in flat files about 2 billion records across multiple files.

1) What is the easiest way I can transfer this data to relational database
?.
2) Any tools I can use ?. and any better ways do it ?

Appreciate your help.


Thanks
Kran,

Re: Tools to migrate data from Json files to PostgreSQL DB.

[Adrian Klaver]

On 3/7/19 1:21 PM, github kran wrote:

Hello PostgreSQL Team,

Are there are any tools to migrate data present in the json files ? to 
the postgreSQL database.

We have data in flat files about 2 billion records across multiple files.

1) What is the easiest way I can transfer this data to relational 
database ?.

2) Any tools I can use ?. and any better ways do it ?


An older blog:

https://www.citusdata.com/blog/2013/05/30/run-sql-on-json-files-without-any-data-loads/

Not sure if all the parts still work or not.

Otherwise I could see using the JSON types and JSON functions built into 
Postgres:


https://www.postgresql.org/docs/10/datatype-json.html

https://www.postgresql.org/docs/10/functions-json.html





Appreciate your help.


Thanks
Kran,



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

Re: Future Non-server Windows support???

[Ron]

On 3/7/19 2:58 PM, Bill Haught wrote:
[snip]


Like why would anyone choose Winbloz *if* you don't *have* too, I have no 
idea.


1999 wants it's insult back.

--
Angular momentum makes the world go 'round.

Re: partial data migration

[Julie Nishimura]

Thank you Ron! What if dev environment is on 9.6, but prod is on version 8.3? 
Will posgtres_fdw still be the right option?

Sent from my iPhone

On Mar 6, 2019, at 11:57 PM, Ron 
mailto:ronljohnso...@gmail.com>> wrote:

On 3/7/19 1:54 AM, Julie Nishimura wrote:
Hello psql friends,
We need to migrate only 6 months worth of data from one instance to another. 
What would be the easiest way to do it? In Oracle, I would set up dblink. What 
about postgresql?

postgres_fdw

--
Angular momentum makes the world go 'round.

Re: partial data migration

[Ron]

(8.3?  That's even older than what we just migrated from!!!)

No.  Make some views (I'd probably make them "month-sized"), COPY each view 
from the source db to a file, and then COPY each file to it's relevant 
target table.


You should also think about a program named pg_bulkload.

https://www.postgresql.org/docs/9.6/sql-copy.html
https://www.postgresql.org/docs/8.3/sql-copy.html


On 3/7/19 7:53 PM, Julie Nishimura wrote:
Thank you Ron! What if dev environment is on 9.6, but prod is on version 
8.3? Will posgtres_fdw still be the right option?


Sent from my iPhone

On Mar 6, 2019, at 11:57 PM, Ron > wrote:



On 3/7/19 1:54 AM, Julie Nishimura wrote:

Hello psql friends,
We need to migrate only 6 months worth of data from one instance to 
another. What would be the easiest way to do it? In Oracle, I would set 
up dblink. What about postgresql?


postgres_fdw



--
Angular momentum makes the world go 'round.

Re: partial data migration

[Julie Nishimura]

Ron, thanksagain. In case if I need to migrate the entire tables, I should be 
able to use pg_dump and pg_restore for certain tables, even between different 
versions, right? In case if I need to migrate from 8 to 9?

Thanks


From: Ron 
Sent: Thursday, March 7, 2019 5:59 PM
To: pgsql-general@lists.postgresql.org
Subject: Re: partial data migration

(8.3?  That's even older than what we just migrated from!!!)

No.  Make some views (I'd probably make them "month-sized"), COPY each view 
from the source db to a file, and then COPY each file to it's relevant target 
table.

You should also think about a program named pg_bulkload.

https://www.postgresql.org/docs/9.6/sql-copy.html
https://www.postgresql.org/docs/8.3/sql-copy.html


On 3/7/19 7:53 PM, Julie Nishimura wrote:
Thank you Ron! What if dev environment is on 9.6, but prod is on version 8.3? 
Will posgtres_fdw still be the right option?

Sent from my iPhone

On Mar 6, 2019, at 11:57 PM, Ron 
mailto:ronljohnso...@gmail.com>> wrote:

On 3/7/19 1:54 AM, Julie Nishimura wrote:
Hello psql friends,
We need to migrate only 6 months worth of data from one instance to another. 
What would be the easiest way to do it? In Oracle, I would set up dblink. What 
about postgresql?

postgres_fdw


--
Angular momentum makes the world go 'round.

Re: partial data migration

[Ron]

Yes, for whole tables (even sets of tables) "pg_dump --table=" is good at 
that.  Even better, you can run the 9.6 pg_dump against the 8.3 database and 
get parallelism with "--jobs".


On 3/7/19 8:11 PM, Julie Nishimura wrote:
Ron, thanksagain. In case if I need to migrate the entire tables, I should 
be able to use pg_dump and pg_restore for certain tables, even between 
different versions, right? In case if I need to migrate from 8 to 9?


Thanks


*From:* Ron 
*Sent:* Thursday, March 7, 2019 5:59 PM
*To:* pgsql-general@lists.postgresql.org
*Subject:* Re: partial data migration
(8.3?  That's even older than what we just migrated from!!!)

No.  Make some views (I'd probably make them "month-sized"), COPY each 
view from the source db to a file, and then COPY each file to it's 
relevant target table.


You should also think about a program named pg_bulkload.

https://www.postgresql.org/docs/9.6/sql-copy.html
https://www.postgresql.org/docs/8.3/sql-copy.html


On 3/7/19 7:53 PM, Julie Nishimura wrote:
Thank you Ron! What if dev environment is on 9.6, but prod is on version 
8.3? Will posgtres_fdw still be the right option?


Sent from my iPhone

On Mar 6, 2019, at 11:57 PM, Ron > wrote:



On 3/7/19 1:54 AM, Julie Nishimura wrote:

Hello psql friends,
We need to migrate only 6 months worth of data from one instance to 
another. What would be the easiest way to do it? In Oracle, I would set 
up dblink. What about postgresql?


postgres_fdw



--
Angular momentum makes the world go 'round.


--
Angular momentum makes the world go 'round.

Re: partial data migration

[Julie Nishimura]

Great, thanks!

Sent from my iPhone

On Mar 7, 2019, at 7:48 PM, Ron 
mailto:ronljohnso...@gmail.com>> wrote:

Yes, for whole tables (even sets of tables) "pg_dump --table=" is good at that. 
 Even better, you can run the 9.6 pg_dump against the 8.3 database and get 
parallelism with "--jobs".

On 3/7/19 8:11 PM, Julie Nishimura wrote:
Ron, thanksagain. In case if I need to migrate the entire tables, I should be 
able to use pg_dump and pg_restore for certain tables, even between different 
versions, right? In case if I need to migrate from 8 to 9?

Thanks


From: Ron 
Sent: Thursday, March 7, 2019 5:59 PM
To: 
pgsql-general@lists.postgresql.org
Subject: Re: partial data migration

(8.3?  That's even older than what we just migrated from!!!)

No.  Make some views (I'd probably make them "month-sized"), COPY each view 
from the source db to a file, and then COPY each file to it's relevant target 
table.

You should also think about a program named pg_bulkload.

https://www.postgresql.org/docs/9.6/sql-copy.html
https://www.postgresql.org/docs/8.3/sql-copy.html


On 3/7/19 7:53 PM, Julie Nishimura wrote:
Thank you Ron! What if dev environment is on 9.6, but prod is on version 8.3? 
Will posgtres_fdw still be the right option?

Sent from my iPhone

On Mar 6, 2019, at 11:57 PM, Ron 
mailto:ronljohnso...@gmail.com>> wrote:

On 3/7/19 1:54 AM, Julie Nishimura wrote:
Hello psql friends,
We need to migrate only 6 months worth of data from one instance to another. 
What would be the easiest way to do it? In Oracle, I would set up dblink. What 
about postgresql?

postgres_fdw


--
Angular momentum makes the world go 'round.

--
Angular momentum makes the world go 'round.

Re: Tools to migrate data from Json files to PostgreSQL DB.

[Pavel Stehule]

Hi

čt 7. 3. 2019 v 22:21 odesílatel github kran  napsal:

> Hello PostgreSQL Team,
>
> Are there are any tools to migrate data present in the json files ? to the
> postgreSQL database.
> We have data in flat files about 2 billion records across multiple files.
>
> 1) What is the easiest way I can transfer this data to relational database
> ?.
> 2) Any tools I can use ?. and any better ways do it ?
>

I wrote pgimportdoc

https://github.com/okbob/pgimportdoc

Regards

Pavel


> Appreciate your help.
>
>
> Thanks
> Kran,
>

Re: partial data migration

[Adrian Klaver]

On 3/7/19 5:53 PM, Julie Nishimura wrote:
Thank you Ron! What if dev environment is on 9.6, but prod is on version 
8.3? Will posgtres_fdw still be the right option?


Thanks to the documentation writers:

https://www.postgresql.org/docs/11/postgres-fdw.html#id-1.11.7.42.15



Sent from my iPhone

On Mar 6, 2019, at 11:57 PM, Ron > wrote:



On 3/7/19 1:54 AM, Julie Nishimura wrote:

Hello psql friends,
We need to migrate only 6 months worth of data from one instance to 
another. What would be the easiest way to do it? In Oracle, I would 
set up dblink. What about postgresql?


postgres_fdw

--
Angular momentum makes the world go 'round.



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

Re: Tools to migrate data from Json files to PostgreSQL DB.

[Tony Shelver]

You may want to assess how you want to store and access the data in
Postgres before deciding on an import strategy.

I have a system with a mix of relational and JSON data.  The data was
originally sourced in flat file format.
I wrote a few Python programs to take the data, then format to JSON, which
I then submitted to pg functions.
To make life easier, I submitted it as 1 JSON field to be decomposed by
Postgres into relational data, and another to be loaded straight into pg
JSON columns.

The functions then either strip out the data from JSON using the PG JSON
functions and store it relationally, or plug it straight into a JSON data
element.

Not terribly difficult to do, especially if you are not navigating complex
JSON structures in pl/pgsql to strip out the data.

Plenty of python JSON examples out there. Less so for PG :)

On Thu, 7 Mar 2019 at 23:21, github kran  wrote:

> Hello PostgreSQL Team,
>
> Are there are any tools to migrate data present in the json files ? to the
> postgreSQL database.
> We have data in flat files about 2 billion records across multiple files.
>
> 1) What is the easiest way I can transfer this data to relational database
> ?.
> 2) Any tools I can use ?. and any better ways do it ?
>
> Appreciate your help.
>
>
> Thanks
> Kran,
>