Re: [GENERAL] Increased I/O / Writes

at this tool: https://github.com/grayhemp/pgcookbook/blob/master/statement_statistics_collecting_and_reporting.md -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp https://github.com/grayhemp +1 (415) 867-9984, +7 (499) 346-7196, +7 (988) 8

Re: [GENERAL] replicating many to one

hat. [1] http://skytools.projects.pgfoundry.org/skytools-3.0/ [2] http://www.slony.info/ -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (499) 346-7196, +7 (988) 888-1979 gray...@gmail.com -- Sent via pgsql-general maili

Re: [GENERAL] Understanding and implementing a GiST Index

that is on the operations > of an entire query? Looking at the number of tree nodes touched for a scan > would be nice (and I would not be surprised if there is already a facility > for it). > > Project code is here if anyone is interested, any help would be great. I > have very little i

Re: [GENERAL] How to synchronize tables in remote (production) and local databases in case if the structure of local database's table has been modified

# alter table t add s text; ALTER TABLE skonoplev@[local]:5432 ~=# copy t(i) from '/tmp/t.dump'; COPY 5 skonoplev@[local]:5432 ~=# select * from t; i | s ---+--- 1 | 2 | 3 | 4 | 5 | (5 rows) -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/

Re: [GENERAL] Converting char to varchar automatically

name from pg_cast, pg_type as t1, pg_type as t2 where t1.oid = castsource and t2.oid = casttarget and castmethod = 'b' order by 1, 2; -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (499) 346-7196, +7 (988)

Re: [GENERAL] Converting char to varchar automatically

On Tue, Oct 7, 2014 at 10:16 AM, Tom Lane wrote: > Sergey Konoplev writes: >> BTW, where can I find a list of type1->type2 pairs that doesn't >> require full table lock for conversion? > > There aren't any. Sometimes you can skip a table rewrite, but that &g

Re: [GENERAL] Converting char to varchar automatically

ot once per column. > > regards, tom lane > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Kind regards, Sergey Konoplev Post

[GENERAL] PgToolkit 1.0.2 release testing

in database adapters - Made it to process TOAST tables and indexes providing bloat information and rebuilding instructions - Set an additional protection against the "incorrect result of cleaning" error -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.li

Re: [GENERAL] statement_timeout doesn't work

On Mon, Jul 21, 2014 at 11:32 AM, Sergey Konoplev wrote: > On Mon, Jul 21, 2014 at 10:16 AM, David G Johnston > wrote: >>> So, If I separate the commands everything will will work as expected, >>> correct? >> >> I would assume so. >> >> If you wait

Re: [GENERAL] statement_timeout doesn't work

essfully then both of those commands will die if they > exceed the timeout specified. Thank you. I'll try it. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (499) 346-7196, +7 (988) 888-1979 gray...@gmail.com -- Sent vi

Re: [GENERAL] statement_timeout doesn't work

meout-doesn-t-work-tp5811704p5812037.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] statement_timeout doesn't work

