Real application clustering in postgres.

Hi team, Is there any possibility/options to setup a real application clustering in Postgres as in Oracle we have a RAC feature. What about multi-master replication in Postgres. would you please suggest how it is useful and how can setup it. Thanks.

Re: Performance Problem

taking a quick glance at config file I do not see any modifications to any key settings shared_buffers, efffecttive cache size work_mem meaning the server is running at the default settings which results in horrible performance here is a website that gives suggested config changes based on serve

Re: Performance Problem

On Wed, Mar 4, 2020 at 6:17 PM Kordexa Stroken wrote: > Hello iam working on a project and using Postgresql 10.My main server 2 x > E5 2680 v3 Cpu NVMe M.2 500 Gb Disk 32 GB Ram and my postgresql server is a > VM(VirtualBox) Centos7 16GB ram Postgresql 10 Db size 4 GB and my solution > is N-Tier

Re: `DROP DATABASE RESTRICT` ?

On Wed, Mar 4, 2020 at 6:17 PM Brennan Vincent wrote: > Hello all, > > I was wondering if there is some reason why Postgres doesn't support a > `RESTRICT` option that would refuse to drop a database if it contains > any schemas or objects, similarly to the semantics of `DROP SCHEMA > [...] RESTRI

`DROP DATABASE RESTRICT` ?

Hello all, I was wondering if there is some reason why Postgres doesn't support a `RESTRICT` option that would refuse to drop a database if it contains any schemas or objects, similarly to the semantics of `DROP SCHEMA [...] RESTRICT`. Is there a fundamental reason not to support it, or is it jus

Performance Problem

Hello iam working on a project and using Postgresql 10.My main server 2 x E5 2680 v3 Cpu NVMe M.2 500 Gb Disk 32 GB Ram and my postgresql server is a VM(VirtualBox) Centos7 16GB ram Postgresql 10 Db size 4 GB and my solution is N-Tier Architecture & 40 online user . My postgresql.conf here: h

Re: Determining the type of an obkect in plperl

how can I determine what the data type of the value element is? perl has a ref function which can tell what type of object. https://perldoc.perl.org/functions/ref.html -- This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus

Re: Advice request : simultaneous function/data updates on many databases

On 04/03/20, David G. Johnston (david.g.johns...@gmail.com) wrote: > On Wed, Mar 4, 2020 at 4:41 PM Rory Campbell-Lange > wrote: > > > Any idea on how to run execute_dynamic across many databases at roughly > > the same time? > > > > I'm just wondering if Guyren Howe's idea of having many transac

Re: Determining the type of an obkect in plperl

On Wed, Mar 4, 2020 at 4:21 PM stan wrote: > Probably a bit off topic, but I suspect someone on this list knows how to > do > this. > > I am in the process of writing a plperl function. In this function I need > to compare the data in the NEW versus OLD structures. I am writing this as > a > gene

Re: Advice request : simultaneous function/data updates on many databases

