Re: [GENERAL] template1 database is facing high datfrozenxid

: oldest xmin is far in the past We stopped standby and the problem solved. :) Thanks. On Fri, Feb 12, 2016 at 4:11 PM, Adrian Klaver wrote: > On 02/12/2016 04:03 PM, AI Rumman wrote: > >> In pg_subtrans, I have files like: >> > > Are you sure you are looking at the same dat

Re: [GENERAL] template1 database is facing high datfrozenxid

> . Does it mean that I have too many open transactions? If yes, it is not showing in pg_stat_activity. On Fri, Feb 12, 2016 at 3:38 PM, AI Rumman wrote: > Used this query in each of the database:: > > SELECT t.relname, l.database, l.locktype, l.pid , l.mode, l.granted, >

Re: [GENERAL] template1 database is facing high datfrozenxid

present, db is working, but t is going towards wraparound. On Fri, Feb 12, 2016 at 3:28 PM, Adrian Klaver wrote: > On 02/12/2016 03:10 PM, AI Rumman wrote: > >> I checked it and I did not find any log running sql or any open >> transaction. Not even in pg_prepared_xacts. &g

Re: [GENERAL] template1 database is facing high datfrozenxid

/12/2016 02:56 PM, AI Rumman wrote: > >> Hi, >> >> I am running Postgresql 9.1 and I can see the datfrozenxid is going high >> and vacuum process is not bringing it down. And this has been happening >> on template1 database. >> >> 2016-02-12 16:51

[GENERAL] template1 database is facing high datfrozenxid

