Re: [GENERAL] Memory usage per session

[John R Pierce]

On 7/8/2016 12:00 PM, Karl Czajkowski wrote:

3. Rewrite or refactor such complex stored procedures in a different
programming language such as C or Python, so your PL/pgsql stored
procedures remain small glue around libraries of code.  Postgres
makes it very trivial to extend the system with such procedural
libraries.


4.   move said business logic to app servers to unload the database 
server from doing so much compute, and get better all around 
performance.   use plpgsql functions for inner functions only where 
there are significant performance gains.its easy to cluster app 
servers, its not so easy to parallelize database servers.




--
john r pierce, recycling bits in santa cruz



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

Re: [GENERAL] Memory usage per session

[Karl Czajkowski]

On Jul 08, John McKown modulated:
...
> I think the "problem" that he is having is fixable only by changing how
> PostgreSQL itself works. His problem is a PL/pgSQL function which is
> 11K lines in length. When invoked, this function is "compiled" into a
> large tokenized parse tree. This parse tree is only usable in the
> session which invoked the the function. Apparently this parse tree
> takes a lot of memory.

Right.  I'm not entirely sure the original poster wants to hear
practical solutions, but I think there are three ways that someone
familar with Postgres would avoid this type of problem:

1. Connection pooling. Set the max connection limit and other tuning
   parameters appropriately for your workload and available
   resources. Don't get into a state as was described (thousands of
   open connections and only hundreds "active"). Make your client
   applications more disciplined.

2. Buy more RAM.  You can easily grow to 512GB in just basic
   dual-socket servers these days.  This hardware cost may well 
   be worth it to avoid human labor costs.

3. Rewrite or refactor such complex stored procedures in a different
   programming language such as C or Python, so your PL/pgsql stored
   procedures remain small glue around libraries of code.  Postgres
   makes it very trivial to extend the system with such procedural
   libraries.
   

Karl


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

[John R Pierce]

On 7/8/2016 11:52 AM, Paul Tilles - NOAA Federal wrote:
I am currently running Version 9.3.10 of postgres with RHEL 6.  I am 
going to upgrade my O/S soon to RHEL 7. Do I need to upgrade to 
version 9.4.x of postgres?


was 9.3 installed from the PGDG yum repository, or from the default RHEL 
repository?


frankly, I would NOT do an in place upgrade of RHEL6 to RHEL7, I would 
instead bring up a clean RHEL 7, and configure the services you need, 
migrate your data to it.using the PGDG yum repo, you can install any 
supported postgres version you want on any supported OS, even sevveral 
versions at once (necessary for major version upgrades).



--
john r pierce, recycling bits in santa cruz



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

[GENERAL] RHEL 7

[Paul Tilles - NOAA Federal]

I am currently running Version 9.3.10 of postgres with RHEL 6.  I am going
to upgrade my O/S soon to RHEL 7. Do I need to upgrade to version 9.4.x of
postgres?

Paul Tilles

Re: [GENERAL] pasting a lot of commands to psql

[Alvaro Herrera]

Tom Lane wrote:
> Francisco Olarte  writes:
> > On Thu, Jul 7, 2016 at 8:18 PM, Alvaro Herrera  
> > wrote:
> >> I've wished sometimes for a "\set READLINE off" psql metacommand for
> >> this kind of thing.  It's pretty annoying when the text being pasted
> >> contains tabs and readline uses to do completion.
> 
> > Doesn't 'cat | psql ' disable it?
> 
> Sure, but you could as well use 'psql -n'.  I think the point is to be
> able to turn it on and off without starting a fresh session.  (Admittedly,
> maybe there's not a lot of usability gain there.)

If your command line already connected to the correct server/database,
with the correct login role, then yeah you can do that.  If you have to
switch role (say the role that runs the commands is not a login role),
it's not so convenient to disconnect and launch a new psql.

Now of course this not a huge new feature, but a usability improvement
only -- but psql is full of small usability features and they make it a
pleasure to use.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, 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] Memory usage per session

[Pavel Stehule]

2016-07-08 17:49 GMT+02:00 :

