[GENERAL] PG 9.1 - FK + Check constraint

Hi guys! The column ent_id bigint on table table1 refers to other tables. if ent_id = 1 then = table2.idif ent_id = 2 then = table3.idelse = no checks I need a FK Constraint on table table1 checking if records on table2.id and table3.idexists. I know that currently, CHECK expressions cannot con

Re: [GENERAL] count records in two different table joined by

2017-07-07 22:32 GMT+12:00 Thomas Markus : > Hi, > > Am 07.07.17 um 12:16 schrieb Patrick B: > > Hi guys! > > I've got 2 tables, and I need to get some data between them. > > test1: > > WITH account_status AS ( > select > CASE > WHEN regda

[GENERAL] count records in two different table joined by

Hi guys! I've got 2 tables, and I need to get some data between them. test1: WITH account_status AS ( select CASE WHEN regdate = 1 THEN 'yes' WHEN regdate = 2 THEN 'no' from test1 end as status_a ) select status_a from account_status group by status_a test2: WITH user_status AS ( sele

Re: [GENERAL] effective_io_concurrency increasing

2017-06-19 13:19 GMT+12:00 Melvin Davidson : > > > On Sun, Jun 18, 2017 at 9:02 PM, Patrick B > wrote: > >> Hi guys. >> >> I just wanna understand the effective_io_concurrency value better. >> >> My current Master database server has 16 vCPUS and I &g

[GENERAL] effective_io_concurrency increasing

Hi guys. I just wanna understand the effective_io_concurrency value better. My current Master database server has 16 vCPUS and I use effective_io_concurrency = 0. What can be the benefits of increasing that number? Also, do you guys have any recommendations? I'm using PG 9.2 and the official do

Re: [GENERAL] plpgsql function with offset - Postgres 9.1

2017-06-16 10:35 GMT+12:00 David G. Johnston : > On Thu, Jun 15, 2017 at 3:19 PM, Patrick B > wrote: > >> 2017-05-29 19:27 GMT+12:00 Albe Laurenz : >> >>> Patrick B wrote: >>> > I am running a background task on my DB, which will copy data from >>&

Re: [GENERAL] plpgsql function with offset - Postgres 9.1

2017-05-29 19:27 GMT+12:00 Albe Laurenz : > Patrick B wrote: > > I am running a background task on my DB, which will copy data from > tableA to tableB. For > > that, I'm writing a PL/PGSQL function which basically needs to do the > following: > > > > >

[GENERAL] Extract from text id other table - PG 9.1

Hi guys, I've got tableA with 3 columns. id(seriaL) | type(character varying(256)) | string(character varying(256)) I have the type/string value stored in another table, and from that i would like to get the id. Example: http://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=15c571caa36876f00a0a2eaace70

Re: [GENERAL] Regexp + spaces PG 9.1

2017-05-31 16:34 GMT+12:00 David G. Johnston : > On Tue, May 30, 2017 at 9:17 PM, Patrick B > wrote: > >> >> regexp_matches(name, '((main|medium).name/\d+.\d+)') as filename, >> >> >> Example here: http://sqlfiddle.com/#!15/5f4f0/4 >> >&g

[GENERAL] Regexp + spaces PG 9.1

Hi guys, I've got a column which stores the file name on it, the column is character varying(255). I'm selecting that value in a CTE query; basically: test1 AS ( SELECT regexp_matches(name, '((main|medium).name/\d+.\d+)') as filename, * from test1; ) select filename[1] from test1 Exa

[GENERAL] plpgsql function with offset - Postgres 9.1

Hi guys, I am running a background task on my DB, which will copy data from tableA to tableB. For that, I'm writing a PL/PGSQL function which basically needs to do the following: 1. Select the data from tableA 2. The limit will be put when calling the function 3. insert the selected dat

[GENERAL] union all taking years - PG 9.6

Hi guys. I have two tables, where 'tableA' is the old and 'tableC' is the new one. I say "new/old" because we are migrating the data from tableA to tableC soon. I created a view selecting from both tables, with a UNION ALL between them. When selecting from that view, it's really slow. I can't eve

Re: [GENERAL] Pattern Matching question - PG 9.6