No hope here? On Tue, Jul 15, 2014 at 9:49 PM, Sergey Konoplev wrote: > Hi, > > PostgreSQL 9.2.7, Linux 2.6.32 > > Several days ago I found one of my servers out of connections, > pg_stat_activity showed that everything was waiting for the DROP/ALTER > INDEX transaction (s

[GENERAL] statement_timeout doesn't work

Hi, PostgreSQL 9.2.7, Linux 2.6.32 Several days ago I found one of my servers out of connections, pg_stat_activity showed that everything was waiting for the DROP/ALTER INDEX transaction (see the record 2 below), that, as I guess, was waiting for the function call (record 1). -[ RECORD 1 ]

Re: [GENERAL] Synonym/thesaurus dictionaries for FTS

FYI On Wed, Jul 9, 2014 at 4:58 PM, Sergey Konoplev wrote: > Are there any publicly available synonym/thesaurus dictionaries for FTS? So, I've found several worth attention open projects providing synonyms and thesaurus dictionaries. http://archive.services.openoffice.org/pu

[GENERAL] Synonym/thesaurus dictionaries for FTS

Hi, Are there any publicly available synonym/thesaurus dictionaries for FTS? Thank you. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (499) 346-7196, +7 (988) 888-1979 gray...@gmail.com -- Sent via pgsql-general

Re: [GENERAL] Vacuuming strategy

good idea to set scale factor and this high threshold instance wide. You can try per table settings instead if you want. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (499) 346-7196, +7 (988) 888-1979 gray...@gmail.c

Re: [GENERAL] Vacuuming strategy

0 autovacuum_max_workers = 5 autovacuum_naptime = 10s autovacuum_vacuum_scale_factor = 0.05 autovacuum_analyze_scale_factor = 0.05 autovacuum_vacuum_cost_delay = 5ms -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (499) 346-7196, +7 (988) 888-1979 gr

Re: [GENERAL] WAL archiving from a standby backup strategy Postgresql 9.3

On Thu, Apr 24, 2014 at 4:37 AM, Michael Paquier wrote: >> 9.3 introduced a cascading replication feature [1] that allows you to >> stream WAL from standbys to other standbys. > Picky correction: cascading replication has been added in 9.2. Oh, right, pardon me. -- Kind

Re: [GENERAL] WAL archiving from a standby backup strategy Postgresql 9.3

g from 9.2, you can make base backups from standbys [3]. [1] http://www.postgresql.org/docs/9.3/static/warm-standby.html#CASCADING-REPLICATION [2] http://www.postgresql.org/docs/9.3/static/app-pgreceivexlog.html [3] http://www.postgresql.org/docs/9.3/static/app-pgbasebackup.html -- Kind

Re: [GENERAL] Disable an index temporarily

On Apr 19, 2014 1:53 PM, "Torsten Förtsch" wrote: > > Hi, > > an index can be INVALID (pg_index.indisvalid=false). > > I want to temporarily disable an index so that it won't be used to > access data but will still be updated. > > Can I simply set pg_index.indisvalid=false and later turn it true a

[GENERAL] Re: [PERFORM] Hot standby 9.2.1 PANIC: WAL contains references to invalid pages

e problem appears when hot_standby is set on, so you need to turn it off. Also, take a look at the link below: http://www.databasesoup.com/2013/12/why-you-need-to-apply-todays-update.html -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415)

Re: [GENERAL] streaming replication and recovery

http://www.postgresql.org/docs/current/static/functions-admin.html#FUNCTIONS-RECOVERY-CONTROL [3] http://www.postgresql.org/docs/current/static/warm-standby.html#SYNCHRONOUS-REPLICATION -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-998

Re: [GENERAL] Thousands of errors...what happened?

. It is out of file descriptors. Assuming you are on Linux, the ways to fix it (to increase the limit) are described by the link [1]. Chose one that suits you better. [1] http://www.cyberciti.biz/faq/linux-increase-the-maximum-number-of-open-files/ -- Kind regards, Sergey Konoplev PostgreSQL

Re: [GENERAL] Upgrade: 9.0.5->9.4

a locale difference: > > lc_ctype cluster values do not match: old "C", new "en_US.UTF-8" > Failure, exiting > > How do I remedy this? Drop your newly created 9.4 cluster dir and re-init it with C locale like this: initdb --locale=C -D ... -- Kind regar

Re: [GENERAL] Dead rows not getting removed during vacuum

ctivity or idling in transaction backends on your slaves? -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To

Re: [GENERAL] Dead rows not getting removed during vacuum

On Thu, Mar 20, 2014 at 5:27 AM, Granthana Biswas wrote: > Has anyone ever faced the issue of dead rows not getting removed during > vacuum even if there are no open transactions/connections? What does the pg_prepared_xacts view show? -- Kind regards, Sergey Konoplev PostgreSQL Consulta

Re: [GENERAL] Backup WAL Replication Server

does pg_backup create an output file? (the same server/file system, network partition, the same and then scp to another server, something else) What is the file system utilization for the partition where you keep your data with and without pg_backup? (iostat -xk 10) -- Kind regards, Sergey Kono

Re: [GENERAL] [BUGS] Very slow query in PostgreSQL 9.3.3

. It should do the trick. If it wont, please, show the plans. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@po

Re: [GENERAL] Adding a non-null column without noticeable downtime

oo_migration_tmp ON foo (id) WHERE bar IS NULL; /* PSQL=/usr/local/bin/psql total_updated=0 updated=1 time ( while [ $updated -gt 0 ]; do updated=$(($PSQL -X Game2 <&1 */ DROP INDEX foo_migration_tmp; ANALYZE foo; ALTER TABLE foo ALTER bar SET NOT NULL; -- Kind regards, Ser

Re: [GENERAL] Ordering Results by a Supplied Order

p://www.depesz.com/2010/07/25/how-to-order-by-some-random-query-defined-values/. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray...@gmail.com -- Sent via pgsql-general mailing list

Re: [GENERAL] Postgresql GROUP BY "SIMILAR" but not equal values

I would recommend OP to look at the PL/R http://www.joeconway.com/plr/doc/index.html. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray...@gmail.com -- Sent via pgsql-general mailing lis

Re: [GENERAL] The timezone oddities

On Tue, Feb 4, 2014 at 12:41 PM, Adrian Klaver wrote: > On 02/04/2014 12:09 PM, Sergey Konoplev wrote: > So the postgresql.conf is the one created by initdb for this particular > installation? > > If that is the case it would seem that initdb could not determine what the > time

Re: [GENERAL] The timezone oddities

On Tue, Feb 4, 2014 at 11:29 AM, Adrian Klaver wrote: > On 02/04/2014 11:23 AM, Sergey Konoplev wrote: >> Gentoo Linux, PostgreSQL 9.2.4. >> >> I'm trying to find out why postgres uses a specific time zone that I >> don't expect to be used, and without any s

[GENERAL] The timezone oddities

refore, the time zone is GMT. postgres@ip-xx ~ $ psql psql (9.2.4) Type "help" for help. postgres=# show timezone; TimeZone -- GMT (1 row) Do you have any thoughts of where else this GMT could be set from? Thank you in advance. -- Kind regards, Sergey Konoplev PostgreSQL Con

Re: [GENERAL] Drop all overloads of a function without knowing parameter types

On Tue, Feb 4, 2014 at 8:35 AM, Alvaro Herrera wrote: > Sergey Konoplev escribió: >> On Mon, Feb 3, 2014 at 10:09 AM, Tom Lane wrote: >> > People periodically ask for extensions flavored more or less like this, >> > but I'm suspicious of building any such t

Re: [GENERAL] Drop all overloads of a function without knowing parameter types

On Tue, Feb 4, 2014 at 8:35 AM, Alvaro Herrera wrote: > Sergey Konoplev escribió: >> On Mon, Feb 3, 2014 at 10:09 AM, Tom Lane wrote: >> > People periodically ask for extensions flavored more or less like this, >> > but I'm suspicious of building any such t

Re: [GENERAL] Drop all overloads of a function without knowing parameter types

he only signature, but if there are 2 or more signatures then print an error specifying all the forms of the function, eg.: ERROR: Can not drop function 'foo' because it has more then one signature: foo(integer), foo(text). I am sure It would simplify life significantly. -- Kind regards, Se

[GENERAL] PgToolkit v1.0.1 release testing

--help output - Got rid of hard-coded connection parameters (thanks to Hubert "depesz" Lubaczewski) - Allowed processing of the postgres and template1 databases - Resolved the several simultaneously running instances collisions issue (thanks to Gonzalo Gil) Thank you in advance. --

Re: [GENERAL] Filtering queries by IP

DT='2013-11-21' SUB='192.168.1.12' rm tmp/filtered.log if [ ! -z $SUB ]; then cat /var/log/postgresql/postgresql-$DT.log | \ perl -pe 's/(^\d{4}-\d{2}-\d{2} )/###$1/; s/\n/@@@/; s/###/\n/' | \ grep -E "$SUB" | perl -pe 's/@@@/\n/g' &

Re: [GENERAL] Looking for settings/configuration for FASTEST reindex on idle system.

ion in one line) by 5 in parallel. You can also cluster all your tables a similar way by several tables in parallel. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray...@gmail.c