> Hi
>
>
> >> Oracle: about 5M
> >> postgreSql: about 160М
>
>
>
> >The almost session memory is used for catalog caches. So you should to
> have big catalog and long living sessions.
>
> >What do you do exactly?
>
> I've generate test code that  emulates instruction tree size for our
> production code.
> This test shows:
> -What is the size of instruction tree for our typical  BP
>   it's greater than 300M for each session
> -How often do PostgreSql parse the text
>   When postgres clean cache, so much often
>

PostgreSQL parses the source code of functions once per session


>
>
> So Oracle is much better in this case.
> It's very difficult really estimate in such case, to buy Oracle or to by
> hardware.
>

Should be - The implementation of PLpgSQL and PL/SQL is strongly different.
When your case is not usual, then the migration to Postgres needs redesign
sometimes.

Regards

Pavel

Re: [GENERAL] Memory usage per session

[John McKown]

On Fri, Jul 8, 2016 at 11:26 AM, Melvin Davidson 
wrote:

>
>
> On Fri, Jul 8, 2016 at 11:49 AM,  wrote:
>
>> Hi
>>
>>
>> >> Oracle: about 5M
>> >> postgreSql: about 160М
>>
>>
>>
>> >The almost session memory is used for catalog caches. So you should to
>> have big catalog and long living sessions.
>>
>> >What do you do exactly?
>>
>> I've generate test code that  emulates instruction tree size for our
>> production code.
>> This test shows:
>> -What is the size of instruction tree for our typical  BP
>>   it's greater than 300M for each session
>> -How often do PostgreSql parse the text
>>   When postgres clean cache, so much often
>>
>> So Oracle is much better in this case.
>> It's very difficult really estimate in such case, to buy Oracle or to by
>> hardware.
>>
>>
>
> *My questions:*>What is the actual O/S that PostgreSQL is installed on?
> >How much total memory is on the server?
> >I would be very curious about the values you have  specified in
> postgresql.conf?
> > Also, what is the exact version of PostgreSQL you are using?
> >What is the total time to complete the test for all 3 DB's?
> >The best I can tell is that with all the unknowns, you are comparing
> apples to oranges.
>
> *Your answers:*
> >There is real problem for us.
> >The PL/pgSQL interpreter parses the function's source text and produces
> an internal binary instruction tree the first time the function is called
> (within each session)
>
>
> *Your answer is jibberish and has nothing to do with my questions.*
>
> *Have you even tuned the postgresql.conf?*
> *You cannot fairly compare PostgreSQL with any other database unless you
> first tune it's postgres.conf.*
>
> *Melvin Davidson*
>

I think the "problem" that he is having is fixable only by changing how
PostgreSQL itself works. His problem is a PL/pgSQL function which is 11K
lines in length. When invoked, this function is "compiled" into a large
tokenized parse tree. This parse tree is only usable in the session which
invoked the the function. Apparently this parse tree takes a lot of memory.
And "n" concurrent users of this, highly used, function will therefore
require "n" times as much memory because the parse tree is _not_
shareable.  This is explained in:
​​
https://www.postgresql.org/docs/9.5/static/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING

​In previous posts, he implied that he is running on some version of
Windows by referencing the VC compiler. I am _guessing_ that the other DBs
mentioned: MSSQL and Oracle implement their server side programming
differently so that it takes less memory. Perhaps by allowing the "compiled
program" to be shared between session.




-- 
"Pessimism is a admirable quality in an engineer. Pessimistic people check
their work three times, because they're sure that something won't be right.
Optimistic people check once, trust in Solis-de to keep the ship safe, then
blow everyone up."
"I think you're mistaking the word optimistic for inept."
"They've got a similar ring to my ear."

>From "Star Nomad" by Lindsay Buroker:

Maranatha! <><
John McKown

Re: [GENERAL] Memory usage per session

[Melvin Davidson]

On Fri, Jul 8, 2016 at 11:49 AM,  wrote:

> Hi
>
>
> >> Oracle: about 5M
> >> postgreSql: about 160М
>
>
>
> >The almost session memory is used for catalog caches. So you should to
> have big catalog and long living sessions.
>
> >What do you do exactly?
>
> I've generate test code that  emulates instruction tree size for our
> production code.
> This test shows:
> -What is the size of instruction tree for our typical  BP
>   it's greater than 300M for each session
> -How often do PostgreSql parse the text
>   When postgres clean cache, so much often
>
> So Oracle is much better in this case.
> It's very difficult really estimate in such case, to buy Oracle or to by
> hardware.
>
>

*My questions:*>What is the actual O/S that PostgreSQL is installed on?
>How much total memory is on the server?
>I would be very curious about the values you have  specified in
postgresql.conf?
> Also, what is the exact version of PostgreSQL you are using?
>What is the total time to complete the test for all 3 DB's?
>The best I can tell is that with all the unknowns, you are comparing
apples to oranges.

*Your answers:*
>There is real problem for us.
>The PL/pgSQL interpreter parses the function's source text and produces an
internal binary instruction tree the first time the function is called
(within each session)


*Your answer is jibberish and has nothing to do with my questions.*

*Have you even tuned the postgresql.conf?*
*You cannot fairly compare PostgreSQL with any other database unless you
first tune it's postgres.conf.*

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

Re: [GENERAL] Memory usage per session

[AMatveev]

Title: Re: [GENERAL] Memory usage per session


Hi


>> Oracle: about 5M
>> postgreSql: about 160М



>The almost session memory is used for catalog caches. So you should to have big catalog and long living sessions. 

>What do you do exactly?

I've generate test code that  emulates instruction tree size for our production code.
This test shows:
-What is the size of instruction tree for our typical  BP  
  it's greater than 300M for each session 
-How often do PostgreSql parse the text 
  When postgres clean cache, so much often 

So Oracle is much better in this case. 
It's very difficult really estimate in such case, to buy Oracle or to by hardware.

Re: [GENERAL] Memory usage per session

[AMatveev]

Title: Re: [GENERAL] Memory usage per session


Hi

>> >> The test performs about 11K lines of code
>> >> Oracle: about 5M
>> >> postgreSql: about 160М


>What is the actual O/S that PostgreSQL is installed on? 
>How much total memory is on the server? 
>I would be very curious about the values you have  specified in postgresql.conf?
> Also, what is the exact version of PostgreSQL you are using?
>What is the total time to complete the test for all 3 DB's?
>The best I can tell is that with all the unknowns, you are comparing apples to oranges.

There is real problem for us.
The PL/pgSQL interpreter parses the function's source text and produces an internal binary instruction tree the first time the function is called (within each session) 

The size of this instruction tree depends only of postgreSql build.

And size is big, and size is not share between session.

I understand that nobody says: Damn One second I will implement it soon :)

Thank all for constructive answers.

Re: [GENERAL] Memory usage per session

[Achilleas Mantzios]

On 08/07/2016 17:07, amatv...@bitec.ru wrote:

Hi


The test performs about 11K lines of code
Oracle: about 5M
postgreSql: about 160М



Do you have 100 CPUs on this system which apparently doesn't have 16G
of RAM available for PG to use?

We can say at fact:
We currently  work at oracle.
Our code base about 4000 k line of code
In out last project we have:
3000 current connection
200 active session
So 16g it's very optimistic.
Of course  we think about buy hardware or software.
It's other question.
So with this memory consumption it can be really cheaper to by Oracle.

If not, you should probably consider connection pooling to reduce the
number of PG sessions to something approaching the number of CPUs/cores
you have in the system.

It's  possible only  with  application  server,


No, you can deploy PgPool or PgBouncer.
Apart from that, I just checked in my system. User sessions have size of 16M. 
Not 160M.


for local network thick client has reasonable advantages.
  We just can't implement today all functions on thin client.






--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt



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

Re: [GENERAL] Memory usage per session

[Melvin Davidson]

On Fri, Jul 8, 2016 at 10:07 AM,  wrote:

> Hi
>
> >> >> The test performs about 11K lines of code
> >> >> Oracle: about 5M
> >> >> postgreSql: about 160М
>
>
> > Do you have 100 CPUs on this system which apparently doesn't have 16G
> > of RAM available for PG to use?
> We can say at fact:
> We currently  work at oracle.
> Our code base about 4000 k line of code
> In out last project we have:
> 3000 current connection
> 200 active session
> So 16g it's very optimistic.
> Of course  we think about buy hardware or software.
> It's other question.
> So with this memory consumption it can be really cheaper to by Oracle.
> > If not, you should probably consider connection pooling to reduce the
> > number of PG sessions to something approaching the number of CPUs/cores
> > you have in the system.
> It's  possible only  with  application  server,
> for local network thick client has reasonable advantages.
>  We just can't implement today all functions on thin client.
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

What is the actual O/S that PostgreSQL is installed on?
How much total memory is on the server?
I would be very curious about the values you have  specified in
postgresql.conf?
 Also, what is the exact version of PostgreSQL you are using?
What is the total time to complete the test for all 3 DB's?
The best I can tell is that with all the unknowns, you are comparing apples
to oranges.

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

Re: [GENERAL] Memory usage per session

[AMatveev]

Title: Re: [GENERAL] Memory usage per session


Здравствуйте.

>> Oracle: about 5M
>> postgreSql: about 160М


>​I'm admittedly ignorant of this type of testing. But if the memory usage for PostgreSQL is in the server, perhaps due to caching (how to test?), then it likely would _not_ linearly scale up >as the number of clients increased because every clients >would share the same cache data within the server.​ Or are you measuring the memory usage where the client is on one machine and the >PostgreSQL server is a different machine, with the client machine getting the memory hit?

I can send zip file with test code on request;
To say the truth it's described in documentation:
https://www.postgresql.org/docs/9.5/static/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING
The PL/pgSQL interpreter parses the function's source text and produces an internal binary instruction tree the first time the function is called (within each session) 

It's very sad :(

The test is very simply:
Generate code:
DECLARE
  svSql "varchar";
BEGIN
  for nvi in 1..10
  loop
    svSql = 'CREATE OR REPLACE FUNCTION perfa."func'||nvi||'" (
)
RETURNS void AS
$body$
DECLARE
  svSql "varchar";
BEGIN
  svSql:=;
  PERFORM perfb."func'||(nvi-1)*10+1||'"();
  PERFORM perfb."func'||(nvi-1)*10+2||'"();
  PERFORM perfb."func'||(nvi-1)*10+3||'"();
  PERFORM perfb."func'||(nvi-1)*10+4||'"();
  PERFORM perfb."func'||(nvi-1)*10+5||'"();
  PERFORM perfb."func'||(nvi-1)*10+6||'"();
  PERFORM perfb."func'||(nvi-1)*10+7||'"();
  PERFORM perfb."func'||(nvi-1)*10+8||'"();
  PERFORM perfb."func'||(nvi-1)*10+9||'"();
  PERFORM perfb."func'||(nvi-1)*10+10||'"();         
END;
$body$
LANGUAGE ''plpgsql''
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER';
    EXECUTE svSql;
  end loop;
END;

Download jmetter
In 50 thread run this funciton.


About 4g memory will be consumed in one second 
It's hard to miss
:))

Re: [GENERAL] pasting a lot of commands to psql

[Tom Lane]

Francisco Olarte  writes:
> On Thu, Jul 7, 2016 at 8:18 PM, Alvaro Herrera  
> wrote:
>> I've wished sometimes for a "\set READLINE off" psql metacommand for
>> this kind of thing.  It's pretty annoying when the text being pasted
>> contains tabs and readline uses to do completion.

> Doesn't 'cat | psql ' disable it?

Sure, but you could as well use 'psql -n'.  I think the point is to be
able to turn it on and off without starting a fresh session.  (Admittedly,
maybe there's not a lot of usability gain there.)

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] Memory usage per session

[AMatveev]

Hi

>> >> The test performs about 11K lines of code
>> >> Oracle: about 5M
>> >> postgreSql: about 160М


