Re: [GENERAL] sync the data's from catalog table

the above scenario in postgres? if it there please guide me how to proceed on this. and one more question, Can we restrict the postgres user to not modify the pg_authid table. Please share your thoughts on it. Thanks On Mon, Nov 13, 2017 at 2:12 PM, Laurenz Albe <laurenz.a...@cybertec.at>

[GENERAL] sync the data's from catalog table

Hi Team, How can I sync the data's from pg_authid to manually created table (user table) whenever the update or insert happens on pg_authid table. Thanks Dinesh Kumar

Re: [GENERAL] Postgres HA

Hi Dylan, On Thu, Feb 23, 2017 at 4:28 AM, Dylan Luong wrote: > Hi > > > > I am a DBA at the University of South Australia. For PostgreSQL High > Availability, we currently have setup a Master/Slave across two datacenters > using PostgreSQL (WAL) streaming replication.

[GENERAL] Re: [GENERAL] “Loop” in plpgsql Function - PostgreSQL 9.2

Hello, On Mon, Jan 18, 2016 at 1:37 PM, drum.lu...@gmail.com wrote: > I've created a function that allows me to do an huge update. > > But I need to limit this function. I need to do 50k rows (example) and > then stop it. After that I need to continue from the rows that

Re: [GENERAL] SQL conversion tool

On Wed, Nov 18, 2015 at 10:58 AM, dinesh kumar <dineshkuma...@gmail.com> wrote: > On Wed, Nov 18, 2015 at 10:41 AM, Sachin Srivastava < > ssr.teleat...@gmail.com> wrote: > >> Hi, >> >> Please inform which is the best tool for SQL conversion because

Re: [GENERAL] SQL conversion tool

On Wed, Nov 18, 2015 at 11:24 AM, Thomas Kellerer wrote: > Sachin Srivastava schrieb am 18.11.2015 um 10:41: > > > Please inform which is the best tool for SQL conversion because I have > to migration Oracle database into PostgreSQL. > > Ora2Pg works quite well

Re: [GENERAL] SQL conversion tool

On Wed, Nov 18, 2015 at 10:41 AM, Sachin Srivastava wrote: > Hi, > > Please inform which is the best tool for SQL conversion because I have to > migration Oracle database into PostgreSQL. > > Pentaho is the tool you need to have a look. Also, talend works great. You

Re: [GENERAL] Query regarding

Hi, On Wed, Nov 4, 2015 at 1:21 AM, M Tarkeshwar Rao < m.tarkeshwar@ericsson.com> wrote: > Hi all, > > We have started to convert some oracle sql scripts and converting them to > postgres, but facing some issues to create table. > > I have some common doubts in create table script ie. > >

Re: [GENERAL] function null composite behavior

On Mon, Oct 26, 2015 at 3:34 PM, Rikard Pavelic wrote: > On Sun, 25 Oct 2015 22:31:03 +0100 > Rikard Pavelic wrote: > > > I assume there is no way to get sql like result from plpgsql > > function? > > I should try harder ;) > > Managed to get it working using

Re: [GENERAL] PSQL Tools

On Sun, Oct 18, 2015 at 7:04 AM, wrote: > Hello > > Is anyone aware of any tools like TOAD that are available for Postgresql? > > PgAdmin fits here. > Regards > > John Wiencek > -- Regards, Dinesh manojadinesh.blogspot.com

Re: [GENERAL] converting in() clause into a with prefix?

On Fri, Oct 16, 2015 at 11:18 AM, Benjamin Smith wrote: > I have a horribly-performing query similar to below, and I'd like to > convert > it to use a "WITH mytable as ( ... ) " without having to re-architect my > code. > For some reason, using a WITH prefix seems to

Re: [GENERAL] Creating Report for PieChart

+Adding to Scott On Tue, Oct 13, 2015 at 6:25 PM, Scott Mead wrote: > > > > On Tue, Oct 13, 2015 at 9:14 PM, Scott Mead wrote: > >> >> >> On Oct 13, 2015, at 19:56, Alex Magnum wrote: >> >> Hello, >> I need to process some