2017-05-15 16:10 GMT+12:00 David G. Johnston : > On Sunday, May 14, 2017, Patrick B wrote: > >> >> Demo: http://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=3c3a3f870eb4d0 >> 02c5b4200042b25669 >> <http://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=c2fbb7da5a2397

Re: [GENERAL] Pattern Matching question - PG 9.6

2017-05-15 15:20 GMT+12:00 Patrick B : > Hi guys, > > Demo: http://dbfiddle.uk/?rdbms=postgres_9.6&fiddle= > 3c3a3f870eb4d002c5b4200042b25669 > <http://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=c2fbb7da5a2397f7cda5126ed239c080> > > FYI - NEW LINK http://dbf

[GENERAL] Pattern Matching question - PG 9.6

Hi guys, Demo: http://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=3c3a3f870eb4d002c5b4200042b25669 AS you can see above, when performing this query: > SELECT * FROM test1 WHERE client_id = 10 AND path ~ > '^/testfile/cl

Re: [GENERAL] Select from tableA - if not exists then tableB

2017-05-09 10:19 GMT+12:00 Brian Dunavant : > From what you're saying about migrating, I'm assuming the new table > has additional columns or something. If you can map the difference, > then you could use CTE's to select from the first table, and if > nothing is there, then pull from the second t

[GENERAL] Select from tableA - if not exists then tableB

Hi guys, I have two tables that supports the same data, but different table DDL (We are migrating all the data from one to another). What I need is basically: 1. Query looks for the data on table A, 2. if it doesn't find it on table A, go look for it on table B Now, how could I do that in a Sel

Re: [GENERAL] Nullsif ? PG 9.1

2017-04-09 20:18 GMT+12:00 Patrick B : > Hi guys, > > I've got this select: > SELECT split_part(n.node_full_path::text, '/'::text, 8)::integer AS id, > > However, not always I will get the 8th field, and because of that, i may > get no data somethings. >

[GENERAL] Nullsif ? PG 9.1

Hi guys, I've got this select: SELECT split_part(n.node_full_path::text, '/'::text, 8)::integer AS id, However, not always I will get the 8th field, and because of that, i may get no data somethings. Example: /filesuser/client/27801123/attachment/4510/main /filesuser/client//attachment/2131

Re: [GENERAL] regexp_matches where clause - PG 9.1

2017-04-07 14:19 GMT+12:00 David G. Johnston : > On Thu, Apr 6, 2017 at 7:15 PM, Patrick B > wrote: > >> >> David, >> That won't work. >> > > ​Actually, it works fine, you just keep moving the under-specified problem > space. > ​ > I'd s

Re: [GENERAL] regexp_matches where clause - PG 9.1

2017-04-07 14:08 GMT+12:00 David G. Johnston : > On Thu, Apr 6, 2017 at 6:33 PM, Patrick B > wrote: > >> When actually I just want the 'main'' >> > > ​SELECT * FROM tbl WHERE path_name ~ '/main$' ? > > David J. > ​ > David,

Re: [GENERAL] regexp_matches where clause - PG 9.1

2017-04-06 18:10 GMT+12:00 Patrick B : > > 2017-04-06 17:35 GMT+12:00 Arjen Nienhuis : > >> >> >> On Apr 6, 2017 05:57, "Patrick B" wrote: >> >> Hi guys, >> >> i've got this column: >> >> path_name character varying(25

Re: [GENERAL] regexp_matches where clause - PG 9.1

2017-04-06 17:35 GMT+12:00 Arjen Nienhuis : > > > On Apr 6, 2017 05:57, "Patrick B" wrote: > > Hi guys, > > i've got this column: > > path_name character varying(255) >> > > I store full S3 bucket path for the attachments of my applicat

[GENERAL] regexp_matches where clause - PG 9.1

Hi guys, i've got this column: path_name character varying(255) > I store full S3 bucket path for the attachments of my application on it; example: /{s3bucket}/filesuser/client/27801123/attachment/4510/main > /{s3bucket}/filesuser/client/27801123/attachment/4510/file > I wanna do a select, wh

Re: [GENERAL] effective_cache_size X shared_buffer

2017-04-03 13:23 GMT+12:00 Patrick B : > Hi guys. > > I'm thinking about increasing the query cache for my PG 9.2 server. > I've got a project happening, which is doing lots and lots of writes and > reads during the night, and in the morning I see PG cache warming up