> Do you have 100 CPUs on this system which apparently doesn't have 16G
> of RAM available for PG to use?
We can say at fact:
We currently  work at oracle.
Our code base about 4000 k line of code
In out last project we have:
3000 current connection
200 active session
So 16g it's very optimistic.
Of course  we think about buy hardware or software.
It's other question.
So with this memory consumption it can be really cheaper to by Oracle.
> If not, you should probably consider connection pooling to reduce the
> number of PG sessions to something approaching the number of CPUs/cores
> you have in the system.
It's  possible only  with  application  server,
for local network thick client has reasonable advantages.
 We just can't implement today all functions on thin client.



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

Re: [GENERAL] Memory usage per session

[Stephen Frost]

* amatv...@bitec.ru (amatv...@bitec.ru) wrote:
> > On 08/07/2016 14:11, amatv...@bitec.ru wrote:
> >> The test performs about 11K lines of code
> >> Memory usage per session:
> >> Oracle: about 5M
> >> MSSqlServer: about 4M
> >> postgreSql: about 160М
> 
> > Visual C???
> > You will have to run PostgreSQL on a proper Unix system to test for 
> > performance.
> Of cause we understand that unix is faster( damn fork :)))
> Our Current problem is memory:
>  (160m vs 5M) * 100 sessions  = 16G vs 0.5G
> We just can get "out of memory".
> :(((

Do you have 100 CPUs on this system which apparently doesn't have 16G
of RAM available for PG to use?

If not, you should probably consider connection pooling to reduce the
number of PG sessions to something approaching the number of CPUs/cores
you have in the system.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [GENERAL] Memory usage per session

[John McKown]

On Fri, Jul 8, 2016 at 8:16 AM,  wrote:

> Hi
>
> > On 08/07/2016 14:11, amatv...@bitec.ru wrote:
> >> Hi.
> >> The test performs about 11K lines of code
> >> Memory usage per session:
> >> Oracle: about 5M
> >> MSSqlServer: about 4M
> >> postgreSql: about 160М
>
>
> > Visual C???
> > You will have to run PostgreSQL on a proper Unix system to test for
> performance.
> Of cause we understand that unix is faster( damn fork :)))
> Our Current problem is memory:
>  (160m vs 5M) * 100 sessions  = 16G vs 0.5G
> We just can get "out of memory".
> :(((
>

​I'm admittedly ignorant of this type of testing. But if the memory usage
for PostgreSQL is in the server, perhaps due to caching (how to test?),
then it likely would _not_ linearly scale up as the number of clients
increased because every clients would share the same cache data within the
server.​ Or are you measuring the memory usage where the client is on one
machine and the PostgreSQL server is a different machine, with the client
machine getting the memory hit?

-- 
"Pessimism is a admirable quality in an engineer. Pessimistic people check
their work three times, because they're sure that something won't be right.
Optimistic people check once, trust in Solis-de to keep the ship safe, then
blow everyone up."
"I think you're mistaking the word optimistic for inept."
"They've got a similar ring to my ear."

>From "Star Nomad" by Lindsay Buroker:

Maranatha! <><
John McKown

Re: [GENERAL] Memory usage per session

[Pavel Stehule]

Hi

2016-07-08 15:16 GMT+02:00 :

> Hi
>
> > On 08/07/2016 14:11, amatv...@bitec.ru wrote:
> >> Hi.
> >> The test performs about 11K lines of code
> >> Memory usage per session:
> >> Oracle: about 5M
> >> MSSqlServer: about 4M
> >> postgreSql: about 160М
>
>
> > Visual C???
> > You will have to run PostgreSQL on a proper Unix system to test for
> performance.
> Of cause we understand that unix is faster( damn fork :)))
> Our Current problem is memory:
>  (160m vs 5M) * 100 sessions  = 16G vs 0.5G
> We just can get "out of memory".
> :(((
>

The almost session memory is used for catalog caches. So you should to have
big catalog and long living sessions.

What do you do exactly?

Regards

Pavel

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

Re: [GENERAL] Memory usage per session

[AMatveev]

Hi

> On 08/07/2016 14:11, amatv...@bitec.ru wrote:
>> Hi.
>> The test performs about 11K lines of code
>> Memory usage per session:
>> Oracle: about 5M
>> MSSqlServer: about 4M
>> postgreSql: about 160М


> Visual C???
> You will have to run PostgreSQL on a proper Unix system to test for 
> performance.
Of cause we understand that unix is faster( damn fork :)))
Our Current problem is memory:
 (160m vs 5M) * 100 sessions  = 16G vs 0.5G
We just can get "out of memory".
:(((



-- 
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] [BUGS] Where clause in pg_dump: need help

[Sameer Kumar]

On Fri, 8 Jul 2016, 8:06 p.m. Prashanth Adiyodi, 
wrote:

> Hi Sameer, Please see comments inline
>
>
>
>
>
> *Prashanth Adiyodi  *
>
> *Technical Account Manager*
>
> *Skype: prashanth.adiyodi*
>
> *Mob: +91-9819488395*
>
> [image: celltick]
>
>
>
>
>
>
>
> *From:* Sameer Kumar [mailto:sameer.ku...@ashnik.com]
> *Sent:* Friday, July 08, 2016 3:18 PM
> *To:* Francisco Olarte; Prashanth Adiyodi
> *Cc:* pgsql-general@postgresql.org
> *Subject:* Re: [GENERAL] [BUGS] Where clause in pg_dump: need help
>
>
>
>
>
> On Fri, Jul 8, 2016 at 5:38 PM Francisco Olarte 
> wrote:
>
> 1.- CCing to the list ( remember to hit reply-all or whatever your MUA
> uses for that, otherwise threads may get lost ).
>
> 2.- Try to avoid top-posting, it's not the style of the list ( or mine ).
>
> On Fri, Jul 8, 2016 at 4:43 AM, Prashanth Adiyodi
>  wrote:
> > Basically my requirement is, I have a live Db with certain tables and a
> backup Db at another location (both on postgressql).
>
>
>
> Both databases are PostgreSQL (?). What version?
>
> Yes, Both are postgres SQL, ver 9.3.4
>
>
>
> I need to take a backup of this live DB every night for the previous day
> (i.e the backup script running on 07/07/2016 will take the backup of the DB
> for 06/07/2016).
>
>
>
> Does this need to be done for one table or multiple tables?
>
> Multiple tables
>
>
>
> This backup will be then transferred to the backup DB server and will be
> inserted into that DB.
>
>
>
> What will you be doing on the target database? Is it a read-only database?
>
> It is not a read only database
>
>
>
> From what I have read pg_dump is the solution (similar to export in
> oracle), do you think of any other approach to get to this objective, have
> you come across a script or something that already does this,
>
>
>
> May be you can use
> psql -c "COPY (SELECT .. WHERE..) TO stdout" | psql -c "COPY (mytable)
> FROM stdin"
>
>
>
> OR
> Setup replication and have a scheduled script to set recovery_target_time
> and puase_at_recovery_target to effectively replicate changes from one DB
> to other DB and maintaining a gap. But then the targetDB would be a read
> only replica and needs to be exactly same at the main DB/sourceDB
>
>
>
> You need to explain more on version of the database, what exactly you aim
> at doing with the target DB.
>
> Hi, the requirement is this, I have multiple tables where there may not be
> a timestamp column. I need to run a script which will execute post-midnight
> say at 2 AM and create a dump file (say data.sql), which will have records
> for all the previous day. I will then transfer this file to the target
> server and dump this data there, the idea is to create two copies of the
> data in case of a disaster on the original database server.
>

So it is more like a DR server which always lags behind the master by a day
or is at mid night time of previous day.

Above you have mentioned this target db server (which I assume serves the
purpose of DR) is not read-only(?). What kind of writes will you be doing
on this servers?

>
>
> Your requirement is a bit 'understated'. I assume your problem is:
>
> 1.- You have a backup with a series of tables which get inserted WITH
> a timestamp.
> 2.- At the end of the day you want to transfer the inserted data, and
> only the inserted data, to another server and insert it ther.
>
> If BOTH servers are postgres, you can do it easily with a series of
> COPY commands easily. If the target one is not postgres I would use it
> too, but pass the COPY data through a perl script to generate whatever
> syntax the target DB needs ( I've done that to go from postgres to sql
> server and back using freebcp, IIRC, on the sql server side )
>
> You still can have problems IF you have updates to the tables, or
> deletions, or . But
> if you just have insertions, copy is easy to do.
>
> Francisco Olarte.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
> --
>
> --
>
> Best Regards
>
> Sameer Kumar | DB Solution Architect
>
> *ASHNIK PTE. LTD.*
>
> 101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533
>
> T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com
>
-- 
--
Best Regards
Sameer Kumar | DB Solution Architect
*ASHNIK PTE. LTD.*

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533

T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com

Re: [GENERAL] Memory usage per session

[Achilleas Mantzios]

On 08/07/2016 14:11, amatv...@bitec.ru wrote:

Hi.
We have tested postgreSql,Oracle,MSSqlServer.
The test performs about 11K lines of code
Memory usage per session:
Oracle: about 5M
MSSqlServer: about 4M
postgreSql: about 160М
The result of postgreSql is very sad(Our typical business logic has about 30K 
lines of code).
How can I reduce memory consumption per session?
Note, we cant move the business logic to an application server as it will lead 
us to performance problem.

I can send the test script on request.

Test description:
PostgreSQL 9.5.3, compiled by Visual C build 1800, 64-bit


Visual C???
You will have to run PostgreSQL on a proper Unix system to test for performance.


At the same time we run 50 sessions that perform the following functions:
CREATE OR REPLACE FUNCTION perfa.func9
...
BEGIN
   svSql:='';
   PERFORM perfb."func91"();
   ...
END;

CREATE OR REPLACE FUNCTION perfb.func91
...
BEGIN
   PERFORM perfc."func911"();
   ...
END;

CREATE OR REPLACE FUNCTION perfc.func911 (
)
RETURNS void AS
$body$
DECLARE
   svSql BIGINT;
BEGIN
   svSql:=0;
   ...
   svSql:=svSql+10;
END;
$body$






--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt



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

[GENERAL] Memory usage per session

[AMatveev]

Hi.
We have tested postgreSql,Oracle,MSSqlServer.
The test performs about 11K lines of code
Memory usage per session:
Oracle: about 5M
MSSqlServer: about 4M
postgreSql: about 160М
The result of postgreSql is very sad(Our typical business logic has about 30K 
lines of code).
How can I reduce memory consumption per session?
Note, we cant move the business logic to an application server as it will lead 
us to performance problem.  

I can send the test script on request.

Test description:
PostgreSQL 9.5.3, compiled by Visual C build 1800, 64-bit
At the same time we run 50 sessions that perform the following functions:
CREATE OR REPLACE FUNCTION perfa.func9
...
BEGIN
  svSql:='';
  PERFORM perfb."func91"();
  ...  
END;

CREATE OR REPLACE FUNCTION perfb.func91 
...
BEGIN
  PERFORM perfc."func911"();
  ... 
END;

CREATE OR REPLACE FUNCTION perfc.func911 (
)
RETURNS void AS
$body$
DECLARE
  svSql BIGINT;
BEGIN
  svSql:=0;
  ...
  svSql:=svSql+10;   
END;
$body$



-- 
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] pasting a lot of commands to psql

[Francisco Olarte]

Hi:

On Thu, Jul 7, 2016 at 8:18 PM, Alvaro Herrera  wrote:
>> You might have better luck with "psql -n", or maybe not.
> I've wished sometimes for a "\set READLINE off" psql metacommand for
> this kind of thing.  It's pretty annoying when the text being pasted
> contains tabs and readline uses to do completion.

Doesn't 'cat | psql ' disable it? I use it with other programs for
these purpose ( as well as things like ls | cat to avoid
colors/wordwrapping, just makes the program see a non-tty on
stidn/stdout ).

Francisco Olarte.


-- 
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] [BUGS] Where clause in pg_dump: need help

[Sameer Kumar]

On Fri, Jul 8, 2016 at 5:38 PM Francisco Olarte 
wrote:

> 1.- CCing to the list ( remember to hit reply-all or whatever your MUA
> uses for that, otherwise threads may get lost ).
>
> 2.- Try to avoid top-posting, it's not the style of the list ( or mine ).
>
> On Fri, Jul 8, 2016 at 4:43 AM, Prashanth Adiyodi
>  wrote:
> > Basically my requirement is, I have a live Db with certain tables and a
> backup Db at another location (both on postgressql).


Both databases are PostgreSQL (?). What version?


> I need to take a backup of this live DB every night for the previous day
> (i.e the backup script running on 07/07/2016 will take the backup of the DB
> for 06/07/2016).


Does this need to be done for one table or multiple tables?


> This backup will be then transferred to the backup DB server and will be
> inserted into that DB.


What will you be doing on the target database? Is it a read-only database?



> From what I have read pg_dump is the solution (similar to export in
> oracle), do you think of any other approach to get to this objective, have
> you come across a script or something that already does this,
>
>
May be you can use
psql -c "COPY (SELECT .. WHERE..) TO stdout" | psql -c "COPY (mytable) FROM
stdin"

OR
Setup replication and have a scheduled script to set recovery_target_time
and puase_at_recovery_target to effectively replicate changes from one DB
to other DB and maintaining a gap. But then the targetDB would be a read
only replica and needs to be exactly same at the main DB/sourceDB

You need to explain more on version of the database, what exactly you aim
at doing with the target DB.


> Your requirement is a bit 'understated'. I assume your problem is:
>
> 1.- You have a backup with a series of tables which get inserted WITH
> a timestamp.
> 2.- At the end of the day you want to transfer the inserted data, and
> only the inserted data, to another server and insert it ther.
>
> If BOTH servers are postgres, you can do it easily with a series of
> COPY commands easily. If the target one is not postgres I would use it
> too, but pass the COPY data through a perl script to generate whatever
> syntax the target DB needs ( I've done that to go from postgres to sql
> server and back using freebcp, IIRC, on the sql server side )
>
> You still can have problems IF you have updates to the tables, or
> deletions, or . But
> if you just have insertions, copy is easy to do.
>
> Francisco Olarte.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
-- 
--
Best Regards
Sameer Kumar | DB Solution Architect
*ASHNIK PTE. LTD.*

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533

T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com

Re: [GENERAL] [BUGS] Where clause in pg_dump: need help

[Francisco Olarte]

1.- CCing to the list ( remember to hit reply-all or whatever your MUA
uses for that, otherwise threads may get lost ).

2.- Try to avoid top-posting, it's not the style of the list ( or mine ).

On Fri, Jul 8, 2016 at 4:43 AM, Prashanth Adiyodi
 wrote:
> Basically my requirement is, I have a live Db with certain tables and a 
> backup Db at another location (both on postgressql). I need to take a backup 
> of this live DB every night for the previous day (i.e the backup script 
> running on 07/07/2016 will take the backup of the DB for 06/07/2016). This 
> backup will be then transferred to the backup DB server and will be inserted 
> into that DB. From what I have read pg_dump is the solution (similar to 
> export in oracle), do you think of any other approach to get to this 
> objective, have you come across a script or something that already does this,

Your requirement is a bit 'understated'. I assume your problem is:

1.- You have a backup with a series of tables which get inserted WITH
a timestamp.
2.- At the end of the day you want to transfer the inserted data, and
only the inserted data, to another server and insert it ther.

If BOTH servers are postgres, you can do it easily with a series of
COPY commands easily. If the target one is not postgres I would use it
too, but pass the COPY data through a perl script to generate whatever
syntax the target DB needs ( I've done that to go from postgres to sql
server and back using freebcp, IIRC, on the sql server side )

You still can have problems IF you have updates to the tables, or
deletions, or . But
if you just have insertions, copy is easy to do.

Francisco Olarte.


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