Hi, I am running Postgresql 9.1 and I can see the datfrozenxid is going high and vacuum process is not bringing it down. And this has been happening on template1 database. 2016-02-12 16:51:50.400 CST [19445][@] : [13-1] WARNING: oldest xmin is > far in the past > 2016-02-12 16:51:50.400 CST [194

Re: [GENERAL] How to stop autovacuum for daily partition old tables

But, will it not create transaction wraparound for those table? Thanks. On Wed, Jan 20, 2016 at 4:44 PM, Melvin Davidson wrote: > > ALTER TABLE your_schema.your_table SET (autovacuum_enabled = false, > toast.autovacuum_enabled = false); > > On Wed, Jan 20, 2016 at 6:22 PM, A

[GENERAL] How to stop autovacuum for daily partition old tables

Hi, I have a table with daily partition schema on Postgresql 9.1 where we are keeping 2 years of data. Often I experience that autovacuum process is busy with old tables where there is no change. How can I stop it? Please advice. Thanks.

Re: [GENERAL] Building 9.4 rpm for Red Hat 5

We build our own rpms. Thanks. On Tue, Jan 19, 2016 at 12:34 PM, Adrian Klaver wrote: > On 01/19/2016 12:29 PM, AI Rumman wrote: > >> We have all our scripts running on older Postrgesql versions for years >> where it is using PGHOME and other paths. We need to make the 9.

Re: [GENERAL] Building 9.4 rpm for Red Hat 5

. Drake wrote: > On 01/19/2016 12:21 PM, AI Rumman wrote: > >> Hi All, >> >> My production boxes are running on Redhat 5 and I need to build >> Postgresql 9.4 rpm for it following our environment setup. >> If I build the rpm on Centos 5, will it be ok to run on Re

[GENERAL] Building 9.4 rpm for Red Hat 5

Hi All, My production boxes are running on Redhat 5 and I need to build Postgresql 9.4 rpm for it following our environment setup. If I build the rpm on Centos 5, will it be ok to run on Red Hat 5 boxes? Can you please let me know? Thanks.

Re: [GENERAL] pgpool ssl handshake failure

I configured Postgresql 9.4 and still getting the same error. Thanks. On Thu, Oct 15, 2015 at 7:16 AM, Adrian Klaver wrote: > On 10/15/2015 06:59 AM, AI Rumman wrote: > >> Hi, >> >> I am using pgpool-II version 3.4.3 (tataraboshi). >> Where my database is P

[GENERAL] pgpool ssl handshake failure

Hi, I am using pgpool-II version 3.4.3 (tataraboshi). Where my database is Postgresql 8.4. I am trying to configure ssl mode from client and between pgpool and database it is non-ssl. I configured as document and now I am getting this in my log: > > *2015-10-13 22:17:58: pid 1857: LOG: new conn

Re: [GENERAL] ERROR: invalid input syntax for type date: IS IT A BUG here?

: > On 08/21/2015 02:32 PM, AI Rumman wrote: > >> Hi All, >> >> I am using Postgresql 9.1 where have a partitioned table as below: >> >> events_20150101 >> events_20150102 >> events_20150103 >> ... >> events_over

[GENERAL] ERROR: invalid input syntax for type date: IS IT A BUG here?

Hi All, I am using Postgresql 9.1 where have a partitioned table as below: > events_20150101 > events_20150102 > events_20150103 > ... > events_overflow When I am running the following query it gives me result: *SQL 1: * > select all relname, pg_total_relation_size(relname::text) as s, > subs

[GENERAL] official rpm build spec file

Hi, How to get postgresql official rpm spec file? Please let me know. I want to build my own Postgresql rpm. Thanks.

Re: [GENERAL] Postgresql upgrade from 8.4 to latest

Thanks for good suggestions. On Tue, Jul 28, 2015 at 3:13 PM, Joshua D. Drake wrote: > > On 07/28/2015 01:35 PM, AI Rumman wrote: > >> But what I read, in-place upgrade has smaller outage, compared to >> dump/restore. >> > > Correct, in fact if you do it with

Re: [GENERAL] Postgresql upgrade from 8.4 to latest

. On Tue, Jul 28, 2015 at 1:29 PM, Joshua D. Drake wrote: > > On 07/28/2015 01:12 PM, AI Rumman wrote: > >> Hi, >> >> I need to upgrade Postgresql database from 8.4 to latest stable version >> (9.4). The db size is almost 2.5 TB. >> Is pg_upgrade in-place is

[GENERAL] Postgresql upgrade from 8.4 to latest

Hi, I need to upgrade Postgresql database from 8.4 to latest stable version (9.4). The db size is almost 2.5 TB. Is pg_upgrade in-place is a good idea for it? Thanks for advice. Regards.

Re: [GENERAL] pg_dump error

I am not using any comma here. With the same command I am able to take dump on other disks. But with ISOLON CIFS, I am getting the error. Thanks. On Mon, Jul 27, 2015 at 10:56 AM, Adrian Klaver wrote: > On 07/27/2015 10:55 AM, AI Rumman wrote: > >> No it is "-s"

Re: [GENERAL] pg_dump error

No it is "-s" for schema only backup. Thanks. On Mon, Jul 27, 2015 at 10:53 AM, Adrian Klaver wrote: > On 07/27/2015 10:48 AM, AI Rumman wrote: > >> Hi, >> >> I am getting the following error during pg_dump: >> >> pg_dump dbname -

[GENERAL] pg_dump error

Hi, I am getting the following error during pg_dump: pg_dump dbname -v -t tablename -s -Fc -f dbname_tablename,sqlc pg_dump: [custom archiver] WARNING: ftell mismatch with expected position -- ftell used I am using Postgresql 9.1 and I have enough disk space on it. The backup drive is on ISOLON

Re: [GENERAL] Setting up HA postgresql

Hi, I made the following document 4 years back: http://www.rummandba.com/2011/02/postgresql-failover-with-pgpool-ii.html You may have a look if it makes any good to your work. BTW, if you want to setup a share-nothing high scalable system with data-sharding, you can go for pl/proxy. Thanks. On

Re: [GENERAL] Postgresql CIFS

Thanks. On Wed, Mar 4, 2015 at 10:22 PM, John R Pierce wrote: > On 3/4/2015 9:10 PM, AI Rumman wrote: > >> I am working on setting up a new database server with shared disk and >> cluster failover. >> In this environment, only one node will be active at a certain tim

[GENERAL] Postgresql CIFS

Hi All, I am working on setting up a new database server with shared disk and cluster failover. In this environment, only one node will be active at a certain time. Underneath, we are planning to have shared storage with CIFS protocol. As I am newbie with this storag, can anyone please help me wi

[GENERAL] increasing varchar column size is taking too much time

Hi, I started the following query in Postgresql 9.1 where only this sql is running on the host and it has been taking more than an hour and still running. alter table userdata.table1 alter column name type varchar(512); Here is the table description: > d+ userdata.table1 >

[GENERAL] what is parse ?

Hi All, I am facing some slow sqls in my database as follows: 2015-01-29 18:57:19.777 CST [29024][user@user] 10.6.48.226(59246): [1-1] LOG: duration: 3409.729 ms parse : 2015-01-29 18:57:19.782 CST [29140][user@user] 10.6.48.227(36662): [1-1] LOG: duration: 3468.549 ms parse : 2015-01-29 18:5

[GENERAL] pgcluu error

Hi, I am trying to use pgcluu with collected stats and got the error: Can't call method "print" on an undefined value at /opt/pgdata/pgcluu_prod/pgcluubin/pgcluu line 5494 Any one has idea? Thanks.

Re: [GENERAL] not finding rows using ctid

I didn't execute any Vacuum Full and I tried to get the row after 3 hours of the issue. Thanks. On Thu, Aug 7, 2014 at 1:51 PM, Adrian Klaver wrote: > On 08/07/2014 12:40 PM, AI Rumman wrote: > >> >> Hi, >> >> I am getting the logs as follows: >> >

[GENERAL] not finding rows using ctid

Hi, I am getting the logs as follows: LOG: process 32145 acquired ExclusiveLock on tuple (153420,5) of relation 663326 of database 475999 after 1123.028 ms But, when I am executing sqls to find the row on that table using the ctid = '(153420,5)', I get no rows. Any idea, why? Thanks.

Re: [GENERAL] Whats is lock type transactionid?

Yes. But as we are using bind variables, we are not able to get the ID of the tuple. On Thu, Jul 17, 2014 at 2:08 PM, Douglas J Hunley wrote: > > On Thu, Jul 17, 2014 at 12:54 PM, AI Rumman wrote: > >> I am experiencing lock contention on one single UPDATE statement at a >

Re: [GENERAL] Whats is lock type transactionid?

:40 PM, Douglas J Hunley wrote: > On Thu, Jul 17, 2014 at 3:34 PM, AI Rumman wrote: > >> Hi, >> >> I have been facing lock contention in my Postgresql 9.1 DB. >> And when I am querying in the pg_locks table I found a lock type with >> transactionid. >>

[GENERAL] Whats is lock type transactionid?

Hi, I have been facing lock contention in my Postgresql 9.1 DB. And when I am querying in the pg_locks table I found a lock type with transactionid. Could someone please tell me what it means? Thanks.

Re: [GENERAL] lock contention, need profiling idea

There was no CREATE INDEX command running on the host. On Mon, Jun 30, 2014 at 5:06 PM, Michael Paquier wrote: > > > > On Tue, Jul 1, 2014 at 7:36 AM, AI Rumman wrote: > >> I see lots of similar log message at a certain time in a day on >> Postgresql 9,.1: >

[GENERAL] lock contention, need profiling idea

I see lots of similar log message at a certain time in a day on Postgresql 9,.1: LOG: process 18855 still waiting for ShareLock on transaction 2856146023 after 1001.209 ms STATEMENT: UPDATE table1 SET time = $1 WHERE id = $2 The table1 size is 17 G. What could be the reason for this lock conte

[GENERAL] skipping analyze of "table1" --- lock not available?

Could someone please tell me why I am getting these in my log: 2014-06-23 00:00:00.031 CDT [11379][@] : [1-1]LOG: skipping analyze of "table1" --- lock not available 2014-06-23 00:00:00.056 CDT [11380][@] : [1-1]LOG: skipping analyze of "table1" --- lock not available 2014-06-23 00:00:00.081 CDT

Re: [GENERAL] Lock during insert statement

Got it. Thanks. Any special parameter to tune it? Like wal_buffers or shared_buffers? On Wed, May 21, 2014 at 3:28 PM, Jeff Janes wrote: > On Wed, May 21, 2014 at 3:14 PM, AI Rumman wrote: > >> Could any one please tell me why my system is waiting to get lock for an >>

[GENERAL] Lock during insert statement

Could any one please tell me why my system is waiting to get lock for an INSERT statement? 2014-05-21 07:52:49.965 PDT [9-1]LOG: process 31407 acquired ExclusiveLock on extension of relation 429298276 of database 21497 after 3219.963 ms 2014-05-21 07:52:49.965 PDT [10-1]STATEMENT: INSERT INTO ta

[GENERAL] what should be the best autovacuum configuration for daily partition table

Hi, I have a table with daily partition setup where old partitions are static tables that is after each day we don't get any new data in old partitions. The database size is 2 TB and I am running with autovacuum on for Postgresql 8.4. Now, I am facing a problem where old tables are not being vacuu

[GENERAL] Is it good to have toast table for information schema table?

Hi, Is it good to have toast table for information schema table? I am using Postgresql 8.4 and current state is: *select datname, datfrozenxid from pg_database; * datname | datfrozenxid ---+-- template1 | 1462730397 template0 | 1462741467 postgres | 1562

Re: [GENERAL] Is it safe to stop postgres in between pg_start_backup and pg_stop_backup?

What we did in this kind of higher performance storage migration, setting up standby on that mounts and then executed a failover. On Thu, Apr 3, 2014 at 3:58 PM, Alan Hodgson wrote: > On Thursday, April 03, 2014 02:48:03 PM Steven Schlansker wrote: > > On Apr 2, 2014, at 3:08 PM, Jacob Scott w

[GENERAL] When does pg_archivecleanup work?

As I have very low wal_keep_segments compare to my wal generation, I am collecting archive wal files at slave. Now in order to clean up archive wal collection directory at slave, I used "archive_cleanup_command". I watched that after archive wal files were pilling up at slave and after certain poin

[GENERAL] EMC SRDF technology for creating replication with Postgresql?

Hi, Did any of here use EMC SRDF technology for creating replication with Postgresql? Thanks.

[GENERAL] what does the error mean?

Hi, What does the error message mean? ERROR: unexpected chunk number 1 (expected 2) for toast value 179638221 in pg_toast_16700 Please let me know. Thanks.

[GENERAL] Seems like bug in 9.1.3, need to confirm.

HI, I am working on Postgresql 9.1.3. I executed the following query and got an error: select relname, pg_size_pretty(pg_table_size(relname::text)) as s from pg_stat_user_tables order by pg_table_size(relname::text) desc limit 10; ERROR: relation "tab_20130206" does not exist That table does n

Re: [GENERAL] Question(s) about crosstab

Hi, Once I faced the same problem of adding new type and reqriting the query working with crosstab function. Then I created a dynamic crosstab function. You may have a look at it if it work out for you: http://www.rummandba.com/2013/03/postgresql-dynamic-crosstab-function.html Thanks. On Tue, D

Re: [GENERAL] Data Guard for Postgres?

Postgresql 9.2 streaming replication which is very much similar with Oracle Data Guard. On Fri, Dec 13, 2013 at 1:57 PM, Roy Anderson wrote: > Good day. I am inquiring as to whether there is a free solution > available that approximates, in Postgres, what Data Guard does for > Oracle DB. Can any

[GENERAL] pg_start_backup('label',true) why do I need 2nd parameter?

Hi all, A few days back, I faced a problem where I *pg_start_backup('label') *was hang in the server forever. I stopped the process and then used *pg_start_backup('label',true) *and it worked. Now I am trying to investigate why I need to use true as second parameter and read the doc "There is an

Re: [GENERAL] how to get the connected session pointer ( Archive * AH)

:05 AM, Luca Ferrari wrote: > On Tue, Oct 22, 2013 at 1:32 AM, AI Rumman wrote: > > Hi, > > > > Is there a way in Postgresql C function to get the connected session > pointer > > ( Archive * AH) > > and use it for further execution? > > If I read pg_ar

[GENERAL] how to get the connected session pointer ( Archive * AH)

Hi, Is there a way in Postgresql C function to get the connected session pointer ( Archive * AH) and use it for further execution? Thanks.

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

Hi, I have a very basic question. If I create index concurrently, then do I need to analyze the table? If yes, when? Please let me know. Thanks.

[GENERAL] what is BIND in OS log for Postgresql

Hi, I got in my OS log using ps command as follows: postgres 17087 2770 0 Sep30 ?00:07:49 postgres: myapp appname 10.0.0.1(35607) BIND postgres 32545 2770 1 Oct01 ?00:19:09 postgres: myapp appname 10.0.0.1(35783) SELECT postgres 32546 2770 1 Oct01 ?00:15:25 postgres:

[GENERAL] recover deleted data

Is there any way to recover deleted record in Postgresql 9.2 if we don't have any backup. Thanks.

Re: [GENERAL] 9.2 Replication in Ubuntu ; need help

Yes, I can access to the primary server perfectly. On Thu, Sep 12, 2013 at 11:55 AM, Suzuki Hironobu wrote: > Hi, > > > (13/09/12 23:53), AI Rumman wrote: > >> Hi, >> >> I am trying to setup replication with Postgresql 9.2 in Ubuntu on Amazon >> Ec2. >

[GENERAL] 9.2 Replication in Ubuntu ; need help

Hi, I am trying to setup replication with Postgresql 9.2 in Ubuntu on Amazon Ec2. But stuck in the process. Postgresql standby log is showing: 2013-09-12 14:45:47 UTC LOG: entering standby mode 2013-09-12 14:45:47 UTC LOG: redo starts at 1/3920 2013-09-12 14:45:47 UTC LOG: record with zero

Re: [GENERAL] regexp idea

Thanks. That's awesome. Do you have any good guide where I may get more knowledge on REGEXP? On Tue, Aug 27, 2013 at 3:57 PM, Rob Sargent wrote: > On 08/27/2013 12:44 PM, AI Rumman wrote: > >> Hi, >> >> I have a string like: >> Gloucester Catholic vs. St. A

[GENERAL] regexp idea

Hi, I have a string like: Gloucester Catholic vs. St. Augustine baseball, South Jersey Non-Public A final, June 5, 2013 I need to extract date part from the string. I used the follows: regexp_matches(title,'[.* ]+\ (Jul|August|Sep)[, a-zA-Z0-9]+' ) But it gives me result August as it stops at "

[GENERAL] pg_get_triggerdef can't find the trigger using OID.

Why can't pg_get_triggerdef find the trigger using OID. testdb=# SELECT testdb-# p.oid, testdb-# n.nspname as "Schema", testdb-# p.proname as "Name", testdb-# pg_catalog.pg_get_function_result(p.oid) as "Result data type", testdb-# pg_catalog.pg_get_function_arguments(p.oid) as "Argument

[GENERAL] last_vacuum field in not updated

Hi, I am using Postgresql 9.2 where I have a table "table1". I used vacuum command in that table, but last_vacuum column of pg_stat_user_tables has not been updated. Any idea for it? \d table1 Table "public.table1" Column | Type | Modifiers --+--

Re: [GENERAL] setting high value for wal_keep_segments

Yeah, I already set it like that and it works. Thanks. On Thu, Aug 8, 2013 at 11:59 AM, bricklen wrote: > On Thu, Aug 8, 2013 at 6:23 AM, AI Rumman wrote: > >> Hi, >> >> I am going to sync slave with my master which is almost 500 G. I am not >> using archive d

[GENERAL] setting high value for wal_keep_segments

Hi, I am going to sync slave with my master which is almost 500 G. I am not using archive directory instead of I am using wal files for streaming. As it may take almost 3 hours, I am thinking of setting up 400 for wal_keep_segments where I have enough space available. Without the space issue, cou

Re: [GENERAL] last_vacuum field is not updating

Yes, I am sure that I am looking for the same table. On Tue, Jul 16, 2013 at 4:34 AM, Luca Ferrari wrote: > On Mon, Jul 15, 2013 at 6:43 PM, Giuseppe Broccolo > wrote: > > > Are you sure you are the table's owner? > > It should not be a permission problem: it works even after a revoke > all on

[GENERAL] last_vacuum field is not updating

Why does vacuum table is not updating the field last_vacuum of pg_stat_user_tables? select * from pg_stat_user_tables where relname = 'table1'; -[ RECORD 1 ]-+-- relid | 5452445 schemaname| public relname | table1 seq_scan | 5

Re: [GENERAL] dynamic partitioning

il) > it creates 100 partition tables that contain 1 entry instead of 5 > partitions with > 20 entries.. > > Any ideas in that?? > > Thanks again! > > Dafni > > > > > On Wed, Jun 26, 2013 at 5:32 PM, AI Rumman wrote: > >> Yes, you missed the trigger