Re: [GENERAL] Sudden slow down and spike in system CPU causes max_connections to get exhausted

lways > /sys/kernel/mm/transparent_hugepage/enabled echo madvise > /sys/kernel/mm/transparent_hugepage/defrag , the names might be slightly different on CentOS, like redhat_transparent_hugepage or something like this, I don't remember exactly. -- Kind regards, Sergey Konoplev

Re: [GENERAL] help interpreting pg_stat_user_index view values

On Sun, Jan 5, 2014 at 2:19 PM, Gavin Flower wrote: > On 06/01/14 11:08, Sergey Konoplev wrote: > [...] > >> An index might be considered as useless when there were no idx scans for >> the significantly long period. However it might be non-trivial to define >> this p

Re: [GENERAL] help interpreting pg_stat_user_index view values

cific enough > to be a relatively useful index? No. The reason is the same as in the previous question. Eg OFFSET 150 LIMIT 50. > I am assuming an index with values like idx4 could never exist, it is an > impossible result. Is that a correct assumption? Yes, this is correct one. -- Ki

Re: [GENERAL] Replication failed after stalling

d checkpoint warnings happen on master. You can see it in logs. Turn log_checkpoints on if it is off. And also how many WAL your system generates and for what period. ls -lt /path/to/pg_xlog/ | wc -l ls -lt /path/to/pg_xlog/ | head ls -lt /path/to/pg_xlog/ | tail -- Kind regards, Sergey Kono