Re: [GENERAL] Pattern match against array elements?

On Mon, Oct 12, 2015 at 10:58 AM, Israel Brewster wrote: > Is there any way to do a pattern match against the elements of an array in > postgresql (9.4 if the version makes a difference)? I have a grouped query > that, among other things, returns an array of values, like:

Re: [GENERAL] Database size on disk

On Mon, Oct 5, 2015 at 12:47 PM, Quiroga, Damian wrote: > Hi, > > > > I’m looking for some advice on how to determine the DB size on disk to > trigger some cleanup tasks (deleting old data) if some threshold is reached. > > > > Let’s say that for simplicity I don’t want

Re: [GENERAL] Unexpected query result

Hi, On Mon, Oct 5, 2015 at 5:02 AM, Begin Daniel wrote: > In order to process a large amount of data I need to run a procedure using > parallel batch processes. > The query I run in each process is expected to ... > > It seems, you are trying to achieve the same, what we

Re: [GENERAL] Fwd: postgresql databases disappearing and reappearing and disappearing on localhost - Postgresql x649.3 on Windows 8.1 (64)

On Tue, Aug 4, 2015 at 2:28 PM, Killian Driscoll killiandrisc...@gmail.com wrote: I am using postgresql 9.3 with pgadmin III, and Access 2013 as a front end for data entry using (ODBC connection) linked tables, on a Windows 8.1 (64). OK. I have one main database I am developing on a

Re: [GENERAL] Sorting CSV string and removing Duplicates

On Mon, Jul 27, 2015 at 12:57 PM, dinesh kumar dineshkuma...@gmail.com wrote: On Mon, Jul 27, 2015 at 12:53 PM, Alex Magnum magnum11...@gmail.com wrote: Hello, I have a csv string in a text field that is unsorted and contains duplicates. Is there a simple way to remove these and sort

Re: [GENERAL] Sorting CSV string and removing Duplicates

On Mon, Jul 27, 2015 at 12:53 PM, Alex Magnum magnum11...@gmail.com wrote: Hello, I have a csv string in a text field that is unsorted and contains duplicates. Is there a simple way to remove these and sort the string. E.g 2,18,20,23,1,27,1,2,8,16,17,18,20,22,23,27 i tried string to

Re: [GENERAL] Oracle to PostgreSQL Migration - Need Information

Hi, We recently done the similar migration for one of our customer. We used all opensource tools to achieve this migration process. We used Pentaho Data Integration tool for doing Online DB migration, which took minimal downtime with CDC{Change Data Capture} approach. Also, we used Ora2Pg tool

Re: [GENERAL] Oracle to PostgreSQL Migration - Need Information

On Wed, Jul 8, 2015 at 1:20 PM, John R Pierce pie...@hogranch.com wrote: On 7/8/2015 1:16 PM, dinesh kumar wrote: We recently done the similar migration for one of our customer. We used all opensource tools to achieve this migration process. We used Pentaho Data Integration tool for doing

Re: [GENERAL] postgresql93-9.3.5: deadlock when updating parent table expected?

Hi, If you feel FOR UPDATE is taking much time, then I believe,we can solve this kind of issues using advisory locks http://www.postgresql.org/docs/9.1/static/explicit-locking.html, . Regards, Dinesh manojadinesh.blogspot.com On Wed, Feb 18, 2015 at 10:45 AM, Bill Moran

Re: [GENERAL] Fwd: Data corruption after restarting replica

Hi, On Mon, Feb 16, 2015 at 2:44 AM, Novák, Petr nov...@avast.com wrote: Hello, sorry for posting to second list, but as I've received no reply there, I'm trying my luck here. Thanks Petr -- Forwarded message -- From: Novák, Petr nov...@avast.com Date: Tue, Feb 10,

Re: [GENERAL] BAKUP ISSUE

Hi, On Wed, Jul 9, 2014 at 2:43 PM, Ramesh T rameshparnandit...@gmail.com wrote: i rune problem is when i run the pg_stop_backup() its return error in this way postgres=# select pg_stop_backup(); *NOTICE: pg_stop_backup cleanup done, waiting for required WAL segments to be ar*

