[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

Re: [GENERAL] template1 database is facing high datfrozenxid

kla...@aklaver.com> wrote: > On 02/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. >> >

Re: [GENERAL] template1 database is facing high datfrozenxid

. At present, db is working, but t is going towards wraparound. On Fri, Feb 12, 2016 at 3:28 PM, Adrian Klaver <adrian.kla...@aklaver.com> 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 e

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 <adrian.kla...@aklaver.com> wrote: > On 02/12/2016 04:03 PM, AI Rumman wrote: > >> In pg_subtrans, I have files like: >> > > Are you su

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 <rumman...@gmail.com> wrote: > Used this query in each of the database:: > > SELECT t.relname, l.database, l.locktype,

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

n 20, 2016 at 6:22 PM, AI Rumman <rumman...@gmail.com> wrote: > >> 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 wher

[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 <adrian.kla...@aklaver.com> 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 othe

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

. Drake <j...@commandprompt.com> 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 C

[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.

[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

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 <adrian.kla...@aklaver.com> wrote: > On 10/15/2015 06:59 AM, AI Rumman wrote: > >> Hi, >> >> I am using pgpool-II version 3.4.3 (tataraboshi). >

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

...@aklaver.com wrote: 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_overflow When I am running the following query it gives me result: *SQL 1

[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,

[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 j...@commandprompt.com 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 the link option

[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] Postgresql upgrade from 8.4 to latest

. On Tue, Jul 28, 2015 at 1:29 PM, Joshua D. Drake j...@commandprompt.com 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 a good idea

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 adrian.kla...@aklaver.com wrote: On 07/27/2015 10:55 AM, AI Rumman wrote: No it is -s for schema

[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

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 adrian.kla...@aklaver.com wrote: On 07/27/2015 10:48 AM, AI Rumman wrote: Hi, I am getting the following error during pg_dump: pg_dump dbname -v -t tablename -s -Fc -f dbname_tablename,sqlc

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.

Re: [GENERAL] Postgresql CIFS

Thanks. On Wed, Mar 4, 2015 at 10:22 PM, John R Pierce pie...@hogranch.com 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 time. Underneath

[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

[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 unnamed?

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 unnamed: 2015-01-29 18:57:19.782 CST [29140][user@user] 10.6.48.227(36662): [1-1] LOG: duration: 3468.549 ms parse unnamed:

[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.

[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] 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 adrian.kla...@aklaver.com wrote: On 08/07/2014 12:40 PM, AI Rumman wrote: Hi, I am getting the logs as follows: LOG: process 32145 acquired

[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] Whats is lock type transactionid?

:40 PM, Douglas J Hunley doug.hun...@gmail.com wrote: On Thu, Jul 17, 2014 at 3:34 PM, AI Rumman rumman...@gmail.com 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. Could someone please

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 doug.hun...@gmail.com wrote: On Thu, Jul 17, 2014 at 12:54 PM, AI Rumman rumman...@gmail.com wrote: I am experiencing lock contention on one single UPDATE

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 michael.paqu...@gmail.com wrote: On Tue, Jul 1, 2014 at 7:36 AM, AI Rumman rumman...@gmail.com 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

[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

[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

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 jeff.ja...@gmail.com wrote: On Wed, May 21, 2014 at 3:14 PM, AI Rumman rumman...@gmail.com wrote: Could any one please tell me why my system is waiting to get

[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

[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 |

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 ahodg...@simkin.ca wrote: On Thursday, April 03, 2014 02:48:03 PM Steven Schlansker wrote: On Apr 2, 2014, at 3:08

[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 point

[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

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,

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 roy.ander...@gmail.comwrote: Good day. I am inquiring as to whether there is a free solution available that approximates, in Postgres, what Data Guard does for

[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 fluca1...@infinito.it wrote: On Tue, Oct 22, 2013 at 1:32 AM, AI Rumman rumman...@gmail.com 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_archiver.c correctly

[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

[GENERAL] recover deleted data

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

[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

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 hiron...@interdb.jpwrote: 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. But stuck in the process

[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

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 robjsarg...@gmail.com wrote: On 08/27/2013 12:44 PM, AI Rumman wrote: Hi, I have a string like: Gloucester Catholic vs. St. Augustine baseball, South

[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 data

[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

[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,

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 brick...@gmail.com wrote: On Thu, Aug 8, 2013 at 6:23 AM, AI Rumman rumman...@gmail.com wrote: Hi, I am going to sync slave with my master which is almost 500 G. I am not using archive

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 fluca1...@infinito.it wrote: On Mon, Jul 15, 2013 at 6:43 PM, Giuseppe Broccolo giuseppe.brocc...@2ndquadrant.it wrote: Are you sure you are the table's owner? It should not be a permission

[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 |

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] dynamic partitioning

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 rumman...@gmail.com wrote: Yes, you missed the trigger part. And also you will get error like below during insert: INSERT INTO foo VALUES (99

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 | 20

Re: [GENERAL] Postgres DB crashing

Stop the autovacuum process and try again. On Tue, Jun 18, 2013 at 1:31 PM, bhanu udaya udayabhanu1...@hotmail.comwrote: 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

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 2000

Re: [GENERAL] pg_upgrade link mode

in the middle. On Thu, May 16, 2013 at 2:03 PM, Igor Neyman iney...@perceptron.com 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 Subject: Re

Re: [GENERAL] pg_upgrade link mode

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

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

Re: [GENERAL]

, 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 rumman...@gmail.com wrote: Create the functions: CREATE FUNCTION pg_catalog.text(integer

Re: [GENERAL]

need to add explicit type casts. Character: 15 On Wed, May 15, 2013 at 4:45 PM, AI Rumman rumman...@gmail.com wrote: Ah! I missed that. Which version of 9.2 you are working with? I am also at 9.2 and its working fine. Try out using select 'teste'||1::int; See if it works

Re: [GENERAL]

) does not work; On Wed, May 15, 2013 at 5:38 PM, AI Rumman rumman...@gmail.com 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: The PG version I'm using is 9.2.4. It works but after recreating

[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

[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

[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

Re: [GENERAL] replication behind high lag

On Mon, Mar 25, 2013 at 3:40 PM, Lonni J Friedman netll...@gmail.comwrote: On Mon, Mar 25, 2013 at 12:37 PM, AI Rumman rumman...@gmail.com 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 working

Re: [GENERAL] replication behind high lag

On Mon, Mar 25, 2013 at 3:52 PM, Lonni J Friedman netll...@gmail.comwrote: On Mon, Mar 25, 2013 at 12:43 PM, AI Rumman rumman...@gmail.com wrote: On Mon, Mar 25, 2013 at 3:40 PM, Lonni J Friedman netll...@gmail.com wrote: On Mon, Mar 25, 2013 at 12:37 PM, AI Rumman rumman

Re: [GENERAL] replication behind high lag

On Mon, Mar 25, 2013 at 4:03 PM, AI Rumman rumman...@gmail.com wrote: On Mon, Mar 25, 2013 at 4:00 PM, Lonni J Friedman netll...@gmail.comwrote: On Mon, Mar 25, 2013 at 12:55 PM, AI Rumman rumman...@gmail.com wrote: On Mon, Mar 25, 2013 at 3:52 PM, Lonni J Friedman netll...@gmail.com

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

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

[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

[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

[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

[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

[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?

[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_xlog is getting bigger

On Wed, Dec 19, 2012 at 7:52 PM, Adrian Klaver adrian.kla...@gmail.comwrote: On 12/19/2012 04:12 PM, Tom Lane wrote: Adrian Klaver adrian.kla...@gmail.com writes: Well the question is how long have those idle transactions been around? Idle transactions shouldn't have anything to do with

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 kgri...@mail.com wrote: AI Rumman wrote: I am going to restore a 6 Gb database in my development machine which is running on Centos 5.6

[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

[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 ring...@ringerc.id.au 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

[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

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 ch...@chrullrich.netwrote: * 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. Query

[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

[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

[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   >