Re: [GENERAL] hot standby questions

2017-08-04 5:58 GMT+12:00 Jeff Janes : > On Wed, Aug 2, 2017 at 8:19 PM, armand pirvu > wrote: > >> >> Hi >> >> Just trying to put together the hot_standby setup >> All docs I read are pointing to use as prefered method to use >> pg_basebackup to set

Re: [GENERAL] Monitoring of a hot standby with a largely idle master

2017-07-13 20:15 GMT+12:00 Michael Paquier : > On Thu, Jul 13, 2017 at 5:26 AM, Jeff Janes wrote: > > > > I think that none of the recovery information functions > > (https://www.postgresql.org/docs/9.6/static/functions- >

Re: [GENERAL] current postgresql logfile being written to?

2017-06-22 14:16 GMT+12:00 hvjunk <hvj...@gmail.com>: > > On 22 Jun 2017, at 4:06 AM, Lucas Possamai <drum.lu...@gmail.com> wrote: > > > > 2017-06-22 13:54 GMT+12:00 hvjunk <hvj...@gmail.com>: > >> Hi there, >> >> I was hoping for a meth

Re: [GENERAL] current postgresql logfile being written to?

2017-06-22 13:54 GMT+12:00 hvjunk : > Hi there, > > I was hoping for a method (like archive_command) to handle logfile > processing/archiving/compression, but unless doing it the logrotate way, > I don’t see anything that postgresql provides. Is that correct? > > The closest I

Re: [GENERAL] Huge Pages - setting the right value

2017-06-12 9:52 GMT+12:00 Andrew Kerber : > Was that transparent hugepages or standard hugepages? databases commonly > have problems dealing with transparent hugepages. > > IN my case, it was the Transparent Hugepages Lucas

Re: [GENERAL] Huge Pages - setting the right value

2017-06-12 7:52 GMT+12:00 Andrew Kerber : > I am sure it does not. > > Sent from my iPhone > > > On Jun 11, 2017, at 10:50 AM, pinker wrote: > > > > Andrew Kerber wrote > >> I can't give you an absolutely authoritative answer, but because of the > >> way

Re: [GENERAL] the master has two sync slave, which slave can promote to master and the other slave as the new master's slave?

2017-03-19 17:52 GMT+13:00 lin : > Hi, > everyone. I am puzzled > > about > the question: the master

Re: [GENERAL] Custom type column index - Postgres 9.1

2017-01-17 10:15 GMT+13:00 Patrick B : > Hi guys, > > I've got a custom data type column... The query I'm using its looking over > 9 million rows. > > I've created a BTREE index but it didn't help on the speed. Is there any > special index for custom types? > > Thanks >

Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

> > ERROR: column "date_start" does not exist > > > Patrick > Patrick*** - trying on SQL fiddle i got that error when executing what Adrian suggested.

Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

2016-12-15 14:34 GMT+13:00 Adrian Klaver : > On 12/14/2016 05:19 PM, Patrick B wrote: > > Reading the suggestions might help:) > > Another try: > > CREATE or REPLACE FUNCTION l_extract(date_start text, date_end text)) > > RETURNS void AS $$ > > > begin >

Re: [GENERAL] Moving pg_xlog

2016-12-02 17:10 GMT+13:00 Michael Paquier : > On Fri, Dec 2, 2016 at 1:04 PM, Melvin Davidson > wrote: > > Well, while the location of pg_xlog is not currently configurable, on > Linux system the way to do it is to: > > 1. stop PostgreSQL > >

Re: [GENERAL] My DB has has 5TB, many operations are very slow (on Google Cloud Compute)

> > Instance RAM: 60GB > Instance CPU: 16Cores I got a 3TB Postgres Database running with a Dual Socket Octo Core Intel Xeon E5-2630v3 with 128GB of RAM and SATA disks. I think yes, you could improve your server's RAM. However Melvin is right, there is no enough information. Lucas

Re: [GENERAL] FATAL: could not receive data from WAL stream

2016-09-20 16:29 GMT+12:00 Lucas Possamai <drum.lu...@gmail.com>: > > > 2016-09-20 15:14 GMT+12:00 Venkata B Nagothi <nag1...@gmail.com>: > >> >> On Tue, Sep 20, 2016 at 12:38 PM, Patrick B <patrickbake...@gmail.com> >> wrote: >> >>

Re: [GENERAL] FATAL: could not receive data from WAL stream

2016-09-20 15:14 GMT+12:00 Venkata B Nagothi : > > On Tue, Sep 20, 2016 at 12:38 PM, Patrick B > wrote: > >> Hi guys, >> >> I got a slave server running Postgres 9.2 with streaming replication and >> wal_archive in an EC2 Instance at Amazon. >> >>

Re: [GENERAL] postgres insert + select + values - Pgsql 9.5