Re: [GENERAL] Replication failed after stalling

On Mon, Dec 30, 2013 at 8:56 PM, Joe Van Dyk wrote: > On Mon, Dec 30, 2013 at 10:27 AM, Sergey Konoplev wrote: >> On Mon, Dec 30, 2013 at 12:02 AM, Joe Van Dyk wrote: >> > On Sun, Dec 29, 2013 at 10:52 PM, Sergey Konoplev >> > wrote: >> >> On Sun, Dec

Re: [GENERAL] Replication: GZIP compression in WAL sender/receiver processes communication?

l with compression, a watchdog and lock management. Very useful for cross data center streaming. [1] https://github.com/grayhemp/pgcookbook/blob/master/ssh_tunnel_with_compression_setup.md -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-99

Re: [GENERAL] Replication failed after stalling

ot_standby_feedback = on, right? > > In that case that is expected behaviour. > Some change on the master conflicted with the query on the standby, > perhaps with a tuple cleaned up after a HOT update. Replication will > stall until the query is done. IIRC, the applying process

Re: [GENERAL] Replication failed after stalling

On Sun, Dec 29, 2013 at 9:56 PM, Joe Van Dyk wrote: > On Wed, Dec 18, 2013 at 3:39 PM, Sergey Konoplev wrote: >> >> On Wed, Dec 18, 2013 at 11:26 AM, Joe Van Dyk wrote: >> > I'm running Postgresql 9.3. I have a streaming replication server. >> > Someone

Re: [GENERAL] FATAL: index contains unexpected zero page at block

resql.org/docs/current/static/release-9-3-2.html -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org)

Re: [GENERAL] pg_upgrade & tablespaces

On Thu, Dec 19, 2013 at 1:18 PM, Bruce Momjian wrote: > On Thu, Dec 19, 2013 at 01:08:18PM -0800, Sergey Konoplev wrote: >> On Thu, Dec 19, 2013 at 12:49 PM, Joseph Kregloh >> wrote: >> > On Thu, Dec 19, 2013 at 3:46 PM, Sergey Konoplev wrote: >> >> Ca

Re: [GENERAL] pg_upgrade & tablespaces

On Thu, Dec 19, 2013 at 12:49 PM, Joseph Kregloh wrote: > On Thu, Dec 19, 2013 at 3:46 PM, Sergey Konoplev wrote: >> On Thu, Dec 19, 2013 at 12:27 PM, Joseph Kregloh >> wrote: >> > So what I get from this is that it does create the correct 9.3 files in >> >

Re: [GENERAL] pg_upgrade & tablespaces

/local/pgsql/data/drupal_dbspace/ as the pg_upgrade query tries to do. Can you show what ls -l /home/jkregloh/pg_data/data/pg_tblspc/ prints, please? -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 8