Re: [GENERAL] Insert query hangs what could be the reason

On Thu, Jun 19, 2014 at 1:59 PM, M Tarkeshwar Rao m.tarkeshwar@ericsson.com wrote: Hi, Insert query hangs what could be the reason. Is there any way to find out? Any timeout feature there with query which can be set at client or server end? It might be due to concurrent primary

Re: [GENERAL] Monitoring Streaming Replication in 9.2

Hi, Herehttp://eulerto.blogspot.in/2011/11/understanding-wal-nomenclature.htmlis the blog which has good explanation about this. If you want to find the lag in seconds, then you need to execute something like below. SELECT pg_last_xact_replay_timestamp() - now(); Regards, Dinesh

Re: [GENERAL] break table into portions for writing to separate files

Hi, Does the below kind of approach work for you. I haven't tested this, but would like to give an idea something like below. Create a plpgsql function which takes 3 parameters as From Date, To Date and Interval. prev_interval := '0'::interval; LOOP IF ( From Date + Interval = To Date) THEN

Re: [GENERAL] authentication failure

Hi, On Fri, Jan 3, 2014 at 3:32 PM, Jayadevan M maymala.jayade...@gmail.comwrote: Nope - psql -W psql: FATAL: password authentication failed for user postgres There might be possible of the user's password expiration. Make the user's local authentication as trust, and reload the postgres

Re: [GENERAL] authentication failure

On Fri, Jan 3, 2014 at 5:13 PM, Jayadevan M maymala.jayade...@gmail.comwrote: I am able to login from the non-chroot environment. So it is not an issue with pg_hba.conf and not an issue of password expiration. Is there a debug psql option? OK. Have you checked the PGPASSWORD environment

Re: [GENERAL] [pgadmin-support] Database has been killed after increasing memory

Hi, +Adding postgresql general list. Hope, some one will provide more information. On Mon, Dec 23, 2013 at 1:56 PM, kolsze...@gmail.com kolsze...@gmail.comwrote: Hi I Have problem with postmaster processes which unexpectedly consume very big amount of memory, about 200MB!!!, and as a

Re: [GENERAL] [pgadmin-support] Database has been killed after increasing memory

the required log information on the server level, we can get the complete statistics information using pgbadger. Find the sample here. pgbadger report http://dalibo.github.io/pgbadger/samplev4.html Thanks, Dinesh 2013/12/23 Dinesh Kumar dinesh.ku...@enterprisedb.com Hi, +Adding postgresql general

Re: [GENERAL] pgadmin III query

Hi, On Fri, Dec 6, 2013 at 4:34 PM, Peter Kroon plakr...@gmail.com wrote: Thanks, but i need a non command line option. We can do this with a function which is having the sql queries of pgAdmin raised against the database. = Log all the queries by enabling log_minduration_statement=0. = Do

Re: [GENERAL] pgadmin III query

function with pl/pgsql language. Regards, Dinesh Could you provide a more detailed step by step guide? Best, Peter 2013/12/9 Dinesh Kumar dinesh.ku...@enterprisedb.com Hi, On Fri, Dec 6, 2013 at 4:34 PM, Peter Kroon plakr...@gmail.com wrote: Thanks, but i need a non command line option

Re: [GENERAL] [pgadmin-support] Lost database

Hi Steve, On Tue, Dec 10, 2013 at 11:08 AM, Steve Erickson serick...@digitiliti.comwrote: While prepping a second server today the /var/lib/postgres/ directory got deleted. Yes, everything. We’re running on Ubuntu 10.4 and Postgres 8.4. We immediately shutdown everything but now need to

Re: [GENERAL] pgadmin III query

, Dinesh Best, Peter 2013/12/9 Dinesh Kumar dinesh.ku...@enterprisedb.com Hi Peter, On Mon, Dec 9, 2013 at 7:03 PM, Peter Kroon plakr...@gmail.com wrote: Hi Dinesh, Get all the queries what it has performed. How and where? When I run select * from pg_stat_activity I get the same