[GENERAL] effective_cache_size X shared_buffer

Hi guys. I'm thinking about increasing the query cache for my PG 9.2 server. I've got a project happening, which is doing lots and lots of writes and reads during the night, and in the morning I see PG cache warming up again, as all the cache "was used" by those write and read tasks. So my enviro

Re: [GENERAL] Constraint + where

2017-03-20 13:27 GMT+13:00 Melvin Davidson : > > > On Sun, Mar 19, 2017 at 8:16 PM, Patrick B > wrote: > >> Hi guys, >> >> I've got a column 'type_note' on a new table that it's being designed: >> >> type_note varchar(32) N

[GENERAL] Constraint + where

Hi guys, I've got a column 'type_note' on a new table that it's being designed: type_note varchar(32) NOT NULL; On that column, there will be three different data: 1. yes 2. no 3. maybe I wanna create a FK but just when the data on that column is = maybe. How can I do that? Thanks!

[GENERAL] index on search - pg 9.2

Hi guys I've got a query that is doing a search with wildcards: > OR (description LIKE '%change%') Query: - Taking > 14 secs to run > SELECT j.id, ff.gtime > FROM public.status AS s > JOIN public.job AS j ON j.status_label_id = s.id AND j.clientid = 3369 > JOIN public.log AS ff ON ff.jobid = j

Re: [GENERAL] count case when - PG 9.2