Re: [GENERAL] Replication failed after stalling

query at 9:30 am and replication data started catching up. What do you mean by "COPY on the standby halted replication"? -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray.

Re: [GENERAL] Question(s) about crosstab

ake views and use them with the tablename argument. [1] http://www.cureffi.org/2013/03/19/automatically-creating-pivot-table-column-names-in-postgresql/ -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-19

Re: [GENERAL] Prefix search on all hstore values

e hstore. # select to_tsvector('en_name=>"oh yes", fr_name=>oui'::hstore::text) @@ 'ye:*'; ?column? -- t -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-19

Re: [GENERAL] Prefix search on all hstore values

an array, but that doesn't seem to work with prefix searching. > Any pointers would be much appreciated! The idea is to de-normalize the hstore_column to an assisting table with 2 columns: original_record_id, hstore_column_value. And to create a btree index on hstore_column_value that w

Re: [GENERAL] Primary Key Index Bloat?

docs/9.0/static/contrib.html -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your sub

Re: [GENERAL] Equivalent syntax of PL/SQL using array in PL/pgSQL

$$ declare rec a[]; begin rec[1] := (1, 'a')::a; rec[2] := (2, 'b')::a; rec[1] := rec[1] #= (hstore('id', 3::text) || hstore('n', null)); raise info '% %', rec[1].id, rec[1].n; end $$; INFO: 3 DO [1] http://www.postgresql.org/docs

Re: [GENERAL] Tuning 9.3 for 32 GB RAM

md -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.po

Re: [GENERAL] database redesign

suggest using serial/bigserial types instead of integer/bigint + sequence. This will automatically create a sequence that is depended on the table. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 8

Re: [GENERAL] autovaccum task got cancelled

ONTEXT: automatic vacuum of table > "sd3ops1.public.file" >From the release notes to 9.0.12 [1]: <> [1]: http://www.postgresql.org/docs/9.0/static/release-9-0-12.html -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1

Re: [GENERAL] postgres 9.0.4 configuration and performance issue

try pg_reorg. When you move your cluster to the 64bit version you need to do dump/restore, because it is the only way to migrate between architectures. In this case you don't need to use pg_reorg, as your cluster will be recreated "from scratch". -- Kind regards, Sergey Konoplev Po

Re: [GENERAL] postgres 9.0.4 configuration and performance issue

rom various blogs that I have read ) Hm.. looks like I missed this fact. Is it possible to install the 64bit one? -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray...@gmail.com --

Re: [GENERAL] like & optimization

nd compare with the above. Note, if you have intensive writes on the table you would probably want to set FASTUPDATE to off on the GIN index, because it might lead to unpredictable stalls (http://www.postgresql.org/docs/9.3/static/gin-implementation.html#GIN-FAST-UPDATE). -- Kind regards, Sergey Konopl

Re: [GENERAL] postgres 9.0.4 configuration and performance issue

max_workers = 5 autovacuum_naptime = 5s autovacuum_vacuum_scale_factor = 0.05 autovacuum_analyze_scale_factor = 0.05 autovacuum_vacuum_cost_delay = 5ms -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +

Re: [GENERAL] Postgres replication question :- One master 2 slaves 9.0.10

ne: line 0: Bad configuration option: ExitOnForwardFailure > command-line: line 0: Bad configuration option: ExitOnForwardFailure It looks like your SSH version or implementation doesn't support ExitOnForwardFailure. Try to find an alternative. -- Kind regards, Sergey Konoplev PostgreSQL C

Re: [GENERAL] Postgres replication question :- One master 2 slaves 9.0.10

good idea to set hot_standby_feedback to on and > max_standby_archive_delay to something larger than 30s Doesn't replica need a connection to master for hot_standby_feedback? -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-99

Re: [GENERAL] Postgres replication question :- One master 2 slaves 9.0.10

on not to do it streaming? BTW, you will find the SSH tunnel instructions here http://code.google.com/p/pgcookbook/wiki/Streaming_Replication_Setup -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988)

Re: [GENERAL] truncate/rotate pgbouncer log using .ini settings

it. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: h

Re: [GENERAL] ENUM drop label workaround

> You might need to recreate some foreign keys. We will have a lot of big tables with such fields and we couldn't afford downtime on the ALTERs and other things. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901)

[GENERAL] ENUM drop label workaround

dom_column = 'ccc'; ERROR: invalid input value for enum ref: "ccc" Are there any caveats of this solution and may be there is a better one? -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-049

Re: [GENERAL] Strange message from pg_receivexlog

* * * /bin/bash /var/lib/pgsql/tmsdb/archive_wal.sh >>/var/log/tmsdb/archive_wal.log -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray...@gmail.com -- Sent via pgsql-ge

Re: [GENERAL] Strange message from pg_receivexlog

tate --state NEW -m tcp -p tcp --dport 22 -j ACCEPT -A INPUT -j REJECT --reject-with icmp-host-prohibited -A FORWARD -j REJECT --reject-with icmp-host-prohibited Nothing looks suspicious for me. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1

[GENERAL] Strange message from pg_receivexlog

houghts what it was? -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your sub

Re: [GENERAL] Performance of ORDER BY RANDOM to select random rows?

record IDs are - in our case, if we > delete a large number of records, it might affect things. You can try to look at pg_stats.histogram_bounds to work the issue around, however it is just my assumption, I have newer tried it. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA

Re: [GENERAL] Fastest Index/Algorithm to find similar sentences

), and it successfully finds it. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make chang