Re: [GENERAL] dynamic partitioning

Yes, you missed the trigger part. And also you will get error like below during insert: INSERT INTO foo VALUES (99, 109, 109, 99, '2013-06-26 16:38:58.466'); NOTICE: table_name = (foo_100_to_119) NOTICE: CREATE TABLE foo_100_to_119 (CHECK ( foo_id >= 100 AND foo_id <= 119 )) INHERITS (foo) ERROR

Re: [GENERAL] postgresql query

Which version of Postgresql are you using? However, you may use string_agg like below if its available in your version: \d t1 Table "public.t1" Column | Type | Modifiers +-+--- i | integer | amt| integer | select * from t1; i | amt ---+- 1 | 2

Re: [GENERAL] Postgres DB crashing

Stop the autovacuum process and try again. On Tue, Jun 18, 2013 at 1:31 PM, bhanu udaya wrote: > Hello, > Greetings. > > My PostgresSQL (9.2) is crashing after certain load tests. Currently, > postgressql is crashing when simulatenously 800 to 1000 threads are run on > a 10 million records sche

Re: [GENERAL] pg_upgrade link mode

Yes Lonni. I agree with you. On Thu, May 16, 2013 at 2:23 PM, Lonni J Friedman wrote: > On Thu, May 16, 2013 at 11:03 AM, Igor Neyman > wrote: > > > > From: pgsql-general-ow...@postgresql.org [mailto: > pgsql-general-ow...@postgresql.org] On Behalf Of AI Rumman > > S