2017-03-10 10:17 GMT+13:00 Yasin Sari : > if you want see account_status and the count()- try this: > > SELECT > > CASE > > WHEN AND c.regdate > EXTRACT(epoch FROM (now() - INTERVAL '14 > day')) > > THEN 'trial' > > WHEN last_pay > EXTRACT(epoch FROM (now() - INTERVAL '37 d

Re: [GENERAL] count case when - PG 9.2

2017-03-09 23:15 GMT+13:00 vinny : > On 2017-03-09 05:27, Patrick B wrote: > >> Hi guys. How can I count using 'CASE WHEN'? >> >> Example: >> >> SELECT >>>> >>> >>> CASE >>>> >>> >&

[GENERAL] count case when - PG 9.2

Hi guys. How can I count using 'CASE WHEN'? Example: SELECT CASE WHEN AND c.regdate > EXTRACT(epoch FROM (now() - INTERVAL '14 day')) THEN 'trial' WHEN last_pay > EXTRACT(epoch FROM (now() - INTERVAL '37 day')) THEN 'paying' END as account_status,

[GENERAL] Seq scan X Index scan

Hi all. I'm testing GIN indexes on a wildcard search. Basically I've created this on my test environment: create table test_gin_index ( > name_first CHARACTER VARYING(80) DEFAULT ''::CHARACTER VARYING, > name_last CHARACTER VARYING(80) DEFAULT ''::CHARACTER VARYING > ); insert into test_gin_ind

[GENERAL] Question about TOAST table - PostgreSQL 9.2

Hi all. I have a database which is 4TB big. We currently store binary data in a bytea data type column (seg_data BYTEA). The column is behind binary_schema and the files types stored are: pdf, jpg, png. *Getting the schema binary_schema size:* SELECT pg_size_pretty(pg_database_size('live_databa

Re: [GENERAL] bloat indexes - opinion

2017-02-25 17:53 GMT+13:00 Patrick B : > > > 2017-02-23 11:46 GMT+13:00 Jeff Janes : > >> On Tue, Feb 21, 2017 at 1:44 PM, Patrick B >> wrote: >> >>> Hi guys, >>> >>> I've got a lot of bloat indexes on my 4TB database.

Re: [GENERAL] bloat indexes - opinion

2017-02-23 11:46 GMT+13:00 Jeff Janes : > On Tue, Feb 21, 2017 at 1:44 PM, Patrick B > wrote: > >> Hi guys, >> >> I've got a lot of bloat indexes on my 4TB database. >> >> Let's take this example: >> >> Table: seg >> Inde

Re: [GENERAL] bloat indexes - opinion

2017-02-22 13:10 GMT+13:00 Adrian Klaver : > On 02/21/2017 03:41 PM, Patrick B wrote: > > 2017-02-22 11:11 GMT+13:00 Patrick B > <mailto:patrickbake...@gmail.com>>: > > > > 2017-02-22 10:59 GMT+13:00 Adrian Klaver > <mailto:adrian.kla...@aklaver.

Re: [GENERAL] bloat indexes - opinion

2017-02-22 11:11 GMT+13:00 Patrick B : > 2017-02-22 10:59 GMT+13:00 Adrian Klaver : > >> On 02/21/2017 01:44 PM, Patrick B wrote: >> > Hi guys, >> > >> > I've got a lot of bloat indexes on my 4TB database. >> > >> > Le

Re: [GENERAL] bloat indexes - opinion

2017-02-22 10:59 GMT+13:00 Adrian Klaver : > On 02/21/2017 01:44 PM, Patrick B wrote: > > Hi guys, > > > > I've got a lot of bloat indexes on my 4TB database. > > > > Let's take this example: > > > > Table: seg > > Index: ix_fil

[GENERAL] bloat indexes - opinion

Hi guys, I've got a lot of bloat indexes on my 4TB database. Let's take this example: Table: seg Index: ix_filter_by_tree Times_used: 1018082183 Table_size: 18 GB -- wrong. The table is mostly on pg_toast table. Its real size is 2TB Index_size: 17 GB Num_writes 16245023 Index definition: CREATE

[GENERAL] updating dup row

Hi all, how can I update a row with newest id from another table if it exists somewhere else? Example: *table test1* - id (primary key) - id_user_bill - clientid *table test2* - item_id - userid (there are duplicated rows here) - clientid - id (primary key) -- finding th

Re: [GENERAL] How to evaluate "explain analyze" correctly after "explain" for the same statement ?

2017-02-16 14:57 GMT+13:00 Patrick B : > > I've got two different scenarios: > > Production database server > PG 9.2 > >- I ran one single time, in a slave server that no queries go to that >server, and it took >10 seconds. > > Test database server >

Re: [GENERAL] How to evaluate "explain analyze" correctly after "explain" for the same statement ?

2017-02-16 14:08 GMT+13:00 Tom Lane : > Patrick B writes: > > For the first time I ran the query, it took >10 seconds. Now it is taking > > less than a second. > > How can I clear for good the cache? So i can have a real idea of how long > > the query takes t

Re: [GENERAL] How to evaluate "explain analyze" correctly after "explain" for the same statement ?

2017-02-16 13:25 GMT+13:00 Steve Atkins : > > > On Feb 15, 2017, at 3:58 PM, Patrick B wrote: > > > > Hi all, > > > > I just got a quick question about warm-cache. I'm using PG 9.2. > > > > When I execute this statement soon after I start/restart

[GENERAL] How to evaluate "explain analyze" correctly after "explain" for the same statement ?

Hi all, I just got a quick question about warm-cache. I'm using PG 9.2. When I execute this statement soon after I start/restart the database: explain select id from test where id = 124; The runtime is 40ms. Then, If I execute this statement just after the above one; explain analyze select i

Re: [GENERAL] get inserted id from transaction - PG 9.2

2017-02-15 12:19 GMT+13:00 Tom Lane : > Patrick B writes: > > I'm simply doing an insert and I want to get the inserted id with a > select. > > I'm doing this all in the same transactions. > > > Example: > > BEGIN; > > INSERT INTO test (id,na

[GENERAL] get inserted id from transaction - PG 9.2

Hi all, I'm simply doing an insert and I want to get the inserted id with a select. I'm doing this all in the same transactions. Example: BEGIN; INSERT INTO test (id,name,description) VALUES (default,'test 1','testing insert'); SELECT FROM test ORDER BY id DESC; -- I don't see the inserted ro

Re: [GENERAL] Locks Postgres

2017-02-10 18:18 GMT+13:00 John R Pierce : > On 2/9/2017 9:16 PM, John R Pierce wrote: > >> that spike in your graph suggests you had 8000 concurrent SELECT >> operations... >> > > errr, 7000, still way too many. > Thanks a lot John!! Got it PAtrick

[GENERAL] Locks Postgres

Hi guys I just wanna understand the locks in a DB server: [image: Imagem inline 1] Access share = Does that mean queries were waiting because an update/delete/insert was happening? I'm asking because I got a very big spike with > 30 seconds web response time. Running PG 9.3 Thanks! Patrick

Re: [GENERAL] FATAL: remaining connection slots are reserved for non-replication superuser connections

2017-02-08 16:27 GMT+13:00 Tatsuo Ishii : > > Something is using too many connections. > > > > I may be wrong but I'm unaware of a limit on connections from PHP except > > when you are using persistent connections. Since each PHP script is it's > > own process, it can create one or more connection

[GENERAL] FATAL: remaining connection slots are reserved for non-replication superuser connections

Hi guys, I get these messages at least once a day in my Prod environment: > FATAL: remaining connection slots are reserved for non-replication > superuser connections I do not have a DB pooler and my max_connections is 200. However, max connections for my PHP Application is 120. My server has

[GENERAL] Average - Pg 9.2

Hi guys, I've got a table which has id and created date columns. I want to get the average of inserted rows monthly. How can I get this data? This query is not working as it is showing me same data in both columns. select created_date, AVG(id) OVER(ORDER BY created_date) AS avr from test http:

[GENERAL] Custom type column index - Postgres 9.1

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 Patrick

Re: [GENERAL] Question slow query

2017-01-12 16:48 GMT+13:00 Andreas Joseph Krogh : > På torsdag 12. januar 2017 kl. 03:15:59, skrev Patrick B < > patrickbake...@gmail.com>: > > Hi guys, > > I've got a slow query, running at 25 seconds. > > > -> Bitmap Heap Scan

[GENERAL] Question slow query

Hi guys, I've got a slow query, running at 25 seconds. -> Bitmap Heap Scan on ja_notes r_1103088 (cost=234300.55..1254978.62 rows=553275 width=101) (actual time=1423.411..10572.549 rows=475646 loops=1) Recheck Cond: (n_type = ANY ('{note,

Re: [GENERAL] ERROR: canceling statement due to statement timeout

2017-01-12 13:41 GMT+13:00 Adrian Klaver : > On 01/11/2017 04:31 PM, Patrick B wrote: > >> 2017-01-12 13:23 GMT+13:00 Adrian Klaver > <mailto:adrian.kla...@aklaver.com>>: >> >> On 01/11/2017 04:08 PM, Patrick B wrote: >> >> Hi guy

Re: [GENERAL] ERROR: canceling statement due to statement timeout

2017-01-12 13:23 GMT+13:00 Adrian Klaver : > On 01/11/2017 04:08 PM, Patrick B wrote: > >> Hi guys, >> >> I'm using PostgreSQL 9.2 in two different servers. >> >> server1 (Master Postgres DB server, running Postgres 9.2 / 128GB ram) - >> RAID 1

[GENERAL] ERROR: canceling statement due to statement timeout

Hi guys, I'm using PostgreSQL 9.2 in two different servers. server1 (Master Postgres DB server, running Postgres 9.2 / 128GB ram) - RAID 10 Magnetic disks server2 (Master Postgres DB server, running Postgres 9.2 / 128GB ram) - EBS (AWS) io2 10k IOPS When I run a query, I get this error: ERROR:

Re: [GENERAL] Slow index scan - Pgsql 9.2

2017-01-11 4:05 GMT+13:00 Tomas Vondra : > On 01/10/2017 04:05 AM, Patrick B wrote: > >> ​3,581​ individual pokes into the heap to confirm tuple visibility >> and apply the deleted filter - that could indeed take a while. >> David J. >> >&

Re: [GENERAL] Slow index scan - Pgsql 9.2

> > ​3,581​ individual pokes into the heap to confirm tuple visibility and > apply the deleted filter - that could indeed take a while. > David J. I see.. The deleted column is: deleted boolean Should I create an index for that? How could I improve this query? Does it execute as slowly when y

[GENERAL] Slow index scan - Pgsql 9.2

Hi guys, I've got the following Query: WITH query_p AS ( SELECT CAST(6667176 AS BIGINT) AS client_id), clients AS ( SELECT

[GENERAL] FATAL: requested WAL segment has already been removed

Hi, I got this scenario: master01 --> slave01 ---> slave02 -> slave03 ---> slave04 As you can see, slave03 replicates from slave02 and slave04 from slave03. I'm promoting slave03 into a master, and trying to make slave04 to be able to connect to its new master. AS i'm using PostgreSQL

Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

2016-12-15 14:54 GMT+13:00 Lucas Possamai : > > > 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 REP

Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

2016-12-15 14:00 GMT+13:00 David G. Johnston : > On Wed, Dec 14, 2016 at 5:12 PM, rob stone wrote: > >> >> On Wed, 2016-12-14 at 17:00 -0700, David G. Johnston wrote: >> > On Wed, Dec 14, 2016 at 4:49 PM, Patrick B >> > wrote: >> > > ERROR: func

Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

2016-12-15 10:40 GMT+13:00 Adrian Klaver : > On 12/14/2016 01:30 PM, Patrick B wrote: > >> 1. Why when I run the function manually I get this error? >> >> select logextract(201612015, 201612015); >> >> ERROR: operator do

Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

> > 1. Why when I run the function manually I get this error? >> >> select logextract(201612015, 201612015); >> >> ERROR: operator does not exist: timestamp without time zone >= >> integer >> >> LINE 13: BETWEEN >> > > The answer is above. Look at yo

[GENERAL] Plpgsql - Custom fields Postgres 9.5

Hi, I've got this query, that I manually run it once a month: SELECT uuid, clientid), * FROM logging WHERE logtime BETWEEN '201611015' AND '201612015' As you can see, I select a date. So in December, the date will be: *BETWEEN '201612015' AND '201601015'*, for examp

Re: [GENERAL] WAL history files - Pgsql 9.2

2016-12-12 12:09 GMT+13:00 Patrick B : > 2016-12-12 12:00 GMT+13:00 Venkata B Nagothi : > >> >> On Mon, Dec 12, 2016 at 7:48 AM, Patrick B >> wrote: >> >>> Hi guys, >>> >>> Are the history files copied with the wal_files? Or I have to do

Re: [GENERAL] WAL history files - Pgsql 9.2

2016-12-12 12:00 GMT+13:00 Venkata B Nagothi : > > On Mon, Dec 12, 2016 at 7:48 AM, Patrick B > wrote: > >> Hi guys, >> >> Are the history files copied with the wal_files? Or I have to do it >> separated? >> >> 0003.history': No suc

[GENERAL] WAL history files - Pgsql 9.2

Hi guys, Are the history files copied with the wal_files? Or I have to do it separated? 0003.history': No such file or directory I'm using PostgreSQL 9.2. Cheers Patrick

[GENERAL] Streaming Replication delay getting bigger

Hi guys, I've got some database servers in USA (own data center) and also @ AWS Japan. *USA:* master01 slave01 (Streaming Replication from master01 + wal_files) slave02 (Streaming Replication from master01 + wal_files) *Japan: (Cascading replication)* slave03 (Streaming Replication from slave02

Re: [GENERAL] Wal files - Question | Postgres 9.2

2016-12-01 14:15 GMT+13:00 David G. Johnston : > On Wed, Nov 30, 2016 at 6:05 PM, Patrick B > wrote: > >> https://www.postgresql.org/docs/9.2/static/runtime-config- >> replication.html >> >> wal_keep_segments is the parameter responsible for streaming replica

Re: [GENERAL] Wal files - Question | Postgres 9.2

2016-11-29 23:59 GMT+13:00 Patrick B : > > > 2016-11-29 16:36 GMT+13:00 David G. Johnston : > >> On Mon, Nov 28, 2016 at 8:22 PM, Patrick B >> wrote: >> >>> >>> Ho >>> ​[w] >>> is that even possible?? I don't understand! &

Re: [GENERAL] Monitoring Replication - Postgres 9.2

nside the database as an extension. It uses the same >> syntax as regular cron, but it allows you to schedule PostgreSQL commands >> directly from the database" >> >> It looks like what you want. >> >> Walter. >> >> On Tue, Nov 29, 201

Re: [GENERAL] Monitoring Replication - Postgres 9.2

2016-11-30 14:21 GMT+13:00 John R Pierce : > On 11/29/2016 5:10 PM, Patrick B wrote: > > > Yep.. once a minute or so. And yes, I need to store a history with > timestamp. > > Any idea? :) > > > so create a table with a timestamptz, plus all the fields you want,

Re: [GENERAL] Monitoring Replication - Postgres 9.2

2016-11-30 14:02 GMT+13:00 John R Pierce : > On 11/29/2016 3:31 PM, Patrick B wrote: > > I use these queries to monitor the streaming replication: > > *on master:* > select client_addr, state, sent_location, write_location, flush_location, > replay_location, sync_priority fro

[GENERAL] Monitoring Replication - Postgres 9.2

Hi guys, I use these queries to monitor the streaming replication: *on master:* select client_addr, state, sent_location, write_location, flush_location, replay_location, sync_priority from pg_stat_replication; *On slave:* select now() - pg_last_xact_replay_timestamp() AS replication_delay; Can

Re: [GENERAL] Wal files - Question | Postgres 9.2

2016-11-29 16:36 GMT+13:00 David G. Johnston : > On Mon, Nov 28, 2016 at 8:22 PM, Patrick B > wrote: > >> >> Ho >> ​[w] >> is that even possible?? I don't understand! >> >> > ​https://www.postgresql.org/docs/9.2/static/warm-standby.html >

Re: [GENERAL] Wal files - Question | Postgres 9.2

2016-11-29 15:21 GMT+13:00 David Steele : > On 11/24/16 8:05 PM, Patrick B wrote: > > > hmm.. I really don't get it. > > > > > > > > If I get messages like: > > > > *cp: cannot stat '/walfiles/00021AF800A5': No such fil

Re: [GENERAL] Wal files - Question | Postgres 9.2

2016-11-23 16:18 GMT+13:00 Venkata B Nagothi : > > On Wed, Nov 23, 2016 at 1:59 PM, Patrick B > wrote: > >> >> >> 2016-11-23 15:55 GMT+13:00 Venkata B Nagothi : >> >>> >>> >>> On Wed, Nov 23, 2016 at 1:03 PM, Patrick B >>&

Re: [GENERAL] Wal files - Question | Postgres 9.2

2016-11-23 15:55 GMT+13:00 Venkata B Nagothi : > > > On Wed, Nov 23, 2016 at 1:03 PM, Patrick B > wrote: > >> Hi guys, >> >> I currently have a slave02 server that is replicating from another >> slave01 via Cascading replication. The master01 server is

[GENERAL] Wal files - Question | Postgres 9.2

Hi guys, I currently have a slave02 server that is replicating from another slave01 via Cascading replication. The master01 server is shipping wal_files (via ssh) to both slaves. I'm doing some tests on slave02 to test the recovery via wal_files... The goal here is to stop postgres, wait few min

Re: [GENERAL] Check integrity between servers

2016-11-17 12:19 GMT+13:00 Patrick B : > Would be possible to check the integrity between two database servers? > > Both servers are slaves (streaming replication + wal_files) but I believe > one of them, when recovered from wal_files in a fast outage we got, got > recovered no

[GENERAL] Check integrity between servers

Would be possible to check the integrity between two database servers? Both servers are slaves (streaming replication + wal_files) but I believe one of them, when recovered from wal_files in a fast outage we got, got recovered not 100%. How could I check the data between both DB? I'm using Postg

Re: [GENERAL] Wal files being delayed - Pgsql 9.2

2016-11-14 15:33 GMT+13:00 Venkata B Nagothi : > > On Mon, Nov 14, 2016 at 1:22 PM, Patrick B > wrote: > >> Hi guys, >> >> My current scenario is: >> >> master01 - Postgres 9.2 master DB >> slave01 - Postgres 9.2 streaming replication + wal_files

[GENERAL] Wal files being delayed - Pgsql 9.2

Hi guys, My current scenario is: master01 - Postgres 9.2 master DB slave01 - Postgres 9.2 streaming replication + wal_files slave server for read-only queries slave02 - Postgres 9.2 streaming replication + wal_files slave server @ AWS master01 sends wal_files to both slaves via ssh. *On the ma

[GENERAL] PLPGSQL returning number of rows

Hi guys, I'm writing a simple Plpgsql function to delete some data from different tables. The function starts with a select, and then 2 deletes after that. How can I return the number of rows that each delete performed? CREATE or REPLACE FUNCTION delete_ids_clientid(account_id integer) RETURN

Re: [GENERAL] Turning slave into a master - PostgreSQL 9.2

2016-11-02 8:43 GMT+13:00 Patrick B : > > > 2016-11-02 2:55 GMT+13:00 Scott Marlowe : > >> On Mon, Oct 31, 2016 at 8:01 PM, Patrick B >> wrote: >> > If I change recovery.conf: >> > >> > recovery_target_time = '2016-10-30 02:24:40' >

Re: [GENERAL] Turning slave into a master - PostgreSQL 9.2

2016-11-02 2:55 GMT+13:00 Scott Marlowe : > On Mon, Oct 31, 2016 at 8:01 PM, Patrick B > wrote: > > If I change recovery.conf: > > > > recovery_target_time = '2016-10-30 02:24:40' > > > > > > I get error: > > > > FATAL: requested

Re: [GENERAL] Turning slave into a master - PostgreSQL 9.2

If I change recovery.conf: recovery_target_time = '2016-10-30 02:24:40' I get error: FATAL: requested recovery stop point is before consistent recovery point

Re: [GENERAL] Turning slave into a master - PostgreSQL 9.2

I actually want to restore in a point of time. Don't want to recovery_target_timeline = 'latest' How can I stipulate a date? Thanks 2016-11-01 11:59 GMT+13:00 Patrick B : > > > 2016-11-01 10:33 GMT+13:00 David G. Johnston : > >> On Mon, Oct 31, 2016 at 1:46 P

Re: [GENERAL] Turning slave into a master - PostgreSQL 9.2

2016-11-01 10:33 GMT+13:00 David G. Johnston : > On Mon, Oct 31, 2016 at 1:46 PM, Patrick B > wrote: > >> Hi guys, >> >> I got a test server, let's call it test01. >> >> The test01 has a basebackup from the master. >> I want to turn test01 i

[GENERAL] Turning slave into a master - PostgreSQL 9.2

Hi guys, I got a test server, let's call it test01. The test01 has a basebackup from the master. I want to turn test01 into a master. It doesn't need to catch up with the wal_files, because I don't need it to be up-to-date. So what I did is: - Replaced /var/lib/pgsql/9.2/data/ with the baseback

Re: [GENERAL] Checking Postgres Streaming replication delay

2016-10-31 15:54 GMT+13:00 Venkata B Nagothi : > > On Mon, Oct 31, 2016 at 11:57 AM, Patrick B > wrote: > >> Hi guys, >> >> I'm using this query to measure the delay between a Master and a >> Streaming Replication Slave server, using PostgreSQL 9.2. >&

[GENERAL] Checking Postgres Streaming replication delay

Hi guys, I'm using this query to measure the delay between a Master and a Streaming Replication Slave server, using PostgreSQL 9.2. SELECT > pg_last_xlog_receive_location() receive, > pg_last_xlog_replay_location() replay, > ( > extract(epoch FROM now()) - > extract(epoch FROM pg_last_xact_replay

Re: [GENERAL] pg_sample

2016-10-19 13:39 GMT+13:00 Michael Paquier : > On Wed, Oct 19, 2016 at 9:24 AM, Patrick B > wrote: > > However, this new database test server doesn't need to have all the > data. I > > would like to have only the first 100 rows(example) of each table in my >

[GENERAL] pg_sample

Hi guys, I got a very big database, that I need to export (dump) into a new test server. However, this new database test server doesn't need to have all the data. I would like to have only the first 100 rows(example) of each table in my database. I'm using pg_sample to do that, but unfortunately

Re: [GENERAL] Dump all the indexes/constraints/roles

Thank you guys... good to know that pg_dump does all the job for me :) So.. If I only dump using the --schema-only option, it will dump all the schemas, constraints, indexes and tables? Because probably, I'll have to import the data manually. NOt in a single pg_restore I mean. (AWS issue)

[GENERAL] Dump all the indexes/constraints/roles

Hi guys, I need to export an entire database to another server, for testing purpose. Is there any way to export all indexes and constraints ? Postgres 9.2 Patrick

Re: [GENERAL] Postgres UPGRADE from 9.2 to 9.4

2016-09-09 1:09 GMT+12:00 Scott Marlowe : > On Tue, Sep 6, 2016 at 5:25 PM, John R Pierce wrote: > > On 9/6/2016 4:20 PM, Melvin Davidson wrote: > >> > >> If you use slony to replicate, you CAN have 9.2 on the master and 9.4 on > >> the slave. > > > > > > does rackspace support slony? how about

  1   2   3   >