Re: [GENERAL] How to avoid Force Autovacuum

On Thu, Aug 8, 2013 at 10:59 PM, Vishalakshi Navaneethakrishnan wrote: > Now the problem is autovacuum.. why it was invoked and increased the load? > How to avoid this? Upgrade to the latest minor version 9.2.4 first. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA

Re: [GENERAL] Performance of ORDER BY RANDOM to select random rows?

t depends on the distribution of id values in the table, but in the most cases I faced it works good. I had an idea to play with pg_stats.histogram_bounds to work around the described issue, but it was never so critical for tasks I solved. -- Kind regards, Sergey Konoplev PostgreSQL Consultant an

Re: [GENERAL] How to avoid Force Autovacuum

select name, setting from pg_settings where name ~ 'vacuum' and setting <> reset_val; -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray...@gmail.com -- Sent via pgsql-gen

Re: [GENERAL] Self referencing composite datatype

On Wed, Aug 7, 2013 at 11:38 PM, Alban Hertroys wrote: > On Aug 8, 2013, at 4:11, Sergey Konoplev wrote: >> create table node as ( >> id integer primary key, >> r integer, s integer, >> children integer[] >> ); >> >> and check integrity by

Re: [GENERAL] How to avoid Force Autovacuum

/sys/kernel/mm/transparent_hugepage/enabled cat /sys/kernel/mm/transparent_hugepage/defrag -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray...@gmail.com -- Sent via pgsql-general mailing list

Re: [GENERAL] Self referencing composite datatype

On Wed, Aug 7, 2013 at 7:11 PM, Sergey Konoplev wrote: > so you could download 9.3rc2 and experimant with it. Sorry, 9.3beta2 of course. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1

Re: [GENERAL] Self referencing composite datatype

without FK create table node as ( id integer primary key, r integer, s integer, children integer[] ); and check integrity by triggers. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 88

Re: [GENERAL] Pl/Python runtime overhead

ke python? I'd really > like to get a good grip of the architecture of this type of extension, and > possibly attempt to introduce a language of my own choosing. The docs I've > seen so far are mostly too specific, making it a bit for hard for me to see > the forest fro

Re: [GENERAL] How to avoid Force Autovacuum

, i have seen the pid of > autovacuum process in the result but the query filed is "Empty" Was autovacuum the only process that you saw in pg_stat_activity? What OS do you use? Do you use huge pages? -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linked

Re: [GENERAL] Exit code -1073741819

lar moment of time. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA Profile: http://www.linkedin.com/in/grayhemp Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979 Skype: gray-hemp Jabber: gray...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@

Re: [GENERAL] Hierarchical numeric data type

