[GENERAL] PG 9.1 - FK + Check constraint

[Patrick B]

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 contain subqueries nor
refer to variables other than columns of the current row. Another solution
would be to create an IMMUTABLE functiondoing the check and use that in a
CHECK constraint [1]. However, I'm concern about doing this as I use
replication slaves, where all my selects are ran in there. I'm worried
about replication lag in this case.

Is there any other way to perform this? I'm using PG 9.1 for this.

[1]
https://stackoverflow.com/questions/10135754/how-to-make-a-foreign-key-with-a-constraint-on-the-referenced-table-in-postgresq

Thanks!

Patrick

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

[Patrick B]

2017-07-07 22:32 GMT+12:00 Thomas Markus <t.mar...@proventis.net>:

> 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 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 (
> select
> CASE
>   WHEN regdate = 1 THEN 'yes'
>   WHEN regdate = 2 THEN 'no'
>   from test1
>   join test2 as t2 on t2.test1_id = t1.id
>   end as status_a
> )
>
> select status_a from user_status group by status_a
>
>
> It works fine.. but I would like to get that data in one single query..
> How can I do that?
>
> I'm using Postgres 9.3.
>
> Thanks!
> Patrick
>
>
> one possibility is:
>
> select distinct
> case
> when regdate = 1 THEN 'yes'
> when regdate = 2 THEN 'no'
> end as status_a
> , t2.id is null as test2exists
> from test1 t1 left join test2 t2 on t2.test1_id = t1.id
>
> hth
> Thomas
>
>

hmmm... not really

I want this to work:

WITH account_status AS (
select
CASE
  WHEN regdate = 1 THEN 'yes'
  WHEN regdate = 2 THEN 'no'
end as status_a,
count(t2.id) as t2_count
  from test1 as t1
join test2 as t2 on t2.test1_id = t1.id
  end as status_a
)

select
status_a,
t2_count,
count(*)
from account_status group by status_a, t2_count

[GENERAL] count records in two different table joined by