Re: [GENERAL] Theory question

On Tue, Nov 12, 2013 at 6:57 AM, Jayadevan M maymala.jayade...@gmail.comwrote: Hi, What are the real differences between the bgwriter and checkpointer process? Both of them write data from the buffer to the data files, right? Is it just a matter of 'when' they write? I believe, Checkpoint

Re: [GENERAL] select Xpath is returning values with {}

On Tue, Nov 12, 2013 at 8:46 PM, gajendra s v svgajen...@gmail.com wrote: Hi All, How to remove {} from output I am using below query select (xpath('//Grading_Automated',(select xmlgrading from km_course_mast where id='10'))) from km_course_mast where id='10' The out is below

Re: [GENERAL] when do I analyze after concurrent index creation?

On Fri, Oct 18, 2013 at 9:12 PM, Tom Lane t...@sss.pgh.pa.us wrote: dinesh kumar dineshkuma...@gmail.com writes: When we create an index, i believe the postgres engine it self update it's catalog about the index availability. ANALYZE normally collects statistics about the contents

Re: [GENERAL] when do I analyze after concurrent index creation?

Hi, When we create an index, i believe the postgres engine it self update it's catalog about the index availability. ANALYZE helps you to find the right plan according to the number of rows got selected. I don't think, ANALYZE take care of updating the index entries. I might be wrong here, hope

[GENERAL] PostgreSQL 9.3 pg_dump issue

Hi All, Greetings for the day. Recently, I have installed the PostgreSQL 9.3 on my local ubuntu machine for testing the new features in PG 9.3. I have created few test tables as well. Now, I am trying to take the dump from the 9.3 binaries and getting the below error.

Re: [GENERAL] Postgres 8.4 stopped working---Please urgent help needed

On Sun, Jun 9, 2013 at 3:34 AM, g.si...@utwente.nl wrote: Hi All, I have a problem with PostgreSQL 8.4. It was working perfectly fine but after the restart (within a couple of minutes) of my system whenever I login to PostgreSQL 8.4 (localhost:5432) it gives error as under: *Server

Re: [GENERAL] PostgreSQL registry entries for apt-get/yum/rpm install

Thank you Jasen/Karsten for your guidance. Best Regards, Dinesh manojadinesh.blogspot.com On Sat, Apr 20, 2013 at 3:43 PM, Jasen Betts ja...@xnet.co.nz wrote: On 2013-04-15, dinesh kumar dineshkuma...@gmail.com wrote: --047d7b675e70cee73c04da61865c Content-Type: text/plain; charset=ISO

[GENERAL] Position() Bug ? In PostgreSQL 9.2

Hello Team, I would like to know whether the following behavior is a BUG or an expected behavior. If this is a duplicated case, then kindly ignore. postgres=# SELECT version(); version - PostgreSQL 9.2.3,

Re: [GENERAL] Position() Bug ? In PostgreSQL 9.2

Hi Tom, Thank you very much for the clarification. Let me set an empty string validation, before passing it to position() from API. Thank you once again. Regards, Dinesh manojadinesh.blogspot.com On 2 May 2013 19:19, Tom Lane t...@sss.pgh.pa.us wrote: dinesh kumar dineshkuma...@gmail.com

[GENERAL] PostgreSQL registry entries for apt-get/yum/rpm install

Dear Sirs, Apologizes, if this is a duplicate question. Would like to request you to share your valuable inputs on this. I would like to know the PostgreSQL registry entries when we install it through apt-get/yum/rpm. I mean, when we install the EnterpriseDB PostgreSQL one click installer, it

Re: [GENERAL] help me to clear postgres problem

IIRC, service failure creates some event logs information in windows. And also you can verify the bit rock installer log files from %TEMP% location. Regards, Dinesh manojadinesh.blogspot.com On Mon, Mar 25, 2013 at 5:05 PM, jayaram s 123jaya...@gmail.com wrote: Hello I have installed

Re: [GENERAL] WARNING: pgstat wait timeout