On Wed, Mar 4, 2020 at 4:41 PM Rory Campbell-Lange wrote: > Any idea on how to run execute_dynamic across many databases at roughly > the same time? > > I'm just wondering if Guyren Howe's idea of having many transactions > open waiting for a clock time to commit is in fact feasible due to > (pre

Re: Advice request : simultaneous function/data updates on many databases

On 04/03/20, David G. Johnston (david.g.johns...@gmail.com) wrote: > On Wed, Mar 4, 2020 at 3:55 PM David G. Johnston > wrote: > > > On Wed, Mar 4, 2020 at 3:48 PM Rory Campbell-Lange < > > r...@campbell-lange.net> wrote: > > > >> Any thoughts on how to wrap pl/pgsql function dropping and recreat

Re: Advice request : simultaneous function/data updates on many databases

On 3/4/20 2:42 PM, Guyren Howe wrote: On Mar 4, 2020, at 14:33 , Rory Campbell-Lange > wrote: Essentially we wish to reduce the window where the frontend and backend aren't synchronised. If we have (for example) 200 databases which each take 2 seconds to update,

Determining the type of an obkect in plperl

Probably a bit off topic, but I suspect someone on this list knows how to do this. I am in the process of writing a plperl function. In this function I need to compare the data in the NEW versus OLD structures. I am writing this as a generic subroutine, so I am looping through and comparing the 2

Re: Advice request : simultaneous function/data updates on many databases

On Wed, Mar 4, 2020 at 3:55 PM David G. Johnston wrote: > On Wed, Mar 4, 2020 at 3:48 PM Rory Campbell-Lange < > r...@campbell-lange.net> wrote: > >> Any thoughts on how to wrap pl/pgsql function dropping and recreation code >> within a wrapper pl/pgsql function? > > > Not endorsing this but dyna

Re: Advice request : simultaneous function/data updates on many databases

On Wed, Mar 4, 2020 at 3:48 PM Rory Campbell-Lange wrote: > Any thoughts on how to wrap pl/pgsql function dropping and recreation code > within a wrapper pl/pgsql function? Not endorsing this but dynamic SQL works just fine (though can get hard to read). Use format() and EXECUTE ... USING libe

Re: Advice request : simultaneous function/data updates on many databases

On 04/03/20, Guyren Howe (guy...@gmail.com) wrote: > On Mar 4, 2020, at 14:33 , Rory Campbell-Lange > wrote: > > > > Essentially we wish to reduce the window where the frontend and backend > > aren't synchronised. > > > > If we have (for example) 200 databases which each take 2 seconds to > > u

Re: Advice request : simultaneous function/data updates on many databases

On Mar 4, 2020, at 14:33 , Rory Campbell-Lange wrote: > > Essentially we wish to reduce the window where the frontend and backend > aren't synchronised. > > If we have (for example) 200 databases which each take 2 seconds to > update, a client could be on the wrong frontend code for over 6 minut

Re: Advice request : simultaneous function/data updates on many databases

On 3/4/20 4:33 PM, Rory Campbell-Lange wrote: On 04/03/20, Adrian Klaver (adrian.kla...@aklaver.com) wrote: On 3/4/20 2:22 PM, Rory Campbell-Lange wrote: On 04/03/20, Adrian Klaver (adrian.kla...@aklaver.com) wrote: On 3/4/20 2:04 PM, Rory Campbell-Lange wrote: We have many databases of the s

Re: Advice request : simultaneous function/data updates on many databases

On 04/03/20, Adrian Klaver (adrian.kla...@aklaver.com) wrote: > On 3/4/20 2:22 PM, Rory Campbell-Lange wrote: > > On 04/03/20, Adrian Klaver (adrian.kla...@aklaver.com) wrote: > > > On 3/4/20 2:04 PM, Rory Campbell-Lange wrote: > > > > We have many databases of the same type separated for data gove

Re: Advice request : simultaneous function/data updates on many databases

On Wed, Mar 4, 2020 at 3:04 PM Rory Campbell-Lange wrote: > However, for larger updates, the time when the front end code is > out-of-step with the database can cause end-user problems. > You should try very hard to structure your database migrations so that instead of going directly from unique

Re: Advice request : simultaneous function/data updates on many databases

On 3/4/20 2:22 PM, Rory Campbell-Lange wrote: On 04/03/20, Adrian Klaver (adrian.kla...@aklaver.com) wrote: On 3/4/20 2:04 PM, Rory Campbell-Lange wrote: We have many databases of the same type separated for data governance reasons. They, however, share the same web front-end code. Presently,

Re: Advice request : simultaneous function/data updates on many databases

On 04/03/20, Adrian Klaver (adrian.kla...@aklaver.com) wrote: > On 3/4/20 2:04 PM, Rory Campbell-Lange wrote: > > We have many databases of the same type separated for data governance > > reasons. They, however, share the same web front-end code. > > > > Presently, replacing functions and performi

Re: Advice request : simultaneous function/data updates on many databases

On 3/4/20 2:04 PM, Rory Campbell-Lange wrote: We have many databases of the same type separated for data governance reasons. They, however, share the same web front-end code. Presently, replacing functions and performing data updates on the databases in series often executes across all databases

Advice request : simultaneous function/data updates on many databases

We have many databases of the same type separated for data governance reasons. They, however, share the same web front-end code. Presently, replacing functions and performing data updates on the databases in series often executes across all databases in less than a minute. (The updates are current

Re: Suggestion to reduce COPY command output to csv file

On Wed, Mar 4, 2020 at 11:39 AM postggen2020 s wrote: > Hi Team, > Thanks a lot all of you, for providing support peoples like me. > Could you please provide a suggestion on COPY command. > > Environment: > DB Version:9.5.15 > postgis: > Table contain GIS data. > > I have a table with GIS data, i

Re: Postgres on macOS 10

> On Mar 3, 2020, at 5:06 AM, Nick Renders wrote: > > In the system.log we see the following 2 lines over and over again, until a > user has logged in: > > Mar 3 09:37:19 postgrestest com.apple.xpc.launchd[1] > (com.edb.launchd.postgresql-12[319]): Service exited with abnormal code: 2

Re: Suggestion to reduce COPY command output to csv file

On 3/4/20 10:38 AM, postggen2020 s wrote: Hi Team, Thanks a lot all of you, for providing support peoples like me. Could you please provide a suggestion on COPY command. Environment: DB Version:9.5.15 postgis: Table contain GIS data. I have a table with GIS data, its around 300MB and 2Lacks+ rec

Suggestion to reduce COPY command output to csv file

Hi Team, Thanks a lot all of you, for providing support peoples like me. Could you please provide a suggestion on COPY command. Environment: DB Version:9.5.15 postgis: Table contain GIS data. I have a table with GIS data, its around 300MB and 2Lacks+ records. I want to export all the records to t

Re: Logical replication lag in seconds

For the records - with a simple script I hacked a solution which is purely based on the server. 1. Create a table to track the timestamp of an lsn: CREATE TABLE lsn2date( lsn pg_lsn PRIMARY KEY, seen timestamp NOT NULL DEFAULT NOW() ); CREATE ROLE replication_lag_user WITH LOGIN PASSWORD 'x

Re: pg_dump and public schema

On 3/4/20 5:42 AM, Олег Самойлов wrote: Hi all. PostgresQL 12.1. Strange behaviour with pg_dump and absent public schema. I droped public schema and I work under "username" schema. => \dn List of schemas Name | Owner ---+--- olleg | olleg (1 row) Dump now pg_dump -U postgres -C

Re: Calling a function from a rule?

On Wed, Mar 04, 2020 at 03:04:34PM +0100, Vik Fearing wrote: > On 04/03/2020 14:05, stan wrote: > > I am missing something about how to properly create a rule. > > What you are missing is that you should not ever use RULEs. > > > Thanks to the helpful folks on this list, I am looking a creating s

Re: Calling a function from a rule?

On 04/03/2020 14:05, stan wrote: > I am missing something about how to properly create a rule. What you are missing is that you should not ever use RULEs. > Thanks to the helpful folks on this list, I am looking a creating some > update able views. So, looks like I need to create a rule and a fun

Re: Exportacion por lotes

Muchas gracias a todos. El mar., 3 mar. 2020 a las 21:58, Alvaro Herrera () escribió: > On 2020-Mar-03, Hernan Jesus Gonzalez Carmona wrote: > > > Estimados antes que todo me presento, mi nombre es Hernan Gonzalez, me > > acabo de inscribir en esta lista de correo y desde ya me disculpo si en > >

Re: pg_dump and public schema

Hi, I think "create database" always creates the "public" schema. So, all is well. All you have to do is drop it after running the dump.sql script. Cheers, Paul On Wed, Mar 4, 2020 at 2:43 PM Олег Самойлов wrote: > > Hi all. PostgresQL 12.1. Strange behaviour with pg_dump and absent public > s

pg_dump and public schema

Hi all. PostgresQL 12.1. Strange behaviour with pg_dump and absent public schema. I droped public schema and I work under "username" schema. => \dn List of schemas Name | Owner ---+--- olleg | olleg (1 row) Dump now pg_dump -U postgres -C olleg >dump.sql -- -- PostgreSQL database d

Re: Calling a function from a rule?

On Wed, Mar 04, 2020 at 08:05:06AM -0500, stan wrote: > I am missing something about how to properly create a rule. > > Thanks to the helpful folks on this list, I am looking a creating some > update able views. So, looks like I need to create a rule and a function > for this. Here is what I am tr

Calling a function from a rule?

I am missing something about how to properly create a rule. Thanks to the helpful folks on this list, I am looking a creating some update able views. So, looks like I need to create a rule and a function for this. Here is what I am trying as a test. DROP TRIGGER v_trig_test ON test; CREATE OR

Re: Use of perl modules in plperl ?

On Wed, Mar 04, 2020 at 06:03:22AM -0500, stan wrote: > I am trying to examine the values of $_TD->{new}. I thought the easiest way > to see what this structure looked like was to use the Dumper functionality. > To do so I need to include the appropriate Perl module, which I would think > would be

Re: Use of perl modules in plperl ?

On Wed, Mar 04, 2020 at 06:03:22AM -0500, stan wrote: > I am trying to examine the values of $_TD->{new}. I thought the easiest way > to see what this structure looked like was to use the Dumper functionality. > To do so I need to include the appropriate Perl module, which I would think > would be

Use of perl modules in plperl ?

I am trying to examine the values of $_TD->{new}. I thought the easiest way to see what this structure looked like was to use the Dumper functionality. To do so I need to include the appropriate Perl module, which I would think would be done like this: use Data::Dumper qw(Dumper); But inserting t