[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 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 (
select
CASE
  WHEN regdate = 1 THEN 'yes'
  WHEN regdate = 2 THEN 'no'
  from test1
  join test2 as t2 on t2.test1_id = t1.id
  end as status_a
)

select status_a from user_status group by status_a


It works fine.. but I would like to get that data in one single query.. How
can I do that?

I'm using Postgres 9.3.

Thanks!
Patrick

Re: [GENERAL] effective_io_concurrency increasing

[Patrick B]

2017-06-19 13:19 GMT+12:00 Melvin Davidson <melvin6...@gmail.com>:

>
>
> On Sun, Jun 18, 2017 at 9:02 PM, Patrick B <patrickbake...@gmail.com>
> wrote:
>
>> 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 doc does not say much about which value
>> you should use.
>>
>> If I put it to 1, does it mean I can have a query spread into 1 processor?
>>
>> Thanks
>> P
>>
>
>
>
> *Perhaps you should read the
> doc.https://www.postgresql.org/docs/9.2/static/runtime-config-resource.html
> <https://www.postgresql.org/docs/9.2/static/runtime-config-resource.html>*
> 18.4.6. Asynchronous Behavior *effective_io_concurrency (integer)*
>


I've done that! But I'm looking for some personal experiences and
suggestions!!

[GENERAL] effective_io_concurrency increasing

[Patrick B]

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 doc does not say much about which value
you should use.

If I put it to 1, does it mean I can have a query spread into 1 processor?

Thanks
P

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

[Patrick B]

2017-06-16 10:35 GMT+12:00 David G. Johnston <david.g.johns...@gmail.com>:

> On Thu, Jun 15, 2017 at 3:19 PM, Patrick B <patrickbake...@gmail.com>
> wrote:
>
>> 2017-05-29 19:27 GMT+12:00 Albe Laurenz <laurenz.a...@wien.gv.at>:
>>
>>> 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:
>>> >
>>
>>
> ​https://www.postgresql.org/message-id/CY1PR18MB0490632A9A73E64973F66
> D6BAFCE0%40CY1PR18MB0490.namprd18.prod.outlook.com
>
> David J​
>


My problem is when using LIMIT to select the data I get none. I assume
because the first 3000 rows (i'm using ORDER BY 1) are not candidates for
the migration.

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

[Patrick B]

2017-05-29 19:27 GMT+12:00 Albe Laurenz <laurenz.a...@wien.gv.at>:

> 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:
> >
> >
> > 1.Select the data from tableA
> > 2.The limit will be put when calling the function
> > 3.insert the selected data on Step 1 onto new table
> >
> > Question:
> >
> > * When I stop it and start it again, how can the query "know" that
> it has already
> > processed some rows so it won't do it twice on the same rows? If it
> stopped on row number
> > 100, I need it to continue on row number 101, for example.
> >
> > * How can I ask the function to return the number of processed rows?
> >
> >
> > I can add a column on TableB if needed, but not on tableA.
> >
> > This is what I've done so far:
>
> >   CREATE or REPLACE FUNCTION data_copy(rows integer)
> >   RETURNS SETOF bigint AS $$
>
> CREATE or REPLACE FUNCTION data_copy(p_limit integer, p_offset integer)
> RETURNS integer;
>
> >   declare
> >   row record;
> >   offset_num integer;
>
> num_rows integer := 0;
>
> >   BEGIN
> >   FOR row IN EXECUTE '
> >   SELECT
> >   id,
> >   path,
> >   name,
> >   name_last,
> >   created_at
> >   FROM
> >   tablea
> >   WHERE
> >   ready = true
> >   ORDER BY 1 LIMIT ' || rows || ' OFFSET ' ||
> rows || ''
>
> '... LIMIT ' || p_limit || ' OFFSET ' || p_offset
>
> >   LOOP
>
> num_rows := num_rows + 1;
>
> >   INSERT INTO tableB (id,path,name,name_last,created_at)
> >   VALUES (row.id,row.path,row.name,row.
> name_last,row.created_at);
> >
> >   END LOOP;
>
> RETURN num_rows;
>
> >   END
> >   $$ language 'plpgsql';
>
> There are two problems with this approach:
>
> 1. It will do the wrong thing if rows are added or deleted in "tablea"
> while
>you process it.
>


There will be actually records being inserted in tablea while processing
the migration Any ideas here?

I can add another column in tablea, like example: row_migrated boolean -->
if that helps


>
> 2. Queries with hight OFFSET values have bad performance.
>

No problem. The plan is to perform 2k rows at once, which is not much.



>
> The solution is to avoid OFFSET and to use "keyset pagination":
> http://use-the-index-luke.com/no-offset
>
>
>

Thanks
Patrick

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

[Patrick B]

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=15c571caa36876f00a0a2eaace703a2b

How can I extract, from that tablea.type_m column the tableb.id value?

Thanks
Patrick

Re: [GENERAL] Regexp + spaces PG 9.1

[Patrick B]

2017-05-31 16:34 GMT+12:00 David G. Johnston <david.g.johns...@gmail.com>:

> On Tue, May 30, 2017 at 9:17 PM, Patrick B <patrickbake...@gmail.com>
> wrote:
>
>>
>> regexp_matches(name, '((main|medium).name/\d+.\d+)') as filename,
>>
>>
>> Example here: http://sqlfiddle.com/#!15/5f4f0/4
>>
>>1.  can only get the jpg file name
>>
>> ​Because those are the only two file names consisting of one or more
> numbers, something else, and ending with one or more numbers
>
> I'm pretty sure you mean for the "." in that regex to be "\." so it is
> treated as the period before the extension and not the regex "any" meta
> character.
>
> \d means number, 1-9.  You probably want something like "[\d\w\s]+" to
> match digits, letters (including underscore), and white-space.  Or maybe
> "[^\r\n/\.]+" - specify what it is that won't be in the file name.
>
>>
>>1. I don't get only the file name but the rest as well, which is not
>>what I need
>>
>> ​You get more than just "the rest"...you get an array with two entries
> corresponding to the two parenthetical captures (the fiddle apparently
> doesn't display the "{}" that would make this much more obvious).
>
> (regexp_matches​(...))[#]
>
> The above will let you extract only the array position that you specify.
> You will need to add more parens to delimit exactly what you need.
>
> You can use "(?:regex)" instead of normal parens to group without
> capturing.
>
> David J.
>
>

Thanks David! That helped.

See example:
http://dbfiddle.uk/?rdbms=postgres_9.6=a74cd219191444f0228b9df581548a37

Will do some more tests but i think that's all i need. Cheers
Patrick

[GENERAL] Regexp + spaces PG 9.1

[Patrick B]

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



Example here: http://sqlfiddle.com/#!15/5f4f0/4

As you can see on the example:


   - if the file is a image (jpg), then it will have 2 variations
   (main|medium).
   - If the file is a pdf, then it will only have 1 variation (main).

I basically need a regexp_matches expression that only gets me the file
name, after the main.name/ for example.

On the example I gave there are 2 problems:


   1. I can only get the jpg file name
   2. I don't get only the file name but the rest as well, which is not
   what I need


How to do that?

Thanks!
Patrick

[GENERAL] plpgsql function with offset - Postgres 9.1

[Patrick B]

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 data on Step 1 onto new table

Question:

   - When I stop it and start it again, how can the query "know" that it
   has already processed some rows so it won't do it twice on the same rows?
   If it stopped on row number 100, I need it to continue on row number 101,
   for example.
   - How can I ask the function to return the number of processed rows?


I can add a column on TableB if needed, but not on tableA.

This is what I've done so far:

select data_copy(500);


CREATE or REPLACE FUNCTION data_copy(rows integer)

RETURNS SETOF bigint AS $$


declare

row record;

offset_num integer;


BEGIN


FOR row IN EXECUTE '

SELECT

id,

path,

name,

name_last,

created_at

FROM

tablea

WHERE

ready = true

ORDER BY 1 LIMIT ' || rows || ' OFFSET ' || rows || ''

LOOP


INSERT INTO tableB (id,path,name,name_last,created_at)

VALUES (row.id,row.path,row.name,row.name_last,row.created_at);



END LOOP;


END


$$ language 'plpgsql';

[GENERAL] union all taking years - PG 9.6

[Patrick B]

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 even run explain
analyze (it's been 1h and query did not finished yet).

However, when running both selects with explain analyze, query is fast.


What should I do in this case? Why is that taking so long? I assume it's
because the UNION will look for duplicates?

Thanks
Patrick.

Re: [GENERAL] Pattern Matching question - PG 9.6

[Patrick B]

2017-05-15 16:10 GMT+12:00 David G. Johnston <david.g.johns...@gmail.com>:

> On Sunday, May 14, 2017, Patrick B <patrickbake...@gmail.com> wrote:
>
>>
>> Demo: http://dbfiddle.uk/?rdbms=postgres_9.6=3c3a3f870eb4d0
>> 02c5b4200042b25669
>> <http://dbfiddle.uk/?rdbms=postgres_9.6=c2fbb7da5a2397f7cda5126ed239c080>
>>
>> The rows that I should be getting are:
>>
>> 5   /testfile/client/10/attachment/1000/master/   10
>>
>> 7   /testfile/client/10/attachment/unassigned/file/1001/master   10
>>
>> 8   /testfile/client/10/attachment/unassigned/file/1002/master   10
>>
>> What am I doing wrong?
>>
>
> Without you explaining why 6 and 9 are invalid it's impossible to say how
> you should modify your regex to exclude them.  You may find positive and
> negative look-ahead useful though.
>
> David J.
>


I thought I have already explained it. Here it goes again. Demo page is:
http://dbfiddle.uk/?rdbms=postgres_9.6=ea61e7e1859bdb7f297f853a9dc0e3
d0


As you can see, there is a lot of variations for the same file_id (1000).
File_id (1001/1002) is a new unassigned file, different from the others.

I wanna be able to get ONLY the 'master' variation (
/testfile/client/10/attachment/1000/master/ ) and the unassigned files
variations [if any]
(/testfile/client/10/attachment/unassigned/file/1001/master |
/testfile/client/10/attachment/unassigned/file/1002/master).

So on the demo above, only id IN (5,9,10) are valid for me. The SELECT that
I used as an example is not returning me ONLY the data I need, instead, it
is returning (almost) everything.


To summarize: I wanna use a pattern matching the only returns these rows:

/testfile/client/10/attachment/1000/master/
/testfile/client/10/attachment/unassigned/file/1001/master
/testfile/client/10/attachment/unassigned/file/1002/master



What can I do to fix it?
Thanks
P.

Re: [GENERAL] Pattern Matching question - PG 9.6

[Patrick B]

2017-05-15 15:20 GMT+12:00 Patrick B <patrickbake...@gmail.com>:

> Hi guys,
>
> Demo: http://dbfiddle.uk/?rdbms=postgres_9.6=
> 3c3a3f870eb4d002c5b4200042b25669
> <http://dbfiddle.uk/?rdbms=postgres_9.6=c2fbb7da5a2397f7cda5126ed239c080>
>
>
FYI - NEW LINK
http://dbfiddle.uk/?rdbms=postgres_9.6=ea61e7e1859bdb7f297f853a9dc0e3d0
with more variations.



>
> AS you can see above, when performing this query:
>
>> SELECT * FROM test1 WHERE client_id = 10 AND path ~
>> '^/testfile/client/[0-9]+/attachment/(([0-9]{1,14})|(unassigned))/'
>>
>
> I get 5 rows. But actually I only want/need 3 of them:
>
>
>- with the 'master' variation
>- and if it is unassigned (attachment/unassigned); then i want it too
>
> The rows that I should be getting are:
>
> 5   /testfile/client/10/attachment/1000/master/   10
>
> 7   /testfile/client/10/attachment/unassigned/file/1001/master   10
>
> 8   /testfile/client/10/attachment/unassigned/file/1002/master   10
>
>
> What am I doing wrong?
>
> Thanks
>
> Patrick.
>

[GENERAL] Pattern Matching question - PG 9.6

[Patrick B]

Hi guys,

Demo:
http://dbfiddle.uk/?rdbms=postgres_9.6=3c3a3f870eb4d002c5b4200042b25669



AS you can see above, when performing this query:

> SELECT * FROM test1 WHERE client_id = 10 AND path ~
> '^/testfile/client/[0-9]+/attachment/(([0-9]{1,14})|(unassigned))/'
>

I get 5 rows. But actually I only want/need 3 of them:


   - with the 'master' variation
   - and if it is unassigned (attachment/unassigned); then i want it too

The rows that I should be getting are:

5   /testfile/client/10/attachment/1000/master/   10

7   /testfile/client/10/attachment/unassigned/file/1001/master   10

8   /testfile/client/10/attachment/unassigned/file/1002/master   10


What am I doing wrong?

Thanks

Patrick.

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

[Patrick B]

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 table and pad it with
> nulls so they "match".  This should work fine in 9.1.
>
> For example:
>
> db=# create table old ( id integer );
> CREATE TABLE
> db=# create table new ( id integer, newcol text );
> CREATE TABLE
> db=# insert into old (id) values (1), (2);
> INSERT 0 2
> db=# insert into new (id, newcol) values (1, 'a');
> INSERT 0 1
>
> New table:
>
> db=# with new_check as (
> db(#   select id, newcol from new where id = 1
> db(# )
> db-# select id, null::text as newcol from old where id = 1
> db-# and not exists ( select 1 from new_check )
> db-# union all
> db-# select * from new_check;
>  id | newcol
> +
>   1 | a
> (1 row)
>
> Old table:
>
> db=# with new_check as (
> db(#   select id, newcol from new where id = 2
> db(# )
> db-# select id, null::text as newcol from old where id = 2
> db-# and not exists ( select 1 from new_check )
> db-# union all
> db-# select * from new_check;
>  id | newcol
> +
>   2 |
> (1 row)
>
> Neither:
>
> db=# with new_check as (
> db(#   select id, newcol from new where id = 3
> db(# )
> db-# select id, null::text as newcol from old where id = 3
> db-# and not exists ( select 1 from new_check )
> db-# union all
> db-# select * from new_check;
>  id | newcol
> +
> (0 rows)




Hmm.. that's interesting...

The query is (This is actually a view)

SELECT
split_part(n1.path::text, '/'::text, 18)::integer AS id,
split_part(n1.path::text, '/'::text, 14)::integer AS clientid,
lower(n1.md5::text)::character(32) AS md5, 0 AS cont,
'-1000-1000-3000-6000'::uuid AS guid,
n1.bytes AS byte_count,
n1.last_modified AS last_modified
  FROM tablea n1
  JOIN tableb s2 ON s2.path = n1.path

Where tablec is the new one. AS you can see, there is no reference for the
new tablec on that query, so I need to:

- Get the data from the new table,
- if it is not in there, then go to old table (query above).

\d tablec:

>
>  Table "public.tablec"
>Column   |Type |
>   Modifiers
>
> +-+---
>  id | integer | not null default
> nextval('tablec_id_seq'::regclass)
>  e_type| integer | not null
>  e_id  | bigint  |
>  e_variation   | character varying(16)   | not null
>  path  | character varying(255)  | not null
>  name  | character varying(255)  | not null
>  size  | bigint  | not null
>  md5   | md5_hash| not null
>  modified_date | timestamp without time zone | default
> statement_timestamp()
>  created_date  | timestamp without time zone | default
> statement_timestamp()
>  clientid | bigint  | not null
>  f_id| bigint  |



So, will the best way to use UNION ALL?

Thanks
Patrick.

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

[Patrick B]

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 Select? Can you please provide some examples?


I'm using PostgreSQL 9.1.

Thanks
Patrick

Re: [GENERAL] Nullsif ? PG 9.1

[Patrick B]

2017-04-09 20:18 GMT+12:00 Patrick B <patrickbake...@gmail.com>:

> 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/21314134/file/12312312312
> <(231)%20231-2312>/small/photo.jpg
>
> Note that, sometimes it only goes until the 7th splitted_part, not always
> i will get the 8th.
>
> How can I tell the select, if no 8th field is found, then returns null?
>
> Thanks!
> Patrick
>


Sorry guys.. Got it!
https://www.postgresql.org/docs/9.1/static/functions-conditional.html

[GENERAL] Nullsif ? PG 9.1

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

Example:
/filesuser/client/27801123/attachment/4510/main
/filesuser/client//attachment/21314134/file/12312312312/small/photo.jpg

Note that, sometimes it only goes until the 7th splitted_part, not always i
will get the 8th.

How can I tell the select, if no 8th field is found, then returns null?

Thanks!
Patrick

Re: [GENERAL] regexp_matches where clause - PG 9.1

[Patrick B]

2017-04-07 14:19 GMT+12:00 David G. Johnston <david.g.johns...@gmail.com>:

> On Thu, Apr 6, 2017 at 7:15 PM, Patrick B <patrickbake...@gmail.com>
> wrote:
>
>>
>> David,
>> That won't work.
>>
>
> ​Actually, it works fine, you just keep moving the under-specified problem
> space.
> ​
> I'd suggest creating a self-contained running example that gets you close
> and show what the final output should be.
>
> David J.
>
>
http://sqlfiddle.com/#!15/6d65d

There is an example. Please note the `col2` returns not only 'main'. I need
it to return only main and I need to put it in a where clause. I can't use
~ operator because I will need to do something like:

FROM test1 t1
> JOIN another_view AS s
> WHERE s.full_path = substr(t1.full_path, char_pos '/file..' to get
> /filesuser/client/.../attachment/.../) || 'main'


Thanks
Patrick.

Re: [GENERAL] regexp_matches where clause - PG 9.1

[Patrick B]

2017-04-07 14:08 GMT+12:00 David G. Johnston <david.g.johns...@gmail.com>:

> On Thu, Apr 6, 2017 at 6:33 PM, Patrick B <patrickbake...@gmail.com>
> wrote:
>
>> When actually I just want the 'main''
>>
>
> ​SELECT * FROM tbl WHERE path_name ~ '/main$' ?
>
> David J.
> ​
>


David,
That won't work.

When performing the select, I got:

/{s3bucket}/filesuser/client/27801123/attachment/4510/file/1113/small/main

when actually i want:

/{s3bucket}/filesuser/client/27801123/attachment/4510/main


Patrick.

Re: [GENERAL] regexp_matches where clause - PG 9.1

[Patrick B]

2017-04-06 18:10 GMT+12:00 Patrick B <patrickbake...@gmail.com>:

>
> 2017-04-06 17:35 GMT+12:00 Arjen Nienhuis <a.g.nienh...@gmail.com>:
>
>>
>>
>> On Apr 6, 2017 05:57, "Patrick B" <patrickbake...@gmail.com> wrote:
>>
>> 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, where path_name has only 'main' and not anything
>> else.
>>
>>
>> WHERE path_nane LIKE '%/main'
>>
>>
>>
>
> I was able to do it like this:
>
>
> WHERE (path_name)::text ~ '^\/filesuser\/client/\d+/(
>> attachment)/\d+/(main)+'
>
>
>
> Thanks
> Patrick.
>



 Guys.. sorry but actually it's not exactly what I'm looking for:

The paths */{s3bucket}/filesuser/client/27801123/attachment/4510/main
/{s3bucket}/filesuser/client/27801123/attachment/4510/file* are the root
path for:

/{s3bucket}/filesuser/client/27801123/attachment/4510/file/
1113/small/photo.jpg
/{s3bucket}/filesuser/client/27801123/attachment/4510/main/
111/small/photo.jpg


So for that single file (photo.jpg) i can have:

> /{s3bucket}/filesuser/client/27801123/attachment/4510/file/
> 1113/small/photo.jpg
> /{s3bucket}/filesuser/client/27801124/attachment/4511/main/
> 111/small/photo.jpg
> /{s3bucket}/filesuser/client/27801125/attachment/4512/file
> /{s3bucket}/filesuser/client/27801126/attachment/4513/main



select REGEXP_REPLACE(path_name, '.*/', '') as col2 from seg_table limit 10;

It works, but I get :

photo.jpg
> main
> file


When actually I just want the 'main''

How can I include this regexp in a where clause? Something like:

>
> WHERE REGEXP_REPLACE(path_name, '.*/', '')::text = 'main'


Thanks!
Patrick.

Re: [GENERAL] regexp_matches where clause - PG 9.1

[Patrick B]

2017-04-06 17:35 GMT+12:00 Arjen Nienhuis <a.g.nienh...@gmail.com>:

>
>
> On Apr 6, 2017 05:57, "Patrick B" <patrickbake...@gmail.com> wrote:
>
> 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, where path_name has only 'main' and not anything else.
>
>
> WHERE path_nane LIKE '%/main'
>
>
>

I was able to do it like this:


WHERE (path_name)::text ~
> '^\/filesuser\/client/\d+/(attachment)/\d+/(main)+'



Thanks
Patrick.

[GENERAL] regexp_matches where clause - PG 9.1

[Patrick B]

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, where path_name has only 'main' and not anything else.

Maybe using regexp_matches but then how to put it into a where clause?

Thanks!
Patrick.

Re: [GENERAL] effective_cache_size X shared_buffer

[Patrick B]

2017-04-03 13:23 GMT+12:00 Patrick B <patrickbake...@gmail.com>:

> 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 environment gets very slow for a few hours, until the queries used
> on a daily basis go to the cache.
>
> Question:
> Should I increase effective_cache_size or shared_buffer? What's the
> difference between them?
>
> Thanks
> Patrick
>

Can I also increase shared_buffer on my slave only? Would that make any
difference if using selects on the slave? Or this parameter must be the
same across all servers (Master/slaves) ?

Thanks
P.

[GENERAL] effective_cache_size X shared_buffer

[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 again,
as all the cache "was used" by those write and read tasks.

So my environment gets very slow for a few hours, until the queries used on
a daily basis go to the cache.

Question:
Should I increase effective_cache_size or shared_buffer? What's the
difference between them?

Thanks
Patrick

Re: [GENERAL] Constraint + where

[Patrick B]

2017-03-20 13:27 GMT+13:00 Melvin Davidson <melvin6...@gmail.com>:

>
>
> On Sun, Mar 19, 2017 at 8:16 PM, Patrick B <patrickbake...@gmail.com>
> wrote:
>
>> 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!
>>
>>
> Why just "maybe"? Since there can only be three valid answers, why not FK
> for all three?
>
> --
>



I was able to get what I needed this way:

create table testing_fk_conditional_1 (
> id serial NOT NULL PRIMARY KEY,
> account_id bigint,
> user_id bigint,
> type_note integer NOT NULL,
> CHECK (type_note = 100 AND user_id IS NOT NULL OR type_note = 200 AND
> account_id IS NOT NULL)
> );

[GENERAL] Constraint + where

[Patrick B]

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

[Patrick B]

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.id
> AND ff.clientid = 3369
> AND (ff.description LIKE '%change%')
> ORDER BY gtime DESC
> LIMIT 100


Explain analyze: https://explain.depesz.com/s/1OLW

I'm using PG 9.2 and, read about gin indexes.
I've created the index to test, but the query is not using it.

> create index on log gin (description gin_trgm_ops)


Can you guys help to improve that part please?

Patrick.

Re: [GENERAL] count case when - PG 9.2

[Patrick B]

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 day'))
>
> THEN 'paying'
>
> END as account_status,
>
> c ount(*)
>
> FROM public.clients c
>
> WHERE (
>
>(last_pay > EXTRACT('epoch' FROM now() - '12
> Months'::INTERVAL))
>
>   )
>
>group by 1
>
> ORDER BY 1
>
>
>

Thanks Yasin! That worked.

P.

Re: [GENERAL] count case when - PG 9.2

[Patrick B]

2017-03-09 23:15 GMT+13:00 vinny <vi...@xs4all.nl>:

> On 2017-03-09 05:27, Patrick B wrote:
>
>> 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,
>>>>
>>>
>>> c.id [1]
>>>>
>>>
>>> FROM public.clients c
>>>>
>>>
>>> WHERE (
>>>>
>>>
>>> (last_pay > EXTRACT('epoch' FROM now() - '12
>>>> Months'::INTERVAL))
>>>>
>>>
>>> )
>>>>
>>>
>>> ORDER BY 1
>>>>
>>>  I wanna know how many of 'trial' and 'paying' customers the query
>> returns. can you guys please advice how to do it?
>>
>> Thanks
>> Patrick
>>
>>
> comparisons like "A>B" return a boolean. Booleans can be cast to integers,
> and integers can be summed.
>
> SUM((A>B)::int)
>
> But depending on the situation, indexes etc it could be faster to run e
> separate count query, you'll have to test that.
>


Could you please guys give me a query as an example?

Thanks
P.

[GENERAL] count case when - PG 9.2

[Patrick B]

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,

c.id

FROM public.clients c

WHERE (

   (last_pay > EXTRACT('epoch' FROM now() - '12
Months'::INTERVAL))

  )

ORDER BY 1

I wanna know how many of 'trial' and 'paying' customers the query returns.
can you guys please advice how to do it?

Thanks
Patrick

[GENERAL] Seq scan X Index scan

[Patrick B]

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_index VALUES ('jhon','backer');
> insert into test_gin_index VALUES ('paul','min');
> insert into test_gin_index VALUES ('emily','foo');

CREATE EXTENSION pg_trgm;
> create index on test_gin_index using gin (name_first gin_trgm_ops);

analyze  test_gin_index;


*Explain analyze with SEQ scans:*

explain analyze select * from test_gin_index where name_first ILIKE '%on%';

   QUERY PLAN


-

 Seq Scan on test_gin_index  (cost=0.00..1.04 rows=1 width=19) (actual
time=0.009..0.012 rows=1 loops=1)

   Filter: ((name_first)::text ~~* '%on%'::text)

   Rows Removed by Filter: 2

 Planning time: 0.075 ms

 Execution time: 0.027 ms

(5 rows)


*Explain analyze with INDEX scan:*

explain analyze select * from test_gin_index where name_first ILIKE '%on%';

  QUERY PLAN


---

 Bitmap Heap Scan on test_gin_index  (cost=92.00..96.02 rows=1 width=19)
(actual time=0.020..0.022 rows=1 loops=1)

   Recheck Cond: ((name_first)::text ~~* '%on%'::text)

   Rows Removed by Index Recheck: 2

   Heap Blocks: exact=1

   ->  Bitmap Index Scan on test_gin_index_name_first_idx
(cost=0.00..92.00 rows=1 width=0) (actual time=0.010..0.010 rows=3 loops=1)

 Index Cond: ((name_first)::text ~~* '%on%'::text)

 Planning time: 0.122 ms

 Execution time: 0.042 ms

(8 rows)


Why is SEQ SCAN faster than index scan? This is an environment test but i'm
running the same test on a production environment and also seq scan is
cheaper than index.


Thanks

Patrick

[GENERAL] Question about TOAST table - PostgreSQL 9.2

[Patrick B]

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_database')) As fullprod,

pg_size_pretty(CAST(pg_database_size('live_database') - (SELECT
SUM(pg_total_relation_size(table_schema || '.' || table_name)  )

FROM information_schema.tables WHERE table_schema = 'binary_schema') As
bigint)) As  tobebackedup_size,

pg_size_pretty(CAST((SELECT SUM(pg_total_relation_size(table_schema || '.'
|| table_name) )

FROM information_schema.tables

WHERE table_schema = 'binary_schema') As bigint) )  As junk_size;


fullprod tobebackedup_size junk_size

 - -

4302 GB  489 GB2813 GB




On my database, using pgadmin, I can see a lot of *pg_tast_temp_** and
*pg_temp_** table.

I understand the TOAST code is triggered when a row is wider than the
TOAST_TUPLE_THRESHOLD [1]. I also understand the only way to shrink toast
table is by using a vacuum full or even pg_dump.

*Questions:*

1 - If I take out 500GB of bytea data ( by updating the column seg_data and
setting it to null ), will I get those 500GB of free disk space? or do I
need to run vacuum full or either pg_dump?

2 - If I choose going ahead with VACUUM FULL, I have 3 streaming
replication slaves, Will I need to run the vacuum full on them too?

3 - [2] vacuum full needs some free disk space as same size as the target
table. It locks the table (cannot be used while running vacuum full) and a
REINDEX might be needed after. AM I right?

Thanks in advanced for your help.
Patrick

[1] https://www.postgresql.org/docs/9.2/static/storage-toast.html
[2] https://wiki.postgresql.org/wiki/VACUUM_FULL

Re: [GENERAL] bloat indexes - opinion

[Patrick B]

2017-02-25 17:53 GMT+13:00 Patrick B <patrickbake...@gmail.com>:

>
>
> 2017-02-23 11:46 GMT+13:00 Jeff Janes <jeff.ja...@gmail.com>:
>
>> On Tue, Feb 21, 2017 at 1:44 PM, Patrick B <patrickbake...@gmail.com>
>> wrote:
>>
>>> 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 INDEX ix_filter_by_tree ON seg USING btree
>>> (full_path varchar_pattern_ops) WHERE (full_path IS NOT NULL)
>>>
>>>
>> What is this from?  If you think the table size reported should include
>> toast, then change it to do that, or request the author of whatever-this-is
>> to make that change.
>>
>> What indication is there that the index is bloated?  If the
>> meat-and-potatoes of a table is held in toast, then wouldn't you expect the
>> size of the table and the size of the index to be about the same?
>>
>> Cheers,
>>
>> Jeff
>>
>
>
> I am running queries to see bloat indexes [1]. Also i understand an index
> can't have same size as table.
> If you have any other table that can prove the index is indeed bloat,
> please let me know and i will be happy to post results here.
>
> [1] https://wiki.postgresql.org/wiki/Index_Maintenance
>
> Patrick
>
>

FYI - using this query to see the index size:


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) ||
'.' || quote_ident(idstat.relname))) AS table_size,

   pg_size_pretty(pg_relation_size(quote_ident(idstat.schemaname) ||
'.' || quote_ident(indexrelname))) AS index_size,

   n_tup_upd + n_tup_ins + n_tup_del as num_writes,

   indexdef AS definition

FROM pg_stat_user_indexes AS idstat

JOIN pg_indexes ON indexrelname = indexname

JOIN pg_stat_user_tables AS tabstat ON idstat.relname = tabstat.relname

WHERE indexrelname = 'index_name';

Re: [GENERAL] bloat indexes - opinion

[Patrick B]

2017-02-23 11:46 GMT+13:00 Jeff Janes <jeff.ja...@gmail.com>:

> On Tue, Feb 21, 2017 at 1:44 PM, Patrick B <patrickbake...@gmail.com>
> wrote:
>
>> 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 INDEX ix_filter_by_tree ON seg USING btree
>> (full_path varchar_pattern_ops) WHERE (full_path IS NOT NULL)
>>
>>
> What is this from?  If you think the table size reported should include
> toast, then change it to do that, or request the author of whatever-this-is
> to make that change.
>
> What indication is there that the index is bloated?  If the
> meat-and-potatoes of a table is held in toast, then wouldn't you expect the
> size of the table and the size of the index to be about the same?
>
> Cheers,
>
> Jeff
>


I am running queries to see bloat indexes [1]. Also i understand an index
can't have same size as table.
If you have any other table that can prove the index is indeed bloat,
please let me know and i will be happy to post results here.

[1] https://wiki.postgresql.org/wiki/Index_Maintenance

Patrick

Re: [GENERAL] bloat indexes - opinion

[Patrick B]

2017-02-22 13:10 GMT+13:00 Adrian Klaver <adrian.kla...@aklaver.com>:

> On 02/21/2017 03:41 PM, Patrick B wrote:
> > 2017-02-22 11:11 GMT+13:00 Patrick B <patrickbake...@gmail.com
> > <mailto:patrickbake...@gmail.com>>:
> >
> > 2017-02-22 10:59 GMT+13:00 Adrian Klaver <adrian.kla...@aklaver.com
> > <mailto:adrian.kla...@aklaver.com>>:
> >
> > 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_filter_by_tree
> > > Times_used: 1018082183
> > > Table_size: 18 GB -- wrong. The table is mostly on
> pg_toast table.
> > > Its real size is 2TB
> >
> > How do you know one number is right and the other is wrong?
> >
> >
> >
> > 1. on that table (seg) i store binary data. It is impossible to have
> > only 18GB of it.
> > 2.
> >
> > SELECT schema_name,
> >
> >pg_size_pretty(sum(table_size)::bigint),
> >
> >(sum(table_size) /
> > pg_database_size(current_database())) * 100
> >
> > FROM (
> >
> >   SELECT pg_catalog.pg_namespace.nspname as schema_name,
> >
> >  pg_relation_size(pg_catalog.pg_class.oid) as
> table_size
> >
> >   FROM   pg_catalog.pg_class
> >
> >  JOIN pg_catalog.pg_namespace ON relnamespace =
> > pg_catalog.pg_namespace.oid
> >
> > ) t
> >
> > GROUP BY schema_name
> >
> > ORDER BY schema_name
> >
> >
> > pg_toast2706 GB82.6211283887724086 <-- this belongs to the seg
> > table.
> >
> >
> >
> >
> > Have you looked at the functions here?:
> > https://www.postgresql.org/docs/9.6/static/functions-
> admin.html#FUNCTIONS-ADMIN-DBOBJECT
> > <https://www.postgresql.org/docs/9.6/static/functions-
> admin.html#FUNCTIONS-ADMIN-DBOBJECT>
> >
> > > Index_size: 17 GB
> > > Num_writes 16245023
> > > Index definition: CREATE INDEX ix_filter_by_tree ON seg
> USING btree
> > > (full_path varchar_pattern_ops) WHERE (full_path IS NOT
> NULL)
> > >
> > >
> > >
> > > What is the real impact of a bloat index? If I reindex it,
> queries will
> > > be faster?
> > >
> > > Thanks
> > > Patrick
> >
> >
> >
> >
> > I ran the query before and after the reindex, and it seems it did not
> > help on performance.
> >
> > *The query I used:*
> >
> > explain analyze select * from seg where full_path = '/userfile/123';
>
> The table schema would be useful.
>

Why? If i just wanna know how bloat indexes work?



>
> >
> >
> > *Before reindex:*
> >
> > Index Scan using ix_filter_by_tree on seg  (cost=0.00..144.87
> > rows=215 width=8) (actual time=0.047..0.047 rows=1 loops=1)
> >   Index Cond: (full_path = '/userfile/123')
> > Total runtime: 0.059 ms
> > (3 rows)
> >
> >
> > *After reindex:*
> >
> > Index Scan using ix_filter_by_tree on seg  (cost=0.00..141.83
> > rows=220 width=8) (actual time=0.021..0.021 rows=1 loops=1)
> >   Index Cond: (full_path = '/userfile/123')
> > Total runtime: 0.036 ms
> > (3 rows)
>
> Not showing the complete explain analyze makes the above not all that
> enlightening.
>

I am showing the whole explain analyze mate.


>
> >
> >
> > Note that the '*/cost/*' is pretty much the same.
> >
> > *My question is:*
> > If I have a bloat index. Why do I need to reindex it if I got none
> > performance improvements?
>
> Because it is an indication that you may not have index bloat?
>

Queries for bloat indexes show that I do have bloat indexes. Also, it is
really simple to look.

\d tablename

The table is 18GB big and the index is 17GB big.. this clearly shows me
bloated index.


>
> Not sure a runtime of 0.036 to 0.036 ms over a 2TB table is symptomatic of
> a problem.
>
> Might be worth taking a look at:
>
> https://www.postgresql.org/docs/9.6/static/monitoring-
> stats.html#PG-STAT-ALL-TABLES-VIEW
>
> <adrian.kla...@aklaver.com>
>


Patrick.

Re: [GENERAL] bloat indexes - opinion

[Patrick B]

2017-02-22 11:11 GMT+13:00 Patrick B <patrickbake...@gmail.com>:

> 2017-02-22 10:59 GMT+13:00 Adrian Klaver <adrian.kla...@aklaver.com>:
>
>> 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_filter_by_tree
>> > Times_used: 1018082183
>> > Table_size: 18 GB -- wrong. The table is mostly on pg_toast table.
>> > Its real size is 2TB
>>
>> How do you know one number is right and the other is wrong?
>>
>
>
> 1. on that table (seg) i store binary data. It is impossible to have only
> 18GB of it.
> 2.
>
> SELECT schema_name,
>
>pg_size_pretty(sum(table_size)::bigint),
>
>(sum(table_size) / pg_database_size(current_database())) * 100
>
> FROM (
>
>   SELECT pg_catalog.pg_namespace.nspname as schema_name,
>
>  pg_relation_size(pg_catalog.pg_class.oid) as table_size
>
>   FROM   pg_catalog.pg_class
>
>  JOIN pg_catalog.pg_namespace ON relnamespace =
> pg_catalog.pg_namespace.oid
>
> ) t
>
> GROUP BY schema_name
>
> ORDER BY schema_name
>
>
> pg_toast 2706 GB 82.6211283887724086 <-- this belongs to the seg
> table.
>
>
>
>>
>> Have you looked at the functions here?:
>> https://www.postgresql.org/docs/9.6/static/functions-admin.h
>> tml#FUNCTIONS-ADMIN-DBOBJECT
>>
>> > Index_size: 17 GB
>> > Num_writes 16245023
>> > Index definition: CREATE INDEX ix_filter_by_tree ON seg USING btree
>> > (full_path varchar_pattern_ops) WHERE (full_path IS NOT NULL)
>> >
>> >
>> >
>> > What is the real impact of a bloat index? If I reindex it, queries will
>> > be faster?
>> >
>> > Thanks
>> > Patrick
>>
>
>

I ran the query before and after the reindex, and it seems it did not help
on performance.

*The query I used:*

explain analyze select * from seg where full_path = '/userfile/123';


*Before reindex:*

Index Scan using ix_filter_by_tree on seg  (cost=0.00..144.87 rows=215
width=8) (actual time=0.047..0.047 rows=1 loops=1)
  Index Cond: (full_path = '/userfile/123')
Total runtime: 0.059 ms
(3 rows)


*After reindex:*

Index Scan using ix_filter_by_tree on seg  (cost=0.00..141.83 rows=220
width=8) (actual time=0.021..0.021 rows=1 loops=1)
  Index Cond: (full_path = '/userfile/123')
Total runtime: 0.036 ms
(3 rows)


Note that the '*cost*' is pretty much the same.

*My question is:*
If I have a bloat index. Why do I need to reindex it if I got none
performance improvements?

Cheers
Patrick

Re: [GENERAL] bloat indexes - opinion

[Patrick B]

2017-02-22 10:59 GMT+13:00 Adrian Klaver <adrian.kla...@aklaver.com>:

> 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_filter_by_tree
> > Times_used: 1018082183
> > Table_size: 18 GB -- wrong. The table is mostly on pg_toast table.
> > Its real size is 2TB
>
> How do you know one number is right and the other is wrong?
>


1. on that table (seg) i store binary data. It is impossible to have only
18GB of it.
2.

SELECT schema_name,

   pg_size_pretty(sum(table_size)::bigint),

   (sum(table_size) / pg_database_size(current_database())) * 100

FROM (

  SELECT pg_catalog.pg_namespace.nspname as schema_name,

 pg_relation_size(pg_catalog.pg_class.oid) as table_size

  FROM   pg_catalog.pg_class

 JOIN pg_catalog.pg_namespace ON relnamespace =
pg_catalog.pg_namespace.oid

) t

GROUP BY schema_name

ORDER BY schema_name


pg_toast 2706 GB 82.6211283887724086 <-- this belongs to the seg table.



>
> Have you looked at the functions here?:
> https://www.postgresql.org/docs/9.6/static/functions-admin.
> html#FUNCTIONS-ADMIN-DBOBJECT
>
> > Index_size: 17 GB
> > Num_writes 16245023
> > Index definition: CREATE INDEX ix_filter_by_tree ON seg USING btree
> > (full_path varchar_pattern_ops) WHERE (full_path IS NOT NULL)
> >
> >
> >
> > What is the real impact of a bloat index? If I reindex it, queries will
> > be faster?
> >
> > Thanks
> > Patrick
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

[GENERAL] bloat indexes - opinion

[Patrick B]

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 INDEX ix_filter_by_tree ON seg USING btree
(full_path varchar_pattern_ops) WHERE (full_path IS NOT NULL)



What is the real impact of a bloat index? If I reindex it, queries will be
faster?

Thanks
Patrick

[GENERAL] updating dup row

[Patrick B]

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 the dup records
INSERT INTO test2_results

SELECT

item_id,

userid

count(*) as dup_count,

MAX(id) as recent_id

FROM

test2

GROUP BY

item_id,

userid

HAVING COUNT(*) > 1;


if test1.id_user_bill = test2.id, then
update test1.id_user_bill with test2_results.recent_id

I'm using PG 9.2

Thanks!
Patrick.

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

[Patrick B]

2017-02-16 14:57 GMT+13:00 Patrick B <patrickbake...@gmail.com>:
>
> 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 > PG 9.2
>
>- This is the server that I'm working on. When I ran the query here
>for the first time, it also took >10 seconds. And it is not a LOCK as no
>one was/is using this database server. (using explain analyze)
>- When I ran the query for the second time (using explain analyze), it
>took 1 second to run.
>- On the third time, it took < than 1 second.
>- This server I can reboot the machine/PG or stop/start Postgres
>Process.
>- I've already done: service postgresql stop;
>sync; echo 3 > /proc/sys/vm/drop_caches; service postgresql start
>
>
> I've made some changes to the query and would like to get its real runtime
> so I can compare and keep working if I need to.
>
> *The question is:*
>
> How can I clear the cache, to get a real good estimation of how much the
> query is taking to run?
>
> P.
>


BTW


  ->  Index Only Scan Backward using ix_geo_time_end_user on geo mg
 (cost=0.00..7369.78 rows=24149 width=8) (actual time=0.020..0.020 rows=0
loops=1)


one of the reasons the query is too expensive... the index has 6GB.

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

[Patrick B]

2017-02-16 14:08 GMT+13:00 Tom Lane <t...@sss.pgh.pa.us>:

> Patrick B <patrickbake...@gmail.com> 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 to run?
>
> TBH, I think you're probably obsessing over the wrong thing.  It's
> highly unlikely that cache effects would be sufficient to explain
> a 10-second runtime for a query that otherwise takes less than 1 msec.
> What seems more likely is that the query was waiting on a lock, or
> something else that created a non-cache-related bottleneck.
>
> Also, I think you're coming at things from completely the wrong direction
> if you believe that the worst-case, nothing-in-any-level-of-cache case
> is the "true" runtime.  Most people who are worried about performance
> spend a great deal of effort ensuring that that case doesn't happen to
> them in practice.  As an example, the first few queries in a fresh
> session will almost always run slower than later queries, because it
> takes some time to ramp up the new backend's local catalog caches to have
> all the useful data in them.  But the correct response to that observation
> is to try to make sure your sessions last awhile and execute many queries,
> not to decide that the uncached state is the "true" runtime.  It's only
> representative if you're intentionally shooting yourself in the foot.
>
> regards, tom lane
>


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 > PG 9.2

   - This is the server that I'm working on. When I ran the query here for
   the first time, it also took >10 seconds. And it is not a LOCK as no one
   was/is using this database server. (using explain analyze)
   - When I ran the query for the second time (using explain analyze), it
   took 1 second to run.
   - On the third time, it took < than 1 second.
   - This server I can reboot the machine/PG or stop/start Postgres Process.
   - I've already done: service postgresql stop;
   sync; echo 3 > /proc/sys/vm/drop_caches; service postgresql start


I've made some changes to the query and would like to get its real runtime
so I can compare and keep working if I need to.

*The question is:*

How can I clear the cache, to get a real good estimation of how much the
query is taking to run?

P.

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

[Patrick B]

2017-02-16 13:25 GMT+13:00 Steve Atkins <st...@blighty.com>:

>
> > On Feb 15, 2017, at 3:58 PM, Patrick B <patrickbake...@gmail.com> 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 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 id from test where id = 124;
> >
> > The runtime is 0.8ms.
>
> This doesn't make seem to make sense.
>
> "explain select ..." doesn't run the query. All it shows is the plan the
> planner chose and some estimates of the "cost" of different steps, with no
> time. Where are you getting 40ms from in this case?
>

>From my DB-VISUALIZER - it shows how long the query took.


>
> "explain analyze select ..." does run the query, along with some -
> potentially non-trivial - instrumentation to measure each step of the plan,
> so you can see whether the planner estimates are reasonable or wildly off.
>
>
> Well.. that is what's happening.

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 to run?

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

[Patrick B]

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 id from test where id = 124;


The runtime is 0.8ms.

Probably the statement is cached, right?


So, I do (To clear the cache):


   - service postgresql stop
   - echo 3 > /proc/sys/vm/drop_caches
   - service postgresql start

But, then, executing the statement again, it runs in 0.8 ms.

How can I clean the cache to get the REAL runtime for that statement?
Thanks
Patrick

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

[Patrick B]

2017-02-15 12:19 GMT+13:00 Tom Lane <t...@sss.pgh.pa.us>:

> Patrick B <patrickbake...@gmail.com> 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,name,description) VALUES (default,'test 1','testing
> > insert');
> > SELECT FROM test ORDER BY id DESC; -- I don't see the inserted row here
>
> Maybe you meant "SELECT * FROM test", or at least "SELECT id FROM test"?
> Because that row certainly should be visible here.
>
> Having said that, the above coding seems rather broken, because it's just
> assuming that the new row will have the highest ID in the table.  Even if
> that's true at the instant of insertion, you have a race condition:
> another transaction could insert and commit a new row with a higher ID
> between your INSERT and your SELECT.
>
> The usual solution for this problem in PG is RETURNING:
>
> INSERT INTO test (id,name,description)
>   VALUES (default,'test 1','testing insert')
>   RETURNING id;
>
>
Thanks guys!

RETURNING id - it's what i was looking for.

Thanks a lot!
Patrick





> That will get you the generated column's value reliably, and it avoids
> one query roundtrip besides.
>
> regards, tom lane
>

[GENERAL] get inserted id from transaction - PG 9.2

[Patrick B]

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 row here


COMMIT;


I only can see that inserted row if I do the select outside of this
transaction.

How could I get that ?

Thanks!
Patrick

Re: [GENERAL] Locks Postgres

[Patrick B]

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

[Patrick B]

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

[Patrick B]

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 connections. I'd check to be sure
> > that every PHP script you have is, indeed, using pg_pconnect and not
> > pg_connect. That missing "p" could be hard to spot. I'm assuming, of
> > course, that you are sure that your PHP script are the only things that
> can
> > connect - no scripts, backups, etc. are consuming connections.
>
> You can disable persistent connection feature of pg_pconnect by
> tweaking php.ini.
>
>
> @Steven, yes, my developer said we are using persistent connections.

However, he checked and he is using pg_connect instead of pg_pconnect.

Patrick

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

[Patrick B]

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 128GB and SSD 10K iops disks (Amazon EBS).


Can you guys please outlines me the steps to troubleshoot this?

Interesting is that I didn't see any IO/CPU limitation on my server.

I'm currently running a Postgres 9.2 - one master and one slave streaming
replication.


Thanks

Patrick

[GENERAL] Average - Pg 9.2

[Patrick B]

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://sqlfiddle.com/#!15/3289b/1

Thanks!
Patrick

[GENERAL] Custom type column index - Postgres 9.1

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

Re: [GENERAL] Question slow query

[Patrick B]

2017-01-12 16:48 GMT+13:00 Andreas Joseph Krogh <andr...@visena.com>:

> 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 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,note_1,note_2}'::n_type[]))
> Filter: (timezone('Etc/UTC'::text, 
> d_modified) >= '2016-08-07 23:12:34'::timestamp without time zone)
> Rows Removed by Filter: 13725231
>
>
>
> As you can see, 13.725.231 rows were removed by Filter..
>
> Should I create an index for this column?
>
>> d_modified  | timestamp with time zone | default
>> statement_timestamp()
>
> I tried but it didn't help... the query doesn't even hit the index.
>
>
>
> CREATE INDEX q ON test USING BTREE (d_modified);
>
>
>
> Am I missing something?
>
>
> Yes, you've not shown us:
> 1. The query
> 2. The schema
> 3. Complete EXPLAIN-output
>
>

Sorry about that.. just wanted to understand how to index a timestamp
column. I was able to do this way:

>
> CREATE INDEX CONCURRENTLY ON note (timezone('Etc/UTC'::text, d_date));


Cheers
Patrick

[GENERAL] Question slow query

[Patrick B]

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,note_1,note_2}'::n_type[]))
Filter: (timezone('Etc/UTC'::text,
d_modified) >= '2016-08-07 23:12:34'::timestamp without time zone)
Rows Removed by Filter: 13725231



As you can see, 13.725.231 rows were removed by Filter..

Should I create an index for this column?

> d_modified  | timestamp with time zone | default
> statement_timestamp()

I tried but it didn't help... the query doesn't even hit the index.


CREATE INDEX q ON test USING BTREE (d_modified);


Am I missing something?

Thanks!

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

[Patrick B]

2017-01-12 13:41 GMT+13:00 Adrian Klaver <adrian.kla...@aklaver.com>:

> On 01/11/2017 04:31 PM, Patrick B wrote:
>
>> 2017-01-12 13:23 GMT+13:00 Adrian Klaver <adrian.kla...@aklaver.com
>> <mailto:adrian.kla...@aklaver.com>>:
>>
>> 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 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: canceling statement due to statement timeout
>>
>> statement_timeout is 0 in both servers.
>>
>> However, on server1 I am able to run the query. Only on server2
>> that I
>> get that error.
>>
>> Why? If it is same DB???
>>
>>
>> It is not the same DB if it is on two different servers not
>> connected by replication. More to the point statement_timeout is a
>> client connection setting, so is the client you use to connect to
>> server2 the same as the one you use for server1?
>>
>> Is AWS being 'helpful' and setting a timeout?
>>
>> Is there anything in the log before the ERROR shown above that
>> indicates something is setting statement_timeout?
>>
>> <mailto:adrian.kla...@aklaver.com>
>>
>>
>> Same database, different database servers; server1 is the old Master
>> server and I'm using it to compare.
>>
>> It is not the client, because if I run the query manually using explain
>> analyze i get the error:
>>
>
> Well that is a client also.
>
> Are you sure there is not something in your AWS setup that is doing this?
>
>
>
>> live_db=> explain analyze
>>
>> SELECT DISTINCT id0
>> FROM
>> (SELECT
>>
>> [...]
>>
>> ERROR:  canceling statement due to statement timeout
>>
>>
>> just a remind that on server1 works, but on server2 it doesn't.
>>
>
> Server1 is not on AWS and server2 is, see above.
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>


the statement_timeout was settled to user level. The user I was using to
run the query had 10s statement_timeout. I changed it to 0 and the query
worked.

The query is taking 20s to run. I know it need to be improved and I will do
it.

I think it was working on server1 but not on server2, because as we are
using AWS there is the EBS latency that we didn't have before on slave1.

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

[Patrick B]

2017-01-12 13:23 GMT+13:00 Adrian Klaver <adrian.kla...@aklaver.com>:

> 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 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: canceling statement due to statement timeout
>>
>> statement_timeout is 0 in both servers.
>>
>> However, on server1 I am able to run the query. Only on server2 that I
>> get that error.
>>
>> Why? If it is same DB???
>>
>
> It is not the same DB if it is on two different servers not connected by
> replication. More to the point statement_timeout is a client connection
> setting, so is the client you use to connect to server2 the same as the one
> you use for server1?
>
> Is AWS being 'helpful' and setting a timeout?
>
> Is there anything in the log before the ERROR shown above that indicates
> something is setting statement_timeout?
>
> <adrian.kla...@aklaver.com>
>>
>
Same database, different database servers; server1 is the old Master server
and I'm using it to compare.

It is not the client, because if I run the query manually using explain
analyze i get the error:

live_db=> explain analyze

SELECT DISTINCT id0
> FROM
> (SELECT

[...]

ERROR:  canceling statement due to statement timeout


just a remind that on server1 works, but on server2 it doesn't.

[GENERAL] ERROR: canceling statement due to statement timeout

[Patrick B]

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: canceling statement due to statement timeout

statement_timeout is 0 in both servers.

However, on server1 I am able to run the query. Only on server2 that I get
that error.

Why? If it is same DB???

Patrick

Re: [GENERAL] Slow index scan - Pgsql 9.2

[Patrick B]

2017-01-11 4:05 GMT+13:00 Tomas Vondra <tomas.von...@2ndquadrant.com>:

> 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.
>>
>>
>> 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 you run it for a 2nd time?
>>
>>
>> No, it doesn't. I think it's because of cache?
>>
>>
>> I would think because of the NOT "deleted" clause. Which is
>> interesting, because that's a column which you conveniently didn't
>> include in the definition below.
>>
>>
>> My mistake.
>>
>>
>> Would an Index be sufficient to solve the problem?
>>
>>
> Not a separate index - the query probably would not benefit from two
> separate indexes. But you can amend the existing index, to allow index-only
> scans, i.e. creating an index like this:
>
>   CREATE INDEX ON (clientid, is_demo, deleted, id, job, job_share_mode)
>
> This will make the index larger, but it should allow index-only scans.
>
> The other thing you could try is partial index, i.e.
>
>   CREATE INDEX ON (clientid) WHERE NOT is_demo AND NOT deleted;
>
> You can also combine those approaches, but you'll have to include all
> columns into the index, even those in the index predicate:
>
>   CREATE INDEX ON (clientid, is_demo, deleted, id, job, job_share_mode)
>   WHERE NOT is_demo AND NOT deleted;
>
> I'd bet all of those will outperform the current plan.
>
> regards
>
> --
> Tomas Vondra  http://www.2ndQuadrant.com
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>


Thanks for the reply!

I decided to create a partial index for that query, as it is part of a much
bigger one and it is run at all the time.

Patrick

Re: [GENERAL] Slow index scan - Pgsql 9.2

[Patrick B]

>
> ​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 you run it for a 2nd time?


No, it doesn't. I think it's because of cache?


I would think because of the NOT "deleted" clause. Which is interesting,
> because that's a column which you conveniently didn't include in the
> definition below.


My mistake.


Would an Index be sufficient to solve the problem?

Patrick

[GENERAL] Slow index scan - Pgsql 9.2

[Patrick B]

Hi guys,

I've got the following Query:

WITH

   query_p AS (

   SELECT CAST(6667176 AS
BIGINT) AS client_id),




 clients AS (

   SELECT

   client.id
,client.job_share_mode

   FROM

   customers AS
client

   WHERE

   (client.clientid
= (SELECT qp.client_id FROM query_p AS qp))

   AND

   NOT
client.is_demo

   AND

   NOT
client.deleted

   )

Select qp.client_id, (SELECT COUNT(0) FROM customers AS c WHERE (c.clientid
= qp.client_id) AND NOT c.deleted) AS client_count

FROM query_p AS qp


*Explain Analyze:*

CTE Scan on "query_p" "qp"  (cost=0.01..1060.57 rows=1 width=8) (actual
time=4065.244..4065.246 rows=1 loops=1)

  CTE query_p

->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.003
rows=1 loops=1)

  SubPlan 2

->  Aggregate  (cost=1060.53..1060.54 rows=1 width=0) (actual
time=4065.229..4065.229 rows=1 loops=1)

  ->  Index Scan using "clientid_customers" on "customers" "c"
 (cost=0.00..1059.01 rows=607 width=0) (actual time=9.105..4063.728
rows=2513 loops=1)

Index Cond: ("clientid" = "qp"."client_id")

Filter: (NOT "deleted")

Rows Removed by Filter: 1068

Total runtime: 4075.753 ms



Why a search for "client_id" is so slow??


*Table customers:*

  Table "public.customers"

 Column |Type |
Modifiers

+-+-

 id | bigint  | not null default
"nextval"('"customers_seq"'::"regclass")

 clientid   | bigint  | not null default 0

 name_first | character varying(80)   | default
''::character varying

 name_last  | character varying(80)   | default
''::character varying

 company| character varying(255)  | default
''::character varying


*Index clientid_customers:*

CREATE INDEX

clientid_customers

ON

customers

(

"clientid"

);



Thanks!

Patrick

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

[Patrick B]

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 9.1, I need to manually copy the .history files
from pg_xlog on slave03 to pg_xlog on slave04 (I don't need to copy the wal
files as my archive_command does that).


When I promote slave03:

pg_ctl promote -D datadir

... it works fine!

Then, I restart postgres on slave04 with the new history file and the
message that I get is:

FATAL:  could not receive data from WAL stream: 00040001234
FATAL:  requested WAL segment has already been removed

Why does that happen? I can't find 00040001234 even on the
slave03(new master)! wal_keep_segments is 500.

Thanks
Patrick

Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

[Patrick B]

2016-12-15 14:54 GMT+13:00 Lucas Possamai <drum.lu...@gmail.com>:

>
>
> 2016-12-15 14:34 GMT+13:00 Adrian Klaver <adrian.kla...@aklaver.com>:
>
>> 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
>>
>>   execute '
>>
>>   COPY
>>
>>   (
>>
>>   SELECT
>>
>>   uuid,
>>
>>   clientid,
>>
>>   *
>>
>>   FROM
>>
>>   logging
>>
>>   WHERE
>>
>>   logtime
>>
>>   BETWEEN
>>
>>  date_start
>>
>>   AND
>>
>>  date_end
>>
>>   )
>>
>>   TO ''/var/lib/postgresql/'|| date_start ||'_logs.csv''';
>>
>> end
>>
>> $$ language 'plpgsql';
>>
>>
>> select l_extract('201611015', '201612015');
>>
>>
>>
>>
> select l_extract('201611015','201612015');
> ERROR:  column "date_start" does not exist
>
>
> Patrick
>
>


BETWEEN

''' || date_start || '''

AND

''' || date_end || '''


worked!

Thanks David.

Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

[Patrick B]

2016-12-15 14:00 GMT+13:00 David G. Johnston <david.g.johns...@gmail.com>:

> On Wed, Dec 14, 2016 at 5:12 PM, rob stone <floripa...@gmail.com> 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 <patrickbake...@gmail.com>
>> > wrote:
>> > > ERROR:  function logextract(integer, integer) does not exist
>> > > LINE 1: select logextract(20160901,20161001);
>> > >
>> >
>> > So change the constants you are passing into your function to text
>> > (i.e., surrounding them with single quotes) so it matches the new
>> > function signature.
>> >
>> > There exists an element of understanding the options you are being
>> > given and adapting if something basic like this is overlooked.
>> >
>> > David J.
>>
>>
>> 1) Have you run a \df+ and made sure the function has been created
>> correctly?
>>
>
> ​It was created originally using integer arguments - and thus was being
> called that way.  It was intentionally changed to use "text" arguments per
> a suggestion but without any recognition that the call site needed to
> change as well - hence the error.​  Running \df+ would give the expected
> output.  What could be a problem is if the original function wasn't dropped
> so while the text arg'd one was created the actual call would still
> reference the old int arg'd version and any changes would not appear to
> have been made.
>
>
>> 2) In your first post there is a single apostrophe after the execute
>> instruction. Can't see the closing apostrophe but then my eyesight is
>> not the best.
>>
>
> ​I'd recommend using the "format" function but last time I did that the
> person I way trying to help got mad...​
>
>
>> 3) I've always found it easier to TO_CHAR a date column when using it
>> for comparison purposes.
>>
>
> ​I'm not following this "use text" approach at all...​I get the logistics
> but PostgreSQL allows for comparison of date typed data...
>
> David J.
>
>

I've done:

1. Deleted all the functions;
2. Created a new function:

 CREATE or REPLACE FUNCTION l_extract(date_end text))

RETURNS void AS $$


DECLARE

date_start date := CURRENT_DATE;


begin

  execute '

  COPY

  (

  SELECT

  uuid,

  clientid,

  *

  FROM

  logging

  WHERE

  logtime

  BETWEEN

 ' || date_start || '

  AND

  ' || date_end || '

  )

  TO ''/var/lib/postgresql/'|| date_start ||'_logs.csv''';

end

$$ language 'plpgsql';


3. Calling the function:

select l_extract('20160901');

select l_extract('2016-09-01'); --> doesn't work either


4. Error:

ERROR:  operator does not exist: timestamp without time zone >= integer

LINE 13: BETWEEN

 ^

HINT:  No operator matches the given name and argument type(s). You might
need to add explicit type casts.

QUERY:

  COPY

  (

  SELECT

  uuid,

  clientid,

  *

  FROM

  logging

  WHERE

  logtime

  BETWEEN

2016-12-15

  AND

  20160901

  )

  TO '/var/lib/postgresql/2016-12-15_logs.csv'

CONTEXT:  PL/pgSQL function iknock_log_extract(text) line 7 at EXECUTE


5. \d+ logging:

log_time   | timestamp(3) without time zone


6. Query below works:

  SELECT

  uuid,

  clientid,

  *

  FROM

  logging

  WHERE

  logtime

  BETWEEN

 '2016-12-15'

  AND

  '20160901'


Still can't understand what's going on =\

Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

[Patrick B]

2016-12-15 10:40 GMT+13:00 Adrian Klaver <adrian.kla...@aklaver.com>:

> 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 does not exist: timestamp without time
>> zone >=
>> integer
>>
>> LINE 13: BETWEEN
>>
>>
>> The answer is above. Look at your original query at the top of the
>> post.
>>
>>
>>
>>  I presume this is wrong: _CREATE or REPLACE FUNCTION
>> logextract(date_start integer, date_end integer) _- But what
>> should I
>> use instead?
>>
>>
>> <mailto:adrian.kla...@aklaver.com>
>>
>>
>> You mean the error would be:
>> select logextract(201612015, 201612015);
>>
>> When it was supposed to be: select logextract(201611015, 201612015);???
>>
>> This is not the cause, because it was a mistake when I typed the email.
>> Even doing:
>>
>> select logextract(20161115,20161215);
>>
>>
>> I get same error.
>>
>
> You would. The error is:
>
> ERROR:  operator does not exist: timestamp without time zone >= integer
>
> Change this:
>
>
> BETWEEN
>
>  ' || date_start || '
>
>   AND
>
>   ' || date_end || '
>
> to
>
> BETWEEN
>
>date_start::text
>
> AND
>
>date_end::text
>
> Or change the argument types to text and then:
>
> BETWEEN
>
>date_start
>
> AND
>
>date_end
>
> In either case you will have your original query.
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com



I tried either changing the argument types:

logextract(date_start text, date_end text)


and also the Between:

 BETWEEN


   date_start::text


AND


   date_end::text


None of those worked:

ERROR:  function logextract(integer, integer) does not exist

LINE 1: select logextract(20160901,20161001);

Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

[Patrick B]

>
> 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 your original query at the top of the post.
>
>
>>
>>  I presume this is wrong: _CREATE or REPLACE FUNCTION
>> logextract(date_start integer, date_end integer) _- But what should I
>> use instead?
>>
>>
>> 
>>
>
You mean the error would be:
select logextract(201612015, 201612015);

When it was supposed to be: select logextract(201611015, 201612015);???

This is not the cause, because it was a mistake when I typed the email.
Even doing:

select logextract(20161115,20161215);

I get same error.

[GENERAL] Plpgsql - Custom fields Postgres 9.5

[Patrick B]

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

I always need to run this on the 15th of each month.
I was thinking about creating a PLPGSQL function and a Cron task, so this
task can be automated.

Also, the file must be saved with the date+.csv. Example:

CREATE or REPLACE FUNCTION logextract(date_start integer, date_end integer)

RETURNS void AS $$

begin

  execute '

  COPY

  (

  SELECT

  uuid,

  clientid),

  *

  FROM

  logging

  WHERE

  logtime

  BETWEEN

 ' || date_start || '

  AND

  ' || date_end || '

  )

  TO ''/var/lib/postgresql/'|| date_start ||'_logs.csv''';

end

$$ language 'plpgsql';



*Questions:*

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


 I presume this is wrong: *CREATE or REPLACE FUNCTION logextract(date_start
integer, date_end integer) *- But what should I use instead?


2. To call the function, I have to login to postgres and then run: select
logextract(201612015, 201612015);
How can I do it on cron? because the dates will be different every time.

Thanks
Patrick

Re: [GENERAL] WAL history files - Pgsql 9.2

[Patrick B]

2016-12-12 12:09 GMT+13:00 Patrick B <patrickbake...@gmail.com>:

> 2016-12-12 12:00 GMT+13:00 Venkata B Nagothi <nag1...@gmail.com>:
>
>>
>> On Mon, Dec 12, 2016 at 7:48 AM, Patrick B <patrickbake...@gmail.com>
>> wrote:
>>
>>> 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.
>>>
>>
>> Can you please explain the scenario you are referring to ? during
>> streaming replication ? or during standby promotion ?
>>
>>
>>
>
>
> Sure...
>
> I've got:
>
> > Master01 (sending wal_files to the slaves)
> > slave01 (streaming replication from master01 + wal_files)
> > slave02 (streaming replication from master01 + wal_files)
>
> I'll turn slave01 into a master; on recovery.conf:
>
> trigger_file = '/tmp/pg_failover_trigger';touch /tmp/pg_failover_trigger
>
>
> Then, the new scenario will be:
> > slave01 (*NEW MASTER*)
> > slave02 (streaming replication from slave01 + wal_files)
>
>
> Will slave02 be able to switch the timeline using wal_files provided by
> the new master?
> Thanks
>
>

No.. it didn't copy. i tested here. I had to manually copy the history file
from /var/lib/pgsql/9.2/data/pg_xlogs from the new master to the same
directory on the slaves.

Re: [GENERAL] WAL history files - Pgsql 9.2

[Patrick B]

2016-12-12 12:00 GMT+13:00 Venkata B Nagothi <nag1...@gmail.com>:

>
> On Mon, Dec 12, 2016 at 7:48 AM, Patrick B <patrickbake...@gmail.com>
> wrote:
>
>> 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.
>>
>
> Can you please explain the scenario you are referring to ? during
> streaming replication ? or during standby promotion ?
>
>
>


Sure...

I've got:

> Master01 (sending wal_files to the slaves)
> slave01 (streaming replication from master01 + wal_files)
> slave02 (streaming replication from master01 + wal_files)

I'll turn slave01 into a master; on recovery.conf:

trigger_file = '/tmp/pg_failover_trigger';touch /tmp/pg_failover_trigger


Then, the new scenario will be:
> slave01 (*NEW MASTER*)
> slave02 (streaming replication from slave01 + wal_files)


Will slave02 be able to switch the timeline using wal_files provided by the
new master?
Thanks

[GENERAL] WAL history files - Pgsql 9.2

[Patrick B]

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

[Patrick B]

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 + wal_files)
slave04 (Streaming Replication from slave02)

*Running this query on slave02:*

select now() - pg_last_xact_replay_timestamp() AS replication_delay;
 replication_delay
---
 00:00:00.802012
(1 row)

*Same query on slave03 and slave04:*

select now() - pg_last_xact_replay_timestamp() AS replication_delay;
 replication_delay
---
 00:56:53.639516
(1 row)


*slave02:*

SELECT client_hostname, client_addr,
pg_xlog_location_diff(pg_stat_replication.sent_location,
pg_stat_replication.replay_location) AS byte_lag FROM pg_stat_replication;
 client_hostname |  client_addr  | byte_lag
-+---+--

 | slave03  |  2097400

 | slave04 |  3803888

(2 rows)


Why is that delay that big? Is it because networking issue? I tried to find
out what the cause is, but couldn't find anything.

SCP and FTP (big files) between those servers are really fast, +1.0MB/s.
I'm using PostgreSQL 9.2.14

Thanks!
Patrick.

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

[Patrick B]

2016-12-01 14:15 GMT+13:00 David G. Johnston <david.g.johns...@gmail.com>:

> On Wed, Nov 30, 2016 at 6:05 PM, Patrick B <patrickbake...@gmail.com>
> wrote:
>
>> https://www.postgresql.org/docs/9.2/static/runtime-config-
>> replication.html
>>
>> wal_keep_segments is the parameter responsible for streaming replication
>> be able to recover itself without using wal_files, is that right?
>>
>
> [...] ​without using wal_files[sic] [from an external archive location]
>
> David J.
> ​
>
>

Yep David! Thanks for correcting me.

Got it now. Thanks everyone.

one more question...  I'm working with wal_keep_segments = 1000 - Would
that in your opinion be a safe number?

Yes. I got plenty of free space and I'm using pg_xlogs in a different
partition on the master.

Patrick

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

[Patrick B]

2016-11-29 23:59 GMT+13:00 Patrick B <patrickbake...@gmail.com>:

>
>
> 2016-11-29 16:36 GMT+13:00 David G. Johnston <david.g.johns...@gmail.com>:
>
>> On Mon, Nov 28, 2016 at 8:22 PM, Patrick B <patrickbake...@gmail.com>
>> wrote:
>>
>>>
>>> Ho
>>> ​[w]
>>>  is that even possible?? I don't understand!
>>>
>>>
>> ​https://www.postgresql.org/docs/9.2/static/warm-standby.html
>> """​
>>
>> If you use streaming replication without file-based continuous archiving,
>> you have to set wal_keep_segments in the master to a value high enough to
>> ensure that old WAL segments are not recycled too early, while the standby
>> might still need them to catch up. If the standby falls behind too much, it
>> needs to be reinitialized from a new base backup. If you set up a WAL
>> archive that's accessible from the standby, wal_keep_segments is not
>> required as the standby can always use the archive to catch up.
>> ​"""
>>
>> Basically you did just that when you destroyed the archive.  Apparently
>> the master doesn't churn through WAL quickly enough to have had to discard
>> the segments from the prior two hours.
>>
>> David J.
>> ​
>>
>>
>
> That was really helpful! Thanks David!
>
> Patrick
>
>



Hey guys,

https://www.postgresql.org/docs/9.2/static/runtime-config-replication.html

wal_keep_segments is the parameter responsible for streaming replication be
able to recover itself without using wal_files, is that right?

Re: [GENERAL] Monitoring Replication - Postgres 9.2

[Patrick B]

2016-12-01 5:54 GMT+13:00 Melvin Davidson <melvin6...@gmail.com>:

>
> On Wed, Nov 30, 2016 at 8:04 AM, Cachique <cachi...@gmail.com> wrote:
>
>> You can try pg_cron.
>> https://github.com/citusdata/pg_cron
>> "pg_cron is a simple cron-based job scheduler for PostgreSQL (9.5 or
>> higher) that runs inside 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, 2016 at 10:40 PM, Patrick B <patrickbake...@gmail.com>
>> wrote:
>>
>>>
>>>
>>> 2016-11-30 14:21 GMT+13:00 John R Pierce <pie...@hogranch.com>:
>>>
>>>> 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,
>>>> have a script (perl?  python?  whatever your favorite poison is with
>>>> database access) that once a minute executes those two queries (you'll need
>>>> two database connections since only the slave knows how far behind it is),
>>>> and inserts the data into your table.
>>>>
>>>>
>>>> --
>>>> john r pierce, recycling bits in santa cruz
>>>>
>>>>
>>>
>>> Can't I do it on the DB size? Using a trigger maybe? instead of using
>>> Cron?
>>>
>>> Patrick
>>>
>>>
>>
> >The OP wants to run queries on the master and the slave, and combine them.
>
> Another option, although a bit convoluted, would be to extract the data to
> a csv file, scp it to destination server, and then copy in from there
> eg:
> Contents of bash script
> ===
> #!/bin/bash
> psql -U postgres
> \t
> \f c
> \o results.csv
> select now() as time_pk,
>client_addr,
>state,
>sent_location,
>write_location,
>flush_location,
>replay_location,
>sync_priority
>   from pg_stat_replication;
> \q
>
> scp results.csv destination_server/tmp/.
>
> psql -U postgres -h destination_server/tmp/.
> COPY data_table
> FROM '\tmp\results.csv'
> WITH csv;
>  \q
>
> --
>


I see...


but there is queries like this:

select now() - pg_last_xact_replay_timestamp() AS replication_delay;


that need to be ran into a slave.. how can I insert that data into a table
on the slave?

Patrick

Re: [GENERAL] Monitoring Replication - Postgres 9.2

[Patrick B]

2016-11-30 14:21 GMT+13:00 John R Pierce <pie...@hogranch.com>:

> 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, have a
> script (perl?  python?  whatever your favorite poison is with database
> access) that once a minute executes those two queries (you'll need two
> database connections since only the slave knows how far behind it is), and
> inserts the data into your table.
>
>
> --
> john r pierce, recycling bits in santa cruz
>
>

Can't I do it on the DB size? Using a trigger maybe? instead of using Cron?

Patrick

Re: [GENERAL] Monitoring Replication - Postgres 9.2

[Patrick B]

2016-11-30 14:02 GMT+13:00 John R Pierce <pie...@hogranch.com>:

> 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 from pg_stat_replication;
>
> *On slave:*
> select now() - pg_last_xact_replay_timestamp() AS replication_delay;
>
> Can I create a table to store that data?
>
>
> sure, why not ? do you want this table to just have one row with the
> last value you stored?  or do you want to store a history with timestamp ?
>
> I also need the data is constantly put into this table. How would be the
> best way to do it?
>
>
> um, that data changes continuously, what do you mean, 'constantly'  ?   if
> you mean once a minute or something, use a script that samples the data and
> stores it in your table, and waits a minute, then repeats.if you mean
> literally continously, why not just query the data as you have, thats the
> 'live' value ...   you oculd use a view, I suppose.
>
>
> --
> john r pierce, recycling bits in santa cruz
>
>
Yep.. once a minute or so. And yes, I need to store a history with
timestamp.

Any idea? :)

Thanks!

[GENERAL] Monitoring Replication - Postgres 9.2

[Patrick B]

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 I create a table to store that data?
I also need the data is constantly put into this table. How would be the
best way to do it?

Cheers
Patrick

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

[Patrick B]

2016-11-29 16:36 GMT+13:00 David G. Johnston <david.g.johns...@gmail.com>:

> On Mon, Nov 28, 2016 at 8:22 PM, Patrick B <patrickbake...@gmail.com>
> wrote:
>
>>
>> Ho
>> ​[w]
>>  is that even possible?? I don't understand!
>>
>>
> ​https://www.postgresql.org/docs/9.2/static/warm-standby.html
> """​
>
> If you use streaming replication without file-based continuous archiving,
> you have to set wal_keep_segments in the master to a value high enough to
> ensure that old WAL segments are not recycled too early, while the standby
> might still need them to catch up. If the standby falls behind too much, it
> needs to be reinitialized from a new base backup. If you set up a WAL
> archive that's accessible from the standby, wal_keep_segments is not
> required as the standby can always use the archive to catch up.
> ​"""
>
> Basically you did just that when you destroyed the archive.  Apparently
> the master doesn't churn through WAL quickly enough to have had to discard
> the segments from the prior two hours.
>
> David J.
> ​
>
>

That was really helpful! Thanks David!

Patrick

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

[Patrick B]

2016-11-29 15:21 GMT+13:00 David Steele <da...@pgmasters.net>:

> 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 file or
> > director*y
> >
> > In my head, it's saying that it was unable to recover that file and,
> > because of that, there is missing data.
> > Even if the server is able to connect to tbe master via streaming
> > replication, there might be missing data. There might be data that is
> > into master but not into slave.
> >
> > Am I wrong? If so, I don't understand why are the wal_files for, then.
>
> This is normal as far as it goes.  Postgres keeps checking for the WAL
> file that it needs to become consistent and cp keeps throwing errors
> when it can't find the file (yet).
>
> This simply demonstrates how unsuitable cp is as a recovery command.  I
> recommend you look at doing backup/archive with a professional tool such
> as pgBackRest (http://www.pgbackrest.org) or Barman
> (http://www.pgbarman.org/).
>
>
> Thanks for all the replies guys.

Related to the "cp" - That's fine. I'm just doing this in a test
environment. On my prod servers I use wal-e(aws s3) and bash
script(locally) to do this work.

I've done some tests, that only gave me more questions. I'll put everything
I done here and hopefully you guys will be able to help me to understand it.

1. Set up a pg-test-01 machine on Ubuntu 16 Server
2. installed Postgres 9.2 + contribs + libs  + etc
3. Configured postgres to be a streaming replication slave (postgresql.conf
and recovery.conf)
4. Configured my masterdb01 server to ship wal_files to the pg-test-01
server
5. Made a pg_basebackup on my master DB server (DB is 1TB big)
6. Recovered the basebackup on the pg-test-01 machine
7. Postgres started to recover the wal_files and then streaming replication
worked:

LOG:  streaming replication successfully connected to primary


8. Stopped postgres on the pg-test-01 server for 10 minutes
9. Started postgres on the pg-test-01 to see if the wal_files would be
recovered

LOG:  restored log file "00021B2F004F" from archive

LOG:  restored log file "00021B2F0050" from archive

LOG:  restored log file "00021B2F0051" from archive

LOG:  restored log file "00021B2F0052" from archive


10. Removed all the wal_files from pg-test-01 (No wal_files are being
shipped to this server anymore)
11. Stopped postgres for 2 hours
12. Started postgres as streaming replication

cp: cannot stat 'archive/00022B3600BB': No such file or
> directory
> cp: cannot stat 'archive/00022B3600BC': No such file or
> directory
> cp: cannot stat 'archive/00022B3600BD': No such file or
> directory
> LOG:  streaming replication successfully connected to primary



Postgres was stopped for 2h. How can postgres connect using streaming
replication, if no wal_files were in there???

Hos is that even possible?? I don't understand!

Thanks
Patrick

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

[Patrick B]

2016-11-23 16:18 GMT+13:00 Venkata B Nagothi <nag1...@gmail.com>:

>
> On Wed, Nov 23, 2016 at 1:59 PM, Patrick B <patrickbake...@gmail.com>
> wrote:
>
>>
>>
>> 2016-11-23 15:55 GMT+13:00 Venkata B Nagothi <nag1...@gmail.com>:
>>
>>>
>>>
>>> On Wed, Nov 23, 2016 at 1:03 PM, Patrick B <patrickbake...@gmail.com>
>>> wrote:
>>>
>>>> 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 minutes, start postgres again,
>>>> watch it recovering from wal_files, once it's done see the streaming
>>>> replication start working again.
>>>>
>>>> 1 - Stop postgres on slave02(streaming replication + wal_files)
>>>> 2 - Wait for 5 minutes
>>>> 3 - Start postgres - The goal here is to tail the logs to see if the
>>>> wal_files are being successfully recovered
>>>>
>>>> However, when doing step3 I get these messages:
>>>>
>>>> cp: cannot stat '/walfiles/00021AF800A4': No such file or
>>>> directory
>>>>
>>>> cp: cannot stat '/walfiles/00021AF800A5': No such file or
>>>> directory
>>>>
>>>> cp: cannot stat '/walfiles/00021AF800A6': No such file or
>>>> directory
>>>> LOG:  consistent recovery state reached at 1AF8/AB629F90
>>>> LOG:  database system is ready to accept read only connections
>>>> LOG:  streaming replication successfully connected to primary
>>>>
>>>>
>>>>
>>>> still on slave01: *Sometimes the log_delay time is bigger.. sometimes
>>>> is lower*
>>>>
>>>> SELECT CASE WHEN pg_last_xlog_receive_location() =
>>>> pg_last_xlog_replay_location() THEN 0 ELSE EXTRACT (EPOCH FROM now() -
>>>> pg_last_xact_replay_timestamp()) END AS log_delay;
>>>>
>>>>  log_delay
>>>>
>>>> ---
>>>>
>>>>   0.386863
>>>>
>>>>
>>>>
>>>> On master01:
>>>>
>>>> select * from pg_current_xlog_location();
>>>>
>>>>  pg_current_xlog_location
>>>>
>>>> --
>>>>
>>>>  1AF8/D3F47A80
>>>>
>>>>
>>>>
>>>> *QUESTION:*
>>>>
>>>> So.. I just wanna understand what's the risk of those errors... what's
>>>> happening?
>>>> *cp: cannot stat '/walfiles/00021AF800A5': No such file or
>>>> director*y - Means it didn't find the file. However, the file exists
>>>> on the Master, but it didn't start shipping yet. What are the consequences
>>>> of that?
>>>>
>>>
>>> That is just saying that the slave cannot find the WAL file. That should
>>> not be of big importance. Eventually, that will vanish when the log file
>>> gets shipped from the master. Also "cp: cannot stat." errors have been been
>>> fixed in 9.3 i believe.
>>>
>>
>> Hi Venkata !
>>
>> Yeah that's fine.. the streaming replication is already working fine.
>>
>> But, as it didn't find/recover some of the wal_files, doesn't that mean
>> that the DB isn't up-to-date?
>>
>
> Not necessarily. Standby periodically checks if the WAL file it is looking
> for is available at restore_command location and generates that message if
> the file is not available. These messages are not of any harm.
>
> Below link might help you :
>
> https://www.postgresql.org/message-id/4DDC9515.203%40enterprisedb.com
>
>

hmm.. I really don't get it.



If I get messages like:

*cp: cannot stat '/walfiles/00021AF800A5': No such file or
director*y

In my head, it's saying that it was unable to recover that file and,
because of that, there is missing data.
Even if the server is able to connect to tbe master via streaming
replication, there might be missing data. There might be data that is into
master but not into slave.

Am I wrong? If so, I don't understand why are the wal_files for, then.


Patrick

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

[Patrick B]

2016-11-23 15:55 GMT+13:00 Venkata B Nagothi <nag1...@gmail.com>:

>
>
> On Wed, Nov 23, 2016 at 1:03 PM, Patrick B <patrickbake...@gmail.com>
> wrote:
>
>> 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 minutes, start postgres again,
>> watch it recovering from wal_files, once it's done see the streaming
>> replication start working again.
>>
>> 1 - Stop postgres on slave02(streaming replication + wal_files)
>> 2 - Wait for 5 minutes
>> 3 - Start postgres - The goal here is to tail the logs to see if the
>> wal_files are being successfully recovered
>>
>> However, when doing step3 I get these messages:
>>
>> cp: cannot stat '/walfiles/00021AF800A4': No such file or
>> directory
>>
>> cp: cannot stat '/walfiles/00021AF800A5': No such file or
>> directory
>>
>> cp: cannot stat '/walfiles/00021AF800A6': No such file or
>> directory
>> LOG:  consistent recovery state reached at 1AF8/AB629F90
>> LOG:  database system is ready to accept read only connections
>> LOG:  streaming replication successfully connected to primary
>>
>>
>>
>> still on slave01: *Sometimes the log_delay time is bigger.. sometimes is
>> lower*
>>
>> SELECT CASE WHEN pg_last_xlog_receive_location() =
>> pg_last_xlog_replay_location() THEN 0 ELSE EXTRACT (EPOCH FROM now() -
>> pg_last_xact_replay_timestamp()) END AS log_delay;
>>
>>  log_delay
>>
>> ---
>>
>>   0.386863
>>
>>
>>
>> On master01:
>>
>> select * from pg_current_xlog_location();
>>
>>  pg_current_xlog_location
>>
>> --
>>
>>  1AF8/D3F47A80
>>
>>
>>
>> *QUESTION:*
>>
>> So.. I just wanna understand what's the risk of those errors... what's
>> happening?
>> *cp: cannot stat '/walfiles/00021AF800A5': No such file or
>> director*y - Means it didn't find the file. However, the file exists on
>> the Master, but it didn't start shipping yet. What are the consequences of
>> that?
>>
>
> That is just saying that the slave cannot find the WAL file. That should
> not be of big importance. Eventually, that will vanish when the log file
> gets shipped from the master. Also "cp: cannot stat." errors have been been
> fixed in 9.3 i believe.
>

Hi Venkata !

Yeah that's fine.. the streaming replication is already working fine.

But, as it didn't find/recover some of the wal_files, doesn't that mean
that the DB isn't up-to-date?
Otherwise what's the purpose of the wal_files if not be responsible to
contain the essential data to the DB be up-to-date?

Thanks!

[GENERAL] Wal files - Question | Postgres 9.2

[Patrick B]

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 minutes, start postgres again,
watch it recovering from wal_files, once it's done see the streaming
replication start working again.

1 - Stop postgres on slave02(streaming replication + wal_files)
2 - Wait for 5 minutes
3 - Start postgres - The goal here is to tail the logs to see if the
wal_files are being successfully recovered

However, when doing step3 I get these messages:

cp: cannot stat '/walfiles/00021AF800A4': No such file or
directory

cp: cannot stat '/walfiles/00021AF800A5': No such file or
directory

cp: cannot stat '/walfiles/00021AF800A6': No such file or
directory
LOG:  consistent recovery state reached at 1AF8/AB629F90
LOG:  database system is ready to accept read only connections
LOG:  streaming replication successfully connected to primary



still on slave01: *Sometimes the log_delay time is bigger.. sometimes is
lower*

SELECT CASE WHEN pg_last_xlog_receive_location() =
pg_last_xlog_replay_location() THEN 0 ELSE EXTRACT (EPOCH FROM now() -
pg_last_xact_replay_timestamp()) END AS log_delay;

 log_delay

---

  0.386863



On master01:

select * from pg_current_xlog_location();

 pg_current_xlog_location

--

 1AF8/D3F47A80



*QUESTION:*

So.. I just wanna understand what's the risk of those errors... what's
happening?
*cp: cannot stat '/walfiles/00021AF800A5': No such file or
director*y - Means it didn't find the file. However, the file exists on the
Master, but it didn't start shipping yet. What are the consequences of that?

Cheers
Patrick

Re: [GENERAL] Check integrity between servers

[Patrick B]

2016-11-17 12:19 GMT+13:00 Patrick B <patrickbake...@gmail.com>:

> 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 Postgres 9.2
> Cheers
>



As far as I could see, checksum is only allowed on Postgres 9.3?
https://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.3#Data_Checksums

So can't use that! Any other ideas?

[GENERAL] Check integrity between servers

[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 not 100%.

How could I check the data between both DB?

I'm using Postgres 9.2
Cheers

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

[Patrick B]

2016-11-14 15:33 GMT+13:00 Venkata B Nagothi <nag1...@gmail.com>:

>
> On Mon, Nov 14, 2016 at 1:22 PM, Patrick B <patrickbake...@gmail.com>
> wrote:
>
>> 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 master:*
>>
>> select * from pg_current_xlog_location();
>>
>>  pg_current_xlog_location
>>
>> --
>>
>>  1A7C/14AEB2C0
>>
>> (1 row)
>>
>>
>> *On the slaves:*
>>
>> ls -ltr /var/lib/pgsql/archive/
>>
>> -rw--- 1 postgres postgres 16777216 Nov 14 01:21
>> 00021A7A00F9
>>
>>
>> As you can see, the last wal_files on the slaves isn't the
>> 00021A7C* ones. I think the SSH delivery is being delayed. Not sure
>> why tho.
>> How can I see how many files are behind?
>>
>
>
> You can identify the file name by using the function pg_xlogfile_name().
>
> "select pg_xlogfile_name('1A7C/14AEB2C0');"
>
>
>
>
Thanks a lot!

[GENERAL] Wal files being delayed - Pgsql 9.2

[Patrick B]

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 master:*

select * from pg_current_xlog_location();

 pg_current_xlog_location

--

 1A7C/14AEB2C0

(1 row)


*On the slaves:*

ls -ltr /var/lib/pgsql/archive/

-rw--- 1 postgres postgres 16777216 Nov 14 01:21
00021A7A00F9


As you can see, the last wal_files on the slaves isn't the
00021A7C* ones. I think the SSH delivery is being delayed. Not sure
why tho.
How can I see how many files are behind?

Cheers
Patrick.

[GENERAL] PLPGSQL returning number of rows

[Patrick B]

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)

RETURNS integer AS $$


declare

row record;

account_id integer;


BEGIN


FOR row IN EXECUTE '

SELECT

t1.id

FROM

public.table2 t2

JOIN

public.table1 t1 ON t2.id = t1.id

WHERE

t2.account_id = ' || account_id || ''

LOOP


DELETE FROM public.table1 WHERE id IN

(

SELECT

id

FROM

public.table1 t1

WHERE

t1.id = row.id

);


DELETE FROM public.table2 WHERE billable_id IN

(

SELECT

billable_id

FROM

public.table2 t1

WHERE

t1.id = row.id

);



END LOOP;

END


$$ language 'plpgsql';



Cheers

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

[Patrick B]

2016-11-02 8:43 GMT+13:00 Patrick B <patrickbake...@gmail.com>:

>
>
> 2016-11-02 2:55 GMT+13:00 Scott Marlowe <scott.marl...@gmail.com>:
>
>> On Mon, Oct 31, 2016 at 8:01 PM, Patrick B <patrickbake...@gmail.com>
>> wrote:
>> > 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
>>
>> You can try using pg_basebackup to get the replica setup. In 9.2 you
>> gotta make your own recovery.conf, but you already know how to do
>> that. Way easier than trying to rsync by hand etc.
>>
>
>
> I did a pg_basebackup!
>


Hi guys,

I was able to fix it doing:

recovery.conf:

> restore_command = 'cp /var/lib/pgsql/archive/%f %p'
> recovery_target_timeline = 'latest'
> standby_mode = off
> trigger_file = '/tmp/pg_failover_trigger'


Then, I got the error:

> FATAL:  archive file "000219D500EC" has wrong size: 0 instead
> of 16777216


To fix it:

rm 000219D500EC



Restart postgres and then:

>  LOG:  archive recovery complete
> LOG:  database system is ready to accept connections

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

[Patrick B]

2016-11-02 2:55 GMT+13:00 Scott Marlowe <scott.marl...@gmail.com>:

> On Mon, Oct 31, 2016 at 8:01 PM, Patrick B <patrickbake...@gmail.com>
> wrote:
> > 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
>
> You can try using pg_basebackup to get the replica setup. In 9.2 you
> gotta make your own recovery.conf, but you already know how to do
> that. Way easier than trying to rsync by hand etc.
>


I did a pg_basebackup!

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

[Patrick B]

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

[Patrick B]

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 <patrickbake...@gmail.com>:

>
>
> 2016-11-01 10:33 GMT+13:00 David G. Johnston <david.g.johns...@gmail.com>:
>
>> On Mon, Oct 31, 2016 at 1:46 PM, Patrick B <patrickbake...@gmail.com>
>> 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 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 basebackup
>>> - Created recovery.conf:
>>>
>>> restore_command = 'cp /var/lib/pgsql/wal_archive/%f %p'
>>>
>>> recovery_target_timeline = 'latest'
>>>
>>> standby_mode = off
>>>
>>> trigger_file = '/tmp/pg_failover_trigger'
>>>
>>> - touch /tmp/pg_failover_trigger
>>> - service postgresql start
>>>
>>> And then postgres starts recovering the wal_files. But I don't want
>>> that.. as I don't need a up-to-date
>>>
>>> Is the wal_files required anyway?
>>>
>>>
>> ​"...has a basebackup from the master" - the answer to your question
>> depends greatly on the detail behind that sentence.
>>
>> IIRC, unless you know that the data directory is consistent - because the
>> database was offline at the time of the backup - at least some WAL will
>> probably be required to bring the inconsistent backup data directory to a
>> known good state (i.e., post-checkpoint).
>>
>> David J.
>> ​
>> ​
>>
>>
>
>
> I see...
>
>
> as I'm recovering a slave and then turning it into a master, that's why
> the wal_files are required.
>
> Thanks!
> Patrick
>

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

[Patrick B]

2016-11-01 10:33 GMT+13:00 David G. Johnston <david.g.johns...@gmail.com>:

> On Mon, Oct 31, 2016 at 1:46 PM, Patrick B <patrickbake...@gmail.com>
> 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 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 basebackup
>> - Created recovery.conf:
>>
>> restore_command = 'cp /var/lib/pgsql/wal_archive/%f %p'
>>
>> recovery_target_timeline = 'latest'
>>
>> standby_mode = off
>>
>> trigger_file = '/tmp/pg_failover_trigger'
>>
>> - touch /tmp/pg_failover_trigger
>> - service postgresql start
>>
>> And then postgres starts recovering the wal_files. But I don't want
>> that.. as I don't need a up-to-date
>>
>> Is the wal_files required anyway?
>>
>>
> ​"...has a basebackup from the master" - the answer to your question
> depends greatly on the detail behind that sentence.
>
> IIRC, unless you know that the data directory is consistent - because the
> database was offline at the time of the backup - at least some WAL will
> probably be required to bring the inconsistent backup data directory to a
> known good state (i.e., post-checkpoint).
>
> David J.
> ​
> ​
>
>


I see...


as I'm recovering a slave and then turning it into a master, that's why the
wal_files are required.

Thanks!
Patrick

[GENERAL] Turning slave into a master - PostgreSQL 9.2

[Patrick B]

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 basebackup
- Created recovery.conf:

restore_command = 'cp /var/lib/pgsql/wal_archive/%f %p'

recovery_target_timeline = 'latest'

standby_mode = off

trigger_file = '/tmp/pg_failover_trigger'

- touch /tmp/pg_failover_trigger
- service postgresql start

And then postgres starts recovering the wal_files. But I don't want that..
as I don't need a up-to-date

Is the wal_files required anyway?
Patrick

Re: [GENERAL] Checking Postgres Streaming replication delay

[Patrick B]

2016-10-31 15:54 GMT+13:00 Venkata B Nagothi <nag1...@gmail.com>:

>
> On Mon, Oct 31, 2016 at 11:57 AM, Patrick B <patrickbake...@gmail.com>
> 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.
>>
>> SELECT
>>> pg_last_xlog_receive_location() receive,
>>> pg_last_xlog_replay_location() replay,
>>> (
>>> extract(epoch FROM now()) -
>>> extract(epoch FROM pg_last_xact_replay_timestamp())
>>> )::int lag;
>>
>>
>> In your opinion, is that right?
>>
>> Yes, thats right.
>
> Regards,
>
> Venkata B N
> Database Consultant
>
>

Thanks

[GENERAL] Checking Postgres Streaming replication delay

[Patrick B]

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_timestamp())
> )::int lag;


In your opinion, is that right?


Cheers

Patrick

Re: [GENERAL] pg_sample

[Patrick B]

2016-10-19 13:39 GMT+13:00 Michael Paquier <michael.paqu...@gmail.com>:

> On Wed, Oct 19, 2016 at 9:24 AM, Patrick B <patrickbake...@gmail.com>
> 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
> > database.
> >
> > I'm using pg_sample to do that, but unfortunately it doesn't work well.
> > It doesn't get the first 100 rows. It gets random 100 rows.
>
> Why aren't 100 random rows enough to fulfill what you are looking for?
> What you are trying here is to test the server with some sample data,
> no? In this case, having the first 100 rows, or a set of random ones
> should not matter much (never tried pg_sample to be honest).
> --
> Michael
>


Actually it does matter because there is some essential data that has to be
in there so the code can work.

[GENERAL] pg_sample

[Patrick B]

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 it doesn't work well.
It doesn't get the first 100 rows. It gets random 100 rows.

Do you guys have any idea how could I do this?
Thanks
Patrick

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

[Patrick B]

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

[Patrick B]

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

[Patrick B]

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 amazon dms ?
> >
> > slony requires configuring replication on each table.  if the database
> has a
> > large complex schema this could take considerable setup effort.
> >
>
> Not really. As of slony 2.2 you can use a regular expression to add
> tables or sequences.
>
> i.e.:
>
> SET ADD TABLE (
> SET ID=1,
> TABLES='public\\.*'
> );
>
> repeat for sequences.  Two commands. I don't consider that considerable
> effort.
> --
> To understand recursion, one must first understand recursion.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>


https://wiki.postgresql.org/wiki/Slony

The main drawback to Slony-I even as a replication system is the *complexity
of its setup and administration*. The design of the system, with the
database itself being used for queueing row updates, also significantly
increases the amount of data writing and I/O done by the DBMS.
Also, since Slony-I is asynchronous master-slave, all writes have to be
segregated to the master. Additionally, there is a noticeable lag (1-3
seconds) between the master and the slaves which may cause users to have an
inconsistent view of the data.



So, Slony won't be used by us.