2016-09-16 10:07 GMT+12:00 Patrick B : > >> >> A select can make up columns too, not just what you've got in a table, >> so you can: >> >> select j_id, 'test-1 - comments' as comment from test2 where >> customer_id=88897; >> >> and then you can simply insert that into

Re: [GENERAL] Get date timestamp(3) without time zone column - PGSQL 9.5

2016-09-05 15:17 GMT+12:00 Patrick B : > >> You might want to share the version of PostgreSQL you are using. >> >> You might want to try date_trunc and AT TIMEZONE function/operators- >> >> https://www.postgresql.org/docs/9.4/static/functions-datetim >>

Re: [GENERAL] Monitorando WAL Files - PostgreSQL 9.2

Sorry guys.. wrong email 2016-07-07 9:18 GMT+12:00 Lucas Possamai <drum.lu...@gmail.com>: > Olá pessoal, tudo bem? > > Sei que no Postgres 9.4 há o pg_stat_archiver para verificar o > arquivamento e as falhas dos wal_files. > > Como posso monitorar isso na versão 9.2?

[GENERAL] Monitorando WAL Files - PostgreSQL 9.2

Olá pessoal, tudo bem? Sei que no Postgres 9.4 há o pg_stat_archiver para verificar o arquivamento e as falhas dos wal_files. Como posso monitorar isso na versão 9.2? Vocês tem alguma dica? Obrigado! Lucas

Re: [GENERAL] gin index postgres 9.2

yes.. I thought too. the results r still different

[GENERAL] gin index postgres 9.2

I've got the following query: SELECT COUNT(DISTINCT j0_.id) AS sclr0FROM ja_customers j0_WHERE ((LOWER(j0_.name_first) LIKE '%asd%' OR LOWER(j0_.name_last) LIKE '%asd%' OR LOWER(j0_.company) LIKE '%asd%' OR LOWER(j0_.phone) LIKE '%asd%' OR LOWER(j0_.mobile) LIKE

Re: [GENERAL] Unused indexes - PostgreSQL 9.2

On 24 May 2016 at 12:18, Jeff Janes <jeff.ja...@gmail.com> wrote: > On Mon, May 23, 2016 at 4:23 PM, Lucas Possamai <drum.lu...@gmail.com> > wrote: > > > > That index has been added just 3 hours ago, and you can see that the > > times_used goes over 41000

Re: [GENERAL] Unused indexes - PostgreSQL 9.2

Hello Melvin, how are you doing? >> > And what happens if you run this query? > > SELECT idstat.schemaname AS schema, >idstat.relname AS table_name, >indexrelname AS index_name, >idstat.idx_scan AS times_used, >

Re: [GENERAL] Increased I/O / Writes

On 24 May 2016 at 09:40, Kevin Grittner <kgri...@gmail.com> wrote: > On Mon, May 23, 2016 at 4:32 PM, Lucas Possamai <drum.lu...@gmail.com> > wrote: > > > The spikes were happening because, after the reboot, the HUGE PAGES were > > enabled. > > > >

Re: [GENERAL] Function PostgreSQL 9.2

Just an update here: IF (TG_OP = 'INSERT') THEN > UPDATE public.companies SET client_code_increment = > (client_code_increment + 1) WHERE id = NEW.company_id; The line above was updating the client_code_increment even if the customer was inserting data by hiimself, which is wrong. The

Re: [GENERAL] index on ILIKE/LIKE - PostgreSQL 9.2

Hi there, The problem was solved by using lowercase *New index:* > CREATE INDEX CONCURRENTLY ON public.ja_jobs (clientid, lower(title) > varchar_pattern_ops, time_job); *New query:* > SELECT DISTINCT title > FROM public.ja_jobs WHERE lower(title) LIKE lower('RYAN > WER') >

Re: [GENERAL] Connections - Postgres 9.2

On 17 May 2016 at 22:24, Sridhar N Bamandlapally wrote: > Hi > > I control this way > > if "state_change" is from longtime and "state" is idle then I use > > function: > *pg_terminate_backend ( integer ) * ==> return TRUE if > killed-successful else FALSE > > example: >

Re: [GENERAL] Increased I/O / Writes

Just an update about this... As you know, the server was rebooted. The spikes were happening because, after the reboot, the HUGE PAGES were enabled. After disabling them, all got back to normal. Cheers

Re: [GENERAL] Increased I/O / Writes

Following https://www.keithf4.com/a-large-database-does-not-mean-large-shared_buffers/ and http://pgtune.leopard.in.ua/ and https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server I changed the shared_buffer from 51GB to 35GB. Now, I'm getting spikes every 15 minutes. *FATAL*: sorry,

Re: [GENERAL] Increased I/O / Writes

This is my postgresql.conf at the moment: shared_buffer(51605MB) + effective_cache_size(96760MB) + work_mem(32MB) + max_connections(200) *= 148397.08 MB* My server has 128GB of RAM So, I'm using more RAM that I have. (not good at all) I'm glad that it wasn't me who put those confs in there :)