Re: [GENERAL] pg_upgrade link mode

il in the middle. On Thu, May 16, 2013 at 2:03 PM, Igor Neyman wrote: > > From: pgsql-general-ow...@postgresql.org [mailto: > pgsql-general-ow...@postgresql.org] On Behalf Of AI Rumman > Sent: Thursday, May 16, 2013 1:56 PM > To: Fabio Rueda Carrascosa > Cc: pgsql-general

Re: [GENERAL] pg_upgrade link mode

I always think its a bit risky to use link instead of copying. However, I'd suggest to try the --check at first of pg_upgrade. On Thu, May 16, 2013 at 1:42 PM, Fabio Rueda Carrascosa < avances...@gmail.com> wrote: > Hello, I have a 9.1 cluster with 50 databases, only one table per db with > 200

Re: [GENERAL]

ct trim(1) does not work; > > > On Wed, May 15, 2013 at 5:38 PM, AI Rumman wrote: > >> Drop those functions and try again. >> >> >> On Wed, May 15, 2013 at 4:22 PM, Carlos Henrique Reimer < >> carlos.rei...@opendb.com.br> wrote: >> >>>

Re: [GENERAL]

* > > ERROR: operator is not unique: unknown || integer > SQL state: 42725 > Hint: Could not choose a best candidate operator. You might need to add > explicit type casts. > Character: 15 > > > > > > On Wed, May 15, 2013 at 4:45 PM, AI Rumman wrote: > >