Effect on autovacuum worker processes. I hope some one will add more on this. Regards, Dinesh Kumar manojadinesh.blogspot.com On Mon, Feb 4, 2013 at 5:24 PM, Jake Stride j...@stride.me.uk wrote: I have had some issues with a database on EC2 and I have restored it to a new instance

Re: [GENERAL] pg_Restore

Hi Bhanu, Yes, below is the faster approach to follow. I don't know if that helps, but have you tried creating a template database and doing DROP DATABASE xxx; CREATE DATABASE xxx TEMPLATE mytemplate; instead of restoring a dump every time? Maybe that is faster. If you are trying to take

Re: [GENERAL] Postgresql - 8.3 Replication in windows

Hi , In windows we do not have SCP/RSYNC utility commands, which helps us to send the archives to remote/slave server. I hope the below settings will help you while setting replication .. Primary == archive_command = 'copy %p Slave Server\\Archive_Location\\%f' Slave = trigger_file

Re: [GENERAL] Again, problem with pgbouncer

Hi, Do you have MYSITE_MYSITE user at your database. Please login to the database directly (I mean, without any pgbouncer and check once. select* from pg_user where usename ~~* 'MYSITE_MYSITE'; And also please check your's pgbouncer.ini admin users list also. Best Regards, Dinesh

Re: [GENERAL] RE: [GENERAL] INSERT. RETURNING for copying records

Hi David, I am not sure the RETURNING offers you the following behavior .. What I'm looking for +--+-+ | original_rid | rid | +--+-+ | 1| 4 | | 2| 5 | | 3| 6 |

Re: [GENERAL] crosstab

Hi , I believe, we need to have a Dynamic SQL for this to generate the dynamic Crostab.. I have tried the same earlier and wrote the below Calendar Query for the year 2011 including holidays. Crostab Example = select min(case when extract(month from d.dd)= 1 then 'Jan' when

Re: [GENERAL] crosstab

This is really good feature (crostab) to implement .. Best Regards, Dinesh manojadinesh.blogspot.com On Wed, Sep 5, 2012 at 12:21 AM, Vincent Veyron vv.li...@wanadoo.fr wrote: Le mardi 04 septembre 2012 à 08:39 -0700, punnoose a écrit : hi all How could i use crostab to display variable

Re: [GENERAL] Check PostgreSQL status using MS-DOS bat file?

Hi , Dave's instructions are helpful for finding the status of the server.. However, I do have the below the script which is nothing but PgPing in windows ... I hope it helps you in the implementation .. @ECHO OFF set PSQL=C:\Program Files\PostgreSQL\9.1\bin set DBNAME=template1 set

Re: [GENERAL] Threads With Libpq Issue

Thanks Craig/Reid .. Yes it was duplicated ... Sorry Regards, Dinesh On Thu, Aug 2, 2012 at 6:20 AM, Craig Ringer ring...@ringerc.id.au wrote: Argh, disregard. Your post was duplicated, and this copy only just reached me.

Re: [GENERAL] Threads With Libpq

); } if(pthread_join(Ind2,NULL)0) { printf(Ind2 is completed); } } Thank you all for the wonderful guidance on this ... Best Regards, Dinesh manojadinesh.blogspot.com On Wed, Aug 1, 2012 at 8:34 PM, dinesh kumar dineshkuma...@gmail.comwrote: Hi Merlin/Alban, Thank you very much for your guidance

[GENERAL] Threads With Libpq Issue

Respected All, This is my first post in PG-Generals. If it is not the place for these kind of queries, then please guide me where i need to post. I have a quick question regarding pthread with PostgreSQL 9.0 Libpq. I'm facing a problem with Pthread and libpq. Please find the below program

Re: [GENERAL] Threads With Libpq

: On Wed, Aug 1, 2012 at 8:09 AM, Alban Hertroys haram...@gmail.com wrote: On 1 Aug 2012, at 14:32, dinesh kumar wrote: Respected All, This is my first request/post in PG-Generals. If it is not the place for these kind of queries, then please guide me where i need to be. I have