[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

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

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

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

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"

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

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

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

[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

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

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.

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

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

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

: 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

[GENERAL] 8.1 Table partition and getting error

I am using POstgreql 8.1. I create table partition as follows: alter table crm rename to crm_bak; CREATE TABLE crm ( crmid integer NOT NULL, description text, deleted integer NOT NULL DEFAULT 0 ) WITHOUT OIDS; ALTER TABLE crm OWNER TO vcrm; create table crm_deleted ( check ( deleted = 1 )

[GENERAL] VACUUM FULL for performance

MayVACUUM FULL on a table improve perfromance of the system?

[GENERAL] How to find which query are doing seq_scan

I am using Postgresql 8.1. I need to know which query are executing seq_scan on tables as statistics said there were 4 seq_scan on the tables for the last 2 days. Any idea please.

[GENERAL] server gets slow at certain time

I am using Postgresql 8.1 in 8-cpu cores with 32 gb ram with CestOS 5. I have different DBs in the system and the large one is 12 GB and it is a CRM DB and heavily used. Every day after 1 pm the system gets slow and after analyzing the nmon spreadsheet, I found that IO spike at that period. Users w

Re: [GENERAL] server gets slow at certain time

At present, checkpoint_segment=3 checkpoint_timeout=300 On 10/29/10, Thom Brown wrote: > On 29 October 2010 13:24, AI Rumman wrote: > >> I am using Postgresql 8.1 in 8-cpu cores with 32 gb ram with CestOS 5. >> I have different DBs in the system and the large one is 12 GB and

[GENERAL] max_fsm_pages increase

I using Postgresql 8.1 and during vacuum at night time, I am getting the following log: number of page slots needed (2520048) exceeds max_fsm_pages (356656) Do I need to increase max_fsm_pages to 2520048? Does it have any bad affect?

[GENERAL] index in desc order

Is it possible to create an index in descending order?

Re: [GENERAL] index in desc order

But I am using Postgresql 8.1. Is it possible here? On Tue, Nov 2, 2010 at 3:42 PM, Szymon Guz wrote: > > > On 2 November 2010 10:36, AI Rumman wrote: > >> Is it possible to create an index in descending order? >> > > yes... > > create index i on t(i desc); > > > regards > Szymon >

[GENERAL] migrate from 8.1 to 9.0

I am going to migrate my produciton DB from postgresql 8.1 to 9.0.1. Anyone please tell me what the important things I have to look for this migration. Thanking you all.

[GENERAL] REINDEX requirement?

How do I know that index require REINDEX?

[GENERAL] good settings for DB parameters such as shared_buffers, checkpoint_segment in Postrgesql 9

I am going to install Postgresql 9.0 for my running applicaiton which is at 8.1. My Db size is 3 GB. Server Specification: dual-core 4 cpu RAM: 32 GB OS: Centos What will be good settings for DB parameters such as shared_buffers, checkpoint_segment and etc. Any help please.

[GENERAL] POstgresql 9.0.1 FTS NOTICE: word is too long to be indexed

I getting error in Postgresql 9.0.1. select description ,to_tsvector(description) from crm where crmid = 1; NOTICE: word is too long to be indexed DETAIL: Words longer than 2047 characters are ignored. Any idea pelase how to solve it.

[GENERAL] FTS is taking "

I found that FTS is taking "') as c; c --- (1 row) select * from ts_debug('english', '') as c ; alias | description | token | dictionaries | dictionary | lexemes ---+-+--+--++- tag | XML tag | |

[GENERAL] between SIMPLE and ENGLISH FULL TEXT Configuration in Postgresql 9.0.1.?

What is the difference between SIMPLE and ENGLISH FULL TEXT Configuration in Postgresql 9.0.1.

[GENERAL] Full Text Partial Match at begining

Is it possible to match "%text' in Postgresql 9 Full Text. select to_tsvector('english','this is advantage') @@ to_tsquery('tage'); f I need to get result true for this type of matching. Any idea please.

[GENERAL] Which query is good - IN or OR

A simple query I can write in any of the following two ways: 1. Select col1 from table where col2 in ('A','B'); 2. Select col1 from table where col2 = 'A' or col2 = 'B' Here IN condition may be more than two. I need to know which one is good for good performance. Any idea please.

[GENERAL] postgresql 9 devel rpm

Can anyone tell me please where I can get the postgresql 9 devel rpms?

[GENERAL] pg_statsinfo problem

Does any one use pg_statsinfo for Postgresql 9.0.1? Is there any similar tools to collect DB Snapshots? I am facing problem during pg_statsinfo configuration. I set as follows: shared_preload_libraries = 'pg_statsinfo,pg_stat_statements' custom_variable_classes = 'pg_statsinfo' But my Db is not s

[GENERAL] Postgresql 9 connection problem

I have very strange problem with my application, upon occasions I get following error when connecting to postgres. This happens without any pattern, often happens when I run some extensive tests in my application. Unable to connect to PostgreSQL server: server closed the connection unexpectedly Th

[GENERAL] is it good to disable autovacuum and schedule routine vacuum process?

I am using Postgresql 9.0.1. I want to know which one is good regarding VACUUM - Routine VACUUM manualy or AutoVacuum. Recently, I found in my production that some tables were not vacuumed for a good period of time when the autovacuum was enabled and the DB was slow. I vacuumed the DB manually an

[GENERAL] What is pg_toast_temp_* in my DB?

What is pg_toast_temp_* in my DB?

[GENERAL] cast problem in Postgresql 9.0.1

I migrated my DB from Postgresql 8.1 to Postgresql 9.0.1. I have a table "testtab" \d testtab id int, hours varchar When I execute the following: select sum(hours) from testtab I get cast error. Then, I created following IMPLICIT CAST functions in my DB => CREATE FUNCTION pg_catalog.integer(va

[GENERAL] index for ilike operation

I found that in Postresql 9.0.3 documentation: *It is also possible to use B-tree indexes for ILIKE and ~*, but only if the pattern starts with non-alphabetic characters, i.e., characters that are not affected by upper/lower case conversion. *Can anyone please tell me how to configure that?* *

[GENERAL] <>(not equal to) in where clause can use index

Is it possible to add some operator class in Postgresql 9 so that <>(not equal to) in where clause can use index? If yes how? Any idea please.

[GENERAL] Full text t_tsquery is giving error

I am getting the following results: select * from ts_debug('Rumman (AI)'); alias | description | token |dictionaries| dictionary | lexemes ---+-+++--+-- asciiword | Word, all ASCII | Rumman | {syn,english

[GENERAL] question regarding full_page_write

I can't clearly understand what FULL_PAGE_WRITE parameter is stand for. Documentation suggest that If I make it OFF, then I have the chance for DB crash. Can anyone please tell me how it could be happened?

[GENERAL] why does the toast table exist?

I found in my Postgresql 9.0.1 DB as follows: select oid,relname,reltoastrelid,relpages,relfilenode,reltuples from pg_class where oid in ( 90662,90665); -[ RECORD 1 ]-+--- oid | 90662 relname | audit_trial reltoastrelid | 90665 relpages | 7713 relfilenode |

[GENERAL] ERROR: invalid byte sequence for encoding "UTF8": 0xc35c

I am getting error in Postgresql 9.0.1. update import_details_test set data_row = '["4","1 Monor JoÃ\u083ão S. AntÃ\u0083ão (Schools 21,22,76)( Ru)","http://www.asdas.aa.nj.us","","908 436 4861","","","--None--","",","","--None--","","","0","","--None--","0","2008-12-29 17:53:08","","2010-08-2

[GENERAL] index bloat query understand

I got a query to calculate index bloat as follows: http://pgsql.tapoueh.org/site/html/news/20080131.bloat.html --- CREATE OR REPLACE VIEW bloat AS SELECT schemaname, tablename, reltuples::bigint, relpages::bigint, otta, ROUND(CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages/o

[GENERAL] pg_connect connection problem

I am connecting to Postgresql 9 from my php application using pg_connect. After 30 concurrent connections from a single host, I am getting database connection error at my app. Does any one have any idea why the problem is occurring.

[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

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

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

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 >

[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 wrote: > On 08/07/2014 12:40 PM, AI Rumman wrote: > >> >> Hi, >> >> I am getting the logs as follows: >> >

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

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

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

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

[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

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]

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]

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

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

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

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

[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] 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] 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] 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] 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] 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] 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 wrote: > On 08/27/2013 12:44 PM, AI Rumman wrote: > >> Hi, >> >> I have a string like: >> Gloucester Catholic vs. St. A

  1   2   3   >