Re: [GENERAL]

stated in my first note, this is what I've done to fix the > select 2 (select trim(1);) but after creating this function/cast the select > 1 (select 'teste'||1;) started not working. > > > On Wed, May 15, 2013 at 4:24 PM, AI Rumman wrote: > >> Create the func

Re: [GENERAL]

Create the functions: CREATE FUNCTION pg_catalog.text(integer) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(int4out($1));'; CREATE CAST (integer AS text) WITH FUNCTION pg_catalog.text(integer) AS IMPLICIT; http://www.rummandba.com/2010/10/migrate-database-from-postgresql-81-to.html

[GENERAL] check_postgres_last_vacuum for all databases

Hi, I have been setting up nagios alert for check_postgres_last_vacuum. I went through the code and saw that I have to define the databases in order to check for all database tables. In my environment, database creation and deletion is dynamic. So any time any one can create database or delete for

[GENERAL] Character set display

Hi, I got a data like: AHrühn And I need the output like: AHrühn The DB is running with UTF8 on Postgresql 9.2. Any help will be appreciated. Thanks.

Re: [GENERAL] how to completely disable toasted table in postgresql and best practices to follow

According to doc, YES. http://www.postgresql.org/docs/9.2/static/storage-toast.html MAIN allows compression but not out-of-line storage. (Actually, out-of-line storage will still be performed for such columns, but only as a last resort when there is no other way to make the row small enough to fit