Re: [GENERAL] Connections - Postgres 9.2

On 17 May 2016 at 08:56, Venkata Balaji N <nag1...@gmail.com> wrote: > > On Tue, May 17, 2016 at 6:28 AM, Lucas Possamai <drum.lu...@gmail.com> > wrote: > >> hmm.. thanks for all the answers guys... >> >> >> One more question: Those IDLE c

Re: [GENERAL] Connections - Postgres 9.2

hmm.. thanks for all the answers guys... One more question: Those IDLE connections.. are using the server's resources? To solve that problem I would need a Pool connection, right? Would the pool connection solve that IDLE connections? But more important than that, are the IDLE connections using

Re: [GENERAL] index on ILIKE/LIKE - PostgreSQL 9.2

> > With those sizes, the gin index will probably be naturally kept mostly > in the file-system cache, if it is used regularly. So the original > slowness of your first query is likely just a cold-cache problem. Can > you generate a stream of realistic queries and see what it stabilizes > at? >

Re: [GENERAL] index on ILIKE/LIKE - PostgreSQL 9.2

> How big is the table? The gin index? shared_buffers? RAM? What > kind of IO system do you have, and how many other things were going on > with it? > - Just a reminder that I'm not running these tests on my prod server.. I'm running on my test server. So the confs will be different The

Re: [GENERAL] index on ILIKE/LIKE - PostgreSQL 9.2

> > >> > Trying redoing the query with CTE as below: > > WITH ja_jobs as > (SELECT DISTINCT title > FROM ja_jobs > WHERE clientid = 31239 AND time_job > 1457826264 > ) > SELECT title > FROM ja_jobs > WHERE title ILIKE 'RYAN WER%' > ORDER BY title > LIMIT 10; > hmm.. still slow

Re: [GENERAL] index on ILIKE/LIKE - PostgreSQL 9.2

> > >> > The main problem is WHERE title ILIKE '%RYAN WER%' > When you put a % on the left of the text, there is no way to optimize > that, so yes, it will be slow. > > If you can eliminate the leading percent and just have trailing, it will > be much faster. > > Hmm.. yep.. I suppose I can do

[GENERAL] index on ILIKE/LIKE - PostgreSQL 9.2

Hi there! I've got a simple but slow query: SELECT DISTINCT title > FROM ja_jobs WHERE title ILIKE '%RYAN WER%' > and clientid = 31239 AND time_job > 1457826264 > order BY title > limit 10 Explain analyze: Limit (cost=5946.40..5946.41 rows=1 width=19) (actual > time=2746.759..2746.772

Re: [GENERAL] Unused indexes - PostgreSQL 9.2

On 11 May 2016 at 09:50, Melvin Davidson wrote: > My bad, WHERE indexrelname = ' {YOUR INDEX NAME } '; > > Oh! ok... public ja_feedlog ix_ja_feedlog_visitids 1 94 GB 1939 MB 0 CREATE INDEX "ix_ja_feedlog_visitids" ON "ja_feedlog" USING "btree" ("visitid") public

Re: [GENERAL] Unused indexes - PostgreSQL 9.2

> > > And what happens if you run this query? > > SELECT idstat.schemaname AS schema, >idstat.relname AS table_name, >indexrelname AS index_name, >idstat.idx_scan AS times_used, >pg_size_pretty(pg_relation_size(quote_ident(idstat.schemaname) || > '.' ||

Re: [GENERAL] Unused indexes - PostgreSQL 9.2

> > >> > >Some time ago I changed the pg_stat_temp directory from > /var/lib/pgsq/whatever to /tmp > Have you checked the postgres log to see if there are any errors about it > not being able to write to the pg_stat_temp dir? > > Yep no errors =\

Re: [GENERAL] Unused indexes - PostgreSQL 9.2

> > > Sorry, I was too busy looking at the content. > > Has the size / # rows changed recently? If the planner thinks it can load > all the rows faster, it will use a seqscan regardless if you have an index. > > If that is the case, you can force index use by doing a > > SET enable_seqscan = off

Re: [GENERAL] Unused indexes - PostgreSQL 9.2

On 11 May 2016 at 09:06, Lucas Possamai <drum.lu...@gmail.com> wrote: > >>> >> My crystal ball is not working, you have a PostgreSQL version? >> > > Maybe you should have a look on the subject of this email... > > >> >> in postgresql.conf are

[GENERAL] Unused indexes - PostgreSQL 9.2

Hi all, I ran a query to search for unused indexes, and get some free space in my DB: SELECT > --*, > relid::regclass AS table, > indexrelid::regclass AS index, > --pg_size_pretty(pg_relation_size(indexrelid::regclass)) AS > index_size, >