k? Suggestions? Use integer arrays. It works just like you need select array_to_string(c, '.') from (values (array[1,10,2]), (array[1,5,3])) as sq(c) order by c; array_to_string - 1.5.3 1.10.2 and it is pretty fast when indexed. -- Kind regards, Sergey Konoplev Post

Re: [GENERAL] Exit code -1073741819

ing or dropping the index to see if that helps? >> >> >>> >>> >>> Thank you! >>> >>> >>> >>> Reimer >>> 47-3347-1724 47-9183-0547 msn: carlos.rei...@opendb.com.br >>> <mailto:carlos.rei...@opendb.com.br&

Re: [GENERAL] Installing 9.2 on Ubuntu from packages: what is the current recommendation?

; Thanks, > Tim Bowden > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA Profile:

Re: [GENERAL] Fastest Index/Algorithm to find similar sentences

to_tsvector('The tiger is the largest cat'); ?column? -- t Or may be I understand something wrong again? > > Janek > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/m

Re: [GENERAL] Fastest Index/Algorithm to find similar sentences

On Thu, Jul 25, 2013 at 3:54 PM, Janek Sendrowski wrote: > The Fulltextsearch is not really suitable because it doesn't have a tolerance. What do you exactly mean by tolerance here? -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA Profile: http://www.linkedin.com/in/

Re: [GENERAL] Speed up Switchover

You can find the discussion about that on following link: > http://www.postgresql.org/message-id/flat/ca+tgmoy4j+p7jy69ry8gposmmdznyqu6dtionprcxavg+sp...@mail.gmail.com#ca+tgmoy4j+p7jy69ry8gposmmdznyqu6dtionprcxavg+sp...@mail.gmail.com -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA

Re: [GENERAL] Speed up Switchover

is that pg_rewind uses the WAL to determine changed data blocks, and does not require reading through all files in the cluster. That makes it a lot faster when the database is large and only a small portion of it differs between the clusters. -- Kind regards, Sergey Konoplev PostgreSQL Consul

Re: [GENERAL] Rule Question

best way here is to use trigger that does new.a = new.b. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA Profile: http://www.linkedin.com/in/grayhemp Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979 Skype: gray-hemp Jabber: gray...@gmail.com -- Sent via p

Re: [GENERAL] unique index corruption

. However, if you have some high/bulk-update/delete operations autovacuum might not manage with bloat, and in this case you can use this tool pgcompactor (https://code.google.com/p/pgtoolkit/) in conjunction with pgstattuple extension or pg_repack (https://github.com/reorg/pg_repack). -- Kind

Re: [GENERAL] Transaction control in shards through PLPROXY

s - it could cause ones, mostly if somebody forget to do commit/rollback as it still holds locks. Moreover it involves application<->DBs communications and persistence, so it is surely might affect performance. > > Warm regards, > GB > > > On Mon, Jul 15, 2013 at 10

Re: [GENERAL] Transaction control in shards through PLPROXY

> updates on shards can be rolled back if any one among the set fails? It is called two-phase commit. You need to consult with this [1] section of documentation. [1] http://www.postgresql.org/docs/9.2/static/sql-prepare-transaction.html -- Kind regards, Sergey Konoplev PostgreSQL Consul

Re: [GENERAL] Triggers NOT running as table owner

th SECURITY DEFINER could be used to access to the objects of their owners illegally. > > --strk; > > http://strk.keybit.net > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.pos

Re: [GENERAL] Get multiple columns with counts from one table.

the tablefunc module will help you to get this. http://www.postgresql.org/docs/9.2/static/tablefunc.html#AEN144882 It is documented pretty good and has a lot of useful examples. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA Profile: http://www.linkedin.com/in/grayhemp Phone: USA +1

Re: [GENERAL] Really poor gist index performance with tstzrange types

e(now(), now(), '[]'::text) && duration)) -> Bitmap Index Scan on f_duration_idx1 (cost=0.00..918.26 rows=1 width=0) (actual time=0.030..0.030 rows=0 loops=1) Index Cond: (tstzrange(now(), now(), '[]'::text) < duration) Total runtime: 0.

  1   2   3   >