Re: [GENERAL] replication behind high lag

On Mon, Mar 25, 2013 at 4:03 PM, AI Rumman wrote: > > > On Mon, Mar 25, 2013 at 4:00 PM, Lonni J Friedman wrote: > >> On Mon, Mar 25, 2013 at 12:55 PM, AI Rumman wrote: >> > >> > >> > On Mon, Mar 25, 2013 at 3:52 PM, Lonni J Friedman >> > w

Re: [GENERAL] replication behind high lag

On Mon, Mar 25, 2013 at 3:52 PM, Lonni J Friedman wrote: > On Mon, Mar 25, 2013 at 12:43 PM, AI Rumman wrote: > > > > > > On Mon, Mar 25, 2013 at 3:40 PM, Lonni J Friedman > > wrote: > >> > >> On Mon, Mar 25, 2013 at 12:37 PM, AI Rumman >

Re: [GENERAL] replication behind high lag

On Mon, Mar 25, 2013 at 3:40 PM, Lonni J Friedman wrote: > On Mon, Mar 25, 2013 at 12:37 PM, AI Rumman wrote: > > Hi, > > > > I have two 9.2 databases running with hot_standby replication. Today > when I > > was checking, I found that replication has not been work

[GENERAL] replication behind high lag

Hi, I have two 9.2 databases running with hot_standby replication. Today when I was checking, I found that replication has not been working since Mar 1st. There was a large database restored in master on that day and I believe after that the lag went higher. SELECT pg_xlog_location_diff(pg_curren

Re: [GENERAL] How to join table to itself N times?

On Wed, Mar 20, 2013 at 7:38 PM, W. Matthew Wilson wrote: > I got this table right now: > > select * from market_segment_dimension_values ; > +--+---+ > | market_segment_dimension | value | > +--+---+ > | geography

[GENERAL] crosstab creating multiple rows for same id

Hi, I am using the crosstab function in Postgresql 9.0. The functions works fine when the distinct category value is less. But when it goes to to higher like 100 or more, it creates multiple rows for the same rowid. Any idea about it? Thanks.

[GENERAL] no implicit cast error in 9.2?

Hi, I am using PostgreSQL 9.2.3 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.6 20120305 (Red Hat 4.4.6-4), 64-bit. I got a bit confused after installing this version. So far I used to know that from Postgresql 8.3 implicit casting has been removed and the following should not work at 8.

[GENERAL] Is it possible to migrate database from Postgresql 8.2 to 9.2 using pg_upgrade?

Is it possible to migrate database from Postgresql 8.2 to 9.2 using pg_upgrade? Thanks.

[GENERAL] Query Parallelizing with dblink

Hi All, I made an idea to utilize multiple core for Postgresql query. I used dblink to achieve this task. You may find it: http://www.rummandba.com/2013/01/postgresql-query-parallelizing-with.html It is just a basic idea and completely usable for my data model. But I share it with you all so tha

[GENERAL] pg_upgrade failed for 9.0 to 9.2

Hi, When I was upgrading database from 9.0 to 9.2 using pg_upgrade, I got the error: CREATE VIEW stats_slowest_queries AS SELECT pg_stat_activity.procpid, (('now'::text)::timestamp(6) with time zone - pg_stat_activity.query_start) AS execution_time, pg_stat_activity.current_query FROM pg_stat

[GENERAL] postgresql 9.2 build error

I am trying to build Postgresql 9.2 ./configure --prefix=/usr/pgsql-9.2 --with-ossp-uuid --with-libxml Got the error at config.log: configure:9747: result: no configure:9752: checking for uuid_export in -luuid configure:9787: gcc -o conftest -O2 -Wall -Wmissing-prototypes -Wpointer-arit

[GENERAL] Is there any plugin for Nagios, which sends Postgresql ERROR information with SQL query in mail?

Is there any plugin for Nagios, which sends Postgresql ERROR information with SQL query in mail?

Re: [GENERAL] pg_xlog is getting bigger

On Wed, Dec 19, 2012 at 7:52 PM, Adrian Klaver wrote: > On 12/19/2012 04:12 PM, Tom Lane wrote: > >> Adrian Klaver writes: >> >>> Well the question is how long have those idle transactions been around? >>> >> >> Idle transactions shouldn't have anything to do with pg_xlog bloat. >> What causes xl

[GENERAL] pg_xlog is getting bigger

Hi, I am working on a Postgresql 9.0 server. I have no replication and archive mode setup. But I found that the pg_xlog is getting bigger and bigger. Right now it is 20 GB. How should I recover these spaces? Please let me know. Thanks.

Re: [GENERAL] pg_restore error with out of memory

I modified the shared_buffer=50 MB and maintenance_work_mem = 50 MB But still getting the same error. On Thu, Dec 13, 2012 at 7:36 PM, Kevin Grittner wrote: > AI Rumman wrote: > > I am going to restore a 6 Gb database in my development machine > which is running on Centos 5.6 with

[GENERAL] pg_restore error with out of memory

I am going to restore a 6 Gb database in my development machine which is running on Centos 5.6 with memory 1 GB. During restoration I got error as follows: LOG: checkpoints are occurring too frequently (22 seconds apart) HINT: Consider increasing the configuration parameter "checkpoint_segments

[GENERAL] deadlock detected

Hi all, I am using Postrgesql 9.1 I got a message in my log: ERROR: deadlock detected DETAIL: Process 20265 waits for ShareLock on transaction 27774015; blocked by process 20262. Process 20262 waits for ShareLock on transaction 27774018; blocked by process 20265. Process 20265:

[GENERAL] should I increase default_statistics_target

Using explain analyze of a large query I found that in every step there are a lot difference between the number of rows between actual and estimated. I am using default_statistics_target 200. Should I increase it?

Re: [GENERAL] Where should I start for learn development

should start by writing simple C functions as extension and then for more. Thanks to all again. On Tue, Jul 3, 2012 at 7:32 PM, Craig Ringer wrote: > On 07/03/2012 07:50 PM, AI Rumman wrote: > > Hello, > > I have been working with Postgresql for the last 3 years. Before that I >

Re: [GENERAL] I cannot insert bengali character in UTF8

WOW. Great informative answer. Thanks. On Fri, Jul 20, 2012 at 7:11 PM, Christian Ullrich wrote: > * AI Rumman wrote: > > I am using database with UTF8 and LC_CTYPE set as default value in >> Postgresql 9.1. >> But I cannot insert bengali character in a column. >>

[GENERAL] I cannot insert bengali character in UTF8

I am using database with UTF8 and LC_CTYPE set as default value in Postgresql 9.1. But I cannot insert bengali character in a column. Query Failed:INSERT into tracker (user_id, module_name, item_id, item_summary) values ('1','Leads','353','বাংলা টেস্��...')::ADODB error::->ERROR: invalid byte sequ

[GENERAL] Where should I start for learn development

Hello, I have been working with Postgresql for the last 3 years. Before that I worked with Oracle, Mysql and other databases. Now, its time to learn the internals of Postgresql system. I downloaded the source code and imported it in my eclipse environment. But I have very limited knowledge on C pr

[GENERAL] is there any query so that I may find the list of columns those have rules?

I am getting the following error: ALTER TABLE base_table ALTER COLUMN base_table_field1 TYPE numeric(10,6); ERROR: cannot alter type of a column used by a view or rule DETAIL: rule _RETURN on view master_view depends on column "base_table_field1" I know that I have a view based in the "base_tabl

[GENERAL] Is it possible to send data in client end?

I am using Postgresql 9.1. I know that COPY is used to export data as csv. But COPY creates file in server end. I need to create a file in client end. My application uses php. Is there any tool for it?

  1   2   3   >